8
  • 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?

Improve this question
17
  • Please check your database user role permission has IIS APPPOL user allowed. Commented Dec 23, 2015 at 12:52
  • Can you please give me more information? Where/How to check? What IIS APPOL means? Commented Dec 23, 2015 at 12:54
  • Please check stackoverflow.com/questions/1933134/… and stackoverflow.com/questions/7698286/… Commented Dec 23, 2015 at 12:55
  • I checked out that question and if you look at the last comment it says "...This is only for connecting to SQL Server with Windows Authentication." I am not using Windows authentication I am providing SQL server credentials so this does not apply to me? I tried it anyway and it did not work. ;-( Commented Dec 23, 2015 at 13:05
  • 1
    Have you enable Sql server Network Configuration TCP/IP enable from Sql server configuration Manager? Commented Dec 23, 2015 at 13:09

4 Answers 4

Reset to default
5
+25

try Application Pool --> Advanced Settings

NetworkServices

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

4

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

Improve this answer

3 Comments

That solution is for windows authentication. I am logging in using SQL Server authentication, hence the use of sa for the username.
You can make a SQL server account that isn't a Windows account. Ensure that mixed mode authentication is enabled on your server in SSMS. You don't want to use sa (system administrator) for the app account, since this would give your app too many permissions and could potentially introduce some serious security risks. I'm assuming that your app won't need to create and delete databases, delete tables, etc.
Just set integrated security=false to the connection string. That should do the job (see example in answer below)
1

Try setting Integrated Security to false in your connection string. That should prevent it from trying to use Windows authentication.

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx

Improve this answer

1 Comment

You will probably also need to specify the server and the port number in your connection string.
0

Since you supplied the user id and password, you need to set the "Integrated Security=False" to the connection string. By doing so, it will not use the Application Pool user to connect to the database. For example:

myConnection = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=MyDB;Integrated Security=False;User ID=sa;Password=myPass");
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.