Where to store Your custom SQL Connection Strings in SharePoint

Artigo
07/31/2012

This article is about SharePoint tools to control and manage your custom connection strings, and most importantly, the access accounts and passwords. With SharePoint multi-server Farms, managing custom SQL connection strings can be a challenge for the Farm Administrator and the Developer. We have two aspects here, Security and Manageability. For security, the password has to be secured at rest (on the disk through encryption), in memory in the application to avoid extracting it from memory dump and finally through the wire (or Air) (using SSL will do it). For Manageability, the more automated and centralized the better. If you have 2 or more web front end servers, each server will need a copy of the connection string and doing this manually is risky and error prone. Proper backup and restore policies must be simple and manageable because losing such data is critical for recovery. It doesn’t matter which tools are used manage connection strings as long as it covers these requirements.

There are several storage mechanisms available, 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. This is a great MSDN article about the pros and cons of each mechanism. https://msdn.microsoft.com/en-us/library/ee413935.aspx (applies to 2007 and 2010 versions of SharePoint). You should not store a connection string in a Web Part unless you find a way to encrypt it because a user can export the Web Part from a page and can see all properties, including any connection string data even access accounts and passwords if not done right.

The most popular choice Web.Config

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. It is not a best practice in multi-server Farms with many servers because of extra manageability burden put onto the Farm Administrator. Manual configuration is never a best practice in multi-server Farms.

If you do decide to use the Web.config in production environments you should always encrypt passwords before storing them in files. For more information, see MSDN for encrypting connection information at: https://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, a very manual and error prone process.

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, your Developer should use the SPWebConfigModification class in a feature event receiver in a WSP, this will update the SharePoint Web.config file automatically including all destination servers. Typically the web.config update will happen 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. https://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 and highly recommended place to store and encrypt the user name and password for access by custom coded solutions. The secure store service provides a way for Farm Administrator to store credentials securely rather than hard coding them in code or configuration files. This can simplify the manageability aspect of having to encrypt portions of the Web.config file plus if hands over more control to the Farm Administrator to centrally manage access accounts and passwords. Below is a sample code block from the SDK at this url https://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). The balance of the connection string data such as server name and database can be stored in a place of your choosing and many choose a SharePoint list in Central Admin site but remember about recoverability in disaster scenarios, the Central Admin site may not be restorable.

Secure Store can help you with NTLM “double hop” issues where credentials cannot be transferred across more than one computer boundary because typically the Secure Store is one hop away from the SQL data. . 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

Application Setting Manager for Large Scale Farms

Because of the complexities in choosing and implementing an appropriate strategy for the storage of configuration data, such as connection strings, 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 along with testing are the only ways to legitimately tune everything appropriately.

Final 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

https://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)

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

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

Updates to this blog, Aug 1, 2012

What about using odc files for your custom apps? Like they do in Excel, read this. https://blogs.msdn.com/b/sharepointdev/archive/2012/02/09/how-to-programmatically-update-an-odc-file-shubh-raj-singh.aspx

Partilhar via