SqlClient: Unable to connect to SQL Server using "localhost" as the server name
When trying to connect to SQL Server running in a Docker container in WSL I am unable to connect using localhost as the server name. I can, however, connect using 127.0.0.1 or ::1.
The application is a .NET 7.0.7 application using Microsoft.Data.SqlClient.dll version 5.0.2 as part of Microsoft.EntityFrameworkCore.SqlServer 7.0.8.
This connection string doesn’t work:
Server=tcp:localhost,1433;Initial Catalog=dbname;Persist Security Info=False;User ID=sa;Password=password;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;MultipleActiveResultSets=True
This connection string does:
Server=tcp:::1,1433;Initial Catalog=dbname;Persist Security Info=False;User ID=sa;Password=password;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;MultipleActiveResultSets=True
As does this one:
Server=tcp:127.0.0.1,1433;Initial Catalog=dbname;Persist Security Info=False;User ID=sa;Password=password;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;MultipleActiveResultSets=True
This is the exception detail:
Microsoft.Data.SqlClient.SqlException
HResult=0x80131904
Message=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: 0 - No connection could be made because the target machine actively refused it.)
Source=Core Microsoft SqlClient Data Provider
StackTrace:
at 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, SqlConnectionString connectionOptions, Boolean withFailover)
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)
at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass18_0.<Exists>b__0(DateTime giveUp)
at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_0`2.<Execute>b__0(DbContext _, TState s)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation, Func`2 verifySucceeded)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists(Boolean retryOnNotExists)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists()
at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
at Program.<Main>$(String[] args) in C:\Development\Xenon7\Xenon.Administration\Program.cs:line 109
Inner Exception 1:
Win32Exception: No connection could be made because the target machine actively refused it.
From WSL, I can see that it’s listening on the correct ports:
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 0.0.0.0:1433 0.0.0.0:* LISTEN
tcp6 0 0 :::1433 :::* LISTEN
From Windows I can see the same:
Active Connections
Proto Local Address Foreign Address State
TCP 127.0.0.1:1433 0.0.0.0:0 LISTENING
TCP [::1]:1433 [::]:0 LISTENING
This is also confirmed by checking Resource Monitor, which shows wslrelay.exe listening on port 1433 on both “1Pv6 loopback” and “IPv4 loopback” and lists the Firewall Status as “Allowed, not restricted”.
In WSL (i.e. not from inside the docker container), running sudo nc 0.0.0.0 1433 -l and sudo nc :: 1433 -l shows connection attempts when using 127.0.0.1 or ::1 as the server address (as expected), but using localhost doesn’t show any network activity.
Using tcpdump in WSL doesn’t show any traffic when attempting to connect using localhost.
I also experience the same issue using SSMS and sqlcmd.
From SSMS, I can connect using 127.0.0.1 or ::1, but cannot connect using localhost.
Here is the error:
===================================
Cannot connect to localhost.
===================================
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: 0 - The wait operation timed out.) (Framework Microsoft SqlClient Data Provider)
------------------------------
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-258-database-engine-error
------------------------------
Error Number: 258
Severity: 20
State: 0
------------------------------
Program Location:
at 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, Boolean isFirstTransparentAttempt, SqlAuthenticationMethod authType, String certificate, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, Boolean useOriginalAddressInfo, Boolean disableTnir)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir)
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, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling)
at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
===================================
The wait operation timed out
By default, SSMS attempts to use the Named Pipes Provider when trying to connect with localhost specified as the Server name. Changing to localhost,1433 or setting the Network protocol to TCP/IP (from default) in the connection Options / Connection Properties tab, shows it trying to use the TCP Provider (as expected), with the same results.
It appears that localhost is not being resolved in any SQL application.
Other applications, however, are able to connect (from Windows) using localhost to applications running in Docker, including MySQL, Apache, etc. In these instances, localhost is resolving to ::1, as does ping when run from a Command Prompt.
For what it’s worth, this used to work on a previous machine using Windows 10 and WSL2, so I’m not sure if this is a Windows issue, or a Microsoft SQL Data Provider issue, which may have also been a different version at the time.
In summary:
- It doesn’t appear to be a mssql-docker issue, because connecting via
127.0.0.1or::1works, and it appears that WSL is not receiving any traffic when a connection attempt is made tolocalhost. - It doesn’t appear to be a Docker or WSL issue, for the same reason as the above point, and also because other applications are working using
localhost. - It doesn’t appear to be an SSMS issue, as other applications using the Microsoft SQL Data Provider experience the same issue.
I don’t know the inner workings of the Microsoft SQL Data Provider, however, it feels like the provider itself is failing to resolve localhost or perhaps is trying to resolve it as a Named Pipes address and caching that (failed) result even when using the TCP/IP provider.
I am not running any SQL Server instances in Windows. Microsoft SQL Server 2019 LocalDB is confirmed as stopped as reported by sqllocaldb.
Other information:
- SSMS is using Microsoft.Data.SqlClient version 3.13.23069.2
sqlcmdis using Microsoft ODBC Driver 17.10 for SQL Server version 2017.1710.3.1- Windows 11 Pro 22H2 build 22621.1928
- Microsoft SQL Server Developer Edition (64-bit) version 16.0.4045 RTM (Docker)
- Microsoft SQL Server Express (64-bit) version 15.0.4153.1 (when running)
- Docker version 24.0.2, build cb74dfc
- Installed Programs
- Microsoft ODBC Driver 17 for SQL Server version 17.10.3.1
- Microsoft OLE DB Driver for SQL Server version 18.6.5.0
- Microsoft SQL Server 2019 LocalDB version 15.0.4153.1
- Microsoft System CLR Types for SQL Server 2019 version 15.0.2000.5
- ODBC Data Source Administrator (32-bit)
- ODBC Driver 17 for SQL Server version 2017.1710.03.01
- SQL Server version 10.00.22621.1848
- ODBC Data Source Administrator (64-bit)
- ODBC Driver 17 for SQL Server version 2017.1710.03.01
- SQL Server version 10.00.22621.1848
SSMS:
SQL Server Management Studio 19.1.56.0
SQL Server Management Objects (SMO) 16.200.48044.0+eeb184ee48a91ebc6a27a5d192c0d67bdfaae8b6
Microsoft T-SQL Parser 17.0.8.0+3c5555b8bd579d12add8f155f1dbc871e3e734c4
Microsoft Analysis Services Client Tools 16.0.20010.0
Microsoft Data Access Components (MDAC) 10.0.22621.1848
Microsoft MSXML 3.0 6.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 10.0.22621
WSL
WSL version: 1.2.5.0
Kernel version: 5.15.90.1
WSLg version: 1.0.51
MSRDC version: 1.2.3770
Direct3D version: 1.608.2-61064218
DXCore version: 10.0.25131.1002-220531-1700.rs-onecore-base2-hyp
Windows version: 10.0.22621.1928
I was unable to find any similar issues in this repo, other than #1455 which appears to have the opposite problem to mine.
There is also an issue (https://github.com/microsoft/mssql-docker/issues/813) in the mssql-docker repo which is the same issue as this.
My apologies in advance if this is the wrong place for this. Let me know and I’ll move it if required.
About this issue
- Original URL
- State: closed
- Created a year ago
- Reactions: 3
- Comments: 16 (7 by maintainers)
Closing this issue as it is by design
That aligns with my testing. I see code in the native SNI library that appears to decide to use ComputerName when it sees localhost. (I’m not set up to debug it.) ComputerName doesn’t resolve to the localhost IPs, thus the behavior.
The MS ODBC Driver for SQL Server also has the same behavior. SQL Server installed locally, listening on TCP localhost IP addresses ONLY (127.0.0.1 and ::1):
I’ve inquired why it might behave this way. My thought is it might have to do with cluster or mirroring scenarios. Others have suggested it’s to improve the connection encryption experience with regard to the name in certificates. It does seem odd. But as you said, it’s easy to work around.
I was experience that exact same behavior that was driving me crazy. I’ve ended up going with a different workaroud. 1st Identify which IP is being resolved by the DNS server for ‘localhost’. In my case it was resolving for my hostname and the IP from my VPN connection. 2nd, enabled port forwarding from the previous identified IP:1433 to 127.0.0.1:1433 Now, all applications can indeed connect to localhost transparently without having to swap ‘localhost’ by ‘127.0.0.1’
used commands (as elevated priviledges)
netsh interface portproxy show allnetsh interface portproxy resetnetsh interface portproxy add v4tov4 listenaddress=[your resolved IP address] listenport=1433 connectaddress=127.0.0.1 connectport=1433More info on port forwarding: https://woshub.com/port-forwarding-in-windows/ Hope this helps!!