1

I get the following error when trying to view my website.

Cannot open database requested by the login. The login failed. Login failed for user 'NT AUTHORITY\SYSTEM'.

I have tried just about everything I can think of, including suggestions from similar questions on SO. I can't find anything wrong with my connection string, and it was working earlier. I had to delete the database to start fresh, and as far as I can gather that caused the problem -- but I thought the DB is created automatically? It was the first time I ran the website.

At one point I had fixed this, but I don't remember what I did...

The user in question has sysadmin rights.

Stack trace: https://paste.ee/p/cJfj9

Connection string: "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\aspnet-EDB-20170516.mdf;Initial Catalog=aspnet-EDB-20170516;Integrated Security=True;User Instance=True"

Edit: The suggested duplicate appears to differ from my question in a significant way. They are talking about remoting in, I have IIS and SQL Server running on the same machine I am attempting to access from.

Uwe Keim
  • 36,867
  • 50
  • 163
  • 268
Sinjai
  • 800
  • 7
  • 30
  • 2
    Possible duplicate of [Web App getting Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'](http://stackoverflow.com/questions/10957443/web-app-getting-login-failed-for-user-nt-authority-anonymous-logon) – Brian Ogden May 16 '17 at 21:46

1 Answers1

2

As the error is trying to tell you, you need to add your Windows user to the database.

Side note: You really shouldn't run anything as NT AUTHORITY\SYSTEM; you should set up IIS to use a separate, less-privileged user.

SLaks
  • 800,742
  • 167
  • 1,811
  • 1,896
  • How do you get that from that error? I also don't know how to do that without storing the password as plaintext in the connection string. Isn't that trouble? Regardless, SSMS shows that NT AUTHORITY\SYSTEM just has access to the public server role, although I added sysadmin in an attempt to fix the error. Is my assertion that the database is automatically created if not found correct? – Sinjai May 16 '17 at 21:51
  • `Login failed` means that the user hasn't been added to the database. Adding to the SQL Server is not enough. This has nothing to do with storing passwords. – SLaks May 16 '17 at 21:59
  • @Sinjai your asp.net application is hosted under IIS and operates under user `NT AUTHORITY\SYSTEM` and that user doesn't have access to the database. @SLaks answer is correct. Either give permission to that user or another user of your choosing. Personally, I use `ApplicationPoolIdentity`. – maxbeaudoin May 16 '17 at 21:59
  • Sorry, this is all new to me. How do I give a user permission for the database? Isn't the connection string what determines who's connecting to the database? – Sinjai May 17 '17 at 14:03
  • @Sinjai: `Integrated Security=True` means it connects as your Windows user. However, you still need to grant that user access to log in to the database using the `ADD LOGIN` command or the SSMS UI. – SLaks May 17 '17 at 14:35
  • Why doesn't the error say my user is logging in, rather than `NT AUTHORITY\SYSTEM`? I'm sure these are stupid questions, please bear with me. I really appreciate the help. How do I add the login with SSMS? I'm not sure how to "open" the database. Do I go to Database -> Attach? I suppose I'm not understanding how the whole database-engine / database structure works. – Sinjai May 17 '17 at 14:54
  • @Sinjai Your app is apparently running as `NT AUTHORITY\SYSTEM`. You can (and should) change this in IIS settings. I haven't worked with detached database files in SSMS, so I'm not sure. – SLaks May 17 '17 at 15:15
  • In basic settings, I changed from pass-through authentication to my specific user. Is this correct, or am I supposed to be changing things elsewhere? – Sinjai May 17 '17 at 18:15
  • I can't for the life of me figure out how to grant permissions. I now get the identical error with IIS APPPOOL\MySite as the user. I tried attaching the DB to the SQL Server and adding a login via the security folder, but that doesn't seem to have affected anything. – Sinjai May 17 '17 at 20:08
  • I'm going to tentatively say I've had success... I restarted my computer and it started working for no apparent reason, although the roles that I assigned to the SQL Server login don't seem to have activated (if I go to login -> user mapping, it just says public). – Sinjai May 17 '17 at 20:30