Unable to connect to SQL Server using "localhost" as the server name

[omfgicbf]

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.1 or ::1 works, and it appears that WSL is not receiving any traffic when a connection attempt is made to localhost.
• 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
• sqlcmd is 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 (microsoft/mssql-docker#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.

[Kaur-Parminder]

@omfgicbf Thanks for bringing this up. we will look into it and update.

[arellegue]

@omfgicbf. We investigated the issue but we are unable to reproduce it. However, the following are my suggestions that you may want to try:

1. Flush your Windows DNS cache by opening a command prompt and entering the following command: ipconfig /flushdns

2. Check if there is localhost entry in the hosts file and that it is bound to 127.0.0.1. If there is no entry for localhost, then add one. For example: 127.0.0.1 localhost

   In Windows, the hosts file is in C:\Windows\System32\drivers\etc
   In Linux, the hosts file is in /etc/

3. Check the content of the dns cache to see what ip address is bound to localhost.

   ipconfig /displaydns

[omfgicbf]

Hi, @arellegue, thanks for following up.

Per your suggestions:

1. Before doing anything, I tried again to make sure it was still not working (confirmed).
2. ipconfig /flushdns
3. Tried again, still not working. (::1 and 127.0.0.1 work, localhost,1433 does not).
4. Changed 127.0.0.1 kubernetes.docker.internal to 127.0.0.1 kubernetes.docker.internal localhost in hosts file (Windows).
5. ipconfig /flushdns
6. Tried again, still not working.

Either way, per the hosts file, localhost name resolution is handled within DNS itself.

Prior to, and during all the above steps, Windows was able to resolve localhost:

C:\Windows\System32>ping localhost

Pinging Icarus [::1] with 32 bytes of data:
Reply from ::1: time<1ms
Reply from ::1: time<1ms
Reply from ::1: time<1ms
Reply from ::1: time<1ms

Ping statistics for ::1:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms

I used Process Monitor and TCPView to see what it was doing. When Ssms.exe attempts to connect to localhost,1433 I can see it trying to connect to port 1433 on every IPv4 and IPv6 interface on my computer, except the localhost addresses ::1 and 127.0.0.1.

I confirmed this with Wireshark.

In Wireshark, I also noticed that connecting to other servers (e.g. Azure SQL) via hostname will cause an NBNS name query for the host part of the address, however, attempting to connect to localhost does not. This may be as expected, as the NetBIOS Name Service would probably resolve localhost internally.

Browsing to \\localhost works as expected.

To reiterate, this does not happen with any other application (that I've tried so far, and there have been several now).

[arellegue]

@omfgicbf. Did you try to ping localhost using ipv4? i.e. ping localhost -4.
What ip address did it resolve to?

However, this issue is not related to MDS driver. This issue is mostly related to your network dns not resolving localhost. Our suggestion is for you to contact your Network Administrator to try to fix the localhost resolution in your dns.

[arellegue]

@omfgicbf. Could you provide the docker command used to run your mssql-server container.

[omfgicbf]

Hi @arellegue,

Resolution is working:

Pinging Icarus [127.0.0.1] with 32 bytes of data:
Reply from 127.0.0.1: bytes=32 time<1ms TTL=128
Reply from 127.0.0.1: bytes=32 time<1ms TTL=128
Reply from 127.0.0.1: bytes=32 time<1ms TTL=128
Reply from 127.0.0.1: bytes=32 time<1ms TTL=128

Ping statistics for 127.0.0.1:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms

DNS is not used to resolve localhost and in any case, every other application manages to resolve localhost and connect to services running in Docker.

Here is the docker run command:

docker run \
	-e "ACCEPT_EULA=Y" \
	-e "SA_PASSWORD=password" \
	--name test-sql \
	-p 1433:1433 \
	-h test.sql \
	-v test-sql-data:/var/opt/mssql \
	-d mcr.microsoft.com/mssql/server:2022-latest

I've included the docker inspect results too:

[
    {
        "Id": "9027c87856a90497cc6b7be50660ca759b35dfe1d56090e35d043bda97d236b4",
        "Created": "2023-06-28T22:44:07.53962533Z",
        "Path": "/opt/mssql/bin/permissions_check.sh",
        "Args": [
            "/opt/mssql/bin/sqlservr"
        ],
        "State": {
            "Status": "running",
            "Running": true,
            "Paused": false,
            "Restarting": false,
            "OOMKilled": false,
            "Dead": false,
            "Pid": 68978,
            "ExitCode": 0,
            "Error": "",
            "StartedAt": "2023-07-06T11:20:19.094282435Z",
            "FinishedAt": "2023-07-06T03:24:45.906510851Z"
        },
        "Image": "sha256:05ce0918105bd05db0d2372a38cfea69bfaa10c0295d65f26c2d07b5696f365d",
        "ResolvConfPath": "/var/lib/docker/containers/9027c87856a90497cc6b7be50660ca759b35dfe1d56090e35d043bda97d236b4/resolv.conf",
        "HostnamePath": "/var/lib/docker/containers/9027c87856a90497cc6b7be50660ca759b35dfe1d56090e35d043bda97d236b4/hostname",
        "HostsPath": "/var/lib/docker/containers/9027c87856a90497cc6b7be50660ca759b35dfe1d56090e35d043bda97d236b4/hosts",
        "LogPath": "/var/lib/docker/containers/9027c87856a90497cc6b7be50660ca759b35dfe1d56090e35d043bda97d236b4/9027c87856a90497cc6b7be50660ca759b35dfe1d56090e35d043bda97d236b4-json.log",
        "Name": "/test-sql",
        "RestartCount": 0,
        "Driver": "overlay2",
        "Platform": "linux",
        "MountLabel": "",
        "ProcessLabel": "",
        "AppArmorProfile": "",
        "ExecIDs": null,
        "HostConfig": {
            "Binds": [
                "test-sql-data:/var/opt/mssql"
            ],
            "ContainerIDFile": "",
            "LogConfig": {
                "Type": "json-file",
                "Config": {}
            },
            "NetworkMode": "default",
            "PortBindings": {
                "1433/tcp": [
                    {
                        "HostIp": "",
                        "HostPort": "1433"
                    }
                ]
            },
            "RestartPolicy": {
                "Name": "no",
                "MaximumRetryCount": 0
            },
            "AutoRemove": false,
            "VolumeDriver": "",
            "VolumesFrom": null,
            "ConsoleSize": [
                49,
                188
            ],
            "CapAdd": null,
            "CapDrop": null,
            "CgroupnsMode": "host",
            "Dns": [],
            "DnsOptions": [],
            "DnsSearch": [],
            "ExtraHosts": null,
            "GroupAdd": null,
            "IpcMode": "private",
            "Cgroup": "",
            "Links": null,
            "OomScoreAdj": 0,
            "PidMode": "",
            "Privileged": false,
            "PublishAllPorts": false,
            "ReadonlyRootfs": false,
            "SecurityOpt": null,
            "UTSMode": "",
            "UsernsMode": "",
            "ShmSize": 67108864,
            "Runtime": "runc",
            "Isolation": "",
            "CpuShares": 0,
            "Memory": 0,
            "NanoCpus": 0,
            "CgroupParent": "",
            "BlkioWeight": 0,
            "BlkioWeightDevice": [],
            "BlkioDeviceReadBps": [],
            "BlkioDeviceWriteBps": [],
            "BlkioDeviceReadIOps": [],
            "BlkioDeviceWriteIOps": [],
            "CpuPeriod": 0,
            "CpuQuota": 0,
            "CpuRealtimePeriod": 0,
            "CpuRealtimeRuntime": 0,
            "CpusetCpus": "",
            "CpusetMems": "",
            "Devices": [],
            "DeviceCgroupRules": null,
            "DeviceRequests": null,
            "MemoryReservation": 0,
            "MemorySwap": 0,
            "MemorySwappiness": null,
            "OomKillDisable": false,
            "PidsLimit": null,
            "Ulimits": null,
            "CpuCount": 0,
            "CpuPercent": 0,
            "IOMaximumIOps": 0,
            "IOMaximumBandwidth": 0,
            "MaskedPaths": [
                "/proc/asound",
                "/proc/acpi",
                "/proc/kcore",
                "/proc/keys",
                "/proc/latency_stats",
                "/proc/timer_list",
                "/proc/timer_stats",
                "/proc/sched_debug",
                "/proc/scsi",
                "/sys/firmware"
            ],
            "ReadonlyPaths": [
                "/proc/bus",
                "/proc/fs",
                "/proc/irq",
                "/proc/sys",
                "/proc/sysrq-trigger"
            ]
        },
        "GraphDriver": {
            "Data": {
                "LowerDir": "/var/lib/docker/overlay2/600e97fa47db5322fc7dabc687ffd256e1bd10c38e5b38fc8b29bb43a1cc4ae6-init/diff:/var/lib/docker/overlay2/7c34bdf1c7bd1881696fa1747db0651315470acb22defca43e05c5951462052f/diff:/var/lib/docker/overlay2/96afa40fd7ed37a7e6811dd4d711a1f8fa254cc7865cdf49e2f049587e7efaa8/diff:/var/lib/docker/overlay2/7b788d0b59dd8d253bace0c0f3ec94249807af85a4070bef554107211d0cfd93/diff",
                "MergedDir": "/var/lib/docker/overlay2/600e97fa47db5322fc7dabc687ffd256e1bd10c38e5b38fc8b29bb43a1cc4ae6/merged",
                "UpperDir": "/var/lib/docker/overlay2/600e97fa47db5322fc7dabc687ffd256e1bd10c38e5b38fc8b29bb43a1cc4ae6/diff",
                "WorkDir": "/var/lib/docker/overlay2/600e97fa47db5322fc7dabc687ffd256e1bd10c38e5b38fc8b29bb43a1cc4ae6/work"
            },
            "Name": "overlay2"
        },
        "Mounts": [
            {
                "Type": "volume",
                "Name": "test-sql-data",
                "Source": "/var/lib/docker/volumes/test-sql-data/_data",
                "Destination": "/var/opt/mssql",
                "Driver": "local",
                "Mode": "z",
                "RW": true,
                "Propagation": ""
            }
        ],
        "Config": {
            "Hostname": "test.sql",
            "Domainname": "",
            "User": "mssql",
            "AttachStdin": false,
            "AttachStdout": false,
            "AttachStderr": false,
            "ExposedPorts": {
                "1433/tcp": {}
            },
            "Tty": false,
            "OpenStdin": false,
            "StdinOnce": false,
            "Env": [
                "ACCEPT_EULA=Y",
                "SA_PASSWORD=password",
                "PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin",
                "MSSQL_RPC_PORT=135",
                "CONFIG_EDGE_BUILD="
            ],
            "Cmd": [
                "/opt/mssql/bin/sqlservr"
            ],
            "Image": "mcr.microsoft.com/mssql/server:2022-latest",
            "Volumes": null,
            "WorkingDir": "",
            "Entrypoint": [
                "/opt/mssql/bin/permissions_check.sh"
            ],
            "OnBuild": null,
            "Labels": {
                "com.microsoft.product": "Microsoft SQL Server",
                "com.microsoft.version": "16.0.4045.3",
                "org.opencontainers.image.ref.name": "ubuntu",
                "org.opencontainers.image.version": "20.04",
                "vendor": "Microsoft"
            }
        },
        "NetworkSettings": {
            "Bridge": "",
            "SandboxID": "4557b5877fd00c1d285d9f6f36926f52a66a7cb0674ff1c0b1a099f998649c95",
            "HairpinMode": false,
            "LinkLocalIPv6Address": "",
            "LinkLocalIPv6PrefixLen": 0,
            "Ports": {
                "1433/tcp": [
                    {
                        "HostIp": "0.0.0.0",
                        "HostPort": "1433"
                    },
                    {
                        "HostIp": "::",
                        "HostPort": "1433"
                    }
                ]
            },
            "SandboxKey": "/var/run/docker/netns/4557b5877fd0",
            "SecondaryIPAddresses": null,
            "SecondaryIPv6Addresses": null,
            "EndpointID": "bffc719f579d3a85f6eaa68e59f665b67e2483a8ca80e7e0befed5f558f79b52",
            "Gateway": "172.17.0.1",
            "GlobalIPv6Address": "",
            "GlobalIPv6PrefixLen": 0,
            "IPAddress": "172.17.0.2",
            "IPPrefixLen": 16,
            "IPv6Gateway": "",
            "MacAddress": "02:42:ac:11:00:02",
            "Networks": {
                "bridge": {
                    "IPAMConfig": null,
                    "Links": null,
                    "Aliases": null,
                    "NetworkID": "8716e6dff42c8fd6a9483dc3da16df75da7b8949398593a1f93257778447e4ab",
                    "EndpointID": "bffc719f579d3a85f6eaa68e59f665b67e2483a8ca80e7e0befed5f558f79b52",
                    "Gateway": "172.17.0.1",
                    "IPAddress": "172.17.0.2",
                    "IPPrefixLen": 16,
                    "IPv6Gateway": "",
                    "GlobalIPv6Address": "",
                    "GlobalIPv6PrefixLen": 0,
                    "MacAddress": "02:42:ac:11:00:02",
                    "DriverOpts": null
                }
            }
        }
    }
]

To reiterate, WSL is not seeing any network activity when SSMS is attempting to connect.

SSMS is attempting to connect to all of the Windows interface IP addresses except the loopback address (which SQL Server is listening on). Even if Docker or the SQL Docker container were not running or misconfigured, SSMS is not even attempting to connect to the loopback address.

Manually specifying the loopback address (i.e. ::1 or 127.0.0.1) to SSMS results in a successful connection, so Docker, the SQL Docker container, WSL and port mapping seem to be working as expected.

To further isolate Docker, the SQL Docker container or WSL from being somehow related to the issue, I installed SQL Server 2022 in Windows and configured it to listen only on the loopback addresses (the default is to listen on ALL interfaces). I stopped the SQL Docker container, Docker and WSL and confirmed that the Windows SQL Server instance was listening on the loopback addresses (and only on those).

I get the same result... I cannot connect using localhost:1433, however, I can connect using ::1,1433 or 127.0.0.1,1433.

However, if I configure SQL Server to listen on at least one other interface IP address (e.g. an uplink on 192.168.1.x) and try to connect to localhost:1433, it magically works.

It appears that SSMS is somehow treating localhost as "any IP address on this machine, except the loopback addresses".

I can however rule out SSMS, as I get the same results using a .NET application.

[arellegue]

@omfgicbf. Just to confirm, is the password in docker run command really "password" or just a place holder? If it's in fact the actual password for sa user then the mssql-server container may not be actually running due to a valid password format restriction for mssql-server.

[omfgicbf]

Hi @arellegue,

No, the real password is not "password".

If it was a problem with the mssql-server Docker container failing to start due to an invalid password restriction then connecting via the other methods would not work.

Could this be the problem?

DataSource sets ServerName to Environment.MachineName if the supplied hostname is localhost.

SqlClient/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SNI/SNIProxy.cs

Line 732 in 5001097

ServerName = IsLocalHost(host) ? Environment.MachineName : host;

SqlClient/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SNI/SNIProxy.cs

Lines 759 to 760 in 5001097

	private static bool IsLocalHost(string serverName)
	=> ".".Equals(serverName) || "(local)".Equals(serverName) || "localhost".Equals(serverName);

ServerName is then used to get a list of IP addresses to connect to:

SqlClient/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SNI/SNICommon.cs

Line 203 in 5001097

return Dns.GetHostAddresses(serverName);

Dns.GetHostAddresses does not include loopback addresses in its return value (or at least it doesn't in .NET 7.0.7 on Windows).

This can be confirmed with the following code:

using System.Net;

var addrs = Dns.GetHostAddresses(Environment.MachineName);

foreach (var addr in addrs)
{
	Console.WriteLine(addr.ToString());
}

See dotnet/runtime#27534 (comment)

Hi @Kaur-Parminder, could you please review and relabel if appropriate?

[omfgicbf]

Hi @arellegue,

No, the real password is not "password".

If it was a problem with the mssql-server Docker container failing to start due to an invalid password restriction then connecting via the other methods would not work.

Could this be the problem?

DataSource sets ServerName to Environment.MachineName if the supplied hostname is localhost.

SqlClient/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SNI/SNIProxy.cs

Line 732 in 5001097

ServerName = IsLocalHost(host) ? Environment.MachineName : host;

SqlClient/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SNI/SNIProxy.cs

Lines 759 to 760 in 5001097

	private static bool IsLocalHost(string serverName)
	=> ".".Equals(serverName) || "(local)".Equals(serverName) || "localhost".Equals(serverName);

ServerName is then used to get a list of IP addresses to connect to:

SqlClient/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SNI/SNICommon.cs

Line 203 in 5001097

return Dns.GetHostAddresses(serverName);

Dns.GetHostAddresses does not include loopback addresses in its return value (or at least it doesn't in .NET 7.0.7 on Windows).

This can be confirmed with the following code:

using System.Net;

var addrs = Dns.GetHostAddresses(Environment.MachineName);

foreach (var addr in addrs)
{
	Console.WriteLine(addr.ToString());
}

See dotnet/runtime#27534 (comment)

Hi @Kaur-Parminder, could you please review and relabel if appropriate?

For what it's worth, Dns.GetHostAddresses does what you'd expect if you just pass it localhost:

using System.Net;

var addrs = Dns.GetHostAddresses("localhost");

foreach (var addr in addrs)
{
	Console.WriteLine(addr.ToString());
}

::1
127.0.0.1

[David-Engel]

When specifying the server, to narrow it down, try np:localhost and tcp:localhost. Leaving it just localhost means SqlClient will first try Named Pipes, then TCP. Also, Windows vs Linux will use different code bases unless you set AppContext.SetSwitch("Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows", true);, then Windows will use the same MDS code as Linux. There does appear to be MDS managed SNI code that you pointed to that is translating localhost into the machine name, but it's only on the named pipes path. There should be similar native SNI code (private) used on Windows (and SSMS) that might be doing something similar. Testing with the above settings should narrow it down.

[omfgicbf]

Hi @David-Engel,

Trying with np:localhost and tcp:localhost both exhibit the issue as well. I had also previously tried with localhost,1433 and by manually specifying TCP as the protocol in SSMS. I can see it trying TCP when tracing network traffic, and connecting via the native path does work if I specify the addresses manually.

I tried with AppContext.SetSwitch("Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows", true); as you suggested and it works with that switch, so the issue does appear to be in the native path.

I am probably way off, but I did try following it manually and as far as I got was the native call into SNIGetPeerAddrStrWrapper, which is where I hit a roadblock. I couldn't see anything on the way that might account for what I'm experiencing. It could be that Microsoft.Data.SqlClient.SNI.x64 is returning an unexpected value for localhost, which I guess would make it an issue outside of SqlClient. I was going to try to debug this, but I don't seem to be able to build SqlClient and I got lost trying to make a small reproduction calling SNIGetPeerAddrStrWrapper directly.

If it matters, we can probably exclude Linux from the equation, as I can reproduce this by installing SQL Server in Windows and disabling all but the loopback addresses.

As a side note (and I acknowledge I am probably completely and absolutely wrong on this, but thought it worth mentioning in case anyway) when I was looking through the call hierarchy for GetDnsIpAddresses I could see it getting hit from some TCP paths, e.g. SNITCPHandle. However, it doesn't seem to affect the managed code path that works, so is probably not relevant here.

Notwithstanding all of the above, I acknowledge this is an edge case and has a workaround by using the addresses rather than the hostname, so has a low priority.

[David-Engel]

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):

C:\> sqlcmd -S tcp:127.0.0.1
1> exit
C:\> sqlcmd -S tcp:localhost
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: The wait operation timed out.
.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : 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'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.

[collinstevens]

@omfgicbf I felt a huge sigh of relief when I read through every word of your posts here, it's describing my last couple days of troubleshooting.

I've been trying to use Docker Engine without Docker Desktop as well as Podman, but ran into this incredibly weird behavior you are seeing with localhost only with Microsoft SQL Server images.

I've resorted to trying to diagnose this issue with pktmon of all tools because I thought that there was some obscure networking issue specifically with SSMS and localhost resolution or WSL 2 itself. It turns it out it was just this issue. Not a WSL 2 localhostForwarding issue or a mirrored networking issue or a WSL 2 NAT issue or a WSL 2 IPv6 issue.

Unfortunately, if you're using DataGrip which is using the Microsoft JDBC driver, you won't see this behavior and you'll be able to connect on localhost while your peers on SSMS won't.

[IvoESilva]

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)

• Show all port forward rules: netsh interface portproxy show all
• Reset all port forward rules: netsh interface portproxy reset
• Enable port forwarding rule: netsh interface portproxy add v4tov4 listenaddress=[your resolved IP address] listenport=1433 connectaddress=127.0.0.1 connectport=1433

More info on port forwarding: https://woshub.com/port-forwarding-in-windows/
Hope this helps!!

[GabrielHSFerreira]

I have the same problem. The workaround I found was disable IPV6 on WSL network adapter. This way I am able to connect to mssql using localhost via SSMS.

Edit: I had to disable IPV6 on both WSL and principal adapter.

[kf-gonzalez]

Closing this issue as it is by design