2

I have an asp.net web app running on IIS, and everything is fine, until I try to access the database I get this error: enter image description here

I have read the 2 parts of the Using LocalDB with Full IIS: 1 Part | 2 Part

I followed the instructions and still get the error, is there a way to check that I have followed the steps correctly?

Is there anything else I can check for that may be causing this error?

Note: I tryed this with asp.net and asp.net core applications, the core one doesn't give any description at all so I am testing this with asp.net. The database that I am using is the one that visual studio provides. If you would like me to provide any further information ill be happy to, I have been stuck with this issue for a while now and can't seem to resolve it.

Anton Toshik
  • 2,621
  • 2
  • 19
  • 42
  • Can you access your localdb through SSMS? Is tcp/ip enabled in SQL Server configuration manager? – gofr1 Oct 15 '16 at 12:35
  • Please check this criteria [sqlmusings](http://www.sqlmusings.com/2009/03/11/resolving-a-network-related-or-instance-specific-error-occurred-while-establishing-a-connection-to-sql-server/) – Kuldip Rana Oct 15 '16 at 13:19
  • @gofr1 I can Connect to the database using SSMS, however the configuration manager is giving me and error 'invalid namespace' – Anton Toshik Oct 15 '16 at 15:13
  • Please, try solution from [here](https://www.mssqltips.com/sqlservertip/2382/sql-server-configuration-manager-cannot-connect-to-wmi-provider/) – gofr1 Oct 15 '16 at 15:35
  • Alright so I arrived at a 'ok' solution for asp.net is it didn't publish the .mdf file and didn't have full permission (read/write) to the wwwroot folder. Of-course there is still no connection to the actual database. – Anton Toshik Oct 15 '16 at 16:35
  • @gofr1 Thank you, in SQL Native Client -> Client Protocols, TCI/IP is Enabled – Anton Toshik Oct 16 '16 at 14:31
  • @AntonToshik that is nice! Can you also check in Config Manager if SQL Server Browser and SQL Server Agent are working? – gofr1 Oct 16 '16 at 14:37
  • @gofr1 It doesn't display any services at all in the configuration manager – Anton Toshik Oct 16 '16 at 15:10
  • Weird... Are you sure there is an SQL Server installed and running on your machine? – gofr1 Oct 16 '16 at 15:16
  • @gofr1 Isn't some kind of sql server express get installed when Visual Studio is installed? – Anton Toshik Oct 16 '16 at 15:36
  • As far as I remember there was some option in VS setup that allow to install SQL Server, but I am not sure about it. The last time I installed VS 2015 I already got SQL server installed on my machine. – gofr1 Oct 16 '16 at 15:43
  • Based on msdn VS install SQL Server Data Tools component by default, no info about full server. – gofr1 Oct 16 '16 at 15:47
  • @gofr1 I restarted the machine and I'm getting "Login failed for user 'IIS APPPOOL\ASP.NET v4.0'." error. I can see the account in SSMS security and it has read and write properties. – Anton Toshik Oct 16 '16 at 16:21
  • @AntonToshik here I can advice to take a look at that [article](https://blogs.msdn.microsoft.com/brian_swan/2010/02/10/sql-server-driver-for-php-understanding-windows-authentication/) to understand how to login to SQL server from IIS properly. What user accounts are used based on IIS auth settings. – gofr1 Oct 16 '16 at 16:50

1 Answers1

4

Turns it's not too complicated, but my inexperience with both IIS and Databases made it confusing. Hopefully this can help someone in the future. There might be some minor steps/tweaks I missed out but that's because the whole solution took a while to setup.

  1. Make sure you have SQL Server actually installed. You can download the 2008 version which I used here. Can be a pain to setup and I actually messed up when installing, here is the vid I watched (I didn't install: Database Engine Services).
  2. Next you want to make sure the IIS identity (IIS APPPOOL\ASP.NET v4.0 in my case) has permission to connect to the Database server. Check the answer here.
  3. Once it has connected to the database server, it need's to be able to connect to a database on the server (I didn't know that a database and a database server are 2 different things). You need to do something simular to Step 2, in SSMS expand the databases folder, and select the database you which to grant access too, there is a security folder in there as well and you want to add the Identity to that folder.

Other useful points:

  • If your sql configuration manager isn't opening please tell it to, thank you gofr1. This is a very useful tool for checking up on your database connection (as it turns out)
  • Make sure you setup your connectionString to point to the sql server. Make sure it doesn't connect to the mdf file with AttachDbFilename=|DataDirectory|\your_database_name_here.mdf located in the connectionString.
  • For asp.net Core read the documentation it is god, as the errors are non-existent once the app is deployed on IIS it will help in dark times. Always check publishOptions.

To further help you understand how the whole process works, thank you to gofr1 read here. Didn't solve the issue but worth a read.

Might include more stuff in here later if I remember I missed anything out.

Community
  • 1
  • 1
Anton Toshik
  • 2,621
  • 2
  • 19
  • 42
  • I having similar issue, but my SQL Configuration Manager is up, both Visual Studio and SSMS can connect to database and I do not using .mdf in connection string. The IIS Application Pools identity is correct and had been set to "Load User Profile" = True. “system.applicationHost/applicationPools” -> applicationPoolDefaults -> processModel -> setProfileEnvironment had been set to True also. Yet, the issue still persists. – Toshihiko Oct 16 '19 at 06:46
  • Then I change the IIS Application Pools Identity to Local System, then change back to the Windows User, then recycle it, it works. – Toshihiko Oct 16 '19 at 06:57