We have a huge .NET application from 3rd party that would be time consuming to disassemble and understand the code. It is compiled in Release mode. Connection strings to the MS SQL databases are defined to be in config file and they seem to be correct. There are more then 6 different databases it uses.
However when app starts, it throws an error like "a network related or instance specific error occurred while establishing a connection to sql server" Traces of the application doesnt provide enough information where it tries to connect when it fails.
Is it possible to profile which connection a .NET application is trying to open without knowing in advance which SQL server name it tries to connect? SQL Profile traces connections on server side opened from other machines, but what is the best tool there to see on a specific machine to which SQL server it connects (with a possibility to see a non-succesfull connections as well)
Sniff the traffic, because it's the only way to be sure.
Seriously. Sniff the traffic. There's nothing that beats troubleshooting a black box emitting a message like that via sniffing.
If you're opposed to using third-party software install Microsoft Network Monitor (or the new Microsoft Message Analyzer).
SQL Server client connections default to TCP port 1433, but the port number can be dynamic, as well. If software is accessing dedicated SQL Server instances installed as default instance then it will likely be using the default port.