Connect SQL Server Management Studio|SSMS to Docker Container
Posted January 15, 2024
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.
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:
# SQL Server container
# Use the SQL Server Docker image from Microsoft
# Add the container name
# Set environment variables for SQL Server configuration
- 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
# Volume to persist SQL Server data
# Define the volume
Here you have:
image: mcr.microsoft.com/mssql/server:latestto get and run the last MSSQL Docker image.
ACCEPT_EULA=Ywill 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,
- 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,1433as 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@passwordenvironment variable. Therefore, you will use
Test@passwordas 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,
-- Insert Rows
INSERT INTO Person (ID, Name, Age) VALUES
(1, 'John Doe', 25),
(2, 'Jane Smith', 30),
(3, 'Bob Johnson', 22);
-- 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:
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.