Sql-server – SQL Alert: 020 – SSPI handshake failure. Tracking the cause

authenticationsql server

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!

Best Answer

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.

Related Solutions

Sql-server – Working example of OPENDATASOURCE command using SQL Authentication

To run it with a SQL login, just need to specify User ID and Password in the connection string (called "init string" in BOL)

SELECT
    * FROM
OPENDATASOURCE (
   'SQLNCLI', -- or SQLNCLI
   'Data Source=OtherServer\InstanceName;Catalog=RemoteDB;User ID=SQLLogin;Password=Secret;').RemoteDB.dbo.SomeTable

Sql-server – SQL Server grant permissions to STANDBY database

Below steps should work :

Create windows login on ServerA
Create user in database mapped to login
Drop login on ServerA - (OPTIONAL -if you want to have that login intact then leave it, else drop it).
Grant any required permissions to user in database

Create login on log shipping ServerB

a. If using SQL authentication then create login while specifying SID from ServerA

b. If using Windows authentication, create login without specifying SID

 ---To query for the SID on the ServerA:

  Select SID
  From sys.database_principals
  Where name = 'Test'

 ---To create the login on the ServerB :

  Create Login [Test]
  With SID = '<put SID from query here>',
  Password = '<add password here>'

Make sure log shipping jobs have completed one full run since completion of step 4

Best Answer

Related Solutions

Sql-server – Working example of OPENDATASOURCE command using SQL Authentication

Sql-server – SQL Server grant permissions to STANDBY database

Related Question