Calling SQL Server from SharePoint Web-Part with Impersonation

  • Article

A question that comes up with fairly persistent regularity is “how come my web-part can’t connect to my SQL database/web-service/whatever with integrated credentials?”. It’s a question that has some rationale and some history behind it; the rationale being “I logged into the page as a user that should have permissions to connect to the service, I'm using integrated/Kerberos security, so why does the login fail from my SharePoint web-part?”

In short it’s because if your code is just connecting as the “current” user, that “user” almost certainly actually doesn’t have access to the endpoint you’re trying to consume because the user token isn’t what you think it is any more.

The good news however is that there’s a fairly simple workaround – short version: get a Window token for the user token you do really have and impersonate for the connection with that. Skip down below if you just want the solution.

Time for a history lesson to give some context on why this happens. Once upon a time, SharePoint was “Windows” authentication protected (amongst some other security schemes) and it was a simple land to live in. “What’s that? Isn’t it now too?” I hear you cry. Well yes sort of, but it’s more complicated than that. Back in the day there wasn’t such a thing as “claims” to worry about so the process of authentication and sub-authenticating for external services/double-hop was a simple process of:

  • Logging in and getting a Windows Auth token (Kerberos).
  • SharePoint authenticating the user with that token.
  • SharePoint impersonating that Windows identity/token within the page, and stuff being able to run on the page under the context of that Windows token.
  • The end.

Connecting to SQL would just be a case of using “integrated” security and the security context being passed flawlessly on to whatever you were connecting to.

No more. Now with claims involved, the process is:

  • Logging in and getting a Windows Auth token.
  • Passing the Windows token to the “SharePoint Windows Authentication Claims provider”.
  • SharePoint getting back a bunch claims from the identity provider (with SAM account name as the main claim).
  • SharePoint authenticating the user with those claims (not token anymore – we’re done with that now).
  • SharePoint impersonating that claim with the page, and stuff running on the page with that claim.
  • Non-claims-aware services not accepting the claim if authentication was just passed on as before.
    • Even if they were claims aware, it’s not as simple as just passing it on, but that’s for another day.

Why the move to claims? In short, users can be federated from many more sources than just the local Active Directory now; claims are the common language used to authenticate any type of user for SharePoint from any source. As long as whatever super-awesome authentication source provides claims for their people then SharePoint can work with those users. Claims is the new black, it really is.

So how do we fix what we’ve broken then if we still need to double-hop to external services? Simple; reverse-engineer the users’ claims back into a Windows Token (if possible – obviously not possible for users that didn’t come from AD). That’s done indirectly and easily with a Windows service SharePoint has called (strangely enough) the “claims to Windows token service” that’s been around since claims came onto the scene for this exact problem in fact. Excel Services uses it to refresh data-connections that come from external sources that need a Windows token, as one example.

For us though we want to code this ourselves so here’s an example of code that uses this exact method. It just calls a SQL Server instance to get server version information (so no specific database is queried) to show a proof-of-concept of a query working. If impersonation didn’t work then neither would the query given SQL Server doesn’t allow anonymous connections.

Connect to SQL with Impersonation – Example Code

Here’s the web-part that connects to SQL as the user in question:

clip_image002

We just run “SELECT @@version” against SQL Server as it doesn’t require any specific database but we’ll be able to see if the connection was successful or not.

Web-Part Code

Here’s the code for the button:

// Begin impersonation with elevated privileges

SPSecurity.RunWithElevatedPrivileges(delegate() {

// Get Windows Token for current claim

WindowsIdentity windowsIdentity = null;

try {

windowsIdentity = SPSecurityContext.GetWindowsIdentity(); // This will fail if C2WTS isn't configured correctly

}

catch (Exception ex) {

lblStatus.Text = ex.Message;

return; // Exit method

}

string sqlServerVersion = string.Empty;

// Impersonate using Windows token

using (WindowsImpersonationContext ctx = windowsIdentity.Impersonate()) {

string connectionString =

string.Format("Server={0};Database=Cats;Trusted_Connection=True;", txtServerName.Text);

SqlConnection con =

new SqlConnection(connectionString);

con.Open();

// Execute basic SQL statement

using (SqlCommand cmd = con.CreateCommand()) {

cmd.CommandText = "SELECT @@version"; // Get version

SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read()) { sqlServerVersion = reader.GetString(0); }

reader.Close();

}

}

lblStatus.Text = string.Format("Result: '{0}'.", sqlServerVersion);  // Set GUI

});

To be doubly sure you can check a SQL profiler if you need evidence as above; the connections are all done as the impersonated user, as if they’d run the query themselves from SQL Server Management Studio or something.

clip_image004

That’s it; fairly simple. Another bonus with claims is you don’t even need Kerberos as an entry point token because we’re coming from a claims world not a Windows authentication world so plain old NTLM logins work just fine.

Enjoy!

// Sam Betts