Search code examples
iisconnectionssms.net-6.0localdb

Access database from an application hosted on IIS

The error I get is:
Cannot open database "DBNAME" requested by the login. The login failed.\r\nLogin failed for user 'IIS APPPOOL\DefaultAppPool'

Initial connection string is as follows, further in the question I'll only be using only a part of it.

Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=DBNAME;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;

I believe I have tried everything:

  1. My application that is hosted on IIS is using a 'ApplicationPoolIdentity' as identity. I have tried changing the identity to other, but to no avail. Only to be greeted by a similar error, but with a changed user. (Havent tried using Custom account)
  2. Tried sharing my localDB instance with the following command sqllocaldb share mssqllocaldb IIS_DB. Restarting the instance, changing my connection string data source to the following (localdb)\\.\\IIS_DB.
  3. Tried plainly using SQLEXPRESS instance with the following data source .\\SQLEXPRESS.

I have added IIS APPPOOL\\DefaultAppPool user to all possible places, given full access to everything, allowed Everyone to access the folders where the database is located.

One thing I haven't tried is to try and repeat everything from scratch, since I might've messed something up. All help is appreciated.

IIS: version 10.0.19041.1
SSMS: version 18.12.1
Backend: .NET 6 minimal API + DAPPER
Solution

  • Thank you @Lex Li I used to login to my PC via the microsoft acc and thus I never did use a windows acc. So whoever stumbles, keep in mind that it's easy to create the acc, windows helps you with that.

    Other than that, what I was missing was exactly this. Use your own acc and not a DefaultAppPool (even though it should still work.).

    Dont forget to check that your SQL instance is running:

    1. Open cmd and write sqllocaldb i (this should show you all of your instances)
    2. to check whether an instance is running. (It is started when you open your local SSMS and connect to your instance or you run it through cmd): Write sqllocaldb i instancename (should see it as running) - (if you want to restart it, write sqllocaldb stop instancename and then sqllocaldb start instancename.

    Other than that, feel free to ask for help in the comments, I might remember this stuff even after a year or two. (Literally spent 7 days on this issue)