Data access from web part problems

Question

I've been pulling my hair out for about two weeks so far and I'm about on the edge.

I want to build a web part that queries and external (not part of the SP farm) database. I'm attempting to port from an MCMS page that has been in production for several years. The problem I'm running into has to do with permissions. In MCMS, the SQL calls to the database are all run under the NT AUTHORITY\NETWORK SERVICE account. This has always worked great because I just need to give execute permissions to NETWORK SERVICE for the stored procedures I use.

Sharepoint doesn't give me this option. In SP, anonymous users execute under the AppPool account which I have no problem with. Authenticated users execute under their user permissions. There are two issues here. The "double hop" issue in NTLM that causes the SQL server to read these users as "null". Even if I employ Kerberos, I would still have to deal with having every authenticated user having SQL access. I don't want to mess with that.

That leaves SPSecurity.RunWithElevatedPrivileges. The problem with this is that I can only specify certain code blocks to run elevated. The page was originally built using SQLDataSource in the ascx page and I can't find a way to use the SPSecurity.RunWithElevatedPrivileges with the SQLDataSource. I know I can create the data source in code but there are a lot of auto generated goodies that SQLDataSource wires up automatically (sorting, paging, etc.) that I'd rather not recreate from scratch.

So, rant over, time for the question. Is there a way to execute the entire web part under the AppPool identity? Or perhaps NT AUTHORITY\NETWORK SERVICE? Maybe a way to use the SQLDataSource (from the toolbox on the ascx page) with SPSecurity.RunWithElevatedPrivileges?

Thanks.

Answer 1

The simple answer is you can't do this without totally messing up your permissions as you are doing it now, (Well I don't know of a way put it that way and haven't come across one)

The solution in my book would be to create a 3rd page away that can have these permissions and talk via an API bypassing the need for mixed permissions in one place.

Doing this you are transforming your old database locally into a format that can be read remotely and easily used within sharepoint.

This does throw up other questions like:

If speed is an issue, is it cache-able? Is there a lot of data? Will it need to be paged? etc..

But these questions can easily be identified and the process made.