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!
Best Answer
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.