June 14, 2011 at 9:28 am
Scenario - Migration to a mirrored database environment
- Application database has multiple database user accounts, each associated with a database server login.
- Wanted to setup database server logins prior to application database being setup. To do this I had to set server login default database set to 'master' as application database was not present at this point in time.
- Installed application database and run scripts to setup database user accounts - linked to database server login accounts.
- Follow steps to engage database mirroring - all good to this point...
Whilst monitoring the SQL Server Logs on the test environment we then observed repeated login failures of the following type:
Login failed for user '.......'. Reason: Failed to open the explicitly specified database. (CLIENT: x.y.z.a]
Error: 18456, Severity: 14, State: 38
From what I understand STATE 38 occurs when DEFAULT database for a login is not available at the server (SQL 2008 and 2008R2).
Important items noted
- Error appears to only appear on the database server that was the original Principal database server but is now acting as the mirror.
This database server (the current mirror) is listed as the Principal database server in the client applications connection string.
I am speculating that the client application connection is first made against the first database server listed in the connection string and looks at the default database for the database server login. This is the master database for which the login would have no rights. Therefore a login failure is recorded in the SQL Server Logs. The connection logic may then attempt to use the application database specified in the connection string. This too possibly fails, but the SQL client logic redirects the application to the current Principal database server where a valid connection is made and all is well.
My goal is to the try to eliminate the Login failures being recorded in the SQL Logs.
Am I right in thinking that the only way of doing this would be to alter the database server logins so as to default to the application database rather than master?
Please let me know if additional info is required.
Thanks for any help with this one.
July 8, 2011 at 9:52 am
I experienced this exact same issue today - did you ever find a resolution?
July 12, 2011 at 9:28 am
No reply. Have taken that this is the default behaviour.
Also found this http://social.msdn.microsoft.com/Forums/da-DK/sqldatabasemirroring/thread/04ed2a37-c784-4682-8389-a6b1a659d858. Might be of assistance.
July 12, 2011 at 9:48 am
Thanks - I actually resolved the errors in the end by mapping the user (in my case, the SQL Service domain account) to the database. Doh!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply