Access database from an application hosted on IIS

Question

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:

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

Changing identity to a custom account is clearly the common way to go, but you cannot merely do that on IIS side, and must also configure your database instance to accept that identity. I didn't see you made any attempt there yet. — Lex Li
– Lex Li, Commented Aug 19, 2022 at 0:29
If you set ApplicationPoolIdentity to LocalSystem. Your application can run under NT AUTHORITY\SYSTEM, which by default is an existing login for the database. — JennyDai
– JennyDai, Commented Aug 19, 2022 at 7:51
@JennyDai I've mentioned this in the question, but it doesn't work. Same error occurs. With the same message: "Login failed for user IIS APPPOOL\SYSTEM" even though it is there with max access. — Zigzagas
– Zigzagas, Commented Aug 19, 2022 at 8:59
@LexLi Creating and using my own windows acc worked. Hopefully I don't run into anything else. — Zigzagas
– Zigzagas, Commented Aug 19, 2022 at 12:35

Zigzagas · Accepted Answer · 2022-08-19 12:35:01Z

1

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:

Open cmd and write sqllocaldb i (this should show you all of your instances)
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)

answered Aug 19, 2022 at 12:35

Zigzagas

732 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

YurongDai Over a year ago

If you can mark your answer, it will lead more people in the right direction.

Zigzagas Over a year ago

Yeah. Forgot about it haha. Thx.

Collectives™ on Stack Overflow

Access database from an application hosted on IIS

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related