ASP.NET membership db using integrated security problem

Question

I published ASP.NET MVC web site to a server on a virtual machine (Hyper-V). SQL Server Express installed on the same server.

The problem is that ASP.Net Membership system doesn't work in integrated mode. When Web.config file contains records as follows:

   <connectionStrings>
    <remove name="LocalSqlServer" />
    <add name="MyDBConnectionString" connectionString="data source=vm-1\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>
   </connectionStrings>

I get an error when trying to register and login to the site.

If I change connection string this way:

   <connectionStrings>
    <remove name="LocalSqlServer" />
    <add name="MyDBConnectionString" connectionString="data source=vm-1\SQLEXPRESS;Initial Catalog=testdb;User ID=XX;Password=XXXXXXX;" providerName="System.Data.SqlClient"/>
   </connectionStrings>

I could register and login without any problem.

What could cause the problem with using ASP.NET membership database in integrated security mode?

Answer 1

If you don't use impersonation you need to grant access to membership objects for the account ASP.NET process is running under. Most likely it's NETWORKSERVICE. When you create membership objects with aspnet_regsql.exe, it also creates special roles (like aspnet_membership_BasicAccess, etc) with execute permissions to the corresponding SPs. So create a login for NETWORKSERVICE account, add a user to your database for that login and grant him one of those aspnet_ roles.

Try the following script i use as part of my database deployment (must be run as database administrator):

IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'NT AUTHORITY\NETWORK SERVICE')
CREATE LOGIN [NT AUTHORITY\NETWORK SERVICE] FROM WINDOWS
GO
CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE] WITH DEFAULT_SCHEMA=[dbo]
GO
GRANT CONNECT TO [NT AUTHORITY\NETWORK SERVICE]
GO

exec sp_addrolemember 'aspnet_Membership_BasicAccess', 'NT AUTHORITY\NETWORK SERVICE'
GO
exec sp_addrolemember 'aspnet_Roles_BasicAccess', 'NT AUTHORITY\NETWORK SERVICE'
GO