Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

We want to ensure that we are following best practice around connecting to a custom database. I'm not a developer as such so thought I would ask SharePoint Overflow :-).

We have several SharePoint Web parts that connect to a custom SQL DB. We have a set of ASP.NET 4 forms that connect to the same database.

Looking through this article I gather that storing the credentials used by our Web parts in a SPWebApplication object would be a good idea.

For our Web forms we were planning on storing the credentials in an encrypted form in web.config files.

A few questions:

  1. Presumably we would need to decrypt credentials using a key; how do we ensure that this is secure?
  2. Is there a means by which we can store a "global" set of credentials securely rather than having to duplicate them in web.config files and SharePoint objects? I imagine we will run into permission issues if we attempt to store credentials in the SPFarm object.

Thanks in advance for any assistance you might be able to offer.

share|improve this question

3 Answers 3

up vote 3 down vote accepted

Don't forget the trusted subsystem model where you just connect to the database using the credentials of the application pool. Downside being you lose info on the actual user who you are connecting for.

share|improve this answer
    
Thanks - I am considering this. Are there any drawbacks with using the app pool identity to access both the content databases and a custom DB? Presumably we would give the account least privilege access, i.e. to stored procs only. –  Benjamin J Athawes Apr 15 '11 at 8:07
    
The main downside is that you lose information about the user who you are accessing the database for. Typically the solution is to pass this information as a variable to a stored procedure. Meaning, inside the procedure you cannot use the USER_NAME macro, but instead need to use the variable. –  Wouter - SharePoint MVP Apr 15 '11 at 8:13
    
To extend this; you could also use Kerberos and pass the user context all the way back to your databases –  Wictor Wilen MCA MCM MVP Apr 15 '11 at 8:15
    
Accessing the custom DB as the app pool identity works in our case. Presumably this means that we would need to use elevated privileges; are there any examples of a decent DAL for this? –  Benjamin J Athawes Apr 15 '11 at 8:27
    
Depending on the area where the code executes (web service, or ASPX code / webparts), you only need to run SPSecurity.RunElevated. As long as you don't open new SPSite objects performance degradation should be low enough to make the solution viable. –  Wouter - SharePoint MVP Apr 15 '11 at 8:33

If you are storing credentials then you should try to take advantage of the Secure Store. You can use this to store a set of credentials for a group of users, or to store individual credentials.

MSDN link

share|improve this answer
    
I suppose I'm wondering if we should be storing credentials at all. We have used SQL auth in the past but I'd like to move to Windows on the understanding that it is more secure. In this scenario the trusted subsystem model (described above) seems like the way to go. –  Benjamin J Athawes Apr 15 '11 at 8:31
1  
Secure Store credentials can be Windows or SQL, or anything else for that matter. You need to consider carefully the implications of giving your app pool identity access rights to your database, if you go the trusted subsystem route. –  SPDoctor Apr 15 '11 at 10:25
1  
In SSS you can store the whole connection string (or any other string). Theres no demand to use login/pwd –  Anders Rask Apr 15 '11 at 10:45
    
I suppose the complexity is that I want all users with access to view a Web part to be able to view the back end data without having to give the users access to the DB. Would I allow the App pool identity access to use the SSS credentials in this scenario? –  Benjamin J Athawes Apr 15 '11 at 11:40
    
No, you would create a group mapping and add your users, possibly through an "all authenticated users" group or something more restrictive. To elaborate on app pool identity, some DBAs might not trust this - depends how important your custom database is - perhaps more sensitive than your content db. In the case of RevertToSelf it is actually the identity of the BCS service, which is presumably different from the app pool identity. –  SPDoctor Apr 15 '11 at 13:36

To store settings in SharePoint I do recommend you to take a look at the Application Setting Manager for SharePoint (developed by the MS Patterns & Practices group); http://msdn.microsoft.com/en-us/library/ff798410.aspx. It's an excellent set of utility classes to maintain farm-wide settings, which are overridable in site collections or sites.

On the other hand; storing secure information in this might be/is a security risk. Fortunatley there's a service application in SharePoint Server 2010 called the Secure Store Service. This is a service made exactly for storing secure credentials and retrieve them in a secure fashion. More info at: http://msdn.microsoft.com/en-us/library/ee557754.aspx

share|improve this answer
1  
SSS is the way to go here. Optimally drop the custom web parts and use BCS instead (this also uses SSS) –  Anders Rask Apr 14 '11 at 22:24
    
Are external content types reusable, i.e. can we set parameters on them to filter results upon SPWeb creation? Also - could we drop SQL authentication and use Windows instead? –  Benjamin J Athawes Apr 15 '11 at 8:06
1  
Note that the Secure Store Service is for SharePoint Server only. –  Wouter - SharePoint MVP Apr 15 '11 at 8:14
    
Yeah Wouter, that surprised some in our company as well since the SKU overview we recieved from Microsoft said it was Standard, but TechNet said otherwise –  Anders Rask Apr 15 '11 at 10:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.