Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2010
    Posts
    4

    Unanswered: AD Username Change not working in SQL Server

    Got a bizarre problem that we can't seem to figure out.

    We have a large enterprise with many regions and each region has it's own domain controller and SQL Server. We have an AD group called "Domain Users" which all standard users are a member of. This AD group has access to the SQL Servers in the "public" role and we restrict security through the applications which hit the SQL Server. The application connects to the SQL server using Windows Authentication.

    Anyways, we have a user who requested a AD username change because of a marriage. The AD username was updated in our main office. She is able to connect to the application in our main office without a problem. However when she connects to any other office, she gets an error from the app which indicates her user account doesn't exist in the application.

    If I run Profiler against the two servers, the server in the main office shows her new user account in the LoginName field. However in the other servers, they all show the old username in the LoginName like the SQL Server has somehow cached the username against a SID or something.

    I've checked the local DC's and the new username has propagated out to all of them.

    I am STUMPED. I'm tempted to just reboot a SQL server to see if that fixed it, however, that is not a great solution for this as we have a lot of these systems and rebooting a ton of production servers isn't viable whenever a username changes.

    Any help would be appreciated!

  2. #2
    Join Date
    Dec 2006
    Posts
    30
    Long shot, but are you sure you don't have an out-of-date global catalog server somewhere?

  3. #3
    Join Date
    Nov 2010
    Posts
    4
    Not sure, can you elaborate on this a little?

    Also, we have some SQL 2000 servers which seem to be unaffected by this, the only ones she is having issues with are SQL 2008

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If I am reading this correctly, the user was added under their old name as a user in SQL Server with Windows Authentication, and permissions were granted to that user. The User's Login name was then changed in AD. Now the user can not log into those servers where she was specifically added as a user?

    This would be SQL Server does not get updates from AD. The name would have to be changed in each SQL Server. The best way around this, of course, would be to organize users in AD Groups, and in Roles in each database. So long as only the AD Group (whose name never changes) is the login from SQL Server's perspective is somewhat dynamic. Otherwise, it gets to be a lot like managing file permissions for individual users.

  5. #5
    Join Date
    Nov 2010
    Posts
    4
    No that is not quite right.

    We don't have any specific users in SQL Server (except admins). There is a AD group this user is a member of which has access to SQL Server.

    When looking at profiler against the server when she tries to log into the system, Profiler shows her OLD AD user name on these SQL 2008 boxes. While on the SQL 2000 boxes, it shows her NEW AD user name and she has no problem logging in.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Ahh, now I see.

    Yes, there would have to be some sort of caching going on for that. SQL Server is not likely caching anything, but Windows on that box may be doing it. In that case, yes a reboot should fix the problem, but it is hardly a good option. I don't know Active Directory well enough to say for sure if there is a better way to fix it or not. Sorry.

  7. #7
    Join Date
    Nov 2010
    Posts
    4
    I'm going to find out if it's the OS or SQL Server tonight hopefully, I'm going to try just restarting the SQL service and see if that fixes it. If not, then OS reboot it is.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Create a new AD group. Set a GPO for that group to flush credential cache. Move the offending SQL Servers into the new AD group. Monday you can move the servers back and destroy the group.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •