# Setting up a local SQL Server database > Part of Prisma's Data Guide. The complete index is at https://www.prisma.io/dataguide/llms.txt. Run SQL Server 2025 or 2022 locally in Docker on any OS, or install it on Windows or Linux, then connect with sqlcmd and create a least-privilege login for your app. Canonical URL: https://www.prisma.io/dataguide/mssql/setting-up-a-local-sql-server-database Content revision: 91b5ea3b95a0824bcd0e0d61f68d6121c6e45039fde2b5f874c4c65eec29030e Source revision: b6716699f6439c3ba6ae3bf7116b7da84963c978 Last updated: 2026-09-30 ## Overview In this guide, you'll set up a SQL Server instance on your own machine for development, connect to it with the `sqlcmd` command-line client, and create a database with its own login, so that your application doesn't connect as the all-powerful `sa` administrator. There are three ways to get a local instance: - [Docker](https://www.prisma.io/dataguide/mssql/setting-up-a-local-sql-server-database#running-sql-server-in-docker) works the same way on Windows, macOS and Linux, and a container is quick to create and to throw away. It's the only option on macOS. This guide shows the commands for Bash and other Unix shells, and for PowerShell where they differ. - [A native Windows installation](https://www.prisma.io/dataguide/mssql/setting-up-a-local-sql-server-database#installing-sql-server-on-windows) runs SQL Server as a Windows service, next to tools such as SQL Server Management Studio. - [A native Linux installation](https://www.prisma.io/dataguide/mssql/setting-up-a-local-sql-server-database#installing-sql-server-on-linux) uses Microsoft's package repositories for Ubuntu and Red Hat Enterprise Linux. Whichever you choose, continue with [creating a database and a login](https://www.prisma.io/dataguide/mssql/setting-up-a-local-sql-server-database#creating-a-database-and-a-least-privilege-login) afterwards. ## Choosing a version and edition SQL Server 2025 (version 17.x) is the current release. It became generally available in November 2025 and receives fixes through cumulative updates (CUs); this guide was tested with CU9. SQL Server 2022 (16.x) is in mainstream support until January 2028. SQL Server 2019 left mainstream support in February 2025 and only gets security fixes until January 2030, so don't start new projects on it. Microsoft's lifecycle pages list the exact dates for [2025](https://learn.microsoft.com/en-us/lifecycle/products/sql-server-2025), [2022](https://learn.microsoft.com/en-us/lifecycle/products/sql-server-2022) and [2019](https://learn.microsoft.com/en-us/lifecycle/products/sql-server-2019). As a rule, develop against the version you'll run in production. The edition decides which features you get and whether you may use it in production. These editions are free: | Edition | Production use | What it's for | | -------------------- | --------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Enterprise Developer | No, development and testing | All Enterprise edition features. In SQL Server 2022 and earlier, this is simply called Developer edition. | | Standard Developer | No, development and testing | New in SQL Server 2025: the Standard edition feature set, so that you don't build on Enterprise-only features when you'll deploy to Standard. | | Express | Yes | Small applications. Each database is limited to 50 GB, and the engine uses at most 4 cores and 1,410 MB of memory for its buffer pool (SQL Server 2025 limits). | Pick the Developer edition that matches the edition you'll deploy to. If you'll deploy on Express, develop on Express, so that you notice its limits early. The [editions and features page](https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2025) compares them in detail. SQL Server runs on Windows and Linux on x86-64 processors only. There are no native builds for macOS or for ARM processors, which is why Docker is the way to go on a Mac. ## Running SQL Server in Docker Microsoft publishes SQL Server as Linux container images on the Microsoft Artifact Registry (`mcr.microsoft.com`). The SQL Server 2025 images are based on Ubuntu, and they contain both the database engine and the `sqlcmd` client. You need [Docker Engine](https://docs.docker.com/engine/install/) on Linux, or Docker Desktop or another Docker-compatible runtime on Windows and macOS. SQL Server needs at least 2 GB of memory, so make sure the virtual machine that Docker Desktop runs has enough to spare. ### Apple Silicon and other ARM machines The images exist only for x86-64 (`linux/amd64`). Microsoft's [Docker quickstart](https://learn.microsoft.com/en-us/sql/linux/install-upgrade/quickstart-install-docker) states that running them under emulation or translation, such as Rosetta 2 or QEMU, isn't tested or supported. In practice, the image does run under Rosetta 2: the Docker steps in this guide were tested with SQL Server 2025 CU9 on an Apple Silicon Mac with OrbStack, which uses Rosetta for x86-64 containers. Docker Desktop can use Rosetta too, but only with the Apple Virtualization framework as its virtual machine manager. With Docker VMM, which [doesn't support Rosetta](https://docs.docker.com/desktop/features/vmm/), x86-64 emulation is slow. So in Docker Desktop, first choose **Apple Virtualization framework** under **Settings** > **General** > **Virtual Machine Manager**, then turn on **Use Rosetta for x86\_64/amd64 emulation on Apple Silicon** in the [settings](https://docs.docker.com/desktop/settings-and-maintenance/settings/). This guide wasn't tested with Docker Desktop. Either way, treat emulation as a development convenience that Microsoft doesn't support. If you hit behavior you can't explain, reproduce it on an x86-64 machine before you debug your own code. You may find older tutorials that recommend Azure SQL Edge as an ARM-native alternative. Microsoft [retired Azure SQL Edge](https://learn.microsoft.com/en-us/lifecycle/products/azure-sql-edge) on September 30, 2025, so it's no longer an option. ### Starting the container Choose a password for the `sa` administrator account. It must follow SQL Server's [password policy](https://learn.microsoft.com/en-us/sql/relational-databases/security/password-policy): at least eight characters, with characters from three of these four groups: uppercase letters, lowercase letters, digits and symbols. If the password doesn't meet the policy, SQL Server can't finish its setup and the container stops. If you typed the password into the `docker run` command itself, it would end up in your shell's history. Instead, read it into an environment variable without echoing it, and let Docker pass that variable on. In Bash: ```bash read -rs -p 'sa password: ' MSSQL_SA_PASSWORD && echo export MSSQL_SA_PASSWORD docker run --detach \ --name mssql \ --hostname mssql \ --platform linux/amd64 \ --env ACCEPT_EULA=Y \ --env MSSQL_SA_PASSWORD \ --publish 127.0.0.1:1433:1433 \ --volume mssql-data:/var/opt/mssql \ mcr.microsoft.com/mssql/server:2025-latest ``` In PowerShell, lines continue with a backtick instead of a backslash: ```powershell $env:MSSQL_SA_PASSWORD = [Net.NetworkCredential]::new('', (Read-Host -AsSecureString 'sa password')).Password docker run --detach ` --name mssql ` --hostname mssql ` --platform linux/amd64 ` --env ACCEPT_EULA=Y ` --env MSSQL_SA_PASSWORD ` --publish 127.0.0.1:1433:1433 ` --volume mssql-data:/var/opt/mssql ` mcr.microsoft.com/mssql/server:2025-latest ``` Alternatively, write `MSSQL_SA_PASSWORD=` into a file that only you can read, and replace `--env MSSQL_SA_PASSWORD` with `--env-file `. Here's what the options do: - `--platform linux/amd64` asks for the x86-64 image explicitly. On an Intel or AMD machine, it changes nothing. On an ARM machine, it states that you want the image to run under emulation, so Docker doesn't warn that the image's platform doesn't match your machine's. - `ACCEPT_EULA=Y` accepts the SQL Server license terms, which the image requires. - `--env MSSQL_SA_PASSWORD`, without a value, copies the variable from your shell into the container, where SQL Server uses it as the `sa` password. Older guides use `SA_PASSWORD`, which is deprecated. - `--publish 127.0.0.1:1433:1433` makes SQL Server reachable on port 1433 of your machine, but only from the machine itself. With `--publish 1433:1433`, Docker would listen on every network interface, and anyone on the same network, say, a café's Wi-Fi, could try passwords against `sa`. - `--volume mssql-data:/var/opt/mssql` stores the databases in a named Docker volume. The data survives when you remove and re-create the container, for example to update to a newer image. - `--name` gives the container the name that you use in other `docker` commands. `--hostname` sets the host name inside the container, which SQL Server reports as its server name, for example in `@@SERVERNAME`. The image contains the Enterprise Developer edition unless you choose another one. For the Standard Developer edition, add `--env 'MSSQL_PID=StandardDeveloper'`; Microsoft's [environment variables reference](https://learn.microsoft.com/en-us/sql/linux/configure/environment-variables) lists all values. The `2025-latest` tag always points to the newest cumulative update. If everyone on your team should run the same build (see [syncing development databases between team members](https://www.prisma.io/dataguide/managing-databases/syncing-development-databases-between-team-members)), use a tag that names the update, such as `2025-CU9-ubuntu-24.04`, from the [list of tags](https://mcr.microsoft.com/product/mssql/server/tags). For SQL Server 2022, use `2022-latest` or one of its update tags. SQL Server needs a few seconds to start. It's ready when its log contains this line: ```bash docker logs mssql | grep "ready for client connections" ``` In PowerShell, use `Select-String` instead of `grep`: ```powershell docker logs mssql | Select-String "ready for client connections" ``` ```text 2026-09-30 03:37:41.88 spid57s SQL Server is now ready for client connections. This is an informational message; no user action is required. ``` Even after this line appears, a login can fail for a few more seconds while SQL Server finishes starting up. If that happens, try again. If the container isn't running at all, `docker logs mssql` shows why, for example that the password doesn't meet the policy. The other `docker` commands in this guide work the same way in Bash and PowerShell. ### Connecting with sqlcmd inside the container The image includes the `sqlcmd` client that's based on the ODBC driver, so you don't have to install anything to connect. Open a SQL shell as `sa`: ```bash docker exec -it mssql /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -C ``` `sqlcmd` asks for the password and then shows its `1>` prompt. Current images install the tools in `/opt/mssql-tools18/bin`. The `/opt/mssql-tools/bin` path from older tutorials doesn't exist in the SQL Server 2025 image. You'll create a database and a login in [the next section](https://www.prisma.io/dataguide/mssql/setting-up-a-local-sql-server-database#creating-a-database-and-a-least-privilege-login). First, it's worth understanding the `-C` option. ### Why sqlcmd needs `-C`: encryption and certificates Version 18 of Microsoft's ODBC driver, and the `sqlcmd` built on it, encrypts connections by default and checks that the server's TLS certificate is trusted, just as a browser does for a website. A fresh SQL Server generates a self-signed certificate at startup, which no client trusts. Without `-C`, the connection fails: ```text Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate]. Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722. ``` You have three ways to deal with this: - `-C` (trust the server certificate) keeps the connection encrypted but skips the check of who's on the other end. An attacker who can intercept the traffic could pose as the server. On a connection to `localhost`, that would require access to your machine, so for a local development server this is a reasonable trade-off. - `-No` makes encryption optional. SQL Server [always encrypts the login packet](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption), which contains the password, but your queries and their results then travel unencrypted. Microsoft's quickstarts suggest `-No`, but when `-C` works, you gain nothing by dropping encryption. - A certificate that your clients trust is the right fix as soon as the connection crosses a network. See Microsoft's guides for [Linux](https://learn.microsoft.com/en-us/sql/linux/security/encrypted-connections) and [Windows](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption). To check whether your current connection is encrypted, run this query as `sa` or another administrator. Logins without the `VIEW SERVER PERFORMANCE STATE` permission, like the application login you'll create later, can't read this view: ```sql SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID; GO ``` ```text encrypt_option ---------------------------------------- TRUE (1 rows affected) ``` With `-No`, the same query returns `FALSE`. Your application's database driver faces the same choice, usually through a `trustServerCertificate` or `TrustServerCertificate` connection string option. Keep that option in your local configuration only. ### Connecting from your machine with the Go-based sqlcmd To connect from outside the container, for example from your editor's terminal, you can install the newer, Go-based `sqlcmd` ([go-sqlcmd](https://github.com/microsoft/go-sqlcmd)). It's a single program for Windows, macOS and Linux: install it with `winget install sqlcmd` on Windows, `brew install sqlcmd` on macOS, or download a release from GitHub. Microsoft's [installation guide](https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-download-install) describes these options and the ODBC-based alternative. Its encryption defaults differ from the ODBC-based `sqlcmd`: in version 1.10.0, a connection without options only encrypts the login, not the queries and results, and `-C` alone doesn't change that. To get a fully encrypted connection to the local server, request encryption with `-N true` and trust the self-signed certificate with `-C`: ```bash sqlcmd -S localhost -U sa -N true -C ``` go-sqlcmd can also create a SQL Server container for you with `sqlcmd create mssql --accept-eula`. It generates a login named after your operating system user, disables `sa`, and stores the credentials in `~/.sqlcmd/sqlconfig`. This guide uses `docker run` instead, for two reasons: in version 1.10.0, `sqlcmd create mssql` publishes the port on all network interfaces with no option to restrict it to localhost, and [it can't select an edition](https://learn.microsoft.com/en-us/sql/linux/install-upgrade/quickstart-install-docker). ## Installing SQL Server on Windows These steps follow Microsoft's documentation. They weren't tested for this guide. SQL Server 2025 runs on Windows 11 and on Windows Server 2019 and later, on x86-64 processors only. Microsoft's [requirements page](https://learn.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server-2025) still lists Windows 10, but Windows 10 Home and Pro [reached the end of support](https://learn.microsoft.com/en-us/lifecycle/products/windows-10-home-and-pro) on October 14, 2025. SQL Server also needs .NET Framework 4.7.2 and at least 6 GB of free disk space. 1. On Microsoft's [SQL Server downloads page](https://www.microsoft.com/en-us/sql-server/sql-server-downloads), choose **Download Standard Developer edition** or **Download Enterprise Developer edition**, or download Express. 2. Run the installer as an administrator. Microsoft's [installation guide](https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-installation-wizard-setup) walks through every page of the wizard. 3. On the **Database Engine Configuration** page, choose the authentication mode: - **Windows Authentication** lets you sign in with your Windows account. Select **Add Current User** to make your account an administrator of the instance. - **Mixed Mode Authentication** also allows SQL Server logins with their own passwords, such as the `app_user` login in the next section. It requires you to set a password for `sa`. You can [change the authentication mode](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode) later. The instance you get determines how you connect to it: - Unless you name the instance, the Developer editions install the [default instance](https://learn.microsoft.com/en-us/sql/sql-server/install/instance-configuration) (`MSSQLSERVER`). You connect to it as `localhost`, and it listens on TCP port 1433 once TCP/IP is enabled. - Express is usually installed as a [named instance called `SQLExpress`](https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/sql-server-express-security). You connect to it as `localhost\SQLEXPRESS`, and it picks a [dynamic TCP port](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port) at every start, not 1433. Before you connect an application, check the network settings. [On new Developer and Express installations, TCP/IP is disabled](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/default-sql-server-network-protocol-configuration). Tools on the same machine can still connect through shared memory, but an application that connects to `localhost:1433` needs TCP/IP: 1. [Enable TCP/IP in SQL Server Configuration Manager](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/enable-or-disable-a-server-network-protocol). 2. For a named instance such as Express's, also [give it a fixed port](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port): in the TCP/IP properties, on the **IP Addresses** tab, delete the value of **TCP Dynamic Ports** under **IPAll** and enter `1433` as its **TCP Port**. Alternatively, install Express as the default instance. 3. Restart the SQL Server service. For tools, install the Go-based `sqlcmd` with `winget install sqlcmd`. For a graphical client, install [SQL Server Management Studio (SSMS) 22](https://learn.microsoft.com/en-us/ssms/install/install), which installs through the Visual Studio Installer, or the [MSSQL extension for Visual Studio Code](https://learn.microsoft.com/en-us/sql/tools/visual-studio-code-extensions/mssql/mssql-extension-visual-studio-code). To connect to the default instance with your Windows account, use `-E` (trusted connection): ```powershell sqlcmd -S localhost -E ``` For Express, or any other named instance, add the instance name: ```powershell sqlcmd -S localhost\SQLEXPRESS -E ``` ## Installing SQL Server on Linux These steps follow Microsoft's quickstarts for [Ubuntu](https://learn.microsoft.com/en-us/sql/linux/install-upgrade/quickstart-install-ubuntu) and [Red Hat Enterprise Linux](https://learn.microsoft.com/en-us/sql/linux/install-upgrade/quickstart-install-red-hat). For this guide, the Ubuntu 22.04 and 24.04 package installations were tested in containers, but not `mssql-conf setup` and the systemd service, which a container doesn't run. The RHEL steps weren't tested; their repository files and packages were confirmed to exist on `packages.microsoft.com`. SQL Server needs an x86-64 machine with at least 2 GB of memory, and its data must be on an XFS or ext4 file system. The installation runs SQL Server as a systemd service. These distributions are supported: | SQL Server version | Supported distributions | | ------------------ | --------------------------------------------------------------------- | | SQL Server 2025 | Ubuntu 22.04 and 24.04, Red Hat Enterprise Linux 9 and 10 | | SQL Server 2022 | Ubuntu 20.04 and 22.04, RHEL 8 and 9, SUSE Linux Enterprise Server 15 | Ubuntu 24.04 and RHEL 10 are supported starting with SQL Server 2025 CU1, and SQL Server 2025 no longer supports SUSE Linux Enterprise Server. The [release notes](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes) have the current list. ### Ubuntu 24.04 and 22.04 Add Microsoft's signing key and the SQL Server 2025 repository, then install the `mssql-server` package. On Ubuntu 24.04: ```bash curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg curl -fsSL https://packages.microsoft.com/config/ubuntu/24.04/mssql-server-2025.list | sudo tee /etc/apt/sources.list.d/mssql-server-2025.list sudo apt-get update sudo apt-get install -y mssql-server ``` The repository file for Ubuntu 22.04 doesn't name a key file, so `apt-get update` would fail with `NO_PUBKEY` if you only stored the key in `/usr/share/keyrings`. Add the key to APT's trusted keys instead: ```bash curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc > /dev/null curl -fsSL https://packages.microsoft.com/config/ubuntu/22.04/mssql-server-2025.list | sudo tee /etc/apt/sources.list.d/mssql-server-2025.list sudo apt-get update sudo apt-get install -y mssql-server ``` ### Red Hat Enterprise Linux 9 and 10 Download the repository file for your RHEL release, here RHEL 9 (use `rhel/10` in the URL for RHEL 10), and install the package: ```bash sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/9/mssql-server-2025.repo sudo yum install -y mssql-server ``` ### Configuring the server The package installs SQL Server but doesn't configure it. Run `mssql-conf setup`, which asks you to choose an edition, to accept the license terms and to set the `sa` password: ```bash sudo /opt/mssql/bin/mssql-conf setup ``` It starts with the choice of edition: ```text Choose an edition of SQL Server: 1) Evaluation (free, no production use rights, 180-day limit) 2) Enterprise Developer (free, no production use rights) 3) Standard Developer (free, no production use rights) 4) Express (free) 5) Standard (PAID) 6) Enterprise (PAID) - CPU core utilization restricted to 20 physical/40 hyperthreaded 7) Enterprise Core (PAID) - CPU core utilization up to Operating System Maximum 8) I bought a license through a retail sales channel and have a product key to enter. 9) Standard (Billed through Azure) - Use pay-as-you-go billing through Azure. 10) Enterprise Core (Billed through Azure) - Use pay-as-you-go billing through Azure. ``` Choose one of the free editions: a Developer edition for development, or Express. When the setup has finished, check that the service is running: ```bash systemctl status mssql-server --no-pager ``` Microsoft's quickstarts also open port 1433 in the firewall to allow remote connections. For a server that only you use on this machine, skip that step. ### Installing the command-line tools The server package doesn't include `sqlcmd`. You can install the Go-based `sqlcmd` from its [GitHub releases](https://github.com/microsoft/go-sqlcmd/releases), or the ODBC-based tools from Microsoft's repository. On Ubuntu 24.04: ```bash curl -sSL -O https://packages.microsoft.com/config/ubuntu/24.04/packages-microsoft-prod.deb sudo dpkg -i packages-microsoft-prod.deb sudo apt-get update sudo apt-get install mssql-tools18 unixodbc-dev ``` On Ubuntu 22.04, which already trusts the key from the previous step: ```bash curl -fsSL https://packages.microsoft.com/config/ubuntu/22.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list sudo apt-get update sudo apt-get install mssql-tools18 unixodbc-dev ``` On RHEL 9 and 10: ```bash curl https://packages.microsoft.com/config/rhel/9/prod.repo | sudo tee /etc/yum.repos.d/mssql-release.repo sudo yum install -y mssql-tools18 unixODBC-devel ``` The packages ask you to accept the license terms of the ODBC driver and the tools. The tools are installed in `/opt/mssql-tools18/bin`, which isn't on your `PATH`. To add it for Bash: ```bash echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc source ~/.bashrc ``` Then connect as `sa`. The `-C` option has the same meaning as [in the container](https://www.prisma.io/dataguide/mssql/setting-up-a-local-sql-server-database#why-sqlcmd-needs--c-encryption-and-certificates): ```bash sqlcmd -S localhost -U sa -C ``` ## Creating a database and a least-privilege login SQL Server separates authentication from authorization in two objects: - A **login** exists at the server level and lets someone connect, either with a password (a SQL Server login) or, on Windows, with a Windows account. - A **user** exists inside a database and is mapped to a login. Permissions in that database, often granted through database roles, belong to the user. The `sa` login is a member of the `sysadmin` role and can do anything, including dropping every database. Use it to administer the server, but give your application a login that can only do what the application needs. That limits the damage that a bug or an SQL injection in the application can do: it can't drop tables, create databases or change other databases. The examples below use the container from the Docker section. On a native installation, use the `sqlcmd` command from the Windows or Linux section instead of `docker exec`. Connect as `sa`: ```bash docker exec -it mssql /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -C ``` `sqlcmd` collects your statements and sends them to the server when you type `GO` on a line of its own. Check which server you're connected to: ```sql SELECT @@VERSION; GO ``` ```text ----------------------------------------------------------------------------- Microsoft SQL Server 2025 (RTM-CU9) (KB5122048) - 17.0.5005.3 (X64) Aug 27 2026 09:30:16 Copyright (C) 2025 Microsoft Corporation Enterprise Developer Edition (64-bit) on Linux (Ubuntu 24.04.4 LTS) (1 rows affected) ``` Create a database, a login for your application, and a user for that login in the new database. Replace `` with a password that meets the password policy: ```sql CREATE DATABASE appdb; GO CREATE LOGIN app_user WITH PASSWORD = '', DEFAULT_DATABASE = appdb; GO USE appdb; GO CREATE USER app_user FOR LOGIN app_user; ALTER ROLE db_datareader ADD MEMBER app_user; ALTER ROLE db_datawriter ADD MEMBER app_user; GO ``` ```text Changed database context to 'appdb'. ``` The `db_datareader` and `db_datawriter` roles let `app_user` read and change the data in all tables of `appdb`, but not create, alter or drop tables. `DEFAULT_DATABASE` makes `appdb` the database that `app_user` starts in after connecting. If a schema migration tool connects with this login, it also needs to change tables. Add it to the `db_ddladmin` role, or better, give the migration tool a login of its own. Microsoft's reference describes [all fixed database roles](https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles). The container images can also create a database and a login on startup, with the `MSSQL_DB`, `MSSQL_USER` and `MSSQL_PASSWORD` [environment variables](https://learn.microsoft.com/en-us/sql/linux/configure/environment-variables). This guide doesn't use them because that user gets more than an application needs: in a test with SQL Server 2025 CU9, it received `CONTROL` permission on the database, which let it create tables and even drop the database, and the login's default database stayed `master`. To test the new login, create a table while you're still connected as `sa`, and then type `EXIT`: ```sql CREATE TABLE dbo.notes ( id int IDENTITY PRIMARY KEY, body nvarchar(200) NOT NULL ); GO EXIT ``` Connect again as `app_user`: ```bash docker exec -it mssql /opt/mssql-tools18/bin/sqlcmd -S localhost -U app_user -C ``` Check where you are, and write and read a row: ```sql SELECT DB_NAME() AS current_database, SUSER_NAME() AS login_name; GO INSERT INTO dbo.notes (body) VALUES (N'Hello from app_user'); SELECT id, body FROM dbo.notes; GO ``` ```text current_database login_name ------------------ ------------ appdb app_user (1 rows affected) (1 rows affected) id body ----------- -------------------- 1 Hello from app_user (1 rows affected) ``` Now try something that the login isn't allowed to do: ```sql CREATE TABLE dbo.should_fail (id int); GO CREATE DATABASE should_fail; GO ``` Both statements fail with error 262: ```text CREATE TABLE permission denied in database 'appdb'. CREATE DATABASE permission denied in database 'master'. ``` Type `EXIT` to leave `sqlcmd`. Your application can now connect to server `localhost`, port `1433`, database `appdb`, with the login `app_user`. Keep that login's password in your application's local configuration, not in your code. ### Replacing `sa` with your own administrator login Everyone knows the name `sa`, so it's the first login that attackers try. Microsoft recommends that you create your own administrator login and then disable `sa`. Connect as `sa` again, create the login and add it to the `sysadmin` role, replacing `` with a password that meets the policy: ```sql CREATE LOGIN dev_admin WITH PASSWORD = ''; ALTER SERVER ROLE sysadmin ADD MEMBER dev_admin; GO ``` Type `EXIT`, connect as `dev_admin` to make sure that the new login works, and disable `sa`: ```bash docker exec -it mssql /opt/mssql-tools18/bin/sqlcmd -S localhost -U dev_admin -C ``` ```sql ALTER LOGIN sa DISABLE; GO ``` From now on, logging in as `sa` fails: ```text Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Login failed for user 'sa'. Reason: The account is disabled.. ``` On Windows, if you chose Windows Authentication during setup, `sa` is [already disabled](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode). Two more things to know about the `sa` password in a container: - The `MSSQL_SA_PASSWORD` value stays in the container's configuration, where anyone who can run `docker inspect` on your machine can read it. Disabling `sa` makes that value useless. If you keep `sa`, change its password with `ALTER LOGIN sa WITH PASSWORD = '';`. - SQL Server uses `MSSQL_SA_PASSWORD` only when it sets up an empty data volume. If you re-create the container on an existing volume with a different value, the old password stays in effect. ## Starting and stopping, and where the data lives With Docker, stop and start the container by name: ```bash docker stop mssql docker start mssql ``` The databases are stored in the `mssql-data` volume, mounted at `/var/opt/mssql` in the container. They survive stopping, starting and re-creating the container. Right after the container starts, SQL Server may still be recovering the databases, and a login whose default database isn't online yet fails with `Cannot open user default database`. Wait a few seconds and try again. On Linux, SQL Server runs as the `mssql-server` systemd service. Stop and start it with `sudo systemctl stop mssql-server` and `sudo systemctl start mssql-server`. The databases are stored under `/var/opt/mssql`. On Windows, SQL Server runs as a Windows service. You can stop and start it in [SQL Server Configuration Manager](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-configuration-manager). The databases are stored in the data directories that you chose during setup. ## Removing SQL Server To remove the Docker container, its data volume and the image: ```bash docker rm -f mssql docker volume rm mssql-data docker rmi mcr.microsoft.com/mssql/server:2025-latest ``` `docker volume rm` deletes all databases in the volume permanently, so back up anything you want to keep first. On Linux, remove the package with `sudo apt-get remove mssql-server` on Ubuntu or `sudo yum remove mssql-server` on RHEL. [Removing the package keeps the database files](https://learn.microsoft.com/en-us/sql/linux/install-upgrade/setup#uninstall). To delete them as well, which can't be undone, run `sudo rm -rf /var/opt/mssql/`. On Windows, follow Microsoft's guide to [uninstall an instance](https://learn.microsoft.com/en-us/sql/sql-server/install/uninstall-an-existing-instance-of-sql-server-setup) from the **Apps** section of **Settings**. ## FAQ **How do you check your SQL Server version?** Run `SELECT @@VERSION;` in `sqlcmd` or any other client, as shown [above](https://www.prisma.io/dataguide/mssql/setting-up-a-local-sql-server-database#creating-a-database-and-a-least-privilege-login). It returns the version, the cumulative update, the edition and the operating system. For just the version number and the edition, query `SELECT SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('Edition');`. Version 17 is SQL Server 2025, and version 16 is SQL Server 2022. **How can you download SQL Server for free?** The Developer editions and Express are free. Download them from [Microsoft's SQL Server downloads page](https://www.microsoft.com/en-us/sql-server/sql-server-downloads) for Windows, install them from Microsoft's package repositories on Linux, or run the container images, which start the Enterprise Developer edition by default. **What is the SQL Server Developer edition?** A Developer edition has all the features of a paid edition for free, but it's licensed for development and testing only. See [choosing a version and edition](https://www.prisma.io/dataguide/mssql/setting-up-a-local-sql-server-database#choosing-a-version-and-edition) for SQL Server 2025's Enterprise Developer and Standard Developer editions. **Can you run SQL Server on a Mac or on an ARM computer?** Not natively: SQL Server only runs on x86-64 processors. On an Apple Silicon Mac, you can run the x86-64 container image with a Docker runtime that uses Rosetta 2, as described in the [Docker section](https://www.prisma.io/dataguide/mssql/setting-up-a-local-sql-server-database#apple-silicon-and-other-arm-machines). Microsoft doesn't support running SQL Server under emulation, so use it for development only. **Is Azure SQL the same as SQL Server?** [Azure SQL](https://learn.microsoft.com/en-us/azure/azure-sql/azure-sql-iaas-vs-paas-what-is-overview) is Microsoft's family of cloud products that use the SQL Server database engine. SQL Server on Azure Virtual Machines is SQL Server itself, running on a virtual machine that you manage. Azure SQL Database and Azure SQL Managed Instance are managed services: they share most features and T-SQL with SQL Server, but they aren't the same product, and some features and administrative tasks differ. **What is the SQL Server Configuration Manager?** [SQL Server Configuration Manager](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-configuration-manager) is a Windows tool that's installed with SQL Server. It manages the SQL Server services and the network protocols that the server accepts connections on, such as TCP/IP. SQL Server on Linux doesn't have it; use `systemctl` and `mssql-conf` there instead.