BCS Meta Man – Using a Connection String stored in the Secure Store

In this blog post I will show you how you can update your code to use a connection string in the Secure Store rather than having the value hard coded in your BCS Meta Man generated c# file.  The reason why you may wish to put the connection details within the Secure Store is that the names of the database and server are likely to be different in my development environment than in my staging and production environments. In order to do this you will need to have the use of the secure store which is only available in SharePoint Server 2010 and not SharePoint Foundations.

To be able to use the details stored within the secure store you will be required to do some manual changes to the code generated by BCS Meta Man.

First of all create a new Secure Store application with the Connection String Field

Central Administration > Service Applications > Secure Store > New

Using the ‘Add Field’ button add a generic field called ‘Connection String’ and remove the other two fields

Click next and then add the Secure Store Administrator on the next screen. Now the Secure Store ID has been created you can click on it and choose to set the credentials, this is where you supply all of the credentials required.

Enter the connection string and the Credential Owner, Click OK

In BCS Meta Man, create a new BCS Meta Man Project and when connecting to your Data Source choose ‘Use Secure Store’ as your Authentication Mode.

You will be prompted for your credentials again, this is to allow BCS Meta Man to connect to the External System. Next create the External Content Type and choose .Net Assembly connector as the Model Type, generate the External Content Type in the usual way.

At this point the Model and assembly will be configured to the use Secure Store credentials for the User Name and Password but not for the Server and Database, Deploy the model to ensure the Secure Store details are correct. If it is working and you can see your External Data then it’s time to configure the code to retrieve the Server and Database, if not then check back to the Secure Store and make sure the credentials you entered have the permissions to the External System you are using.

Locate the BCSMetaMan.cs class and find the following method which retrieves the credentials from the Secure Store

internal Dictionary<string, string> ReadCredentialsFromSecureStore()
{
    // error checking removed for brevity
    string targetId = LobSystemInstance.GetProperties()["SsoApplicationId"] as string;
    ISecureStoreProvider provider = GetSecureStoreProvider();
    var userCredentials = new Dictionary<string, string>(2);
    // get the credentials for the user on whose behalf the code
    // is executing
    using (SecureStoreCredentialCollection credentials = provider.GetRestrictedCredentials(targetId))
    {
        // look for username and password in credentials
        foreach (ISecureStoreCredential credential in credentials)
        {
            switch (credential.CredentialType)
            {
                case SecureStoreCredentialType.UserName:
                case SecureStoreCredentialType.WindowsUserName:
                    userCredentials.Add("UserID", GetStringFromSecureString(credential.Credential));
                    break;
                case SecureStoreCredentialType.Password:
                case SecureStoreCredentialType.WindowsPassword:
                    userCredentials.Add("Password", GetStringFromSecureString(credential.Credential));
                    break;
                default:
                    break;
            }
        }
    }
    return userCredentials;
}

You should be able to see that by default only the Username and Password are retrieved, replace the method with the following to pick up the Generic Field that we are using for the Connection String

internal Dictionary<string, string> ReadCredentialsFromSecureStore()
{
    // error checking removed for brevity
    string targetId = LobSystemInstance.GetProperties()["SsoApplicationId"] as string;
    ISecureStoreProvider provider = GetSecureStoreProvider();
    var userCredentials = new Dictionary<string, string>(2);
    // get the credentials for the user on whose behalf the code
    // is executing
    using (SecureStoreCredentialCollection credentials = provider.GetRestrictedCredentials(targetId))
    {
        // look for username and password in credentials
        foreach (ISecureStoreCredential credential in credentials)
        {
            switch (credential.CredentialType)
            {
                case SecureStoreCredentialType.UserName:
                case SecureStoreCredentialType.WindowsUserName:
                    userCredentials.Add("UserID", GetStringFromSecureString(credential.Credential));
                    break;
                case SecureStoreCredentialType.Password:
                case SecureStoreCredentialType.WindowsPassword:
                    userCredentials.Add("Password", GetStringFromSecureString(credential.Credential));
                    break;
                case SecureStoreCredentialType.Generic:
                    userCredentials.Add("ConnectionString", GetStringFromSecureString(credential.Credential));
                    break;
            }
        }
    }
    return userCredentials;
}

Next switch to the *EntityService.cs class for your External Content Type i.e if your ECT was called Product the filename will be ProductEntityServiceClass.cs

Update the following method

public string GetConnectionStringWithCredentials()
{
    Dictionary<string, string> credentialsFromSecureStore = ReadCredentialsFromSecureStore();
    var connectionStringBuilder = new SqlConnectionStringBuilder(ConnectionString) { UserID = credentialsFromSecureStore["UserID"], Password = credentialsFromSecureStore["Password"] };
    return connectionStringBuilder.ToString();
}

with the following, this method now just returns the connection string

public string GetConnectionStringWithCredentials()
{
    return ReadCredentialsFromSecureStore()["ConnectionString"];
}

Now deploy the solution and it will be using the connection string from the Central Administration secure store, one thing which is worth noting is that when you go to change the credentials again you will not be shown what they were previously.  You also need to make sure that the same secure store setup is used in each of your environments.

So there you have it not only can you use BCS Meta man to generate your c# code and configure the model with a few simple changes you can make the connection string configurable via the secure store.

<Phill />