Connection Strings in SharePoint 2007 with SSO
When writing ASP.NET applications, the question of where to save connection strings was quite straight forward, encrypted in the registry or in one of the .config files on the IIS server. So the question is where to save connection strings when you are developing for SharePoint?
Just to be clear, the methods we are all used to will still work, but they have their limitations... especially if there is more than one FE server in your farm. When you've got more than one FE sever, you need to make sure that the connection strings are the same across all the servers. Not really a problem when you only have one FE server... but add a couple more and you'll start having headaches.
So, this is where the Microsoft SSO service strolls onto the scene. They advertise is as a central credential store where we can keep all our sensitive username/password details. Hooray! If only it were that simple.
In this post, I'll explain how to save your connection strings in SSO.
Step One: Setup SSO
Setup SSO in your Farm. Ok, easier said than done! But that's a topic for another day.
Step Two: Create an Enterprise Application Definition (EAD)
- Load the Central Administration Console and navigate to Operationsà Manager Settings for Single Sign-on
- Click "Manage settings for enterprise application definitions" and then "New Item"
- In the form that opens, enter a
- display name
- application name (this is the name you'll use in code, so keep it simple), for this example, I'll use "Connection_myDB"
- any old email address
- select "group"
- tick "windows authentication"
- In the logon account information, add an additional field call "connection string". The reason for this is so we can manage the other properties of the connection string outside of the code (server name, catalogue name, etc...)
- Click Ok
Step Three: Add Account Information
- Click on "Manage account information for enterprise application definitions" and select your EAD in the drop down box
- Enter "NT AUTHORITY\Authenticated Users" into the group account name
- Make sure "update account information" is selected and then click "set"
- Now you need to enter the details of the connection string, so for example
- Username = "my_db_user"
- Password = "my_secure_password"
- Connection String = "Application Name=App;Data Source=dbserver;Initial Catalog=mydb"
- Click Ok
Right, so now you have an EAD setup in SharePoint. Now let's use it in code.
Step Four: Coding
First, we'll need to add some assembly references and add the relevant using statements.
Windows® SharePoint® Services
(C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\Microsoft.SharePoint.dll)
Microsoft® Office SharePoint® Server component
(C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\Microsoft.SharePoint.Portal.SingleSignon.dll)
using Microsoft.SharePoint;
using Microsoft.SharePoint.Portal.SingleSignon;
Now, to get the details out of the SSO credential store we need to add the following code. I’ve implemented it as an assessor, but you can do as you please. Also, this assumes that the field names retrieved from the ApplicationField array will be in the same order as the fields in the Evidence array. It works for me, but it still needs more testing.
/// <summary>
/// Get the connection string from SSO
/// </summary>
private string ConnectionString
{
get
{
try
{
StringBuilder lConnectionString = new StringBuilder("<conn>;User Id=<username>;Password=<pass>");
//get credentials
ISsoProvider lSSOProvider = SsoProviderFactory.GetSsoProvider();
SsoCredentials lCredentials = lSSOProvider.GetCredentials("Connection_myDB");
Application.ApplicationField[] lAppFields = lSSOProvider.GetApplicationFields("Connection_myDB");
//build connection string
for (int j = 0; j < lAppFields.Length; j++)
{
Application.ApplicationField lField = lAppFields[j];
switch (lField.Field.ToLower().Replace(" ", ""))
{
case "username" :
lConnectionString.Replace("<username>", convertToString(lCredentials.Evidence[j]));
break;
case "password":
lConnectionString.Replace("<pass>", convertToString(lCredentials.Evidence[j]));
break;
case "connectionstring":
lConnectionString.Replace("<conn>", convertToString(lCredentials.Evidence[j]));
break;
default:
lConnectionString.Append(lField.ToString());
break;
}
}
//return connection string
return lConnectionString.ToString();
}
catch (SingleSignonCredsNotFoundException ex)
{
return "Credentials Not Found in SSO Provider\n" + ex.Message;
}
catch (SingleSignonException ex)
{
return ex.Message;
}
}
}
You’ll also need to add this function that converts a SecureString to a String. To be honest, this feels like a bit of a hack, so if you’ve got a better way please let me know.
/// <summary> /// Converts a System.Security.SecureString to System.String
/// </summary>
/// <param name="pValue"></param>
/// <returns></returns>
private string convertToString(System.Security.SecureString pValue)
{
IntPtr lValuePointer = IntPtr.Zero;
string lValueAsString;
try
{
lValuePointer = System.Runtime.InteropServices.Marshal.SecureStringToBSTR(pValue);
lValueAsString = System.Runtime.InteropServices.Marshal.PtrToStringBSTR(lValuePointer);
}
catch (Exception ex)
{
lValueAsString = ex.Message;
}
finally
{
if (lValuePointer != IntPtr.Zero) System.Runtime.InteropServices.Marshal.ZeroFreeBSTR(lValuePointer);
}
return lValueAsString;
}
That's it. you should now have a connection string stored in the SSO credential store.