Closed omfgicbf closed 10 months ago
@omfgicbf Thanks for bringing this up. we will look into it and update.
@omfgicbf. We investigated the issue but we are unable to reproduce it. However, the following are my suggestions that you may want to try:
Flush your Windows DNS cache by opening a command prompt and entering the following command: ipconfig /flushdns
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/
Check the content of the dns cache to see what ip address is bound to localhost.
ipconfig /displaydns
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).
@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.
@omfgicbf. Could you provide the docker command used to run your mssql-server container.
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.
@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.
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
.
ServerName
is then used to get a list of IP addresses to connect to:
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 https://github.com/dotnet/runtime/issues/27534#issuecomment-535641998
Hi @Kaur-Parminder, could you please review and relabel if appropriate?
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
setsServerName
toEnvironment.MachineName
if the supplied hostname islocalhost
.
ServerName
is then used to get a list of IP addresses to connect to:
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
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.
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.
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.
@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.
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 all
netsh interface portproxy reset
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!!
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.
Closing this issue as it is by design
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 using127.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:
This connection string does:
As does this one:
This is the exception detail:
From WSL, I can see that it's listening on the correct ports:
From Windows I can see the same:
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
andsudo nc :: 1433 -l
shows connection attempts when using127.0.0.1
or::1
as the server address (as expected), but usinglocalhost
doesn't show any network activity.Using
tcpdump
in WSL doesn't show any traffic when attempting to connect usinglocalhost
.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 usinglocalhost
.Here is the error:
By default, SSMS attempts to use the Named Pipes Provider when trying to connect with
localhost
specified as the Server name. Changing tolocalhost,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 doesping
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:
127.0.0.1
or::1
works, and it appears that WSL is not receiving any traffic when a connection attempt is made tolocalhost
.localhost
.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:
sqlcmd
is using Microsoft ODBC Driver 17.10 for SQL Server version 2017.1710.3.1SSMS:
WSL
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.