Impersonation with SQL CLR

I’ve been creating a database project that uses SQL CLR procedures to call a variety of web services and return the data back to my user’s query window.  Many of the web services are developed in-house and do not allow anonymous logins to access the data.

Because of this, one of the business rules for accessing this database is that all connections must be made using Windows Authentication.  In my CLR project I’ve set up any procedures to use the SqlContext object to ensure the connection was made using Windows Authentication and is impersonated so the credentials can be passed along to the web service being called.

You’ll find clear instructions on using the SqlContext object in C# to impersonate the login of the user connection to SQL Server in BOL.

I’ve come across a circumstance where an application must connect to the database using a SQL Login.  This login is trusted and needs to be allowed to call the web services but SQL Logins are not authenticated on the domain so exceptions are thrown.

Seemed easy to solve at first, call the CLR procedure with an EXECUTE AS LOGIN = ‘myDomain\myUser’ to impersonate a Windows Login in SQL Server.  However, after much troubleshooting and poking and prodding of the code I found the impersonation with SqlContext is not quite as simple as it seems.

Here are the scenarios I discovered:

  1. Connection: Windows Authentication – SqlContext.Impersonate() returns the WindowsIdentity of the caller
  2. Connection: SQL Login – SqlContext.Impersonate() returns null
  3. Connection: SQL Login (where login is in sysadmin server role) – SqlContext.Impersonate() returns WindowsIdentity of the SQL Server service account
  4. Connection: SQL Login | EXECUTE AS Windows Login – SqlContext.Impersonate() returns null
  5. Connection: SQL Login | EXECUTE AS dbo – SqlContext.Impersonate() returns WindowsIdentity of the SQL Server service account
  6. Connection: Any – using the LogonUser function rather than SqlContext.Impersonate() will always work but requires the developer to use a single user & password for all calls to the CLR procedure

Hopefully, you will be able to solve your impersonation challenges with one of the combinations above.

Leave a Reply

Your email address will not be published. Required fields are marked *