0

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?

Avi
  • 265
  • 2
  • 15

1 Answers1

2

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.

Charlieface
  • 17,078
  • 22
  • 44