Connect SQL Server Management Studio|SSMS to Docker Container

If you have a running SQL server container running on Docker, you need to connect with SQL Server Management Studio (SSMS) to manage MSSQL. However, Microsoft doesn’t have any official SQL Server Management Studio (SSMS) Docker image.

This means we can’t run both MSSQL and SSMS as Docker containers. SSMS connects to SQL Server instances. Microsoft has an SQL Server Docker image for running SQL Server in containers. Now, to connect this MSSQL container to SQL Server Management Studio, you can only run SSMS on your local machine.

Dive into this guide and learn how to connect to a SQL Server instance running in a Docker container and access it using Microsoft SQL Server Management Studio.

Step 1: What you Need to Connect to Docker SQL Server Container from SSMS

Before attempting to connect SQL server management studio to the Docker container you need:

• A Running Microsoft SQL Server Docker Container.
• SSMS should be installed and ready.

Related: How to Run Microsoft SQL Server|MSSQL Express on Docker

If you don’t have a running SQL Server Docker Container, this guide will help you create it and then show you how to connect it to SSMS.

Step 2: Running Microsoft SQL Server on Docker

Ensure Docker is installed and running on your machine. Now, you will use Docker compose to run a Microsoft SQL Server container on Docker. Go ahead and create a docker-compose.yml file and add the following instructions:

version: '3.9'

services:
  # SQL Server container
  mssql-container:
    # Use the SQL Server Docker image from Microsoft
    image: mcr.microsoft.com/mssql/server:latest
    # Add the container name
    container_name: mssql-container 
    # Set environment variables for SQL Server configuration
    environment:
      - ACCEPT_EULA=Y   # Accept the End-User License Agreement
      - SA_PASSWORD=Test@password # Set the 'sa' user password
    # Map host machine port 1433 to container port 1433 for SQL Server
    ports:
      - "1433:1433"
    # Volume to persist SQL Server data
    volumes:
      - mssql-container-data:/var/opt/mssql
# Define the volume
volumes:
  mssql-container-data:

Here you have:

• image: mcr.microsoft.com/mssql/server:latest to get and run the last MSSQL Docker image.
• ACCEPT_EULA=Y will be the End-User License Agreement within your container.
• SA_PASSWORD=Test@password Create the password for the ‘sa’ (system administrator) user. The same password will be used to connect Docker MSSQL to the SQL server management studio.
• MSSQL will run on port 1433. You have this port allocated to another service, change it. For example, "1439:1433".
• The set volumes will make sure you persist your MSSQL data. When you connect to the docker SQL server from SSMS, any database data you create will persist even if you stop the container.

Run the following command to get the MSSQL ready:

docker-compose up --build -d

Once you confirm the MSSQL container is running, it is time to use the SQL server management studio and connect to this Docker container.

Step 3: How to Connect to SQL Server Docker Container using SSMS

Remember, SQL Server Management Studio is installed on your computer. For SSMS to connect to the SQL Server, it must use the details of the running SQL Server container. This means, to connect to the SQL server docker container using SSMS, you need the following:

• The server name.
• The server username and password.

Now, based on the MSSQL container we are running, you will have:

• localhost,1433 as the server’s name. If you used a different port, change it as such.
• The container is running using the system administrator user, the Username will be ‘sa’.
• Password is specified using the SA_PASSWORD=Test@password environment variable. Therefore, you will use Test@password as the password.

On your computer, open your SQL Server Management Studio and connect to the MSSQL docker container using the above details.

Make sure you have SQL Server Authentication selected as follows:

Click Connect and access your Docker SQL Server with MSSQL:

Step 4: Testing the Connected SQL Server Management Studio Docker Container

To confirm that indeed SSMS has successfully connected to Dockerized MSSQL, let’s run some SQL queries and test this connection:

Execute a new SQL Query to the test database to add a new table, and some records and select them using the following script:

-- Create Table
CREATE TABLE Person (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50),
    Age INT
);
GO

-- Insert Rows
INSERT INTO Person (ID, Name, Age) VALUES
(1, 'John Doe', 25),
(2, 'Jane Smith', 30),
(3, 'Bob Johnson', 22);
GO

-- Query the Data
SELECT * FROM Person;

If the Query works as expected, then you have successfully connected your SQL server management studio to the MSSQL docker container:

Conclusion

This guide helped you learn how to connect to the docker SQL server from SSMS. I hope you found these steps helpful and that you can correctly run your MSSQL tasks with Docker.