I'm using Visual Studio for Mac 2017 on macOS Sierra with Microsoft EntityframeworkCore and I'm trying to connect to a remote MS SQL Server. If I start the project in Visual Studio on my Windows machine, everything works fine.
The lightweight, cross-platform offering combines functionality found in tools like SQL Server Management Studio (SSMS) and the Visual Studio Code editor in order to help data developers and operations pros work with SQL Server, Azure SQL Database and Azure SQL Data Warehouse on Windows, Mac or Linux machines. You should now have SQL Server running on your Mac, ready for action! Connect to your SQL Server running in Docker using sql-cli. Use the mssql Visual Studio Code extension to connect.
But the same project on my Mac tells me 'A call to SSPI failed' when I try to get data from the SQL Server. I can connect to the SQL Server using the 'Oracle SQL Developer' tool on my Mac.
So the issue is somewhere in Visual Studio. The connection string: Server=MySQLServer.domain.de;Database=MyDatabase;User Id=sa;Password=.; Any ideas? Thanks a lot.
Dogs and cats living together, mass hysteria! Update Oct 3, 2017 This article has been updated to reflect changes in the latest release of SQL Server for Linux. The 2016 Microsoft Connect; event included a lot of interesting announcements. The one that really got my attention was. My first question, of course: Can I run this on my Mac? The answer (and an increasingly common answer, I might add) is.
Here are the steps that worked for me. Install and configure Docker If you don't already have Docker installed, you'll need to. Next step, you'll need to increase Docker's available memory to 4GB or more. Docker - Preferences.
Increase Memory to at least 4GB. Click Apply & Restart Get the Docker image Open a Terminal window, and download the latest SQL Server for Linux Docker image. Docker pull microsoft/mssql-server-linux:2017-latest Now, launch an instance of the Docker image. Docker run -d -name nameyourcontainer -e 'ACCEPTEULA=Y' -e 'SAPASSWORD=P@55w0rd' -e 'MSSQLPID=Developer' -p 1433:1433 microsoft/mssql-server-linux:2017-latest You should now have SQL Server running on your Mac, ready for action! A few notes on Docker parameters.d: this launches the container in daemon mode, so it runs in the background.name nameyourcontainer: give your Docker container a friendly name, which is useful for stopping and starting containers from the Terminal.e 'ACCEPTEULA=Y: this sets an environment variable in the container named ACCEPTEULA to the value Y. This is required to run SQL Server for Linux.e 'SAPASSWORD=P@55w0rd': this sets an environment variable for the sa database password.
Set this to your own strong password. Also required.e 'MSSQLPID=Developer': this sets an environment variable to instruct SQL Server to run as the Developer Edition.p 1433:1433: this maps the local port 1433 to the container's port 1433. SQL Server, by default, listens for connections on TCP port 1433. microsoft/mssql-server-linux: this final parameter tells Docker which image to use Tip: Get Kitematic Kitematic is a nice desktop application for managing Docker containers. The first time you click Open Kitematic, it will prompt you to download and install it. You can then use Kitematic to view the output of your containers, manage their settings, etc. It's running, now what?
Is a useful command-line tool for SQL Server. To use it, you'll need. Download and install Node.js, if you don't already have it. From the Terminal, install sql-cli globally, so you can use it anywhere. Npm install -g sql-cli Next, connect to your SQL Server instance running in Docker. Mssql -u sa -p P@55w0rd You can now query and execute SQL Server commands from the mssql prompt.
Type.quit and press Enter to exit. Getting an existing SQL database into Docker If you're like me, you have a SQL database you use for development you want to move to your new SQL container running on your Mac. I have good news. Option 1: restore a backup Yes, you can restore a SQL backup file (.bak) created on Windows! You can start by creating a backup of your existing database using SQL Server Management Studio running on your Windows PC/Server. Generate a backup file. Right-click on your database.
Click Tasks - Back Up. Note where the backup file will be created, modify if necessary. Click OK to generate backup file Next, locate the backup file on your Windows machine and copy the file to your Mac.
The details of this step I leave to you, dear reader. I hope it's not too painful. Restore the backup file You'll need to use Docker commands from the Terminal to copy the backup file into the container, and restore the database. Note: Change the paths and names, such as dogfood and container-name to match the name of your database and Docker container. Step 1: Copy the.bak file into your Docker container. Docker cp /Downloads/sql/dogfood.bak container-name:/tmp/dogfood.bak Step 2: Run SQL RESTORE docker exec container-name /opt/mssql-tools/bin/sqlcmd -U sa -P P@55w0rd -Q 'RESTORE DATABASE dogfood FROM DISK='/tmp/dogfood.bak' WITH MOVE 'dogfood' TO '/var/opt/mssql/data/dogfood.mdf', MOVE 'dogfoodLog' TO '/var/opt/mssql/data/dogfoodLog.ldf' ' Step 3: Verify your database is alive docker exec container-name /opt/mssql-tools/bin/sqlcmd -U sa -P P@55w0rd -Q 'SELECT name FROM sys.databases' Ready to rock!
Option 2: generate scripts Another option is to use sql-cli to run the scripts to recreate a database running in Docker. Generate scripts First step is to use SQL Server Management Studio to generate scripts from an existing database. Right-click on your database, and choose Tasks - Generate Scripts.
I chose to separate my table and data scripts from my Views, Stored Procedures, User-Defined Functions, etc. Next, click on Advanced I chose to 'Check for object existence,' 'Script DROP and CREATE,' and set 'Types of data to script' to 'Schema and data.' Checking for object existence and DROP/CREATE allows me to re-run the same script against an existing database, if I want to reset it back to its original state. Choose a location to save your scripts. When finished, repeat the steps to script out your Views, Stored Procedures, and UDFs, if necessary. Run the scripts against your SQL Server running in Docker. Copy the scripts you generated above to your Mac where you can easily get to them from the Terminal.
Open a Terminal, and change to the folder where you placed the scripts. Now, connect to your SQL Server running in Docker using sql-cli, and make sure you are on master mssql use master.
Create a new database mssql CREATE DATABASE devdb Note: Change devdb to match the name of the database you scripted. The generated scripts expect a database with that same name to exist. Change to the database we just created (change devdb to match your database name) mssql use devdb. Run the generated scripts to create all the tables and populate them with data. Mssql.run script.sql. Repeat with the script that contains your other database objects, if necessary. You now have a copy of your database, running on your Mac, without the need for entire Windows VM!
Further reading. Use the mssql to connect and manage your database!. Want to learn more about Docker, such as creating your own containers for your software projects?
I highly recommend awesome. Microsoft's documentation: Share.