Share via

Impersonation in SQL CLR

  • Article

When the user code will try to access a resource outside of the SQL server it will run in the security context under which SQL Server is running. But this is not the ideal situation since it could become a security issue when the logged user that calls the code has less privileges than account under which SQL Server is running. It is also possible that SQL Server will run under a low privilege account and the user cannot access the desired resource. So it is highly recommended that the user code will impersonate before accessing resources outside of the server.

There are 2 possible impersonation types in SQL CLR. I will call them implicit and explicit impersonation. For the implicit impersonation the executing code will try to impersonate the calling user. For this type of impersonation there is no need for the user name and password. In the explicit impersonation the code will try to impersonate any NT user using name and password.

The implicit impersonation is done by obtaining the user impersonation token from the SQL Server calling SqlContext.WindowsIdentity and calling Impersonate method that will change the security token of the thread. At the end of accessing resource the user should revert thread token by calling Undo method of the WindowsImpersonationContext returned by Impersonate method. SQL Server will check if the user did revert the thread token and it will throw an error message if it was not reverted. This means that every in-proc data access call should be made after reverting back. If the managed code throws an exception that is not handled, SQL Server will not show the exception but it will show the error message the user thread token was not reverted. This is the reason for which the Undo should be in a finally block. See the SQL Server Books Online for the help.

SqlContext.WindowsIdentity will be null for the sql logins. If the user will log in as sa, the SqlContext.WindowsIdentity will return the identity under which the server process is running. For windows logins it will return the corresponding windows identity even if the login is sysadmin. If the execution is happening in the context of an Execute As Login (statement directly or some module marked with it ) SqlContext.WindowsIdentity will be null unless the login is sysadmin. In this case SqlContext.WindowsIdentity will return the identity under which the server process is running.

The explicit impersonation is done by calling LogonUser with the name and password constructing WindowsIdentity with the token obtained. This impersonation is working inside of the SQL CLR but is not recommended. The same restriction will apply for reverting before in-proc data access or finishing execution. This means that it will be possible for the user to do an explicit impersonation after an implicit one but it is not possible the other way around. With the explicit impersonation it is possible to leak token handles in the user code (since they are opened in the unmanaged code).

 

Regards,

Mihail Frintu

 

This posting is provided "AS IS" with no warranties, and confers no rights.

Cross Posted from https://blogs.microsoft.com/sqlclr

Comments