2

I have done my best to find an article that answers this question but I have not had much luck.

I manage the database across three domains. One domain has the ability to access the other two, but without a trusted connection (SQL traffic through firewall using SQL credentials). I have started getting a lot of SSPI handshake failures in my logs (and as such alerts). I know that a percentage of them are my fellow developers forgetting to use SQL Auth and the server legitimately failing to authenticate.

However I also believe that there is an application out there (or an ODBC setting, authenticated excel report, etc) that is causing some of the errors. I have tracked the problem down to our RDP farm, but not to the root application/etc.

My question is: How can I determine the application/remote-user/something that is causing the SSPI error so that I can resolve it once and for-all!

1
  • I would suggest to run a server side trace that will help you find out in detail as to what is causing those errors.
    – Kin Shah
    Commented Jun 5, 2014 at 12:16

2 Answers 2

1

Usually when you get the SSPI notification (or from the error log ), you will see the IP address from which the app\user tried to connect. The IP may be internal or external.

Just launch the command line and type:

NSlookup IP

(using the IP address from the log). This will give you the hostname from which the connection request came.

1
  • Paul. Unfortunately the IP is always one of our TS farm! So I expect it is a single user who is being re-assigned around the farm. Short of running a ss-trace and just waiting for it to happen I dont know how else to locate the app. Commented Jun 10, 2014 at 7:21
1

You'll need to run a profiler trace or an extended events session, and even then it may not have the application and/or username.

You may need to run an ODBC trace on the TS servers and see what's logged in there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.