Search This Blog

Friday, July 16, 2010

SQLServer Error: 848, SQL Network Interfaces: The system detected a possible attempt to compromise security.

We faced a strange but simple issue yesterday and as usual I would like to share it with you .



Situation :
-------------
SQL server 2005 SP2
Windows Server 2003 SP2
Cluster : Yes 2 node A-P cluster

Service account of SQL Server Agent service and SQL Server service were same .SQL Server is Clustered .

While SQL Sevrer as well as agent were running fine the account under both the services are running ,got locked(we came to know this later as a rootcause of this issue).Still ,everything was fine and there was no issue since the account got locked after SQL Server and agent were started.

Then we found that all the jobs that were scheduled stopped working .In the job history we found that there is no JOB HISTORY created since the jobs stopped working .But there was not a single failure of the jobs .

Which means that the jobs were not scheduled by the Job schedular >> to the Threads >> to the SPIDs .So , we manually executed the jobs and all of them completed successfully .But again , there was no history being created and those jobs were not doing anything .For example , the backup job was running successfully when we ran it explicitly but no backups were taken .

To drill down further , we ran the commands under the jobs in QA and those were running fine .We created new jobs and there was no change at all in the situation .

Then we checked the SQL Agent logs and found this :

[298] SQLServer Error: 848, SQL Network Interfaces: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you. [SQLSTATE HY000]
[298] SQLServer Error: 848, Cannot generate SSPI context [SQLSTATE HY000]
[382] Logon to server '(local)' failed (ConnAttemptCachableOp)

This was strange to us as why the connectivity error is not being displayed when we were explicitely executing the job, which completes successfully and doing nothing.
But since it was the connectivity error by agent , we decided to run the jobs by logging on to the server using the account under which SQL Server and agent are running.

We then found out the the account was locked under which SQL Server and agent were still running.

Once the account got unlocked at the AD ,the jobs ACTUALLY started working .

To me it looks like a bug in design and i have logged it on the CONNECT :
https://connect.microsoft.com/SQLServer/feedback/details/575388/strange-behaviour-in-sql-agent-job-on-cluster-where-the-job-runs-but-does-not-do-anything

hope it helos you to resolve your issue .

No comments: