SharePoint 2010 Custom Solutions SQL Connection Strings Best Practice

In SharePoint, managing custom application settings (such as SQL connection strings) introduces challenges beyond those encountered by developers who are familiar with ASP.NET and other platforms. We have two aspects here, Security and manageability. For security, the password has to be secured at rest (on the disk through encryption) and in memory (in the application when handling it to avoid extracting it from memory dump) and in the wire if the connection is a SQL login that needs to send a password through the wire (using SSL will do it). For Manageability, the more centralized the better of course as long as we have the proper backup policies because loosing such data is critical for recovery. It doesn’t matter which tools as long as it covers these requirements. This article is about SharePoint tools to achieve these security best practices.

First off, You should not store a connection string in a Web Part (unless you find a way to encrypt it), if a user exports the Web Part from a page, they can see all properties, including any connection string data. There are several different storage mechanisms for configuration settings, including the hierarchical object store, configuration files, property bags, and SharePoint lists. Each mechanism has advantages, disadvantages, and sometimes risks—for example, SharePoint property bags provide an easy-to-use storage mechanism, but developers risk corrupting the configuration database or the content database if they attempt to persist non-serializable types. Here is an MSDN article about the pros and cons of each. http://msdn.microsoft.com/en-us/library/ee413935.aspx it applies to 2007 and 2010 versions of SharePoint.

The most popular choice Web.Config
Web.config files are a good choice to store connection information. Because every site collection is hosted in a web application and every web application has a Web.config file, the Web.config file associated with your farm solution is one place you can store connection strings or other data.  The Web.config files of SharePoint web applications are stored in C:\inetpub\wwwroot\.

To add a connection string to the Web.config file, you can simply add a “connectionStrings” section between the closing (</SharePoint>) and opening ( <system.web>) elements.

</SharePoint>
<connectionStrings>
<add name="MyConnectionString" connectionString="Data Source=serverName;Initial Catalog=DemoDB;Persist Security Info=True;User ID=userName;Password=password;"/>
</connectionStrings >
<system.web>

Another manual way to add a connection string to a web.config file is from IIS. In IIS 7 click on your Web Application and double click connection strings Icon (see graphic below) and simply add a connection by filling out the form. You can set an account and password but it will not be encrypted. It is equivalent to adding a string manually per above step, there is nothing special here.

image

We do not recommend manually configuring a SharePoint Web.config file on multi-server Farms. For readability purposes, you can see the password for the connection, but in production environments you should always encrypt passwords before storing them in files. For more information, see MSDN for encrypting connection information at: http://msdn.microsoft.com/en-us/library/ms178372.aspx. This also needs to be done in the Web.config files of the target web applications on the destination servers.

Walkthrough: Encrypting Configuration Information Using Protected Configuration

Best Practice if choosing Web.config
Settings may be stored in the Web.config file only when SharePoint, Internet Information Services (IIS), or ASP.NET needs access to these settings to be able to operate.

When adding connection strings to web.config, you must use the SPWebConfigModification class in a feature event receiver in a WSP, this will update the SharePoint Web.config file automatically including the destination servers,  automatically when the feature is activated (or removed when the feature is deactivated).

Never edit the web.config file by hand, these settings can disappear on you. See what Joel has to say about this

This is a must read article from MSDN. http://msdn.microsoft.com/en-us/library/ee855123.aspx. It provides options and sample code for storing configuration settings in SharePoint, it applies to 2007 and 2010 versions of SharePoint.

Choosing to use the Secure Store

Using Secure Store in SharePoint 2010 is an optional place to store and encrypt the user name and password for for access by custom coded solutions. This can ease the manageability aspect of having to encrypt portions of the Web.config file. Below is a sample code block from the SDK at this url http://msdn.microsoft.com/en-us/library/ff625799.aspx. and it shows a way to get the credentials from Secure Store (assuming correct permission levels of course)

It can help you with NTLM “double hop” issues where credentials cannot be transferred across more than one computer boundary. The secure store service provides a way to store credentials securely rather than hard coding them in code or configuration files. SP2010 is making it easier to create more sophisticated enterprise solutions.

private static string GetCurrentDMSUsername()
{
    string result = null;

    ISecureStoreProvider secureStoreProvider = SecureStoreProviderFactory.Create();

    using (SecureStoreCredentialCollection credentialCollection = secureStoreProvider.GetCredentials(SecureStoreApplicationID))
    {
        foreach (SecureStoreCredential credential in credentialCollection)
        {
            if (credential.CredentialType == SecureStoreCredentialType.UserName)
            {
                result = GetStringFromSecureString(credential.Credential);
                break;
            }
        }
    }

    return result;
}

See another Code Snippet: Get User Credentials Using the Default Secure Store Provider

Optional Included Component for Large Scale Farms

Because of the complexities in choosing and implementing an appropriate strategy for the storage of configuration data, the SharePoint Guidance team has developed an optional reusable component named the Application Setting Manager. You can use it in your SharePoint applications to provide an easy, consistent, and type-safe means of storing and retrieving configuration data at any level of the SharePoint hierarchy. Because it uses property bags as its underlying storage mechanism, the Application Setting Manager is not suitable for storing large payloads. If you need to store data exceeding 4kilobytes (KB) in size, you should choose an alternative approach to storage. For example, you can configure the Application Setting Manager to use list-based property bags.

Performance

The potential for a significant performance problem is very real if retrieving credentials or other settings is done incorrectly. Caching is usually needed in busy SharePoint custom solutions. SharePoint presents some unique challenges that must be taken into account mainly because of the multi-server nature of a typical SharePoint Farm. You must also be aware of the volume of content that you are storing in cache and make sure that this is not presenting the very same performance problems you are trying to prevent. Performance is always a trade-off and  Knowledge of your application and testing are the only ways to legitimately tune everything appropriately.

 

Recommendations

So what is the right answer? Like so many things in SharePoint, the answer is inevitably it depends, or perhaps the answer is really a combination of these options. Global items should go into the hierarchical object storage or a property bag scoped to the farm or Web application. Items scoped at a lower level should be implemented via a property bag attached to the appropriate item – SPSite, SPWeb, SPList, SPListItem, etc. – or in a SharePoint List. Deciding which option(s) work for your application is a factor of many things, including a degree of personal preference.

Regardless of which option is chosen, a few recommendations can be applied globally:

  • Consider caching frequently-used or expensive-to-retrieve values in memory. Your backend storage is still any one of the options presented here; you just have a mechanism for reducing the performance impact of accessing the values.
  • It is important that you have a standard mechanism for reading from and writing to the persistent store. Typically this is accomplished by means of a utility class with a bunch of static methods to handle retrieving and storing values. Besides the obvious benefits of this approach, it also allows you to easily change the back end storage without impacting any of the rest of your application. If all your application needs to do is make a call to SettingsManager.GetValue() and pass in the name of the setting value it needs, it doesn’t matter whether that value is coming from a SharePoint List, an external database or the hierarchical object storage – the utility class insulates your application from those details.
  • Similar to above, have a common place where all settings for your application are managed – especially if you are using multiple storage options. Don’t force administrators to know where each setting is stored (hierarchical object storage, list, property bag, etc.) to know where they need to go to in order to manage that setting. Have your application do the heavy lifting and pull all of that functionality together in one place for your application.
  • One final recommendation is to set your standards and stick with them. Few things can make an application seem less professional and less well-thought out than a seemingly arbitrary use of different options for storing configuration settings. Know why you are using each option and make sure that it is consistent with the rest of the options chosen for your environment.

Additional Resources

http://msdn.microsoft.com/en-us/library/ff829215.aspx

­A step by step guide in designing BCS entities by using a SQL stored procedure (Jyoti Saikia)

http://msdn.microsoft.com/en-us/library/gg552610.aspx

Technologies for Creating SharePoint Applications http://msdn.microsoft.com/en-us/library/gg454784

Advertisements