.NET SqlClient login failure from background thread in ASP.NET WebForms application

Question

• ASP.NET 4.51, WebForms, VS2013

I am using Quartz.NET to do some background processing where ultimately I make a connection to my SQL Server. This all works locally on my development machine against IIS Express, but when I deploy it to my staging server running IIS I run into problems.

The code to connect to the database could not be simpler:

myConnection = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=MyDB;User ID=sa;Password=myPass");

however this throws an exception of:

System.Data.SqlClient.SqlException: Login failed for user 'IIS APPPOOL\somehost.somedomain.com'.

I am 100% confident the connection string is correct as when used in a normal page it works just fine. So what is throwing me is the reference to IIS APPPOOL.

Is the SqlConnection somehow not using the connection string it was passed? Doing some form of weird user impersonation when the connection is being made?

Put another way. How do I make the SqlConnection() work from within the thread when I know the connection string is correct?

Answer 1

try Application Pool --> Advanced Settings

NetworkServices

Answer 2

Looks like it's failing trying to open a connection to SQL Server.

You need to add a login to SQL Server for IIS APPPOOL\ASP.NET v4.0 and grant permissions to the database.

In SSMS, under the server, expand Security, then right click Logins and select "New Login...".

In the New Login dialog, enter the app pool as the login name and click "OK"

You can then right click the login for the app pool, select Properties and select "User Mapping". Check the appropriate database, and the appropriate roles. I think you could just select db_datareader and db_datawriter, but I think you would still need to grant permissions to execute stored procedures if you do that through EF. You can check the details for the roles here

Refrance : By jeff-ogata