msphpsql: Unable to connect to SQL Server on host from docker

Rather than use IP addresses, which would be problematic in a team environment, you can also use host.docker.internal which will resolve to your host IP.

Data Source=host.docker.internal,1433;Initial Catalog=MyDB;User ID=MyUser;Password=MyPassword

@chucky2305, @Hadis-Fard I actually figured it out using port 1433 and then forgot to post back here. I had to set the connection string to <instance address>,1433 and it worked. I felt it was odd that it required to explicitly set the port. Maybe the docs should be updated to reflect this or I just missed it? DATABASE__DB_SERVER_NAME = '192.168.56.1\sqlexpress,1433';

@drewclauson , @chucky2305 the suggestion here helped me solve the same issue i was experiencing… in my case, specifying the hostname in the connection string did not work. only when i specified the <ip-address>,<portnumber> it worked.

here is how the conn’string look like: Con.String: Server=<Ip-address>,1433;Database=<Database Name>;Integrated Security=False;User Id=<userId>;Password=<password>;MultipleActiveResultSets=True

and then docker run -t mywebapp:demo

Im doing this on a Mac. Open Terminal.

Create a container for sql server to run in, assign a port (1433) and give it a name (sql1).

$ docker run -e “ACCEPT_EULA=Y” -e “MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>” -p 1433:1433 --name sql1 -d microsoft/mssql-server-linux:2017-latest

create a container for your web app, in my case I’m using Visual Studio for Mac and added Docker Support to my projects, so docker-compose.yml actually creates the containers. I added the container_name in order to create named instance to add to a network, and it looks like this:

version: ‘3’

services:

coresecuretest: image: coresecuretest container_name: web build: context: ./CoreSecureTest dockerfile: Dockerfile

api: image: api container_name: api build: context: ./Api dockerfile: Dockerfile

Next I create a network:

Example: docker network create [network name]

$ docker network create simple-network

Then I connect the containers to the network:

Example: docker network connect [network name] [container name]

$ docker network connect simple-network sql1 $ docker network connect simple-network web $ docker network connect simple-network api

Then I inspect the network in order to get the ip of sql1

$ docker network inspect simple-network

---

//// “d7941e4ea63426b9da8743ee167c6a2c7437a91ac0b49eeb11c54056e4016324”: { “Name”: “sql1”, “EndpointID”: “3afd073d4dabd5c375eb9117546c30884cd1b9d844fbdb66cb69957679f4936f”, “MacAddress”: “02:42:ac:13:00:02”, “IPv4Address”: “172.19.0.2/16”, “IPv6Address”: “” }

and use the IP (172.19.0.2) for my connection string, in my case its a sql connection using the port specified when I created sql1 container:

“Server=172.19.0.2,1433;Database=My-Database;User=sa;Password=@Pa55word;”

To ensure all containers are running:

$ docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES ca8fed177295 coresecuretest:dev “tail -f /dev/null” 2 days ago Up 2 days 0.0.0.0:32775->80/tcp web a08310acf7a0 api:dev “tail -f /dev/null” 2 days ago Up 2 days 0.0.0.0:32772->80/tcp api d7941e4ea634 microsoft/mssql-server-linux:2017-latest “/bin/sh -c /opt/mss…” 2 days ago Up 35 minutes 0.0.0.0:1433->1433/tcp sql1

In Sql Operations Studio (I’m using a Mac, Windows users SSMS) I use “localhost/sql1,1433” as server name to connect to this instance of Sql Server.

Hope this helps. It took me a few hours to stumble upon the answers.

I had the same problem. I used a docker container to connect laravel to SQL server on mac, and it just didn’t connect. the problem was, port 1433 of the container was NOT ported to localhost:1433, but to 0.0.0.0:1433. Changed the hostname to 0.0.0.0 and it worked. you can see the hostname by typing this: docker port YOUR_CONTAINER_NAME

@GrahamUndone - I’m having the exact same issue as you. Are you using Entity Framework?

None of those suggestions are working. I get this error regardless of the application I am using or building: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 35 - An internal exception was caught) —> System.AggregateException: One or more errors occurred. (Connection refused <ip address>:1433) —> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException: Connection refused <ip address>:1433

I had a similar issue. solved it by checking my windows host file and found an internal docker IP 10.xxx.xx.xx host.docker.internal

Used this ip to connect using the port i had defined (1432) and was able to connect without any further issues.

Hope this helps.

was struggling with this for hours, for me just changing localhost to 127.0.0.1 fixed it!

Let me share my experience as well - I had the same issue. How I resolved it:

1. SQL Server configuration manager go to SQL Server Network Configuration -> Protocols for {your instance name}
2. Select TCP/IP (ensure it is enabled) and select Properties.
3. Ensure Listen All is set to yes and then in the IP Addresses tab ensure that you have IP address that is Active and Enabled (192.168.70.179 for example) and make note of port number (1433 by default)
4. In your docker / docker compose file use this connection string: Server=192.168.70.179,1433;Database=YourDatabaseName;User Id=YourUser;Password=YourPass;

Managed to get it working with the following to workaround:

I had SQL Server running on windows with a linux container on the same machine trying to connect. I tried quite a lot of things suggested here and stack overflow but had no luck.

The error I was getting was a bit different to the others: Unhandled Exception: System.AggregateException: One or more errors occurred. (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server)) —> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server)

I could ping my machine name from a container so I knew that was not the issue.

Looking at the error more closely it looked like something to do with TCP, so I white listed TCP/UDP on ports 1433,1434. Which still didn’t work.

I then checked to see if remote connections were enabled on the database and it was already enabled.

I then checked Sql Server Configuration Manager -> Sql Server Network Configuration -> Protocols for MSSQLSERVER -> TCP/IP and it was disabled. I enabled it and tried again and it worked.

So I didn’t need to add the port with a comma or use ip address…

Hope this helps someone.

Using Docker to run sql server 2017 container image. Container image for sql server running successfully. I still get the following error when trying to create a connection in SQL ops studio

Error : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)

created container with the following cmd $ docker run -e “ACCEPT_EULA=Y” -e “MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>” -p 1433:1433 --name sql1 -d microsoft/mssql-server-linux:2017-latest

created network and linked network name with container name. I tried the following server names to connect to the server and failed to connect. 0.0.0.0,1433 sql1,1433 localhost/sql1,1433 172.19.0.2,1433

Rather than use IP addresses, which would be problematic in a team environment, you can also use host.docker.internal which will resolve to your host IP.

Data Source=host.docker.internal,1433;Initial Catalog=MyDB;User ID=MyUser;Password=MyPassword

Thanks alot. I’ve been stuck on this problem from quite some days. Tysm

@Justin-Lessard

Your issue relates to a known issue: dotnet/sqlclient#201. Please ensure you update SQL Server to support TLS 1.2 as docker clients do that by default now, in order to work.

Watch the issue for more user experiences and how they resolved this problem.

I had a similar problem with sqlcmd, trying to connect to sqlserver running inside a container in docker-machine on macOS. The solution was to use the IP address of docker-machine’s default instance (192.168.99.100), not localhost as I’d initially assumed. Just thought I’d mention it here for those who find this issue via a search engine.

Details: I was getting a misleading “Login timeout expired” (error code 0x2726) attempting to run “sqlcmd -S localhost -U SA -P $SA_PASSWORD” from macOS. Port 1433 was mapped from the host to sqlserver container, as instructed. A manual “nc localhost 1433” check from macOS resulted in “Connection refused” error. The “docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost,1433 -U SA -P $SA_PASSWORD” check was successful. The solution came with a realisation that sqlserver container runs inside docker-machine (with port forwarding between the two), but there is no forwarding from macOS host to docker-machine on tcp 1433 by default (to set up this forwarding, use the VBoxManage tweak for docker-machine, which was mentioned elsewhere).

$ docker-machine inspect default |grep IPAddress “IPAddress”: “192.168.99.100”, $ sqlcmd -S 192.168.99.100 -U SA -P $SA_PASSWORD 1> exit

actually Ipaddress helped but after i removed tcp port 1433 , it worked , For those facing this issue

@woeterman94 We have had a similar issue https://github.com/dotnet/corefx/issues/9719 at the .NET Corefx Repo. As per comments from the users the issue was resolved after updating to SQL Server 2008 R2 SP3 or SQLServer 2008 SP4.

https://github.com/dotnet/corefx/issues/9719#issuecomment-233425704

Upgrading the server to 2008 R2 SP3 resolved my issue, thanks!

https://github.com/dotnet/corefx/issues/9719#issuecomment-277327056

For what it’s worth, I was also running SQL Server 2008 SP3 and had connectivity issues. I upgraded to SP4 and I was able to connect just fine.

https://github.com/dotnet/corefx/issues/9719#issuecomment-356674773

We were having the same problem here in the company, it was resolved after we made upgrade to SP4

Since, I am not sure what version of SQL Server you are using, could you try the versions mentioned above or any SQLServer version later than the ones mentioned above and revert back. If the issue still exists, kindly create an issue at https://github.com/dotnet/corefx/issues

hello @GrahamUndone what did you do in this section? I didn’t understand very clear. I’m using entity framework

“I added the container_name in order to create named instance to add to a network, and it looks like this:”

version: ‘3’

services:

coresecuretest: image: coresecuretest container_name: web build: context: ./CoreSecureTest dockerfile: Dockerfile

api: image: api container_name: api build: context: ./Api dockerfile: Dockerfile

What did you do there? is the only step I have to finish because I still get the error in Visual Studio for Mac. I get this

“A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server)”

What Did I do?

• I created a docker container as you said
• I Created the network and linked the container with it.

when I run docker network inspect simple-network

I can see the container with the IP as you said in the above comment, but when I try to connect from Visual Studio for Mac I can’t.

Some help?

So, somehow my container can’t access 1433 on outbound connections, so I moved SQL to port 22 and it connects.