Why is ASP.NET ignoring my Membership connection string?

Question

I have an ASP.NET app using built-in Membership functionality. As such, I have a connection string in my web.config that looks like this:

<add name="MembershipSqlServer" connectionString="Data Source=servername;Database=aspnetdb;uid=user;pwd=password;" />

When working on my dev machine, everything is peachy keen. But when I move things to the web server (which also happens to run the SQL Server), I get this error when User.IsInRole() is called:

System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

F$%*&!! Why is it attempting to connect in this way? Why isn't it using user/password from the connection string? Web.config is identical on dev and server, I am using the DB on the server during development.

Answer 1

What does the membership providers section in your web.config look like? Is it possible that you left out the connectionStringName attribute? In which case, I believe it would be trying to connect to the database on your local machine using integrated security.

The membership providers section in your web.config should look something like:

<membership defaultProvider="SqlProvider">
  <providers>
    <add 
      name="SqlProvider" 
      type="System.Web.Security.SqlMembershipProvider" 
      connectionStringName="MembershipSqlServer"
      ...
    />
  </providers>
</membership>

Answer 2

OK, I figured it out... only 35 minutes. :P

Long story short: There are two parts to asp.net membership… a membership provider and a ROLE provider. Why you’d ever want these two things separated, I don’t know… But my web.config wasn’t specifying the role provider and connection string, so it was defaulting to the settings in machine.config (aka LocalSqlServer connection string).

So all this time, my app users were on the server... but the roles were stored in a local .MDF file in App_Data. Ugh.

Answer 3

Post your web.config. Looks like you are pulling something from machine.config somewhere in the chain.

Answer 4

Do you see this <authentication mode="Windows" /> in your web.config? And your other connectionString uses Integrated Security=True; On your Sql server in order to use windows authentication you must have a Login(on the server) for the windows user or group as well as have an associated user in the database.

The simple but not suggested fix would be to create a login for 'NT AUTHORITY\NETWORK SERVICE' on you sql server and then a user in your specific database for that maps to that login. The secure way is to do this for each of the network security groups that need to access the sql server so you can manage the group permissions independently.