SharePoint and <identity impersonate="false" />

Question

I would like to use integrated authentication to access a SQL database from a web part. It should use the IIS Application pool identity.

By default you will get the error:

System.Data.SqlClient.SqlException: Login failed for user 'SERVER\IUSR_VIRTUALMACHINE'.

Because in web.config impersonation is set to true:

<identity impersonate="true" />

I can set this to false and the database code will work. Anonymously accessed sites will also work. Any SharePoint site that uses authentication will fail however so this is not really a solution..

To solve this would I have to encapsulate all my database access code to run with elevated priviliges, is that how SharePoint does it internally? Somehow that doesn't seem like the most performant solution. Is that still the way to go, just use SQL security to access databases from SharePoint custom web parts?

Answer 1

This is incorrect. Because <identity impersonate="true" /> is set to true ASP.NET / IIS will run the thread as the user that is currently logged in (so not the app pool account but the actual user logged into the website).

Something else is going on here. Could you post your connection string for the custom database? (minus the private data off course)

Answer 2

Use SPSecurity.RunWithElevatedPrivileges to run your code in the context of the app pool identity.

Answer 3

The <identity /> and <authentication /> elements in the web.config file will together determine the account that is used in to connect to SQL Server when using integrated authentication.

When <authentication mode="Windows" /> is configured, you're deferring to IIS to authenticate users. I'm guessing that your your web.config contains:

<authentication mode="Windows" />
<identity impersonate="true" />

and that IIS is configured to allow anonymous users. Setting <identity impersonate="true" /> causes IIS to pass the identity of the IIS anonymous access account to SQL Server.

As Lars point out, using SPSecurity.RunWithElevatedPrivileges will achieve what you want. I don't believe you'll see any noticeable impact on performance but that's something you can test :-)