Lookup what SQL connection string did the client use to connect

Question

I have an onpremise SQL database server, to which, many applications connect to.

Created CNAME record for the server and instructed users to change it in their app connection string too. Most of them will, but there will be some who don't, and will pose an issue.

Therefor I wish to monitor the network if there is any app that's still try to connect to the SQL server via hostname, and not with the CNAME record.

What is the best tool to monitor such traffic? -SQL profiler and extended event seems to have no knowledge about that information. -Wireshark can resolve IPs to DNS names, but not sure how it will know what connection string did the application use. -Monitor DNS queries on DNS servers maybe the most promising. All in all. What are the best tools to capture this information?

• J.D.: My understanding is Wireshark will tell you when a CNAME was used in the request. I would think you could just filter Wireshark down to the requests that didn't use the CNAME and then that's your candidate list of users who you can go talk to.
• Avi: @J.D. That's brilliant, I didn't know about Wireshark doing that.. Please make this as anwer.
• Avi: @Charlieface this is also great idea, but in my case, i have no admin rights on the server. Thanks nevertheless.

Answer 1

It doesn't seem to be possible to do this either with a trace or with XEvents. Wireshark may be able to give you this info, but it probably won't work if TLS 1.3 is being used.

One option is to give the server two different IP addresses, and have SQL Server listen on both. Then change the CNAME record to a normal A or AAAA record, to point to that IP address

Then you can monitor local_net_address in sys.dm_exec_connections. Unfortunately I can't find this field in any of the XEvents.