There were thousands of entries on the SQL server with:
Login failed for user 'DOMAIN\service.account'. [CLIENT:local machine
I then looked at the SQL logs that were generated and stored at:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
Again, the error logs were filled with login failures such as:
Logon Error: 18456, Severity: 14, State: 16.
Logon Login failed for user DOMAIN\service.account'. [CLIENT:local machine
The error messages did not reference a specific database, which made finding the cause of the problem difficult. The frustrating part was that SharePoint 2007 was working just fine. There were no errors and no failures from the SharePoint server itself. Users were creating lists, libraries and storing documents.
The SQL Server is using Windows Authentication. The account service.account had all the correct rights and permissions for accessing all of the SharePoint databases. It was set to use ‘master’ as the default database. To be certain, I added the account to the local administrator group, as well as the domain administrators group (overkill). I double-checked SharePoint and the configuration to be sure the correct account was being used.
Then I went back to the SQL server and started looking at the SQL Server Agent logs, and I found my first clue.
My SQL Agent logs were filled with the following error messages:
SQLServer Error: 18456, Login failed for user 'DOMAIN\service.account’ [SQLSTATE 28000]
SQLServer Error: 4060, Cannot open database "DBNAME_SSP_DB" requested by the login. The login failed. [SQLSTATE 42000]
It identified the DBNAME_SSP_DB as the database that was trying to be accessed. So now I had both the database name and I knew it was the SQL Server Agent service. Unfortunately – the DBNAME_SSP_DB database did not exist on my server.
The database DBNAME_SSP_DB was created during an initial installation of SharePoint, and later the SSP was deleted, along with the corresponding database.
I went back to SQL Server Management Studio to look for references to DBNAME_SSP_DB and found a SQL Server Agent Job named DBNAME_SSP_Job_DeleteExpiredSessions
This jobs task is to run every minute to clean up ‘expired sessions from the session state database’.
When the SSP and corresponding database had been deleted, this job had failed to be deleted along with them.
Once this job was deleted, the errors stopped occurring.
