Error: 18456, Severity: 14, State:38

  • adb2303

    SSCertifiable

    Points: 7586

    Hi,

    I've just moved all my SQL Server 2005 databases to a SQL Server 2008 R2 server with the same domain. I am 100% confident that all databases are present and accounted for. I used sp_help_revlogin to get the logins from 2005 server to the 2008 R2 server. All logins were created successfully. I then ran the following:

    ALTER USER [MYDOMAIN\MYACCOUNT] WITH LOGIN = [MYDOMAIN\MYACCOUNT]

    The problem is, I am getting the following error in the SQL Server Error log:

    Login failed for user 'MYDOMAIN\MYACCOUNT'. Reason: Failed to open the explicitly specified database [CLIENT:XX.XX.XX.XXX]

    Error: 18456, Severity: 14, State: 38

    I get this error even if I give the give the domain account sysadmin privileges on this box (which it doesn't have normally).

    I query sys.dm_os_ring_buffers and I can see several RING_BUFFER_SECURITY_ERROR errors, with ErrorCode: 0x534, APIName: LookupAccountSID. This, as far as I know, is a problem resolving the login details against AD. Then why does the very same account work perfectly fine on the same server in all the databases that it's been permissioned for?

    Any help greatly appreciated.

    Andrew

  • adb2303

    SSCertifiable

    Points: 7586

    sp_validatelogins doesn't return anything either

  • anthony.green

    SSC Guru

    Points: 112515

    Run a trace for failed logins and see what database the failure is on.

    Then check to see if the DB exists or if the user has access to the DB.

  • adb2303

    SSCertifiable

    Points: 7586

    hi,

    Already tried that. It shows the database for the failed login attempt as 'master'. I'm hoping that that's still there!

    Cheers, Andrew

  • anthony.green

    SSC Guru

    Points: 112515

    Does the user have access to master

    Remembers you detailed error exists if grant SA rights.

    What is the default database for the user? Does that exist?

  • adb2303

    SSCertifiable

    Points: 7586

    the default database is master.

    This account is a domain admin. When we were on 2005, although it had permissions explicitly granted at the database/object level, it was a member of the sysadmin role via builtin\administrators. Obviously in 2008 that's no longer the case. Through testing, we found that the account didn't require any further permissions, so it was left with the explicit permissions. The software that uses this account and connects to SQL Server doesn't report any errors. I can see activity from this account on each of the databases that it needs to have. I still have the old 2005 box, and comparing permissions, databases, etc. side-by-side, it looks identical.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply