SQL Server Error: 18456, Severity: 14, State: 16 creating SharePoint SSO database

Question

I'm using a web application (SharePoint) to create a database for Microsoft Single Sign On. Every attempt to create the database is giving a 'login failed' style error. For testing purposes I am using the same account on the SharePoint web application and timer service, Microsoft SSO Service, and in the 'Manage Server Settings for Single Sign-On' page in SharePoint.

The two SQL Server errors being logged are:

Login failed for user 'blah'

Error: 18456, Severity: 14, State: 16.

The Windows Server event log error show the same 'Login failed' message with the word 'master' in the data section.

I have tried:

• Giving the user sysadmin permission on SQL Server
• Changing the default database to one where the user has db_owner permission as well as setting it to 'master'
• Making the user local administrator on the SQL Server

The comments in this article on the SQL Protocols blog state that:

State=16 means that the incoming user does not have permissions to log into the target database... This can also happen if for example the default database for user FOO is not online (for example the database is marked suspect).

I think I've covered the permissions and SharePoint is using the databases so they shouldn't be marked suspect.

Does anyone have some ideas?

Solution

This now appears to be resolved. I had to log in to the SharePoint server as the SSO service account, as described by Frank Grossmann. In fact it looks as though this SQL login error is spurious and unrelated to SSO!

Comments

[Ryan Ferretti]

Are you still getting the same error state after making the user a member of the sysadmin fixed server role, or did the error state number change at that point?

[Alex Angas]

The error state number is always 16, whether the user is in the sysadmin role or not.

[Bogdan_Ch]

can you run SQL profiler and see what sql command causes the error? perhaps it will give you more details on error context