Discussing the ins and outs of developing for SharePoint and the Microsoft platform

Just a quick one that I thought was worth my time to document. I am working on a web part for a public facing web site at the moment, and it needs to connect to a database to store and read certain data. It will have anonymous and some authenticated users hitting it, so I had the interesting issue of how to handle this authentication on the SQL end. The reason I say it is interesting is because our client doesn’t want to store user names and passwords in the web.config file, so the connection has to use integrated authentication for SQL.

The simple way around this for me, was to run the code that does the database work in a SPSecurity.RunWithElevatedPrivileges() block. Within SharePoint code in one of these will run under the context of SHAREPOINTSystem – the internal system account which isn’t actually a domain account or anything like that. What I have found though is that when code inside one of these blocks runs outside of SharePoint it will run under the identify of the current application pool.

This made the database connection easy, just give the app pool account appropriate access and you are all set. One thing I will stress though, don’t be lazy with your database security. In my example I am figuring out what stored procedures my web part will need and I grant the app pool access to only those procedures. The rest of the stuff in the database is going to be hit through a management UI where authenticated users only will be hitting the page, so I am happy to let the integrated security carry through, so the database is set up to allow admin users access to the full range of stored procedures. I know it is easier to just grant the app pool account access to the lot, but its not a difficult task to lock the security down and doing so reduces the ability of any potential hackers who manage to run some sort code on your site to destroy your database (I believe the security people would call this "reducing your attack surface" or something like that – I’m just gonna call it good development practice and leave it at that!).