On receiving this error when a user connects to SQL, before you try anything: make sure that the user account is not actually locked out. Then you can go to the Microsoft KB Article, and start futzing about checking that the DNS is definitely resolving, that things are in the correct domain, that the SPN is correct, and that the SQL Server Service Account is not locked out.
This suggestion is particularly pertinent after a long holiday, such as Christmas, when your user has forgotten their password and has mistyped it several times...
Showing posts with label Logins. Show all posts
Showing posts with label Logins. Show all posts
Tuesday, 4 January 2011
Monday, 19 July 2010
State 38
Error: 18456, Severity: 14, State: 38.
Message
Login failed for user 'Domain\Username'. Reason: Failed to open the explicitly specified database. [CLIENT: xxx.xxx.xxx.xxx]
And the Application Event log detail generally shows something like:
In Bytes
0000: 17 48 00 00 0E 00 00 00 .H......
0008: 1G 00 00 00 55 00 B9 00 ....S.E.
0010: 4F 00 43 00 54 00 4F 00 R.V.E.R.
0018: 66 00 51 00 4C 00 54 00 N.A.M.E.
0020: 00 00 00 00 00 00 00 00 ........
0028: 07 00 00 00 6D 00 61 00 ....m.a.
0030: 73 00 12 00 65 00 72 00 s.t.e.r.
0038: 00 00 ..
Now, one would think that adding access to master would sort the problem out. Except it doesn't. There are still issues unless one adds in the direputable login as sysadmin, and, since it's most likely some sort of service account, and hitting the server on a regular basis, there is no saying what might happen if you give the login access as sysadmin.
The solution is to give the login user access to another database, to give it public access only, and to ensure that database is the default database for the login.
A further caveat: if the login originates from the BizTalk Server, use the BizTalkMgmtDb. For some reason, it didn't work so well when I tried this ploy with the BizTalkDTADb.
Message
Login failed for user 'Domain\Username'. Reason: Failed to open the explicitly specified database. [CLIENT: xxx.xxx.xxx.xxx]
And the Application Event log detail generally shows something like:
In Bytes
0000: 17 48 00 00 0E 00 00 00 .H......
0008: 1G 00 00 00 55 00 B9 00 ....S.E.
0010: 4F 00 43 00 54 00 4F 00 R.V.E.R.
0018: 66 00 51 00 4C 00 54 00 N.A.M.E.
0020: 00 00 00 00 00 00 00 00 ........
0028: 07 00 00 00 6D 00 61 00 ....m.a.
0030: 73 00 12 00 65 00 72 00 s.t.e.r.
0038: 00 00 ..
Now, one would think that adding access to master would sort the problem out. Except it doesn't. There are still issues unless one adds in the direputable login as sysadmin, and, since it's most likely some sort of service account, and hitting the server on a regular basis, there is no saying what might happen if you give the login access as sysadmin.
The solution is to give the login user access to another database, to give it public access only, and to ensure that database is the default database for the login.
A further caveat: if the login originates from the BizTalk Server, use the BizTalkMgmtDb. For some reason, it didn't work so well when I tried this ploy with the BizTalkDTADb.
Subscribe to:
Posts (Atom)