SQL Server Mysteries: The Case of the Dropped AD Group Login
Dylan (who kindly wrote up the contents of this blog for me) was modifying the tests for Active Directory Login activities. As Dylan and I reviewed the changes a specific behavior involving Active Directory Group Logins caught our attention.
Imagine you have a group on your domain [CONTOSO\group] which has a member [CONTOSO\user], and the [CONTOSO\group] has login permissions to a SQL Server instance. Running “DROP LOGIN [CONTOSO\group]” we expected that the [CONTOSO\user] would no longer have access to SQL Server. However, what we observed was:
- New connections from [CONTOSO\user] are not accepted
- Existing connections may still function as described here (The existing [CONTOSO\user] connections have their group memberships and permissions cached, so the connection will be able to continue issuing queries until those values are refreshed.)
If [CONTOSO\user] was added as a login and connected to SQL when “DROP LOGIN [CONTOSO\user]” was run, the “DROP LOGIN” command fails with error “Could not drop login 'CONTOSO\user' as the user is currently logged in.” Once [CONTOSO\user] disconnects, “DROP LOGIN [CONTOSO\user]” will succeed and the user will no longer have access to SQL Server.
If [CONTOSO\group] was added as a login and [CONTOSO\user] was logged in when you ran the command “DROP LOGIN [CONTOSO\group]”, the “DROP LOGIN” command succeeds. However, you can run “SELECT login_name FROM sys.dm_exec_sessions WHERE login_name = ‘CONTOSO\user’” to see that [CONTOSO\user] is still logged in.
When you run “DROP LOGIN <loginName>”, SQL Server checks if there are any active connections for the account <loginName>. If there are no connections active for this login, the login is removed, and the command succeeds. However, if the <loginName> is an AD group, SQL Server does not check if any members of the group have active logins. When we dropped [CONTOSO\group], the command succeeded because there were no active connections for [CONTOSO\group], even though [CONTOSO\user] was logged in and a member of [CONTOSO\group]. In fact, [CONTOSO\user] will stay connected until they perform a privileged action (e.g. create a login) or they reset their connection (e.g. log out and back in again, sp_reset_connection). At that time, SQL Server will check the permissions of [CONTOSO\user], see it no longer has a valid login, and kill the connection.
You can force these connections to be reset. After dropping the group login, run “SELECT session_id FROM sys.dm_exec_sessions WHERE login_name=’CONTOSO\user’”. This will give you the session IDs of all [CONTOSO\user]’s connections. Now, run “KILL <session_id>” to terminate the connection. The next time [CONTOSO\user] runs a query, they will be forced to recreate the connection which will fail because they no longer have a valid login.
The connection itself can also force a permissions update by calling sp_reset_connection which causes the thread to log out and back in to SQL Server. This is useful for connection pooling since the connections are persistent and do not perform permissions checks by continuously logging out and back in. By calling sp_reset_connection when you retrieve a pooled connection, the connection has to authenticate and fails if the login permissions have been revoked.
Now what about revoking permissions? Once permissions for an action are revoked for a group, the member users’ permissions are immediately revoked. For example, if [CONTOSO\group] had select permissions on table “foo”, and you ran “REVOKE SELECT ON foo TO [CONTOSO\group]”, [CONTOSO\user] would immediately lose their select permissions on “foo”.
Test it for yourself with the commands below:
# Login as an admin user to SQL Server and add [CONTOSO\group] as a login
#
CREATE LOGIN [CONTOSO\group] FROM WINDOWS
GO
# In another terminal, login to SQL Server as [CONTOSO\user] and run a query to show you have access
#
SELECT @@VERSION
GO
# Switch back to your admin terminal and drop the group login. It will succeed even though [CONTOSO\user] is connected.
#
DROP LOGIN [CONTOSO\group]
GO
# Switch back to your [CONTOSO\user] window and check you still have access
#
SELECT @@VERSION
GO
# Switch to the admin terminal and kill the connection
#
SELECT session_id FROM sys.dm_exec_sessions WHERE login_name = ‘CONTOSO\user’
GO
KILL <session_id from query above>
GO
# Switch to your [CONTOSO\user] window to see they no longer have access. This command should not return the version information.
#
SELECT @@VERSION
GO
Dylan Gray – Software Engineer
Bob Dorr – Principal Software Engineer