Error: 18456, Severity: 14, State: 38
Login failed for user 'produser'. Reason:Failed to open the explicitly specified database. [CLIET: 192.168.10.10]
or
Error: 18456, Severity: 14, State: 16.
Login failed for user 'XXXXXX'. [CLIENT: xxx.xx.x.xxx]
This is one of the most frustrating login failure errors. It does not mention what database the login was trying to connect to. The windows event viewer logs do not give any further information.
It seems that prior to SQL Server 2008 this meant the same as State 16. But in that case you would actually see the database name to which the login was trying to connect.
To troubleshoot state 38 you need to run a profiler trace and capture the following two events.
Errors and Warnings: User Error Message
Security Audit: Audit Login Failed
Make sure you select all the columns and run the trace while the login attempt is made.
For the event “User Error Message” you should see the database name to which the connection is being attempted. Verify the database exists and verify that the user has been created in the database and has the permission to connect to the database. You should also check if this is an orphaned user and fix it.
Login failed for user 'produser'. Reason:Failed to open the explicitly specified database. [CLIET: 192.168.10.10]
or
Error: 18456, Severity: 14, State: 16.
Login failed for user 'XXXXXX'. [CLIENT: xxx.xx.x.xxx]
This is one of the most frustrating login failure errors. It does not mention what database the login was trying to connect to. The windows event viewer logs do not give any further information.
It seems that prior to SQL Server 2008 this meant the same as State 16. But in that case you would actually see the database name to which the login was trying to connect.
To troubleshoot state 38 you need to run a profiler trace and capture the following two events.
Errors and Warnings: User Error Message
Security Audit: Audit Login Failed
Make sure you select all the columns and run the trace while the login attempt is made.
For the event “User Error Message” you should see the database name to which the connection is being attempted. Verify the database exists and verify that the user has been created in the database and has the permission to connect to the database. You should also check if this is an orphaned user and fix it.
Very helpful. thanks. save me a day.
ReplyDeleteSorry, did't help me. Both messages don't tell me the database name if the database does not exist. Just tested it on my testsystem with SQL Server 2008 R2.
ReplyDeleteOk, it did help. There are two "User Error Messages". The first contained the actual database name. Thx!
ReplyDelete