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?