Failure to log in to an IIS application when trying to connect to Sqlserver

Question

When I run an application of the type website in visual studio, database connections work normally. But when trying to run IIS 7 to perform some tests, I get the following message:

Unable to open the "XXX" database requested by logon. Login failure.

User login failure 'IIS APPPOOL Dotnet4'.

The specific database with which I am having difficulties to connect I left the authentication method configured to be accessed through the Windows user login.

To conectionString of my Web.config looks like this:

<add name="LocalConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=XXX;Connect Timeout=3000; Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

Note: They told me I should go to app pool and configure the option Identity with the being of my user, however my user has password and is not accepting I add it in this dialog:

Answer 1

This has nothing to do with configuring the IIS itself. The error says that the user IIS APPPOOL Dotnet4 cannot connect to SQL Server.

This is because your ASP.NET application is using Integrated Security, this means that the application will use the user configured in pool to which it belongs to connect to SQL Server.

Most likely, when installing SQL Server you set that only the current user would have access to the database.

Therefore, it is necessary to add the user IIS APPPOOL Dotnet4 as a login of SQL Server.

1. Open the SSMS and expand the node security

2. Right click on logins and then in new login

3. In Login Name type the user name that appears in the error.

   Note: I have seen many people say that you should not click on search, because some error happens. So just paste the user name into the text box.

   

Another option is to change the form of connection for a user of SQL Server itself. If you have set up any user in the installation, you can use it just by changing its string connecting to

<add name="LocalConnection" 
     connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=XXX;
     Connect Timeout=3000; User ID=XXXX; Password=XXXX;
     MultipleActiveResultSets=True" 
     providerName="System.Data.SqlClient" />

If there is no registered user or if you do not remember the password configured in the installation just open the same screen of the above steps, choose SQL Server Authentication and enter with credentials.

Answer 2

The IIS runs an own user and the same is not authorized to log into your server happens this. Probably the user who is authorized is your windows user and not the IIS.

To solve this you can create a user of SQL Server to make the connection in the database and change your connection string to connect to it. Your connection string would look like this:

<add name="LocalConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=XXX;Connect Timeout=3000; User ID=USUARIO;Password=SENHA;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

Or authorize the user of IIS to connect to your bank

Answer 3

https://docs.microsoft.com/en-us/aspnet/web-forms/overview/deployment/visual-studio-web-deployment/deploying-to-iis

Run this in sql

SQL

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 [Contosouniversityuser] FOR LOGIN [IIS APPPOOL Defaultapppool] GO

EXEC sp_addrolemember 'db_owner', 'Contosouniversityuser' GO