Membership Provider producing unexpected SQL Error

Question

I am getting the following error when calling Membership.GetUser() on a specific page (first page on the site to do so as we implement proper security controls)

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'IIS APPPOOL\UAT SITE'. at .... at System.Web.Security.SqlMembershipProvider.GetUser(String username, Boolean userIsOnline) at System.Web.Security.Membership.GetUser(String username, Boolean userIsOnline)

The site is running fine under IIS Express, but UAT is under IIS 7.5 on 2008R2

Web.Config

<authentication mode="Windows" />
    <identity impersonate="True" />
    <authorization>
      <deny users="?" />
      <allow users="*" />
    </authorization>
    <membership>
      <providers>
        <clear />
        <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="DatabaseGTN" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="GTN" />
      </providers>
    </membership>
    <roleManager enabled="true">
      <providers>
        <clear />
        <add name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="DatabaseGTN" applicationName="GTN" />
      </providers>
    </roleManager>

Under the "Authentication" node for the site, ONLY ASP.NET Impersonation and Windows Authentication are enabled The Web Server Windows Authentication feature is installed according to the server manager.

All other pages on the site are running perfectly happily using the same connection string references, and the audit logging indicates that the correct username COMPANY\MyName is being returned by SYSTEM_USER inside MS SQL, so it looks like every other SQL request is impersonating as expected.

Any suggestions for a configuration option that I may have overlooked would be much appreciated.

I should make that clearer. Rather than adding an additional DB user or role, I want to get the impersonation working correctly. Or I want to be able to tell my boss why it can't be done.

score 0 · Answer 1 · answered Aug 14 '12 at 02:47

0

The only thing I can think of, looking at your error, is to be sure to have added access to your db to "IIS APPPOOL\UAT SITE".

To check that, just open Sql Server Management console and look in the security node if there is this user.

Than in the porperties of this user look under "User mapping" to see if the authorizations for your db are all right.

answered Aug 14 '12 at 02:47

Iridio

9,213
4
49
71

Thanks. But that's precisely the opposite of what I want to allow. Only users who are members of a specific user group are allowed to access the database at all. And it works perfectly happily for thousands of other DB calls, just not for the one the membership provider is carrying out. – Roger Willcocks Aug 14 '12 at 02:52

Membership Provider producing unexpected SQL Error

1 Answers1