How to see the connection string used to connect to SQL Server

Question

Is there a way to see what connection string was used to connect to SQL Server? Or rather what string was used in a failed login attempt.

Many times I deal with complex systems in which I see failures caused by some service failing to log in to SQL. I am guessing the connection string might be wrong, but since I can't see what it was I often have problems figuring out who actually had that problem connecting to be able to reconfigure it and get it fixed.

The errors in the log just say something like 2009-12-01 20:16:31.05 Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 10.124.172.65] 2009-12-01 20:16:31.06 Logon Error: 18452, Severity: 14, State: 1. 2009-12-01 20:16:31.06 Logon Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 10.234.222.13]

Is there some audit I could enable or a tool to sniff out the connection string so I could figure out what's wrong with the string by analyzing the string itself?

Answer 1

Not really. The connection string is only a matter between the client application and the SQL client libraries and there are no out-of-the-box tools to snoop into that tight spot.

However, in your example there is no need to know the connection string. You know the connection string was using Integrated Security ('SSPI handshake' is by definition that), you know it was trying to connect to the server on which this error was logged, you know the cause of failure (error SEC_E_LOGON_DENIED), and you know which client tried to connect (10.234.222.13). There is absolutely nothing in the connection string that would help troubleshoot this problem.

The error you're seeing is an SSPI error, specifically and Kerberos/NTLM error and you should approach it using the Kerberos/NTLM tools and methodologies. Most, if not all, Kerberos/NTLM issues can be troubleshoot using the Troubleshooting Kerberos Errors document.

In your case you'll likely find one of the common culprits:

• services running under a local account trying to connect remotely.
• attempt to connect across untrusted domain boundaries
• (most likely) services running with an expired password

Answer 2

First, there isn't a centralized way to do this out of the box.

However, these types of issues are a good part of the argument for having a DAL, where you centralize all connection and transaction management, along with associated error logging.

SQL Profiler may also give you some info -- although probably not enough to debug the problem, it might give you a hint in the sense of the timing of the error.