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?

TheEdge
  • 9,291
  • 15
  • 67
  • 135
  • Please check your database user role permission has `IIS APPPOL` user allowed. – Parth Trivedi Dec 23 '15 at 12:52
  • Can you please give me more information? Where/How to check? What IIS APPOL means? – TheEdge Dec 23 '15 at 12:54
  • Please check http://stackoverflow.com/questions/1933134/add-iis-7-apppool-identities-as-sql-server-logons?answertab=active#tab-top and http://stackoverflow.com/questions/7698286/login-failed-for-user-iis-apppool-asp-net-v4-0?answertab=active#tab-top – Parth Trivedi Dec 23 '15 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. ;-( – TheEdge Dec 23 '15 at 13:05
  • 1
    Have you enable Sql server Network Configuration TCP/IP enable from Sql server configuration Manager? – Parth Trivedi Dec 23 '15 at 13:09
  • TCP/IP is enabled as well as Shared Memory, but not Named Pipes. As an aside if I use the above credentials and select SQL Server authentication in SSMS then I can connect just fine. And note too that this connection works as expected when I am in code behind for a regular ASPX file. – TheEdge Dec 23 '15 at 13:17
  • Are you transforming your web.config file for different build configurations? Could be that connection string is set differently for yoru deployment configuration. – dbugger Dec 23 '15 at 13:41
  • Nope. Took the connection string from the web.config on the target machine and hard coded it in my call. Just to be sure it was not something like that, that was tripping me up. – TheEdge Dec 23 '15 at 13:44
  • Now since Quartz is a background process, does it start before you set the connection string? If so, it could be using a default connection. – dbugger Dec 23 '15 at 13:51
  • No. I purposely hard coded it so that there was no chance of that while trying to track this down. – TheEdge Dec 23 '15 at 14:04
  • 127.0.0.1 is local host, are you sure you didn't leave the hardcoding in there. is the port correct , 15001 or some thing like that. not using a default port? what is the actual exception? any stack trace? – bhushanvinay Dec 27 '15 at 09:36
  • The DB is on the same host as the web application, hence 127.0.0.1. That exact connection string works everywhere else in the application. So no different port etc. – TheEdge Dec 28 '15 at 22:51
  • may be odd but can you try adding - Persist Security Info=True; – techspider Dec 29 '15 at 22:12
  • I don't know if capitalisation is important when it comes to connection strings but try `User Id` instead of `User ID` – John C Dec 31 '15 at 17:34
  • I wouldn´t utilize 127.0.0.1 but the URL-name (like www.xxxx.com, if possible) OR the actual IP-number of computer in network. See also if your SQL is enabled to accept EXTERNAL CONNECTIONS. – David BS Dec 31 '15 at 20:37
  • @DavidBS The DB is on the same machine on the web server. No external connections to the DB are allowed via the external IP address. – TheEdge Jan 02 '16 at 06:44
  • @JohnC capitalisation did not make a difference. – TheEdge Jan 02 '16 at 06:45

4 Answers4

5

try Application Pool --> Advanced Settings

NetworkServices

enter image description here

Valentin Petkov
  • 1,570
  • 18
  • 23
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

Community
  • 1
  • 1
Hussein Khalil
  • 1,395
  • 11
  • 29
  • That solution is for windows authentication. I am logging in using SQL Server authentication, hence the use of sa for the username. – TheEdge Dec 28 '15 at 22:51
  • 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. – Taraz Jan 02 '16 at 21:12
  • Just set integrated security=false to the connection string. That should do the job (see example in answer below) – TheDaveJay Jan 03 '16 at 00:50
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

Taraz
  • 1,242
  • 13
  • 13
  • You will probably also need to specify the server and the port number in your connection string. – Taraz Jan 02 '16 at 21:22
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");
TheDaveJay
  • 753
  • 6
  • 11