Cannot connect to SQL Server running in docker from app running in docker

I trying to run an ASP.NET Core app using SQL Server from docker.

The app was created in VS 2022 with a target of .NET 8.

I created docker image with this docker_compose:

version: '3.9'
services:
  # SQL Server service
  web_api:
    image: my_app_api
    container_name: my_app_api_container
    ports:
      - "8080:8080"
  sql:
    image: "mcr.microsoft.com/mssql/server:2022-latest"
    container_name: sql_server2022
    ports: # not actually needed, because the two services are on the same network
      - "1433:1433" 
    environment:
      - ACCEPT_EULA=y
      - SA_PASSWORD=mypassword

The connection string is defined in appsettings.json:

"ConnectionString": "Data Source=localhost,1433;Initial Catalog=mycatalog;User ID=SA;Password=mypassword;Connect Timeout=30;Encrypt=False;Trust Server Certificate=False;Application Intent=ReadWrite;Multi Subnet Failover=False"

When I am trying to run app w/o docker with same connection string i.e. app connecting to SQL Server running on docker container - succeeded to to connect to the database.

But when running app FROM docker - failed to start with exception :

Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) 2024-04-21 18:17:02 at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) 2024-04-21 18:17:02 at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) 2024-04-21 18:17:02 at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) 2024-04-21 18:17:02 at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) 2024-04-21 18:17:02 at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) 2024-04-21 18:17:02 at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) 2024-04-21 18:17:02 at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides) 2024-04-21 18:17:02 at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) 2024-04-21 18:17:02 at Microsoft.Data.SqlClient.SqlConnection.Open() 2024-04-21 18:17:02 at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection(Boolean errorsExpected)

Is there something wrong in connection string or docker compose ?

Thanks in advance

Solution

The issue you're encountering when trying to connect your ASP.NET Core application running in Docker to SQL Server also running in Docker is most likely related to the connection string configuration, specifically the data source setting.

In your connection string, you're using "localhost" to refer to the SQL Server:

"ConnectionString": "Data Source=localhost,1433;Initial Catalog=mycatalog;User ID=SA;Password=mypassword;Connect Timeout=30;Encrypt=False;Trust Server Certificate=False;Application Intent=ReadWrite;Multi Subnet Failover=False"

When running the application in Docker, "localhost" refers to the container itself. Since your SQL Server is running in a different container, you need to reference it by the service name defined in your Docker Compose file, which is "sql" in this case. So, you should change the Data Source in your connection string to use the service name:

"ConnectionString": "Data Source=sql,1433;Initial Catalog=mycatalog;User ID=SA;Password=mypassword;Connect Timeout=30;Encrypt=False;Trust Server Certificate=False;Application Intent=ReadWrite;Multi Subnet Failover=False"

This will tell your application to connect to the "sql" service, which is the name of the SQL Server container as defined in your Docker Compose setup.