Connecting to SQL from SharePoint with claims based authentication and integrated security

Recently I had to retrieve data from SQL Server and display in a SharePoint portal. Data security trimming was implemented in SQL, which means I needed to connect to SQL using the current user’s identity.

ASP.NET impersonation is enabled by default in SharePoint. If your web application uses classic mode authentication, then all you need to do is specify integrated security in your SQL connection string and all will work.

If your web application uses claims based authentication however, then integrated security will not work at the SQL end. You will see your code connects as NT Authority\IUSR. This is because SQL Server does not support claims authentication (including SQL Server 2012). The claims identity is unable to be passed to SQL.

To overcome this, we need to convert the claims identity to a Windows identity, and connect to SQL under this Windows identity. The Claims to Windows Token Service (C2WTS) on the SharePoint machine is what enables us to convert a claims identity to a Windows identity.

You invoke the C2WTS via the S4UClient class. This class has two methods, UpnLogon and CertificateLogon. Below is an example of using the UpnLogon method to create a Windows identity from the current claims identity, and connect to SQL under this Windows identity. You will need to add a reference to Microsoft.IdentityModel.dll.

private void QuerySql()
		{
			var windowsIdentity = GetWindowsIdentityFromCurrentClaimsIdentity();
			using (windowsIdentity.Impersonate())
			{
				//Connect to SQL here
			}
		}

		private WindowsIdentity GetWindowsIdentityFromCurrentClaimsIdentity()
		{
			WindowsIdentity windowsIdentity = null;

			//This is required when invoking the C2WTS
			SPSecurity.RunWithElevatedPrivileges(delegate()
			{
				//Get the UPN of the user from the current claims identity
				var claimIdentity = (ClaimsIdentity)Thread.CurrentPrincipal.Identity;
				var upnClaim = claimIdentity.Claims.FirstOrDefault(c => c.ClaimType.Equals(ClaimTypes.Upn, StringComparison.InvariantCultureIgnoreCase));

				//TODO: Ensure that we found the UPN claim

				string upn = upnClaim.Value;

				//Get a WindowsIdentity from the UPN of the user by calling C2WTS service
				try
				{
					windowsIdentity = S4UClient.UpnLogon(upn);
				}
				catch (Exception ex)
				{
					//TODO: Handle
					throw;
				}
			});
			return windowsIdentity;
		}

Note that the claims identity will not contain a UPN claim if your farm is not joined to an AD domain.

Advertisements

About Bernado

Based in Australia, I am a freelance SharePoint and Dynamics CRM developer. I love developing innovative solutions that address business and everyday problems. Feel free to contact me if you think I can help you with your SharePoint or CRM implementation.
This entry was posted in Claim Authentication, SharePoint. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s