Running SQL Server natively on macOS using SQLCMD CLI

Running SQL Server natively 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 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.
The screenshot is displaying the docker settings menu which has a set of options, the two options listed above are checked.

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:

Screenshot shows Active Directory connection panel open with localhost set and user name and password entered.

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.

Wrapping Up

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.

Screenshot shows Azure Data Studio connected to the server, there are two databases listed, AdventureWorksLT and db1.

References

Appendix

  • [1] I realise this will probably never happen but I am in SSMS less and less nowadays.
  • [2] You can use Fabric to some extent but for complex stuff you still need the desktop.
  • [3] Yeah...I said it...Visio...not that I want to use Visio, but sometimes needs must...

Thanks!

Thanks for reading my blog. If you have any comments or suggestions, please leave them in the comments section below. If you have found this post useful, you could always consider buying me a coffee! Dw i'n hoffi coffi (which is Welsh for "I like coffee")!



#mtfbwy



Related Posts

Recent Posts