SQL won't connect after deploying

问题:

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.

回答1:

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

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

回答2:

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.

回答3:

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.

回答4:

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