1

We are migrating the dns entry for our SQL Server database from an machine name to a C-Name, in preparation for moving it from a bare metal box to a virtual machine. We have switched all the connection strings we know about and also issued instructions across our department, asking everyone to switch their connections in case there are one-off apps or db connected spreadsheets, etc.

We would like to monitor the traffic coming into the box to verify that there is no incoming traffic that got to the server via the old machine name. Is there any way to do this? What I need specifically is the url that all of the clients used to reach the server. We tried monitoring the port with Wireshark but that didn't seem to provide the info we needed.

Thanks!

  • I think this question should give you what you want: Find source of spids that show no hostname – LowlyDBA Apr 21 at 18:35
  • Thanks for the response! I may not have been clear in my question. Your link gives me the IP of who is connecting to my server. I want to know what url they used to reach the server. – Dan Csharpster Apr 21 at 18:40
  • 1
    They don't use any URLs to reach the server. – mustaccio Apr 21 at 18:42
9

By the time the client connects, it typically doesn't present the hostname it used. The client resolves the hostname in DNS and connects to the IP(s) that were returned from the DNS lookup.

Kerberos connections will fail if the client uses a hostname that doesn't correspond to an SPN registered for the SQL Server service account. And NTLM can fail if the server is not configured to accept auth at the hostname the client uses. But there's no general way to see this. The configuration changes required to connect with these Auth schemes through a DNS alias are outlined here.

One thing you can do is to add an additional IP address to the server and configure the two DNS entries to resolve to different IP addresses. That you would be able to monitor either at the network level or in sys.dm_exec_connections.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.