WebPart where to store a connection string

Question

I have a webpart that connects to an external DB. I need somewhere to store the connection string. It is possible for this data source to change but would always be the same for the site collection that the webpart is deployed in. Where would the best place to store this information be?

Answer 1

I would recommend you to store things like connection strings in secure storages. For example, SPWeb.Properties require only read permission for site, and they can be revealed easily by any advanced user, for example using SharePoint ECMAScript Client Object Model.

So if you store user name and password for accessing DB in the connection string, it would be probably a bad idea to store it in SPWeb.Properties and other insecure storages.

As you probably know, there is a special service application, which is intended to store credentials: Secure Store Service (Note: it is not available in SharePoint Foundation). And there is MSDN article, which describes how to use this storage:

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

This approach leverages SharePoint Business Connectivity Services, and although it is the intended way to deal with any external database in SharePoint, I admit that in some circumstances the direct database usage might be preferrable (for example, you have a bunch of old working code and you don't want to spend few weeks to migrate it to BCS approach).

So if you want to deal with database directly, actually you can retrieve credentials from Secure Store Services without BCS. This approach is described here:

• http://trentacular.com/2010/11/sharepoint-2010-programatically-retrieve-credentials-from-the-secure-store-service/

And also, there is another article, which probably is exactly what you need, which describes storing a connection string in the SharePoint Secure Store:

• http://www.ericgregorich.com/sharepoint/2011/11/21/accessing-the-sharepoint-secure-store-through-code.html

Answer 2

There are a number of options. Wictor has an interesting post - http://www.wictorwilen.se/Post/Six-ways-to-store-settings-in-SharePoint.aspx

Personally for the complexity of some configuration elements within the systems i work with I would use the web.config and utilse the SPWebConfigModification class to apply the changes to all Web Front End servers.

Answer 3

The Properties member of the root site.

site.RootWeb.Properties["ConnectionString"]

See SPWeb.Properties Property for more information