SQL won't connect after deploying

Question

I recently deployed my website on IIS (LocalDB). But whenever I try to run the website, the SQL fails to connect. I've been through hundreds of posts/articles now but I am unable to resolve it.

Error

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Cannot create an automatic instance. See the Windows Application event log for error details. )

Windows Application event log

Windows API call SHGetKnownFolderPath returned error code: 5. Windows system error message is: Access is denied. Reported at line: 422.

Second log -

Cannot get a local application data path. Most probably a user profile is not loaded. If LocalDB is executed under IIS, make sure that profile loading is enabled for the current user.

My Connection Strings (tried both)-

<appSettings>
    <add key="ConnectionString" value="Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=test;Integrated Security=True" />
    <!--<add key="ConnectionString" value="data source=(LocalDB)\MSSQLLocalDB;UID=SOME_USERNAME;PWD=SOME_PASSWORD;initial catalog=test;connection timeout=30"/>-->
  </appSettings>

I tried editing applicationHost.config.
Load User Profile was already True for me

My ApplicationHost.config file

 <applicationPools>

            <add name="Classic .NET AppPool" managedRuntimeVersion="v2.0" managedPipelineMode="Classic" />
            <add name=".NET v2.0 Classic" managedRuntimeVersion="v2.0" managedPipelineMode="Classic" />
            <add name=".NET v2.0" managedRuntimeVersion="v2.0" />
            <add name=".NET v4.5 Classic" managedRuntimeVersion="v4.0" managedPipelineMode="Classic" />
            <add name=".NET v4.5" managedRuntimeVersion="v4.0" />
            <add name="ASP.NET v4.0" managedRuntimeVersion="v4.0">

            <add name="DefaultAppPool" autoStart="true" managedRuntimeVersion="v4.0" managedPipelineMode="Integrated">
           <processModel identityType="ApplicationPoolIdentity" loadUserProfile="true" setProfileEnvironment="true" />
            </add>
        </applicationPools>

My IIS manager -

DefaultApplicationPool -

My web app configuration -

App Pool permission

EDIT -

Added IIS Apppool using

icacls c:\inetpub\wwwroot /grant "IIS APPPOOL\DefaultAppPool":(OI)(CI)(RX)

Still not able to connect.

Any help is appreciated.

I am on Windows 10 using Visual Studio 2015 with SQL Server 2016.

Did you enable Load User Profile for the account running the app pool? — Tim, Jul 18 '16 at 16:42
Yes, but when you say it was true for you, do you mean your account, or for the account running the app pool of the application? — Tim, Jul 18 '16 at 16:44
DefaultAppPool was true. Which is used by my web application. — Pirate X, Jul 18 '16 at 16:46
Does the app pool account have read/write access to the path where the DB is? — Tim, Jul 18 '16 at 20:03
Check the permissions on the directory the database is in and make sure the app pool account (should be something like IIS AppPool\DefaultAppPool) has read/write permissions. — Tim, Jul 19 '16 at 05:16
@Tim See I've added my screenshot of permission. Can you tell me how to add pool account permission if they aren't there? — Pirate X, Jul 19 '16 at 05:22
Usually just right-click the folder, select Properties, then the Security tab and in that window click Edit - you can add/modify account permissions from there. — Tim, Jul 19 '16 at 05:23

score 11 · Answer 1 · answered May 09 '17 at 20:29

11

Had this same problem. I didn't try all the things you did, though. I did the following:

IIS Manager
Application Pools
Find the pool your app belongs to (for me it was .NET v4.5)
Right click -> Advanced Settings
Scroll down to Identity
Change from whatever (for me it was ApplicationPoolIdentity, same as you have) into LocalSystem.

answered May 09 '17 at 20:29

Robotronx

1,728
2
21
43

3

I'm having the exact same problem and this also worked for me! – rejy11 Dec 11 '18 at 11:22
Also worked in my local IIS, but I'm using a local database, not from the sql server I have the same problem on a provider server. but I don't get it because with the credentials they gave me, I changed the web.config from local iis and it worked. But in their server I have the same problem. Can u please tell me what happened so I can explain to their team? – Belarmino Vicenzo Jan 23 '21 at 08:58

score 1 · Answer 2 · answered Jun 21 '17 at 18:28

Whilst there are workarounds you can try (either running SQL Server under your account or sharing the LocalDB instance and connecting to it using the newly created shared address), I think the best solution here is to upgrade to SQL Server Express 2012 which is what I ended up doing.

Detailed explanation is outlined in this blog entry: https://blogs.msdn.microsoft.com/sqlexpress/2011/12/08/using-localdb-with-full-iis-part-2-instance-ownership/

I wasted almost a day on identifying the cause and workarounds and came to conclusion it was probably simplest and most future proof if I upgrade the SQL Server version, as LocalDB doesn't seemed like it was designed to work in IIS or similar environments after reading the blog.

score 0 · Answer 3 · answered Mar 08 '17 at 10:19

I can see two possible errors.

Let's assume your SQL server is on the same box as the IIS, and lets suppose as your ConnectionString suggests, you installed SQL server with a named instance "MSSQLLocalDB", your connection string should be:

Data Source=localhost\MSSQLLocalDB;Initial Catalog=test;IntegratedSecurity=True"

If LocalDB is on another machine, dont use the hostname (unless you have DNS server), simply use the IP address.

Now given your Application is using APPPOOL\DefaultAppPool, be sure to grant this user permissions to do whatever it is you intend to do in SQL (e.g. if Execute stored procedure). You must add this as a login and then grant it EXECUTE, SELECT, INSERT, DELETE permissions to the database.

The Data Source must always be /

I suggest you connect SSMS and confirm hostname. I wouldn't expect "(" or ")" to appear there. Unless you were merely referring to your (LocalDB) which could be 192.168.0.1, etc.

score 0 · Answer 4 · edited Mar 19 '19 at 10:52

I had exactly same issue and finally I solved. Hope it works for you too. My problem was I created a web application with Visual Studio 2017 and which is by default hosted on IIS Express. And I changed to IIS then I got this error.

In event Log, I saw these 2 errors:

Windows API call SHGetKnownFolderPath returned error code: 5. Windows system error message is: Access is denied.

Cannot get a local application data path. Most probably a user profile is not loaded. If LocalDB is executed under IIS, make sure that profile loading is enabled for the current user.

For me the solution is:

First I have Attached the database with SQL Server Management Studio with login to (LocalDB)\MSSQLLocalDB which I found in my web.config connection string.

<connectionStrings>
 <add name="EFWeatherDBEntities" 
     connectionString="metadata=res://*/Models.EFModel.csdl|res://*/Models.EFModel.ssdl|res://*/Models.EFModel.msl;provider=System.Data.SqlClient;
     provider connection string=&quot;
     data source=(LocalDB)\MSSQLLocalDB;
     attachdbfilename=|DataDirectory|\WeatherDB.mdf;
     integrated security=True;connect timeout=30;
     MultipleActiveResultSets=True;App=EntityFramework&quot;"
     providerName="System.Data.EntityClient" />
</connectionStrings>

Then I realized in IIS when I try to click Test Settings in Basic Settings, I was getting Invalid Application Path error message. So I changed the credential as a different user(my user which is admin but I believe without admin permission it should work as well).

So basically:

click the website in IIS
click Basic Settings on right panel
click Connect As
and enter a user in Specific User section
then check if you still get path error with clicking Test Settings.
Then in Application Pool set Identity to LocalSystem

score 0 · Answer 5 · answered Aug 10 '20 at 12:56

0

Check your SQL Server connection string and enable SQL Server debugging in Project Properties

answered Aug 10 '20 at 12:56

Wandile Nxumalo

15
7

SQL won't connect after deploying

5 Answers5

Linked