[SOLVED] Connect to SQL Server image using a connection string

Issue

I’m trying to connect to SQL Server Image in .NET using a connection string. So at first, I ran my sql-server using a command like this:

docker network create sql-netwrok 
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Arman123!" -p 5435:1433 --net sql-network -d mcr.microsoft.com/mssql/server 

After running my sql-server, I wrote my application Dockerfile like this:

FROM mcr.microsoft.com/dotnet/sdk:5.0 AS build-migration
WORKDIR /app
COPY *.csproj .
RUN dotnet restore

COPY . . 
WORKDIR /app
ENV CONNECTION_STRING="server=<container-name Or IP>,5435;database=Test;User Id=sa;Password=Arman123!;"
RUN dotnet publish -c Release -o out
EXPOSE 88
CMD dotnet run

Then, I tried this command in order to build and run my Dockerfile:

docker build -t migration-sample .
docker run -it --net sql-network migration-sample

But after runnig the application I keep getting this 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 t
he 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)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, Ses
sionData reconnectSessionData, Boolean applyTransientFaultHandling)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions user
Options)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInte
rnal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Migrations.Runner.CreateDatabase(String connectionString) in /app/Runner.cs:line 27
   at Migrations.Runner.Main(String[] args) in /app/Runner.cs:line 14
ClientConnectionId:00000000-0000-0000-0000-000000000000

And this is the way I’m reading connection string from environment variables in my console application:

var connectionString = Environment.GetEnvironmentVariable("CONNECTION_STRING", 
EnvironmentVariableTarget.Process);

Ways I have tried:

I moved inside my migration-sample container using shell, and I received sql-server ping. This means that these two containers can see each other.
I have also written my connection string using container IP & name.

Thanks for your help.

Solution

I solved the issue using the following command:

docker inspect <your sql-server>

After this, you’ll see something similar to this:

"Networks": {
                "sql-network": {
                    "IPAMConfig": null,
                    "Links": null,
                    "Aliases": [
                        "6f927bd17835"
                    ],
                    "NetworkID": "028dabf00c4bda62e47f086026a503915f1eb11cfae7137fe6aba3d68fc9ee35",
                    "EndpointID": "47ca6cf26ea317297297091b3b57187f00a18e0748319b73f34d97d95e881ef1",
                    "Gateway": "172.19.0.1",
                    "IPAddress": "172.19.0.2",
                    "IPPrefixLen": 16,
                    "IPv6Gateway": "",
                    "GlobalIPv6Address": "",
                    "GlobalIPv6PrefixLen": 0,
                    "MacAddress": "02:42:ac:13:00:02",
                    "DriverOpts": null
                }
            }

Instead of using the IPAddress in your connection string, use Gateway. So the connection string would be:

ENV CONNECTION_STRIGN: "Server=<container_gateway_address>,5435; 
Database=Test;User Id=sa; Password=Arman123!;"

Answered By – Arman Ziaei

Answer Checked By – Jay B. (BugsFixing Admin)