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’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:

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’s 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’s 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’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:

# make sure docker is running when you run the command!
sqlcmd create mssql --using https://aka.ms/AdventureWorksLT.bak --accept-eula --user-database db1 --hostname sql2022 --name sql2022 --port 2022 --tag 2022-latest

Here’s 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’s 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’s 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’ve 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.

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.

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'm 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 and enjoy your day. If you have any comments or suggestions, please leave them below...



#mtfbwy



Related Posts

Recent Posts