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.