[Image via Fabian Williams]

Last week I was working on some ASP.NET web forms that generated internal reports against MS CRM using ExcelWriter and I wanted to port the application to one of our SharePoint instances. Though it seemed simple at first, I ran into a few issues. One of the issues happened to be authentication related. It was a typical ‘double hop’ problem where this SharePoint instance was using integrated Windows NTLM authentication and my code was trying to access the CRM SQL Server database. By nature, NTLM is unable to pass the credentials to the database thus producing access errors. (You can find more information on the NTLM issue and using Kerberos as a solution here.)

Since we don’t have Kerberos configured on this environment, our best solution was Secure Store. This service allows a user to authenticate with domain credentials and then use an account established in Secure Store to access the database. In our case, this was the read-only CRM account. This also enables easy to use and convenient access control using AD groups.

Fabian Williams describes how to set up Secure Store in the SP Central Administration site here.

There is some extraneous information in Fabian’s post pertaining to SharePoint Designer, but it provides step by step instructions on the CA part of the process with screenshots. After establishing Secure Store on the site and adding the read-only CRM account credentials, we could implement our solution in code in two major steps:

Add impersonation code in the codebehind of the web part.

• This code allows the web part to authenticate against the database using a different set of credentials from the standard Windows auth.
• Requires the following namespaces: System.Web, System.Web.Security, System.Security.Principal, System.Runtime.InteropServices
• Should be placed inside the web part’s UserControl class
• Source: http://support.microsoft.com/kb/306158

• public const int LOGON32_LOGON_INTERACTIVE = 2; public const int LOGON32_PROVIDER_DEFAULT = 0; WindowsImpersonationContext impersonationContext; [DllImport("advapi32.dll")] public static extern int LogonUserA(String lpszUserName,                                     String lpszDomain,                                     String lpszPassword,                                     int dwLogonType,                                     int dwLogonProvider,                                     ref IntPtr phToken); [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)] public static extern int DuplicateToken(IntPtr hToken,                                         int impersonationLevel,                                         ref IntPtr hNewToken); [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)] public static extern bool RevertToSelf(); [DllImport("kernel32.dll", CharSet = CharSet.Auto)] public static extern bool CloseHandle(IntPtr handle); private bool impersonateValidUser(String sername, String domain, String password) {     WindowsIdentity tempWindowsIdentity;     IntPtr token = IntPtr.Zero;     IntPtr tokenDuplicate = IntPtr.Zero;     if (RevertToSelf())     {          if (LogonUserA(sername, domain, password, LOGON32_LOGON_INTERACTIVE,              LOGON32_PROVIDER_DEFAULT, ref token) != 0)          {              if (DuplicateToken(token, 2, ref tokenDuplicate) != 0)              {                  tempWindowsIdentity = new WindowsIdentity(tokenDuplicate);                  impersonationContext = tempWindowsIdentity.Impersonate();                  if (impersonationContext != null)                  {                      CloseHandle(token);                      CloseHandle(tokenDuplicate);                      return true;                  }              }          }     }     if (token != IntPtr.Zero)         CloseHandle(token);     if (tokenDuplicate != IntPtr.Zero)         CloseHandle(tokenDuplicate);     return false; } private void undoImpersonation() {     impersonationContext.Undo(); }

• After the above code is in place, an ‘if’ statement needs to be wrapped around every location in the code where the web part connects to or authenticates against the database.
  ?

  if (impersonateValidUser("username", "domain", "password"))

• Add code to retrieve the Secure Store credentials and pass them to the connection strings.
  • This code will retrieve the credentials (in our case, the read-only CRM credentials) from Secure Store and then use them to generate the correct connection string.
  • The GetCredentials method will return a Dictionary<String,String> that maps a fieldname (established in the SP CA when setting up Secure Store) to the corresponding entry that was set (the username and password for us).
  • The following code should be placed in the same UserControl codebehind but alongside the actual UserControl class.
  • Requires the following namespaces: System.Runtime.InteropServices, System.Security, Microsoft.BusinessData.Infrastructure.SecureStore, Microsoft.Office.SecureStoreService.Server, Microsoft.SharePoint
  • Source: http://trentacular.com/2010/11/sharepoint-2010-programatically-retrieve-credentials-from-the-secure-store-service/

• Next, the SecureStore credentials should be placed in the impersonateValidUser ‘if’ statement from the last section. Note: the strings used to retrieve the credentials from the dictionary may be different. The string passed to GetCredentials is the application ID. The other strings in the ‘if’ statement depend on what you typed in for the fieldnames in the Secure Store setup.

  Dictionary<string, string> creds = SecureStoreUtils.GetCredentials("CRM"); if (impersonateValidUser(creds["Username"], "domain", creds["Password"]))

• Finally, in the connection strings for the database(s) the User Id and Password attributes need to be added and populated using the retrieved credentials. For example:

  DataSource.ConnectionString = @"Data Source=SERVERNAME;Initial Catalog=DATABASE;User Id=" + creds["Username"] + ";Password=" + creds["Password"] + ";Trusted_Connection=Yes;";

  • NOTE: If there are web controls in the frontend that need to be populated from the database, add the connection string to the data sources during Page_Load using the .ConnectionString property.

Follow

Related posts: