Using the SharePoint Secure Store Application for Database Connection Settings

Introduction

A few weeks back I blogged about accessing the SharePoint Secure Store using C# to retrieve user credentials. Credentials in the Secure Store are stored securely and can be managed via the Central Administration site. That post is here: Retrieving Credentials from the SharePoint Secure Store using C#

In this post, I want to expand on that concept, and demonstrate how the SharePoint Secure Store can be used for storing database connection settings (username, password, database, server).

Often SharePoint solutions are required to access external databases. The SharePoint Secure Store solves the problem of securely storing and managing credentials, but what about managing the database server and database name?

The example below extends the class designed in the first post, to demonstrate how credential and database information can be retrieved by code to connect to and authenticate with external database systems, such as Microsoft SQL Server.

This article assumes you have the SharePoint Secure Store Application configured. To complete the example, you will need access to the Central Administration site, and will need permissions to create new Target Applications and deploy solutions.

The source code for this project can be downloaded from the Microsoft TechNet Gallery, here: Retrieving Credentials from the SharePoint Secure Store using C#

Creating a Target Application in the Secure Store

Before looking at the code, we are going to step through creating a Target Application in the Secure Store.

The new Target Application we create will store a generic user credential (username and password), a database name, and a database server name. We will then use this information in the code example to connect and authenticate with a SQL Server used to store HR information.. The name of the target application will be a description of the target SQL server and database we are connecting with, in this example, HRPro.

1. Browse to the Central Administration site
2. Click on Application Management
3. Click on Manage Service Applications
4. Click the Secure Store Application
5. Create a new Target Application
5.1 On the ribbon, in the Manage Target Applications, click New

5.2 In the Create New Secure Store Target Application page, enter the following information;

Target Application ID: HrPro
Display Name: HR Pro
Contact E-mail: (enter your email address)
Target Application Type: Group

5.3 Click Next. On the next page, Specify the credential fields for the Secure Store Target Application, configure the fields that are used to store the credential and database information.

Special attention needs to be paid here, as a standard for all applications needs to be set. In this example, I'm specifying that the SqlServer must use the field type Key, and the database must use the field type Generic. The code that is used to access this information doesn't have access to field names, only the FieldType enumeration. The standard is: [FieldType]Key = the SQL Server, and [FieldType]Generic = the Database name. This will become clearer in the code example.

Configure the following fields:

Field Name	Field Type	Masked
UserName	Username	No
Password	Password	Yes
SqlServer	Key	No
Database	Generic	No

5.4 Click Next. On the next page, Specify the membership Settings, you need to enter administrators and members. Administrators are people who will be able to manage this target application, while members are people who will have permissions to retrieve (read) the user credentials.

Since the user credentials and database information will be accessed via code under the context of a standard site user, we are adding Domain Users as Members. The SharePoint farm account (and any other administrators) should be added as administrators.

5.5 Click OK to save the new Target Application.
6. Set the credentials of the new Target Application
6.1 Select the new Target Application, and click Set (in the Credentials section of the ribbon)

6.2 Enter the username, password, SQL Server and database information. The user and password need to be a SQL Server user (unless you plan on using impersonation to open the connection to SQL - which is a blog for another day).

6.3 Click OK to save the credential information

You have finished creating the new Target Application. The next step is to write some code that will access and use the credentials and database settings.

Building a Class to Access the Credentials

This part of the example requires creating a some classes and methods for accessing the secure store, retrieving a credential object, and returning it to the caller.

1. Create a new empty SharePoint Project (deploy as a farm solution)
2. Add the following references to the project:

Microsoft.Office.SecureStore.dll (see the reference below about finding the Microsoft.Office.SecureStore.dll in the GAC (Global Assembly Cache))
Microsoft.BusinessData.dll

3. Add a new class to the project, called SecureStoreProxy
4. Make the class as public and static

namespace SecureStoreCredentialsExample
{
 public static class SecureStoreProxy
 {
 
 }
}

5. Add the following using statements

using System;
using System.Linq;
using System.Runtime.InteropServices;
using System.Security;
using Microsoft.BusinessData.Infrastructure.SecureStore;
using Microsoft.Office.SecureStoreService.Server;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint;

4. Add the following code to the SecureStoreProxy class
4.1. Add CredentialType enum. This will be used by the GetDatabaseConnectionSettingsFromSecureStoreService method.

public enum CredentialType
{
 Domain,
 Generic
}

4.2. Add a new class, BaseCredentials, to store credential information. The base class implements IDisposable to ensure the classes SecureString properties are correctly disposed of. It also contains a method for returning a SecureString as a String.

Add an additional two classes, that inherit BaseCredentials, for storing Windows credentials, and Database credentials.

The class that stores the Windows credentials contains an extra property to hold the domain name, and an updated constructor (this class isn't used in this example, but is included for completeness).

The class that stores the database credentials contains extra properties to hold the Sql Server and Database values, a method to create a default connection string, and an updated constructor.

public class BaseCredentials : IDisposable
{
 private readonly SecureString _userName;
 public String UserName
 {
  get { return ConvertToUnsecuredString(_userName); }
 }
 private readonly SecureString _password;
 public String Password
 {
  get { return ConvertToUnsecuredString(_password); }
 }
 public BaseCredentials(SecureString username, SecureString password)
 {
  _userName = username.Copy();
  _password = password.Copy();
 }
 protected string ConvertToUnsecuredString(SecureString securedString)
 {
  if (securedString == null) return String.Empty;
  IntPtr uString = IntPtr.Zero;
  try
  {
   uString = Marshal.SecureStringToGlobalAllocUnicode(securedString);
   return Marshal.PtrToStringUni(uString);
  }
  finally
  {
   Marshal.ZeroFreeGlobalAllocUnicode(uString);
  }
 }

 private Boolean _isDisposed;
 public void Dispose()
 {
  if (_isDisposed) return;
  _userName.Dispose();
  _password.Dispose();
  _isDisposed = true;
 }
}

public class UserCredentials : BaseCredentials
{
 public String DomainName;
 public UserCredentials(SecureString username, SecureString password, SecureString domainName) : base(username, password)
 {
  DomainName = base.ConvertToUnsecuredString(domainName);
 }
 public UserCredentials(SecureString username, SecureString password): base(username, password)
 {
 }
}

public class DatabaseCredentials : BaseCredentials
{
 public readonly String Database;
 public readonly String SqlServer;
 public readonly Boolean UseWindowsAuthentication;
 public String DefaultSqlConnectionString
 {
  get
  {
   var connectionString = new SqlConnectionStringBuilder() { DataSource = SqlServer, InitialCatalog = Database};
   if (UseWindowsAuthentication)
   {
    connectionString.IntegratedSecurity = true;
   }
   else
   {
    connectionString.UserID = UserName;
    connectionString.Password = Password;
   }
   return connectionString.ToString();
  }
 }
 
 public DatabaseCredentials(SecureString username, SecureString password, SecureString sqlServer, SecureString database, Boolean useWindowsAuthentication) : base(username, password)
 {
  Database = ConvertToUnsecuredString(database);
  SqlServer = ConvertToUnsecuredString(sqlServer);
  UseWindowsAuthentication = useWindowsAuthentication;
 }
}

4.3. Add a new public static method used to retrieve database credential information from the Secure Store. This method takes an Application ID (a target application id), and the CredentialType enumeration as inputs, and returns a DatabaseCredentials object.

public static DatabaseCredentials GetDatabaseConnectionSettingsFromSecureStoreService(string applicationId, CredentialType credentialType)
{
 ISecureStoreProvider provider = SecureStoreProviderFactory.Create();
 if (provider == null)
 {
  throw new InvalidOperationException("Unable to get an ISecureStoreProvider");
 }
 using (SecureStoreCredentialCollection credentials = provider.GetCredentials(applicationId))
 {
  var un = from c in credentials
     where c.CredentialType == (credentialType == CredentialType.Domain ? SecureStoreCredentialType.WindowsUserName : SecureStoreCredentialType.UserName)
     select c.Credential;

  var pd = from c in credentials
     where c.CredentialType == (credentialType == CredentialType.Domain ? SecureStoreCredentialType.WindowsPassword : SecureStoreCredentialType.Password)
     select c.Credential;

  var s = from c in credentials
     where c.CredentialType == SecureStoreCredentialType.Key
     select c.Credential;

  var db = from c in credentials
    where c.CredentialType == SecureStoreCredentialType.Generic
    select c.Credential;

  SecureString userName = un.First(d => d.Length > 0);
  SecureString password = pd.First(d => d.Length > 0);
  SecureString sqlServer = s.First(d => d.Length > 0);
  SecureString database = db.First(d => d.Length > 0);
  var databaseConnectionSettings = new DatabaseCredentials(userName, password, sqlServer, database, credentialType == CredentialType.Domain);
  return databaseConnectionSettings;
 }
}

The method connects to the Secure Store, and retrieves the credentials of the Target Application. If the user context that the code is running isn't in the membership of the Target Application, an exception will be thrown.

Once the method has connected to the Secure Store and retrieved the Target Application's credentials (returned as a SecureStoreCredentialCollection), it parses the collection, extracting the username, password, SQL Server and database into a new DatabaseCredentials object.

From this method, you can see the importance of deciding on a standard for which FieldType contains the SQL server, and which FieldType contains the database. There is no method to get the name of a Credential from the provide. The only values that get returned are the CredentialType (Generic, Pin, Key, User, Password, Windows User, Windows Password), and the StringString value itself.

5. Build the solution.

Building a Webpart that uses the Credentials to Connect to a SQL Server Database

The final step in our example is to build a webpart the retrieves the information about the current user from the HR SQL Server database, augments it with data from the SharePoint User Profile Service, and displays it to the user.

1. Add a new standard webpart to the project called GetInformationFromSql
2. Add a the following using statements to the webparts code file

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.Office.Server.UserProfiles;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint.WebControls;

3. Copy the following code into the webpart file.

The webpart contains a label and some code that runs when OnPreRender event. During the OnPreRender event, the webpart will retrieve information about the currently logged on user from the HR SQL server using the credentials retrieved from the Secure Store. It will augment this information with additional information from the User Profile Application.

Notice that the call to GetDatabaseSettingsFromSecureStore is within a Using block to ensure the object is disposed of.

namespace SecureStoreCredentialsExample.GetInformationFromSql
{
 [ToolboxItemAttribute(false)]
 public class GetInformationFromSql : WebPart
 {
  private Label _results;
  private const String SqlCommandText = "select t1.ID,t1.Full_Name, t1.Initials, t1.Job_Title, t1.Ince_Reference, t1.Location, t1.Start_Date, t1.department from HRPro.dbo.personnel_Records t1 where t1.leaving_date is null and t1.phi = 0 and ince_reference = '{0}'";

  private String _applicationId = "HrPro";
  [WebBrowsable(true), WebDisplayName("Application Id"), WebDescription("Secure Store Target Application ID"),
   Personalizable(PersonalizationScope.Shared), Category("Webpart Settings")]
  public String ApplicationId
  {
   get { return _applicationId; }
   set { _applicationId = value; }
  }


  protected override void CreateChildControls()
  {
   _results = new Label();
   Controls.Add(_results);
  }

  protected override void OnLoad(EventArgs e)
  {
   base.OnLoad(e);
   if (!Page.ClientScript.IsClientScriptIncludeRegistered(this.GetType(), "ssce"))
   {
    Page.ClientScript.RegisterClientScriptInclude(this.GetType(), "ssce", "/_layouts/ssce.js?v1");
   }
  }

  protected override void OnPreRender(EventArgs e)
  {
   base.OnPreRender(e);
   _results.Text = String.Empty;
   try
   {
    //Get Database settings from the Secure Store
    using (var databaseSettings = SecureStoreProxy.GetDatabaseConnectionSettingsFromSecureStoreService(ApplicationId,SecureStoreProxy.CredentialType.Generic))
    {
     using (var connection = new SqlConnection(databaseSettings.DefaultSqlConnectionString))
     {
      var userId = SPContext.Current.Web.CurrentUser.LoginName.Contains(@"\")
       ? SPContext.Current.Web.CurrentUser.LoginName.Substring(SPContext.Current.Web.CurrentUser.LoginName.IndexOf(@"\", StringComparison.InvariantCultureIgnoreCase) + 1)
       : SPContext.Current.Web.CurrentUser.LoginName;
      var sqlCommand = new SqlCommand(String.Format(SqlCommandText, userId), connection) { CommandType = CommandType.Text };
      connection.Open();
      var reader = sqlCommand.ExecuteReader();
      if (reader.HasRows)
      {
       reader.Read();
       String fullname = reader["Full_Name"] == DBNull.Value ? String.Empty : (String)reader["Full_Name"];
       String initials = reader["Initials"] == DBNull.Value ? String.Empty : String.Format("({0})", reader["Initials"]);
       String inceRef = reader["Ince_Reference"] == DBNull.Value ? String.Empty : (String)reader["Ince_Reference"];
       String location = reader["Location"] == DBNull.Value ? String.Empty : String.Format("Office: {0}", reader["Location"]);
       String jobTitle = reader["Job_Title"] == DBNull.Value ? String.Empty : (String)reader["Job_Title"];
       String department = reader["Department"] == DBNull.Value ? String.Empty : String.Format("({0})", reader["Department"]);
       DateTime startdate = reader["Start_Date"] == null ? DateTime.MinValue : (DateTime)reader["Start_Date"];
       Uri imageUrl;
       String aboutMe;
       GetProfileInformation(inceRef, out imageUrl, out aboutMe);
       var sb = new StringBuilder();
       aboutMe = aboutMe == String.Empty ? String.Empty : String.Format("<div><span onclick=\"javascript:displayElementInPopup('{0}', 'About Me')\">About Me</span><div><div id=\"{0}\">{1}</div></div></div>", String.Format("incePInstance{0}", inceRef.Trim()), aboutMe);
       String photoWrapper = String.Format("<div><img src=\"{0}\" alt=\"{1}\" style=\"max-width:48px;\"/></div>", imageUrl, fullname);
       String infoWrapper = String.Format("<div><div>{0} {1}</div><div>{2} {3}</div><div>{4}</div><div>{5}</div></div>", fullname, initials, jobTitle, department, location, aboutMe);
       sb.Append(String.Format("<div><table><tr><td>{0}</td><td>{1}</td></tr></table></div>", photoWrapper, infoWrapper));
       _results.Text = sb.ToString();
      }
      connection.Close();
     }
    }
    
   }
   catch (Exception exception)
   {
    _results.Text = String.Format("Something went wrong accessing information from the HR system. Error: {0}",exception.Message);
   }
  }

  private static void GetProfileInformation(string userName, out Uri imageUrl, out string aboutMe)
  {
   try
   {
    SPServiceContext serviceContext = SPServiceContext.GetContext(GetCentralAdministrationSite());
    var upm = new UserProfileManager(serviceContext);
    var domain = Environment.UserDomainName;
    String samAccount = String.Format("{0}\\{1}", domain, userName);
    if (!upm.UserExists(samAccount))
    {
     aboutMe = String.Empty;
     imageUrl = new Uri("/_layouts/images/person.gif", UriKind.Relative);
     return;
    }
    UserProfile up = upm.GetUserProfile(samAccount);
    UserProfileValueCollection pictureUrl = up["PictureURL"];
    UserProfileValueCollection aboutMeField = up["AboutMe"];
    aboutMe = aboutMeField.Value == null ? String.Empty : aboutMeField.Value.ToString();
    imageUrl = new Uri(pictureUrl.Value != null ? pictureUrl.Value.ToString() : "/_layouts/images/person.gif", UriKind.RelativeOrAbsolute);
   }
   catch (Exception)
   {
    aboutMe = String.Empty;
    imageUrl = new Uri("/_layouts/images/ince/anon.png", UriKind.Relative);
   }
  }

  private static SPSite GetCentralAdministrationSite()
  {
   var webApplication = SPAdministrationWebApplication.Local;
   if (webApplication == null)
   {
    throw new NullReferenceException("Unable to get the Central Administration Site.");
   }
   var caWebUrl = webApplication.GetResponseUri(SPUrlZone.Default);
   if (caWebUrl == null)
   {
    throw new NullReferenceException("Unable to get the Central Administration Site. Could get the URL of the Default Zone.");
   }
   return webApplication.Sites[caWebUrl.AbsoluteUri];
  }
 }
 
}

4. Build and deploy the project
5. Add the webpart to a page
6. The webpart connects to the HR System (using the credentials, SQL Server and database information provided from the Secure Store) and displays information.

See Also

Retrieving Credentials from the SharePoint Secure Store using C#

References

How to use the DirectoryServices Namespace in ASP (Double-Hop Authentication Issue)
Microsoft.Office.SecureStoreService.dll
Getting credients from the Secure Store Provider
Visual Studio Project Sample: Retrieving Credentials from the SharePoint Secure Store using C#