3

I have SQL Server 2012 and I am using domain users (from Active Directory). By mistake users were deleted from AD, created new with identical logins and passwords (but with different SIDs), deleted and restored the original ones - in the end old users with old SID's were recreated.

Almost everything seems to be fine again, except for one user. SQL Server somehow got stucked with wrong SID for one user (all other users are fine) and is not getting the right SID from AD. I checked SID in both places - SIDs in AD is not the same as in SQL Server.

As result of this "SID mismatch" this user can not access to database. If it tries, it fails and gets:

  • "Error: 18456, Severity: 14, State: 5."
  • Login failed for user 'DomainX\UserX'. Reason: Could not find a login matching the name provided.

I deleted user from server and all databases it was used, then added - and again got wrong SID! And this "new" user still can not connect to database.

There are two things that "grinds my gears":

  1. how SIDs for other users match and mismatch for just one?
  2. if other users are fine (they got old SIDs), then I can assume there is not problems in domain controller?

I have found a lot about different manipulations with two existing user (or orphaned?) logins, but failed to found about just changing SID.

But the main question is - how to change SID of one user in MS SQL Server (version 11.0.)? Is this even possible?

UPDATE
As I keep thinking about this, I realised that I have wrong question. Since this UserX is domain user, it is not SQL Server local user - that means that I can not change SID directly in SQL Server.

I have two identical servers (ServerA and ServerB) that uses the same users from AD. In ServerB UserX has got right SID and can normaly authenticate in SQL Server (right SID means the same as in AD). To be clear - UserX has got wrong SID in ServerA.

Question - if this user is domain user from Active Directory, where and how change its SID? If UserX can authenticate in ServerB, that means SID must be changed in ServerA within Windows Users?

Please correct me, if some thoughts or ideas has gone in wrong tracks.

UPDATE 2
I have found a solution to my problem, although it did not answered the question. For anyone interested - my colleges found deleted user with that wrong SID in AD. That user was renewed and now in AD both users are active - the good one and the bad one. SQL Server recognized users and problem with user login and SID mismatch somehow resolved itself - UserX in ServerA now has right SID and can access database. Now everything works as it should be! :)

Kristaps Vilerts
  • 133
  • 1
  • 6
  • 1
    check this: http://superuser.com/questions/581042/is-it-possible-to-change-the-sid-of-a-local-user-account-manually . However be very careful with this. changing an SID can really screw things up. – SimonS Apr 07 '16 at 06:19

2 Answers2

2

I had a similar situation with a deleted AD user and SID mismatch. For me, the problem was local SID caching and the solution was to follow the workaround in the link below on the SQL server and client's pc.

https://support.microsoft.com/en-us/help/946358/the-lsalookupsids-function-may-return-the-old-user-name-instead-of-the

  1. Open Registry Editor. To do this in Windows XP or in Windows Server 2003, click Start, click Run, >type regedit, and then click OK.

    To do this in Windows Vista and newer, Click Start, type regedit in the >Start Search box, and then press ENTER.

  2. Locate and then right-click the following registry subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa>

  3. Point to New, and then click DWORD Value.

  4. Type LsaLookupCacheMaxSize, and then press ENTER.

  5. Right-click LsaLookupCacheMaxSize, and then click Modify.

  6. In the Value data box, type 0, and then click OK.

  7. Exit Registry Editor.

Optionally, afterwards, right-click the LsaLookupCacheMaxSize DWORD you created and delete it.

BigO71
  • 36
  • 3
1

Have you tried checking for orphaned users by issuing the following command:

sp_change_users_login 'Report'

(against the master database of the affected SQL Server and against the database_in_question on the affected server.)

If you have orphaned users, you can then issue a:

sp_change_users_login 'Update_one', 'Domain\UserX', 'Domain\UserX'

...against the affected database or if the SQL Login is mapped to a database user issue a:

sp_change_users_login 'Update_one', 'UserX', 'Domain\UserX'

to fix the SID mismatch.

Syntax of sp_change_users_login on the Microsoft site.

John K. N.
  • 1,955
  • 1
  • 16
  • 26