71

Context: I am trying to build a development SQL Server that I can continue to learn SQL on and use Microsoft SQL Server Management Studio (SSMS) to access on a Windows PC.

So I have the AdventureWorks database sitting on a Docker Container for MS SQL Server 2017 running on a DigitalOcean Ubuntu 16.04 box. From my Mac I can remote SSH in to the server, access the container and query the database.

However I wish to use SSMS on my Windows PC and am unsure how I begin to connect to the remote box. In the picture below, there are no options to specify an SSH key or to even login in to the Ubuntu box, only to access the SQL server.

enter image description here

Is this even possible?

noobmaster69
  • 2,985
  • 3
  • 27
  • 44
  • Replace the server name with the public IP address of your droplet, make sure 1433 port is open, provide credentials if necessary and then try again. – Janshair Khan Dec 27 '17 at 05:22
  • @JanshairKhan how do you provide credentials through SSMS? – noobmaster69 Dec 27 '17 at 09:06
  • @JanshairKhan I meant to say how do I provide server credentials through SSMS? – noobmaster69 Dec 27 '17 at 09:14
  • Use SQL Server Authentication with a username and password to provide credentials. – Janshair Khan Dec 27 '17 at 09:44
  • That's credentials to the SQL server though as opposed to credentials for the Ubuntu server which would typically be provided by SSH. How I see it I need to do a double authentication: SSH into the box then use SQL. But not sure how that works in SSMS. – noobmaster69 Dec 27 '17 at 13:23
  • 3
    If you exposed the SQL Server service to the Internet, you don't need SSH to be able to connect to the SQL Server via SSMS. This is why you should use Authentication by providing the public IP address of the server. – Janshair Khan Dec 27 '17 at 13:31
  • What happens when you pressed Connect here? That is exactly how you connect, as long as the constraints in the very first comment are satisfied – Nick.Mc Jan 05 '18 at 02:30

9 Answers9

77

connect via SSMS using the public IP address, followed by comma separator and then the port (xxx.xx.xx.xxx,port)

You'll also need the sa credentials to make this work.

paul stanton
  • 936
  • 7
  • 7
  • Thanks Paul. The issue I had was that whilst I had exposed the port on the IP, I was using the public port to try to connect SSMS to. Infact, it was the private port which seemed to work. – noobmaster69 Jan 05 '18 at 08:42
  • Thanks it works very well. Why coma and not colon? – zolty13 Oct 27 '21 at 04:59
  • Thanks, it worked like a charm. Did not know about the comma separator, I was trying with colons instead haha – jvbs Nov 01 '21 at 13:53
73

In your case, Server Name input will become Server IP, port#

Remote setup image

No need of ssh, you just need to expose SQL Server service to the internet.

When Using SQL Server Management Studio to connect to the docker container on local machine, you can use localhost ip. In that case Server Name input will become 127.0.0.1,port#

To clarity, I attached image of my SSMS Connection Dialog

DevLoverUmar
  • 11,809
  • 11
  • 68
  • 98
  • guys I've got a sql server running on a remote server, but I've got a problem to access the remote server I access with ip xxx.xxx.xxx.xxx:3333, it already has a port in the ip, how can I access to the 1433 port of sql server? – Manuel Gonçalves Apr 23 '20 at 22:25
  • It worked to connect with IP 127.0.0.1 for a local docker container. However, if you have an instance of SQL server running on your computer, you are not going to be able to connect. I just changed the exposed and mapped port from 1433 to 11433 and connected to 127.0.0.1,11433 with user sa and password. – Dragos Durlut Dec 08 '21 at 14:55
  • `localhost` worked for me – Mr Patience Sep 14 '22 at 17:19
11

Following instruction of this site solved it for me on Windows Containers: https://www.sqlshack.com/sql-server-with-a-docker-container-on-windows-server-2016/ It was possible to connect to SQL Server instantly from SSMS.

Give it a try if this is also working on Linux containers with this command:

docker run --name mssqltrek-con1 -d -p 1433:1433 -e sa_password=My$eCurePwd123# -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

After running this you can retrieve the correct ip with:

docker inspect -f "{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}" mssqltrek-con1
Bart VdA
  • 578
  • 1
  • 5
  • 15
7

In my case it was the Cisco VPN that was blocking the host to connect to container IP. I had to uncheck the "Cisco Any Connect Network Access Manager" as shown in the image below for it to work. It ensures that the VPN no longer manages the connection.

enter image description here

Aashish Upadhyay
  • 840
  • 1
  • 7
  • 22
6

You can connect to SQL docker server

  1. Using IP of Machine on which docker image is hosted,port

IP_Of_Machine,Port

  1. Provide User - sa (default) and password.

  2. you can also do it command>

docker exec -it <container_id|container_name> /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <your_password>

Vivek Raj
  • 353
  • 3
  • 3
5

This isn't exactly the same problem as the OP's but I created a sql docker container and then couldn't log in to it from SQL Server Management Studio. It turned out that the sa password I used wasn't secure enough. Once I deleted my container and recreated one with a more secure password it let me connect to it from SQL Server Management Studio. If you do not specify a strong enough password it still creates the container OK - you just can't log in to it! Hopefully this will save someone some time.

David Christopher Reynolds
  • 1,361
  • 1
  • 18
  • 37
4

I benefited from the answers on this page, however, i had to go through my own tweak. for some reason, in my case, it didnt accept localhost,1433 but it did accept 127.0.0.1,1433

Raghav
  • 2,128
  • 5
  • 27
  • 46
4

In my case I was using docker-compose and was unable to connect. I fixed this by explicitly specifying the port in the docker compose file.

mysqldb:
    ports:
      - "1433:1433"
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=SuperSecretPassword!23
    image: registry/mysqlimage
rysama
  • 1,674
  • 16
  • 28
0

I also faced same issue when I try to connect through SSMS, even I tried with localhost,1433 it doesn't help me out. enter image description here

After some R&D I found the way to solve it

  • Open Kubernetes which will be installed as part of docker toolbox (mine was win 10 home which will not support hyper-v)
  • click on sql container which will be in left side of app
  • copy the IP address which will be in right side of app
  • use IP address with port like XXX.XXX.XX.XX,1433 along with credentials in SSMS

enter image description here

user2351143
  • 11
  • 4