Keep getting “Login failed for user IIS APPPOOL\De

Question

I'm trying to give an ASP NET App access to a database (These are test only not production).

It's the first time I try it in this network, and I haven't done it with VS2012, SQL Server 2012 Express, Windows 7 before

What I have done:

• I upgraded my Sql Server 2012 Express since what VS2012 first installs is a version that doesn't allow access from IIS
• I found that I had to configure SQL Server 2012 Express to accept remote connections, then I followed a good blog I found here
• In SSME 2012, I added a new login for all databases NT AUTHORITY\NETWORKSERVICE (I browsed it and found "Servicio de red" which stands for NETWORKSERVICE, so it is actually NT AUTHORITY\Servicio de red)
• I set dbo as the default schema for this login
• Under login properties, in User Mappings, I checked my Database (which I named MiniNorthwind) and then checked db_datareader and db_datawriter in Database Role Membership for MiniNorthwind
• In Internet Information Services Manager I checked that the DefaultAppPool Identity was ApplicationPoolIdentity (It was the default setting)

But I still get an error saying that Login failed for user IIS APPPOOL\DefaultAppPool, if I change the DefaultAppPool identity to NetworkService, then the error changes to Login failed for user NT AUTHORITY\Servicio de red

I don't know what else to do. Please, need help here.

Rafael

Answer 1

In IIS Manager set DefaultAppPool identity as NetworkService. Although it works, you should try to make it work with AppPoolIdentity, since it the Windows 7 attempt to make it safer

Answer 2

Change your connection string.

For example:

//< add name="CS"  connectionString="server=.;database=JackDB;user id=sa;password=ya;">

This solves the error.

Answer 3

In my case, I had to change my connectionString from :

<add name="DatabaseName" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=HrAndPayroll;Integrated Security=True" providerName="System.Data.SqlClient" />

to :

<add name="DatabaseName" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database.mdf;Initial Catalog=HrAndPayroll;Integrated Security=True" providerName="System.Data.SqlClient" />

After I deployed my project, my connectionString got modified hence removing AttachDbFilename=|DataDirectory|\Database.mdf, so I had to give the link of my database explicitly in the connectionString.

Answer 4

application pools -> your application pool used in application -> click it and then click 'Advance Settings' in panel. Find 'Identity' property and change it to localsystem.

Answer 5

When we tried to hit api login method for url ("http://api.xyz.com/v1/login") which we have registered in 'C:\Windows\System32\drivers\etc\hosts' file and hosted on IIS. Then we got an error Login failed for user 'IIS APPPOOL\DefaultAppPool'. Cannot open database" But after running below query on database the problem resolved.

IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'IIS APPPOOL\DefaultAppPool')
BEGIN
    CREATE LOGIN [IIS APPPOOL\DefaultAppPool] 
      FROM WINDOWS WITH DEFAULT_DATABASE=[master], 
      DEFAULT_LANGUAGE=[us_english]
END
GO
CREATE USER [WebDatabaseUser] 
  FOR LOGIN [IIS APPPOOL\DefaultAppPool]
GO
 EXEC sp_addrolemember 'db_owner', 'WebDatabaseUser'

Answer 6

You can change the ApplicationPoolIdentity from IIS7 -> Application Pools -> Advanced Settings.

Under ApplicationPoolIdentity you will find local system. This will make your application run under NT AUTHORITY\SYSTEM, which is an existing login for the database by default.

For more info Look at this link: https://stackoverflow.com/a/8853386/1676736