3

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.

Improve this question

3 Answers 3

Reset to default
3

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.

Improve this answer
5
  • 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
    Commented Apr 15, 2011 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
    Commented Apr 15, 2011 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
    Commented Apr 15, 2011 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
    Commented Apr 15, 2011 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
    Commented Apr 15, 2011 at 8:33
7

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

Improve this answer
4
  • 1
    SSS is the way to go here. Optimally drop the custom web parts and use BCS instead (this also uses SSS)
    – Anders Rask
    Commented Apr 14, 2011 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
    Commented Apr 15, 2011 at 8:06
  • 1
    Note that the Secure Store Service is for SharePoint Server only.
    – Wouter
    Commented Apr 15, 2011 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
    Commented Apr 15, 2011 at 10:44
1

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

Improve this answer
5
  • 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
    Commented Apr 15, 2011 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
    Commented Apr 15, 2011 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
    Commented Apr 15, 2011 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
    Commented Apr 15, 2011 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
    Commented Apr 15, 2011 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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