Running SQL Server natively on macOS using SQLCMD CLI
- data, mac
- 2024-05-08
- 7 minutes to read
- sql server
- sqlcmd
- docker
Table of Contents
Introduction
I wrote recently about how to run SQL Server on macOS using Docker. I have only just discovered that there is an even easier way to do this using the new SQLCMD CLI tool. This is a new version of the SQLCMD utility that was released back in 2023. I have always used a Mac and my life struggle is to not have to use a Windows machine / VM, this definitely gets me closer to that goal (looking at you SSMS[1], Power BI[2], and Visio[3]).
Pre-requisites
Before you can complete the steps below, you need to install and configure Docker and Rosetta 2…
Install Docker and Rosetta 2
- Install Docker from Docker docs.
- Install Rosetta 2 by running
softwareupdate --install-rosetta
from the command line.
Configure Docker
You need to set two settings in Docker, check the following options in Settings > General
:
- Use Virtualization Framework.
- Use Rosetta for x86_64/amd64 emulation on Apple Silicon.
Note that the Use Rosetta for x86_64/amd64 emulation on Apple Silicon
setting is now a general setting since version 4.29.0. If you are using a previous version, you will find it in Settings > Features in development
if it’s not in there either, it’s time to update!
Install sqlcmd
You also need to install sqlcmd, which you can do using Homebrew:
brew install sqlcmd
Checking the help
The help is really useful, it returns a list of the flags that you can use with the create command:
sqlcmd create mssql --help
Here is the output:
Install/Create SQL Server in a container
Usage:
sqlcmd create mssql [flags]
sqlcmd create mssql [command]
Examples:
# Install/Create SQL Server in a container
sqlcmd create mssql
# See all release tags for SQL Server, install previous version
sqlcmd create mssql get-tags
sqlcmd create mssql --tag 2019-latest
# Create SQL Server, download and attach AdventureWorks sample database
sqlcmd create mssql --using https://aka.ms/AdventureWorksLT.bak
# Create SQL Server, download and attach AdventureWorks sample database with different database name
sqlcmd create mssql --using https://aka.ms/AdventureWorksLT.bak,adventureworks
# Create SQL Server with an empty user database
sqlcmd create mssql --user-database db1
# Install/Create SQL Server with full logging
sqlcmd create mssql --verbosity 4
Available Commands:
get-tags Get tags available for mssql install
Flags:
--accept-eula Accept the SQL Server EULA
--architecture string Specifies the image CPU architecture (default "amd64")
--cached Don't download image. Use already downloaded image
--collation string The SQL Server collation (default "SQL_Latin1_General_CP1_CI_AS")
-c, --context-name string Context name (a default context name will be created if not provided)
--errorlog-wait-line string Line in errorlog to wait for before connecting (default "The default language")
-h, --help help for mssql
--hostname string Explicitly set the container hostname, it defaults to the container ID
--name string Specify a custom name for the container rather than a randomly generated one
--os string Specifies the image operating system (default "linux")
--password-encryption string Password encryption method (none) in sqlconfig file (default "none")
--password-length int Generated password length (default 50)
--password-min-number int Minimum number of numeric characters (default 10)
--password-min-special int Minimum number of special characters (default 10)
--password-min-upper int Minimum number of upper characters (default 10)
--password-special-chars string Special character set to include in password (default "!@#$%&*")
--port int Port (next available port from 1433 upwards used by default)
--registry string Container registry (default "mcr.microsoft.com")
--repo string Container repository (default "mssql/server")
--tag string Tag to use, use get-tags to see list of tags (default "latest")
-u, --user-database string Create a user database and set it as the default for login
--using string Download (into container) and attach database (.bak) from URL
Global Flags:
-?, --? help for backwards compatibility flags (-S, -U, -E etc.)
--sqlconfig string configuration file (default "/Users/justin/.sqlcmd/sqlconfig")
--verbosity int log level, error=0, warn=1, info=2, debug=3, trace=4 (default 2)
--version print version of sqlcmd
Use "sqlcmd create mssql [command] --help" for more information about a command.
Get Tags
The get-tags
command will return a list of available versions you can intall (there are lots so I won’t paste the output):
sqlcmd create mssql get-tags
Here is the output:
## condensed output, there are lots of versions but the "latest" tags are probably the simplest to use
- 2017-latest
- 2019-latest
- 2022-latest
- latest
Create a SQL Server instance
Based on the help and the get-tags, I am going to create a container with the following options:
- A 2022 instance of SQL Server.
- Call the host and container sql2022.
- Set the port to 2022.
- Restore the AdventureWorksLT database.
- Create a second database for my use called.
The command will look like this:
# make sure docker is running when you run the command!
sqlcmd create mssql --using https://aka.ms/AdventureWorksLT.bak --accept-eula --user-database AdventureWorksLT --hostname sql2022 --name sql2022 --port 2022 --tag 2022-latest
Here is the output:
Downloading mcr.microsoft.com/mssql/server:2022-latest
Starting mcr.microsoft.com/mssql/server:2022-latest
Created context "mssql" in "/Users/justin/.sqlcmd/sqlconfig", configuring user account...
Disabled "sa" account (and rotated "sa" password). Creating user "justin"
Creating default database [db1]
Downloading AdventureWorksLT.bak
Restoring database AdventureWorksLT
Processed 888 pages for database 'AdventureWorksLT', file 'AdventureWorksLT2022_Data' on file 1.
Processed 2 pages for database 'AdventureWorksLT', file 'AdventureWorksLT2022_Log' on file 1.
RESTORE DATABASE successfully processed 890 pages in 0.021 seconds (330.915 MB/sec).
Now ready for client connections on port 2022
All done! Once complete you will be presented with a list of commands:
HINT:
1. Open in Azure Data Studio: sqlcmd open ads
2. Run a query: sqlcmd query "SELECT @@version"
3. Start interactive session: sqlcmd query
4. View sqlcmd configuration: sqlcmd config view
5. See connection strings: sqlcmd config connection-strings
6. Remove: sqlcmd delete
Running a query I can see that I have a SQL Server 2022 instance running:
sqlcmd query "SELECT @@version"
Here is the output:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2022 (RTM-CU12-GDR) (KB5036343) - 16.0.4120.1 (X64)
Mar 18 2024 12:02:14
Copyright (C) 2022 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 22.04.4 LTS) <X64>
(1 row affected)
Open in Azure Data Studio
I should be able to open the instance in Azure Data Studio by running the command sqlcmd open ads
, but at time of writing there is a bug with how this works for macOS which you can read about on the issues page and so in the meantime, you need to run the command sqlcmd config connection-strings
which will return the connection strings you need to connect to the instance:
sqlcmd config connection-strings
Here is the output:
ADO.NET: Server=tcp:127.0.0.1,2022;Initial Catalog=AdventureWorksLT;Persist Security Info=False;User ID=justin;Password=ADB@5XRB7k$$N#!9EUDQ!o808*OPQ6AO*deQ0!P$17U2Lw%iEQ;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;
JDBC: jdbc:sqlserver://127.0.0.1:2022;database=AdventureWorksLT;user=justin;password=ADB@5XRB7k$$N#!9EUDQ!o808*OPQ6AO*deQ0!P$17U2Lw%iEQ;encrypt=true;trustServerCertificate=true;loginTimeout=30;
ODBC: Driver={ODBC Driver 18 for SQL Server};Server=tcp:127.0.0.1,2022;Database=AdventureWorksLT;Uid=justin;Pwd=ADB@5XRB7k$$N#!9EUDQ!o808*OPQ6AO*deQ0!P$17U2Lw%iEQ;Encrypt=yes;TrustServerCertificate=yes;Connection Timeout=30;
GO: sqlserver://justin:ADB@5XRB7k$$N#!9EUDQ!o808*OPQ6AO*deQ0!P$17U2Lw%[email protected],2022?database=AdventureWorksLT;encrypt=true;trustServerCertificate=true;dial+timeout=30
SQLCMD: export 'SQLCMDPASSWORD=ADB@5XRB7k$$N#!9EUDQ!o808*OPQ6AO*deQ0!P$17U2Lw%iEQ'; sqlcmd -S 127.0.0.1,2022 -U justin -d AdventureWorksLT
Observe that the username and password will be present in the connection strings so for me it is (which I have now cycled!):
- justin
- ADB@5XRB7k$$N#!9EUDQ!o808*OPQ6AO*deQ0!P$17U2Lw%iEQ
Connect in Azure Data Studio
So I can now connect to the instance in Azure Data Studio by using the login info from the connection strings above:
More databases
Now that I have a sql instance running, what about a new database, that’s pretty straight forward:
sqlcmd query "Create database db2"
Or I can create it directly from Azure Data Studio.
Creating new containers from image
Now that I have downloaded the image, I can spin up additional instances using the --cache
switch:
sqlcmd create mssql --accept-eula --user-database db1 --hostname sql2022_test --name sql2022_test --port 3022 --tag 2022-latest --cached
Note that I have had to give the host a distinct name and assign a different port.
Conclusion
And that’s it! Very simple to set up. I have a running instance of SQL Server 2022 with the AdventureWorksLT database attached and a second database called db1
that I can use for my own purposes.