Last post Nov 30, 2010 06:55 PM by nathon
Nov 03, 2010 06:16 PM|nathon|LINK
I have an ASP.NET 4.0/C# web application that I have deployed to a web server. However, pass through authentication is not working. I'm getting an exception in the app event log saying that, "The underlying provider failed on Open". When I check further,
it is attempting to login to the SQL Server as the computer$ account.
What I want to have happen is the following.
I have the following in my web.config.
<identity impersonate="true" /><authentication mode="Windows" /><authorization> <deny users="?" /> <allow roles="DOMAIN\admingroup" /> <allow roles="DOMAIN\contribgroup" /> <allow roles="DOMAIN\readonlygroup" /> <deny users="*" /></authorization>
Next setup IIS to enable Windows Authentication & ASP.NET Impersonation (others are disabled), the site basic settings - connect as is set to Application User (pass-through authentication). My understanding is this should cause my username to be passed through
to my SQL Server. However, it just keeps passing through the computer account (i.e. SYSTEMNAME$).
Anyway, what do I do to get the account to pass to IIS and then through to the SQL Server, instead of the computer account?
Nov 04, 2010 08:18 AM|Keep it Simple|LINK
You have <deny users="?" /> as the very first authorisation rule.
This will deny Anonymous users. I'm assuming your site is for an intranet.
Try moving the abovementioned DENY entry immediately below the list of ALLOW's.
Nov 09, 2010 06:46 AM|Zizhuoye Chen - MSFT|LINK
I think, first, make sure that IIS has disabled the anonymous authentication and confirm that only enabled windows authentication. Remove the first node of authorization section "<deny users="?" />".
You can use Environment.UserName or User.Identity.Name to get the current login user account(impersonate user) on server side, and also check this user account(or user group) is added in the SqlServer database user allowed list.
Second, When connect to SQL Server with windows authentication, make sure some attributes included in connection string similar like:
<add name="ApplicationServices" connectionString="data source=SqlServer; initialcatalog=Northwind; integrated security=SSPI; persist security info=False;
Access Sql Server using windows authentication:
May be you also can have a look at this article:
Hope this can help you.
Nov 15, 2010 09:42 PM|nathon|LINK
Thank you very much! Sorry for not replying very soon. I've been away for a bit. I will try your suggestions and post back if I need anything further. Thanks!
Nov 30, 2010 06:55 PM|nathon|LINK
IT WORKED!!! Thank you all very much for helping with this. I moved the deny statement and updated the connection string and it worked beautifully! Thanks again!