SharePoint and

sharepointsharepoint-api

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?

Best Answer

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 :-)

Related Topic