4

I am working on asp.net web api, when I run my project on micro soft visual studio built in server every thing work fine, but when i host it on local iis server, I got the following error

The underlying provider failed on Open

I am using entity framework, my connection string in web.config is

<add name="Entities" connectionString="metadata=res://*/Models.School.csdl|res://*/Models.School.ssdl|res://*/Models.School.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.\sqlexpress;initial catalog=DBName;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
Usf Noor
  • 219
  • 1
  • 5
  • 21
  • I have got this same error, When i host my application on IIS Express then this issue goes away. But when i try to host it on local IIS then again this issue arises. I guess for local IIS you need to add additional connection to database. I tried this on VS 2015. – Indranil Apr 19 '18 at 10:47

2 Answers2

7

Based on that connection string, the identity trying to connect to your SQL instance is the user account that is specified in your application's IIS app pool (usually iis apppool\defaultapppool). You'll either need to;

  • allow this account to access the SQL database
  • put the necessary credentials for a SQL account into the connection string
  • set the windows account details on the app pool in order for the application to connect.

What's happening is that when you're debugging, the account used to connect via Integrated Security=True is the currently logged in user account - which is likely set up to allow you to connect to the database, especially if you've installed SQL Server during the setup of your machine (it's the default SQL Server setup config to add the current user). However IIS needs a specific account to run under which isn't the currently logged in user - hence the need for the options above.

DiskJunky
  • 4,750
  • 3
  • 37
  • 66
  • 1
    this is a nice and useful answer, points to the issue correctly and nicely, but I had to go changing the stuff in iis server as well.. as per [another here](https://stackoverflow.com/a/14662320/1042705) – Irf Jul 16 '20 at 19:08
0

Under IIS,you are not able to open a database connection and this is normal.

When you run under Visual Studio Built in Server,your asp.net application will be basically running under IISExpress. IISExpress runs under the same user who runs Visual Studio which is 'You'.But when you run under IIS,your application runs under w3wp.exe process and this will be running under the IIS application pool identity. This account is not able to open the database connection.

Please refer https://learn.microsoft.com/en-us/iis/manage/configuring-security/application-pool-identities-and-sql-server-express to how to fix this

Hope it helps!

Rohith
  • 5,527
  • 3
  • 27
  • 31