none
SIDHistory and SQL logon issues.

    Question

  • Hi guys,

    I am really struggling with this one and really hope someone has an answer for me. My SQL knowledge is practically non-existent, so please excuse me if I am not 100% on the ball :)

    PROBLEM

    DomainB\User1 uses an SQL resource on DomainA\SQLServer. This works fine until I clone the user account from DomainB to DomainA in preperation for a migration.

    When a new replicated user account is created in DomainA with a different SID, but with replicated SIDHistory, the SQL login fails with an error similar to a duplicate SID, however this is not the case as the SID on each account is unique.

    This still happens using the DomainB\User1 login.

    TEMPORARY RESOLUTION

    Restarting the SQL service resolves the problem for 1-4 days, then re-appears again with the same issue.

    If I remove the SIDHistory attribute from the AD user account in DomainA, the SQL login also works.

    SITUATION

    This happens for all users I am trying to migrate and we need SIDHistory for resource access reasons.

    Can you think of any reason why SQL would be looking at the SIDHistory value then erroring with an ambiguous logon error rather than looking at just the SID, and also if there is a way to stop this from happening.

    Does restarting the SQL service reload the SID's from AD in to the user table?

    Thanks guys,

    John.


    Friday, November 16, 2012 1:22 PM

All replies

  • Hallo John,

    if you work with domain accounts (integrated security) as with sql acounts the SID is the identification feature which covers the uniqueness of a login / database user.


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Friday, November 16, 2012 1:58 PM
  • are you still using domainb\users to connect to sql server?

    have you dropped domainb/users from sql server or use sp_change_users_login  to map logins?

    check login's SID at sql server is showingnew one or old one. 

    check & share sql server error logs & windows event logs for some details about the error.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Friday, November 16, 2012 4:00 PM
  • Hallo John,

    if you work with domain accounts (integrated security) as with sql acounts the SID is the identification feature which covers the uniqueness of a login / database user.


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Absolutely, but I am referring to the SIDHistory value affecting the logon.

    Monday, November 19, 2012 8:54 AM
  • Hi Rohit,

    are you still using domainb\users to connect to sql server?

    Yes, the Domainb\user1 account is accessing DomainA\SQLServer. Creating the new account in DomainA with the SIDHistory breaks the logon for the DomainA\User1 account.

    have you dropped domainb/users from sql server or use sp_change_users_login  to map logins?

    Not yet, the DomainA\User1 account is not yet in use, but just created. 

    check login's SID at sql server is showingnew one or old one. 

    SID shows original SID in SQL. User can not log on, however, deleting the SIDHistory valuse for DomainA\User1 allows DomainB\User1 to log on. A restart of the SQL service also resolves the issue.

    check & share sql server error logs & windows event logs for some details about the error.

    No details at all of any errors.

    Really stumped on this one now. SQL seems to be confusing the SIDHistory value with the SID for the accounts.

    Monday, November 19, 2012 9:01 AM