Running Sql Server on macOS Using SQLCMD CLI
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 here.
- Install Rosetta 2 by running
softwareupdate --install-rosettafrom 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’re 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:
1brew 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:
1sqlcmd create mssql --help
2
3## output
4Install/Create SQL Server in a container
5
6Usage:
7 sqlcmd create mssql [flags]
8 sqlcmd create mssql [command]
9
10Examples:
11# Install/Create SQL Server in a container
12 sqlcmd create mssql
13# See all release tags for SQL Server, install previous version
14 sqlcmd create mssql get-tags
15 sqlcmd create mssql --tag 2019-latest
16# Create SQL Server, download and attach AdventureWorks sample database
17 sqlcmd create mssql --using https://aka.ms/AdventureWorksLT.bak
18# Create SQL Server, download and attach AdventureWorks sample database with different database name
19 sqlcmd create mssql --using https://aka.ms/AdventureWorksLT.bak,adventureworks
20# Create SQL Server with an empty user database
21 sqlcmd create mssql --user-database db1
22# Install/Create SQL Server with full logging
23 sqlcmd create mssql --verbosity 4
24
25Available Commands:
26 get-tags Get tags available for mssql install
27
28Flags:
29 --accept-eula Accept the SQL Server EULA
30 --architecture string Specifies the image CPU architecture (default "amd64")
31 --cached Don't download image. Use already downloaded image
32 --collation string The SQL Server collation (default "SQL_Latin1_General_CP1_CI_AS")
33 -c, --context-name string Context name (a default context name will be created if not provided)
34 --errorlog-wait-line string Line in errorlog to wait for before connecting (default "The default language")
35 -h, --help help for mssql
36 --hostname string Explicitly set the container hostname, it defaults to the container ID
37 --name string Specify a custom name for the container rather than a randomly generated one
38 --os string Specifies the image operating system (default "linux")
39 --password-encryption string Password encryption method (none) in sqlconfig file (default "none")
40 --password-length int Generated password length (default 50)
41 --password-min-number int Minimum number of numeric characters (default 10)
42 --password-min-special int Minimum number of special characters (default 10)
43 --password-min-upper int Minimum number of upper characters (default 10)
44 --password-special-chars string Special character set to include in password (default "!@#$%&*")
45 --port int Port (next available port from 1433 upwards used by default)
46 --registry string Container registry (default "mcr.microsoft.com")
47 --repo string Container repository (default "mssql/server")
48 --tag string Tag to use, use get-tags to see list of tags (default "latest")
49 -u, --user-database string Create a user database and set it as the default for login
50 --using string Download (into container) and attach database (.bak) from URL
51
52Global Flags:
53 -?, --? help for backwards compatibility flags (-S, -U, -E etc.)
54 --sqlconfig string configuration file (default "/Users/justin/.sqlcmd/sqlconfig")
55 --verbosity int log level, error=0, warn=1, info=2, debug=3, trace=4 (default 2)
56 --version print version of sqlcmd
57
58Use "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):
1sqlcmd create mssql get-tags
2
3## condensed output, there are lots of versions but the "latest" tags are probably the simplest to use
4- 2017-latest
5- 2019-latest
6- 2022-latest
7- latest
Create a SQL Server instance
Based on the help and the get-tags, I’m 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:
1# make sure docker is running when you run the command!
2sqlcmd create mssql --using https://aka.ms/AdventureWorksLT.bak --accept-eula --user-database db1 --hostname sql2022 --name sql2022 --port 2022 --tag 2022-latest
3
4## output
5Downloading mcr.microsoft.com/mssql/server:2022-latest
6Starting mcr.microsoft.com/mssql/server:2022-latest
7Created context "mssql" in "/Users/justin/.sqlcmd/sqlconfig", configuring user account...
8Disabled "sa" account (and rotated "sa" password). Creating user "justin"
9Creating default database [db1]
10Downloading AdventureWorksLT.bak
11Restoring database AdventureWorksLT
12Processed 888 pages for database 'AdventureWorksLT', file 'AdventureWorksLT2022_Data' on file 1.
13Processed 2 pages for database 'AdventureWorksLT', file 'AdventureWorksLT2022_Log' on file 1.
14RESTORE DATABASE successfully processed 890 pages in 0.021 seconds (330.915 MB/sec).
15Now ready for client connections on port 2022
All done! Once complete you will be presented with a list of commands:
1HINT:
2 1. Open in Azure Data Studio: sqlcmd open ads
3 2. Run a query: sqlcmd query "SELECT @@version"
4 3. Start interactive session: sqlcmd query
5 4. View sqlcmd configuration: sqlcmd config view
6 5. See connection strings: sqlcmd config connection-strings
7 6. Remove: sqlcmd delete
Running a query I can see that I have a SQL Server 2022 instance running:
1sqlcmd query "SELECT @@version"
2
3## output
4------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5Microsoft SQL Server 2022 (RTM-CU12-GDR) (KB5036343) - 16.0.4120.1 (X64)
6 Mar 18 2024 12:02:14
7 Copyright (C) 2022 Microsoft Corporation
8 Developer Edition (64-bit) on Linux (Ubuntu 22.04.4 LTS) <X64>
9
10(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 here 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:
1sqlcmd config connection-strings
2
3## output
4ADO.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;
5JDBC: 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;
6ODBC: 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;
7GO: sqlserver://justin:ADB@5XRB7k$$N#!9EUDQ!o808*OPQ6AO*deQ0!P$17U2Lw%[email protected],2022?database=AdventureWorksLT;encrypt=true;trustServerCertificate=true;dial+timeout=30
8SQLCMD: 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’ve now cycled!):
1- justin
2- 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:
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.
References
Appendix
Tags :
#mtfbwy
