In my SQL environment, I manage all of my SQL Server rights by Windows Authentication, based upon Active Directory groups. This allows me to centralize my security administration into groups for specific types of users within my environment. I also get the benefit of my developers not ever accessing my databases with connection strings containing a clear-text password to my databases.
I have had two specific issues arising from managing my security in this way. One of my problems is that this allows my domain administrators to have some level of control over who might have access to a database. I have, on at least two occasions, had an over-zealous admin assign rights to a user “because the user asked for it” without my knowledge. I have already posted my solution to this problem in my post, Monitoring Active Directory Groups for Windows Authentication in SQL Server Using PowerShell. In this post, I would like to discuss my other issue, propagating changes in Active Directory quickly.
When I first started using Windows Authentication for my SQL Servers, based upon Active Directory groups, I would notice that I would add a user to a group in Active Directory and it would take a long time before the user was actually able to use the rights; sometimes they even had to reboot. This led to a lot of frustration on both my side and the users’. It even caused us to jump back to SQL Authentication on some time-sensitive rollouts, because we had no idea why the Active Directory rights were “not working”. So, after a few embarrassing episodes, I decided to dig into why this was happening.
To me, it made no sense why a Microsoft core enterprise system (SQL Server) talking to another Microsoft core enterprise system (Active Directory) couldn’t figure out that new permissions had been granted to a user’s group. So, I dug a little into how Kerberos works and found that the group membership is passed in the Kerberos ticket, not just a reference back to Active Directory (which I had thought). So, this meant that when a ticket was granted to a user’s session, the groups that the particular user was a member of were held until either the ticket was cleared or expired.
It can be useful to see what tickets you have open, which you can do by typing “klist” into a command prompt. This, however, only lets you see your current tickets.
So, that led me to looking into what actions cause your Kerberos tickets to regenerate. Remember that little message bubble that shows up in one of your sessions right after you have changed your password?
When you lock and unlock your computer, you are causing Windows to request new Kerberos tickets. This also explains why rebooting a user’s machine caused the rights to start working, even though it was complete overkill.
Another way to force Windows to request new Kerberos tickets is to run “klist purge” from the command prompt. This explicitly asks Windows to dump your currently Kerberos tickets and thus, request new ones.
Although this is a simple problem, solving it finally relieved a nagging headache I had experienced from time to time.
0 Comments.