Search code examples
c#asp.netsql-serveriis

asp.net app on IIS can't connect to localdb

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.

Solution

  • 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.