13

I have a container hosted ASP.NET Core application but it can't connect to SQL Server on a remote server.

The error is:

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.

This is what I already checked:

  • Try to disable firewall on host machine and DB server. : still get the error
  • Edit connection string to use IP and port : still get the error
  • Ping from application container to DB server: I can ping to DB server normally
  • Connect to database server via SQL Server Management Studio: I can connect normally

So I think that the container can see the DB server but can't connect. What's the other thing should I check?

Thank you very much for your help.

Update

Dockerfile:

FROM mcr.microsoft.com/dotnet/core/aspnet:3.0 AS base
WORKDIR /app
EXPOSE 80
EXPOSE 1433

#our sql server was use this port for connect
EXPOSE 64608

FROM mcr.microsoft.com/dotnet/core/sdk:3.0 AS build
WORKDIR /src

COPY Web.API.sln ./
COPY MyProject.Core/*.csproj ./MyProject.Core/
COPY MyProject.API/*.csproj ./MyProject.API/

RUN dotnet restore
COPY . .

WORKDIR /src/MyProject.API
RUN dotnet build -c Release -o /app

FROM build AS publish
RUN dotnet publish -c Release -o /app

FROM base AS final
WORKDIR /app
COPY --from=publish /app .
ENTRYPOINT ["dotnet", "MyProject.API.dll"]

Run Docker Command:

docker build -t myproject-api:latest .

docker run -d -p 7991:80 --name myproject-api myproject-api:latest

Connection String:

"data source=172.16.0.88\\SQL_DEV,64608; initial catalog=MyProject; persist security info=True; user id=myuser; password=mypassword;"
LLF
  • 668
  • 3
  • 10
  • 27
  • Assuming the remote SQL Server is a default instance listening on port 1433, check remote port connectivity from inside the container with `powershell -Command echo ((new-object Net.Sockets.TcpClient).Client.Connect('your-database-server', 1433)) 'success'` – Dan Guzman Nov 17 '19 at 14:23
  • Post the Docker files and the command you used to run the containers. – Max Nov 17 '19 at 14:36
  • @DanGuzman Thank you for suggestion. I'm quite new for Docker. I will try it after I find out how to run powershell command in my container. – LLF Nov 18 '19 at 03:34
  • @Max I've updated docker file and command on post. Thank you. – LLF Nov 18 '19 at 03:35
  • @LLF try removing all the `EXPOSE 1433, EXPOSE 64608`. these are not needed. Can you provide the settings, how are you creating the connection string? – prisar Nov 18 '19 at 03:58
  • @prisar, thanks for your reply. I've updated my connection string on the post. and EXPOSE is something I add to try after I found that my container can't connect to SQL server. I also try to remove it but it didn't help. – LLF Nov 18 '19 at 04:19
  • You have probably checked this already, but just in case: The container will try to connect using tcp/ip so make sure that the sql server configuration manager shows tcp/ip as enabled and that the TCP/ip properties shows it as listening on all ip addresses (‘listen all’ is set to yes) – camba1 Nov 18 '19 at 07:12
  • @camba1 I already check it. "Listen all is set to Yes" and "IP All dynamic port is 64608" – LLF Nov 18 '19 at 08:54
  • @DanGuzman I don't know how to use powershell from container. So I write some function in my aspnet core like this https://stackoverflow.com/questions/11837541/check-if-a-port-is-open and I found that it can show the port is open. I still confuse that it can see db server and can connect to the port but why it can't connect. – LLF Nov 18 '19 at 09:38
  • @LLF how did you check for tcp/ip settings for the sql server instance running as container? – Golide Jun 28 '21 at 09:39

3 Answers3

4

This is most probably because of the connection string.

Change your connection string to something like below

Server=172.16.0.88\\SQL_DEV,64608;Database=MyProject;UserId=myuser;Password=mypassword

or

Server=172.16.0.88,64608;Database=MyProject;User Id=myuser;Password=mypassword

Which ever works.

prisar
  • 3,041
  • 2
  • 26
  • 27
  • This is my connection string. Is there any thing strange? "data source=172.16.0.88\\SQL_DEV,64608; initial catalog=MyProject; persist security info=True; user id=myuser; password=mypassword;" – LLF Nov 18 '19 at 04:10
  • remove `persist security info` – prisar Nov 18 '19 at 04:42
  • are you able to connect in debugging mode in visual studio – prisar Nov 18 '19 at 04:55
  • It work perfectly fine on vs. or deploy it directly on iis. And did you just remove instance name in your edit? – LLF Nov 18 '19 at 05:00
  • i am not so sure about instance name. try both with instance name and then wothout it. copy from my answer and just replace them with your db name userid and password – prisar Nov 18 '19 at 05:02
  • Linux or windows container ? If linux connect to the container and check with VIM if the appsettings.config has the correct line ending. Within the code try to write down to a file the connectionString before connecting. – Max Nov 18 '19 at 09:01
  • @prisar already try to edit connectionstring but it didn't work either vs or docker. I think it because my database was in SQL_DEV instance. – LLF Nov 18 '19 at 09:12
  • @prisar Thank you very much for your help. Finally I found that you entirely correct after try to add instance on your connection string. It seem aspnetcore that run on linux container need to use different connection string format. for more information: https://stackoverflow.com/questions/55454878/invalid-sql-server-connection-string-on-linux-entity-framework-core – LLF Nov 18 '19 at 15:08
  • @LLF what was your final connection string? I am having the same issue – Adam Polak Moetsi Aug 15 '22 at 20:08
2

I had a same problem and I changed my Dockerfile image

I replace It

FROM mcr.microsoft.com/dotnet/core/aspnet:3.1-buster-slim AS base

with this line

FROM mcr.microsoft.com/dotnet/core/aspnet:3.1-bionic AS base

Now It works !

Saeed
  • 3,415
  • 3
  • 24
  • 41
1

The only solution that seemed to work for me is to put the sql server container and the .net container in the same docker network

https://github.com/microsoft/msphpsql/issues/302#issuecomment-361439294

Adam Polak Moetsi
  • 493
  • 4
  • 8
  • This is a workaround. The problem here is that the two system are indipendent and part of two different networks and level of isolations. – RobyB Sep 27 '22 at 13:30