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

[drewclauson]

I've got a docker container running that I'm unable to access an external SQL Server from. I'm using sqlsrv-4.1.6.1, PHP 7.1.

When trying to connect via PHP, the error message returns as:
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

I am able to connect to the SQL server on the docker host machine from the host and also from another windows machine via SSMS. The docker host machine is Windows 10 64bit.

When I try to telnet from the container to the host IP address, I get:
Trying 10.20.86.176... telnet: Unable to connect to remote host: Connection refused

Using telnet to get to ports like 80 or 22 are successful, since both ports are open and working on the docker host.

Any ideas? Can post my DockerFile if that's helpful

[drewclauson]

Um... Sorry, I just found a section of documentation I missed... I'll try that and reopen if it doesn't fix it...

[drewclauson]

Ok, went through the docs and still getting the exact same message.

[drewclauson]

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

[chucky2305]

@drewclauson: Is there a faster way i can get back to you on this problem? I want to setup my project to connect to Mssql too

[drewclauson]

@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]

Well... I should have searched the issues. #369 refers to here which shows that the port must be specified and it obviously applies in my scenario.

[chucky2305]

Can you share your dockerfile? i have no clue how to get my php running with extension etc for mssql.
@drewclauson
Everything i try is not working.

[drewclauson]

@chucky2305 Take a look here 😄

[gitfortee]

@drewclauson , @chucky2305 the suggestion here helped me solve the same issue i was experiencing.. in my case, specifying the hostnamein 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

[DanielD]

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 :1433) ---> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException: Connection refused :1433

[fmmajd]

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]

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=" -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.

[rdalfonso]

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

[joelGarcia93]

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?

[Kriti86]

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=" -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

[woeterman94]

I'm having the same issue I can't connect from my .NET core application inside my docker container to the SQL server. The SQL server is not running inside a docker container.

It works fine when I run the .NET core application without docker, but somehow inside docker it behaves differently? I tried using the IP address, adding the port etc... nothing seems to work.

Anyone who can help me out?

[eduherminio]

After facing this problem, first thing I would do is ensure that connectivity is the real problem by setting the connection string 'internally' instead of trying to pass it to the container through an environment var.

[woeterman94]

I did that. Connection string is hard coded as a string in my solution. This is not the problem.

[woeterman94]

Here is my error:

The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=51; handshake=391; [Login] initialization=0; authentication=5; [Post-Login] complete=14017; ---> System.ComponentModel.Win32Exception (0x80004005): Unknown error 258\n at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling)\n at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)\n at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)\n at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)\n at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)\n at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)\n at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)\n at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)\n at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)\n at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)\n at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry)\n at System.Data.SqlClient.SqlConnection.Open()\n at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)\n at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.BufferlessMoveNext(Boolean buffer)\n at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded)\n at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.GetResult[TResult](IEnumerable1 valueBuffers, Boolean throwOnNullResult)\n at lambda_method(Closure , QueryContext )\n at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass17_11.<CompileQueryCore>b__0(QueryContext qc)\n

[keeratsingh]

@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

[deathstar1]

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

[ninjacoder88]

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
@fmmajd Could you elaborate what you mean "changed the hostname"? What host name, in your connection string? How did you change it?

[Bongkerz]

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.

[saitejaprattipati]

I am having issue where i am not able to connect to ssms which is in host machine from asp.net core application running in docker container. My connection string is Server=,;Database=Demodb;Integrated Security=True; I am getting error as server not found. any help will be appreciated.

[deathstar1]

Is your server by name ','(comma) ? Did you mean '.'(dot) ?

[saitejaprattipati]

This is my connection string Server=ipaddres,port;Database=Demodb;Integrated Security=True;

[deathstar1]

Use full domain name space and followed by instance name .
Are you able to connect it directly from SQL Server

[liben-y]

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.

[pavlovmilen]

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;

[nicedayesc]

@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';

thank you

[aeslinger0]

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

[ririvas]

I've ran into a similar problem as few have in this thread. Google took me here repeatedly so, I'll comment my problem here.

I am getting the error mentioned in this thread from a container running a service attempting to connect to SQL. And I am also getting this error when running bash in the container running SQL and attempting to connect via sqlcmd.

sudo docker exec -it <mssql container> "bash" # allows me "remote" into container
$
/opt/mssql-tools/bin/sqlcmd -S <IP>-U SA -P "<YourNewStrong@Passw0rd>" # lets connect

The above test does work when specifying localhost. It does not work when attempting to connect via the host IP.

• Specifying the port did not help
• Nor using docker ip addresses or the host ip address

Exploring with Telnet

In an attempt to narrow down the problem. I began running telnet from within the container and the host.
From within the container

• Telnet can connect to an arbitrary external server on port 1433
• Telnet can connect to the host's IP on port 22
• Telnet cannot connect to the host's IP on port 1433

From the host

• Telnet can connect to the host's IP on port 1433

IPTABLES

An investigation into the rules docker setup on the iptables, I found the following

-A DOCKER -d 172.17.0.2/32 ! -i docker0 -o docker0 -p tcp -m tcp --dport 1433 -j ACCEPT
# [! -i docker0] 
# this sections says allow connections coming from network interfaces that are not docker0

These rules are implementation details managed by Docker, so perhaps I have faulty configuration on my containers (e.g. relying on the default bridge on the docker network)

I'm not confident this is a smoking gun in anyway. But hopefully, someone more familiar with the subject matter can rule this out or further identify the problem.

[IamTonyZHOU]

My problem is due to an VPN, ulse Connect MX, it works after close the VPN.

[woeterman94]

As keeratsingh mentioned: try upgrading your SQL server version, as this is most likely a TLS version problem.

[ririvas]

As reference to my prior comment, the issue for me was a misunderstanding of docker networks. The default bridged network created a rule that did not allow docker instances to access the forwarded 1433 port. In order to get my docker instances talking to each other, I had to use the docker bridge network IP associated with the containers.

The port forwarding done for 1433 to the docker network did work as I expected.

[Justin-Lessard]

Was able to reproduce using the following .NET Core 3.1 app running on the mcr.microsoft.com/dotnet/core/runtime:3.1-buster-slim image.

    static void Main(string[] args)
    {
        using var connection = new SqlConnection("...");
        connection.Open();
    }

The program hanged on the connection.Open() line when trying to connect to SQLSERVER 2012 SP3 or 2012 SP4.

Absolutly no issue for anything 2016 and above.

[cheenamalhotra]

@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.

[manikanth]

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.

This helped me, for me it was 192.168.0.108

[mcs6502]

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

[lucasthim]

I'm trying to connect to an SQL Server that is also inside another Docker container.

In my case, I put together the comments of @Kriti86 and @GrahamUndone and it worked! I configured a simple network for docker and checked my SQL server container IP. Then, I used that IP as the value for SERVER. It is important to say that I did not use any port number, just the IP address.

[sulaimanhayek]

For me it worked by setting the network mode to bridge
network_mode: bridge
and the server :
host.docker.internal,[port]

[ElectricLlama]

Not necessarily directly related but.... I couldn't get the openhack/data-load container to load my SQL container with data. It couldn't connect (Login timeout). The only hostname that worked was host.docker.internal Nothing else worked (localhost, 0.0.0.0, the containers internal IP, the containers internal host name). I also had to map my sql server container to a new port since I already had one running.

So in summary, I had to;

# Create the SQL container. Use -p to map external 5001 to internal port 1433
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=YoullNeverGuessThisOne" --name sql1 -h sql1 -p 5001:1433 -d mcr.microsoft.com/mssql/server:2019-latest

# This was the only syntax that could connect. I'm not sure if --network bridge is still required here
docker run --network bridge -e SQLFQDN=host.docker.internal,5001 -e SQLUSER=SA -e SQLPASS=YoullNeverGuessThisOne -e SQLDB=mydrivingDB openhack/data-load:v1

[anserwaseem]

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