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
  • Hi

    I am having same problem . Do you guys get any fix for this ?

    Thanks

    Mumtaz

    Tuesday, September 20, 2016 10:45 AM
  • ...

    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.

    ...

    I am not understanding your procedure quite.

    What do you mean by cloning? You used the "ADMT USER" command and left the target account disabled yet?

    Have a look at this post which contains the procedure: https://www.experts-exchange.com/questions/28345244/Migrated-users-cannot-access-resources-in-source-domain-even-though-sid-history-is-enabled.html


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform
    www.SarpedonQualityLab.com | www.andreas-wolter.com

    Tuesday, September 20, 2016 7:48 PM
  • Hi John,

    Correct me if I am wrong. You are trying to migrate Active directory from one domain to other domain.

    You will get more answers if you post in the windows Active directory forum. please refer to the below link.

    https://technet.microsoft.com/en-us/library/cc974384(v=ws.10).aspx

    please node SQL server uses two types of authentication 1) SQL authentication 2) windows authentication.In SQL server authentication SID values stores with in the master database of sql server.

    I think your SID history is related to the windows authentication. As long as Active directory authentication is verified SQL server security will allow connections.

    Thanks

    Kumar

    Tuesday, September 20, 2016 11:50 PM
  • Hi,

    I believe there is a trust relationship between two domain if not the same forest. if user is migrated each other, the SIDHistory will automatically created, but not for clone user account.

    It may work if User1 in domain B access SQL server in Domain A if the User1 is migrated from Domain A to Domain B due to SIDHistory. 

    well, if you clone User1 (with duplicate SIDHistory) which exist in Domain A and Domain B on same forest, SQL Server will not work for User1.


    Aliyani Sabrey http://netoverme.wordpress.com

    Wednesday, September 21, 2016 12:17 AM
  • John

    I'm no AD expert, but my suspicion is that the original SID from DomainB is included in the access token for the account in DomainA and hence the clash in the SID value.

    The workings of SID History are beyond me, but I wonder if the old DomainA SID is only added if the account uses resources back in DomainA and that might explain why it works for a few days before breaking?

    I'd suggest seeing if you can find more information about what SID History adds to the access token and seeing if this could be the explanation.


    Martin Cairney SQL Server MVP

    Wednesday, September 21, 2016 1:30 AM