Results 1 to 8 of 8

Thread: Updating AD

  1. #1
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Unanswered: Updating AD

    Having a Small issue that Changes to users AD account is take upto 2 hours to be picked up in SQL Server, so i'm assuming SQL Server Caches them and the refresh rate is 2 hours.

    how do i force a refresh?
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Exactly what takes two hours to be picked up? When a user logs in, he gets an access token signed by the domain controller authentication him. This token contains, among others, group SIDs. This token is being presented by the user whenever he needs access to a service, such as a file share, an IIS site with Integrated Authentication, or SQL Server with Windows Authentication. This is to offload the domain controllers, which otherwise would be extremely busy.

    If you change something contained in the access token, the user will have to log off and log in again to get a new access token. This is nothing special for SQL Server, it would be the same for any other service as well.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    things like a user has just gotten married and had her username changed to reflect her new name, and for 2 hours she was unable to use any databases because SQL wouldn't recognise her new username until it re polled AD to JaneSmith permissions now apply to the account with the username JaneJones. and this is after the user has logged off and on again

    all windows permissions are fine but SQL isn't picking them up which is what leads me to think its using a cached copy of ad

    AD ACL Cache - Active Directory seems show a similar issue
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    ok

    to put the events in order

    miss jane Smith gets married and becomes Mrs Jane Jones

    Mrs Jones then says i'm no longer JaneSmith change my User Name to JaneJones

    AD entry is changed from JaneSmith to JaneJones

    Mrs Jones in a member of AD Group DB_X_User

    Mrs Jones logs on using her user name JaneJones

    Mrs Jones then runs a query against DB_X which allows members of DB_X_User to log on and worked perfectly well for JaneSmith however it now rejects her as an unauthorised user and will continue to do for up to 2 hours when suddenly it decides that she actually is a member of DB_X_User after all

    Also if you try to add her directly to the server ie creating a login for her and not the group, SQL when asked to check her account against AD ie the check names button return the JaneSmith user name not JaneJones again this behaviour corrects itself after 2 hours.

    however its not really in the businesses best interests to have people that can't work for 2 hours while we wait for what i assume is SQL to update its AD cache

    also DB_X_User has access to a protected folder on a server in order to save the results of the query, mediately after logging in she has access to this folder. even though she can't access the query that generates the data
    Last edited by m.timoney; 07-18-11 at 09:38.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    SQL Server verifies AD (Active Directory) Credentials like JaneSmith against the DC (Active Directory Domain Controller) whenever credentails are presented. SQL Server will cache AD Credentials while a connection (spid) exists for an AD Login, but that cache ought to flush quickly.

    When the new AD Credentials are presented like JaneJones, those will be validated via the DC too. The only delay for AD Authentication that I would expect is if there is an AD Propigation issue between mutliple DCs within your AD Forrest. I've never seen SQL Server itself induce a delay in authenticating via Active Directory.

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

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    that would make sense to me to unfortunately it is happening, creation seems to be propagating properly. as if you add a group to a AD entry or create a new one it is instantly available

    however updates don't appear to be, if it was my DB rather than AD i would think someone had missed a trigger on the update command while remembering it on the insert and delete commands

    and as i said windows is accepting the changes automatically and instantly its only SQL Server thats having an issue
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just to be clear this AD account was renamed and was not recreated, correct?

    As a second issue, what does
    Code:
    EXECUTE xp_logininfo 'JaneJones'
    show? This might be one of my favorite connundrums with SQL Server and AD which is when an AD user belongs to more than one AD Group that has access to a SQL Server.

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

  8. #8
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    yes renamed not recreated


    account name type privilege mapped login name permission path
    dom\JaneJones user user dom\JaneJones DOM\DB_X_User

    however now well past the 2 hour window so all working
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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