SQL access with integrated security from SharePoint

When creating custom webparts or other custom features in SharePoint that retrieves data from a SQL server, there are a few gotchas and pitfalls that you should be aware of. I spent a few hours battling a couple of problems, so I thought I’d document the things I found out here to spread the knowledge.

I was creating a custom webpart that retrieved data from a SQL server. On my development machine, I used a test SQL user with username and password directly in the connection string in the web.config and things worked as they should. The problems started when I deployed my solution to the test server.

On the test server, I changed the connection string to use integrated security instead, and when trying to access the page, I was greeted with an error message stating that I was not allowed to have a connection string with the words Integrated Security in it. This happens since SharePoint adds a tag mapping that replaces the normal SqlClient assembly with SharePoint’s own. Apparently that SqlClient does not support integrated security. So, what I had to do was to add the following line to my web.config in the tagMappings-section:

<remove tagType=”System.Web.UI.WebControls.SqlDataSource, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a” />

After I added this line, things worked ok when I tried the page on the test server. However, when I asked a colleague to try out the page from his machine, another issue arose. He was greeted with the message: Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. This happens since SharePoint by default tries to impersonate the user browsing the site, so when using integrated security (if your environment is set up for impersonation multi-hop) SharePoint actually connects to the database as you. If impersonation-forwarding is not supported, you are instead greeted with the previously mentioned login failed message.

What I actually wanted to do in my webpart was to connect to the database as the SharePoint service user, not using impersonation. To fix this, I created two helper methods that turns off impersonation temporarily.

private WindowsImpersonationContext ctx = null;
public void UseAppPoolIdentity()
{
  try
  {
    if (!WindowsIdentity.GetCurrent().IsSystem)
    {
      ctx = WindowsIdentity.Impersonate(System.IntPtr.Zero);
    }
  } catch { }
}
public void ReturnToImpersonatingCurrentUser()
{
  try
  {
    if (ctx != null) { ctx.Undo(); }
  }
  catch { }
}

These calls are then placed around the code that should be run as the service user, for instance when retrieving information from the SQL database, like this:

UseAppPoolIdentity();
// Code to retrieve data
ReturnToImpersonatingCurrentUser();