Trying to GRANT ASP.NET access to sample Database from my book

Question

I am trying to set up this book to run database apps under IIS, and grant ASP.NET access to the database. The name of the book is:

Murach's ASP.NET 4 Web Programming with C# 2010 (Murach: Training & Reference)

The book came with two files to do this:

"grant_access_windows_7_vista.bat" which runs this file: "grant_access_windows_7_vista.sql"

Here are the files respectively:

@ECHO off

:: batch file for
:: Murach's ASP.NET 4 Web Programming with C# 2010
:: company: Mike Murach & Associates, Inc.
:: date:    March 16, 2011
:: 
:: Uses SQLCMD utility to run a SQL script that grants
:: ASP.NET access to the Halloween database.

ECHO Attempting to grant ASP.NET access to the Halloween database . . . 
sqlcmd -S localhost\ -E /i grant_access_windows_7_vista.sql
ECHO.
ECHO If no error message is shown, then access has been granted to the database.
ECHO.
PAUSE

Here is the .sql file:

use Halloween
go

sp_grantlogin 'IIS APPPOOL\ASP.NET v4.0'
go

sp_grantlogin 'IIS APPPOOL\ASP.NET v4.0'
go

sp_grantdbaccess 'IIS APPPOOL\ASP.NET v4.0'
go

sp_addrolemember 'db_owner', 'IIS APPPOOL\ASP.NET v4.0'
go

exit

I then run the Query including the word exit and here is what I get:

Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 49
Windows NT user or group 'IIS APPPOOL\ASP.NET v4.0' not found. Check the name again.
Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 49
Windows NT user or group 'IIS APPPOOL\ASP.NET v4.0' not found. Check the name again.
Msg 15401, Level 16, State 1, Line 1
Windows NT user or group 'IIS APPPOOL\ASP.NET v4.0' not found. Check the name again.
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'IIS APPPOOL\ASP.NET v4.0' does not exist in this database.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'exit'.

I am just a beginner and do not know much about SQL or IIS. Any help making this work would be greatly appriciated. I will try to do my part as well by searching GOOGLE, but if someone could explain why this is not working that would be fantasic.

Here is what I have tried... still with no luck:

IIS 7 is trying to use the IIS application pool identity to access your SQL database.

So you must first grant the IIS 7 apppool access to SQL server.

To do that, go in SQL Server Management Studio -> Server Instance -> Security -> Logins -> New Login

Login name will be "IIS APPPOOL\ASP.NET v4.0". This is because everytime you create an application pool in IIS7, it will create an identity which uses the same name as your app pool. In your case, you are using the default app pool which comes with asp.net 4.

Then on your database, go to Security -> Logins -> New login , give it a alias name, then select your user object you added in your previous step (IIS APPPOOL\ASP.NET v4.0) then give it db_owner (or whatever your needs are) schemas/role privileges.

All I got was this error when I tried the things above:

"IIS APPPOOL\ASP.NET v4.0" is not a valid name because it contains invalid characters. (Microsoft SQL Server, Error: 15006

Answer 1

I had this problem too. You have to create a login for "IIS APPPOOL\ASP.NET v4.0" first in SSMS->Security->Logins, not directly on the target database.

See this page I found:　http://blog.sqlauthority.com/2009/08/20/sql-server-fix-error-cannot-open-database-requested-by-the-login-the-login-failed-login-failed-for-user-nt-authoritynetwork-service/

Answer 2

I know this is an old thread but I had the same issue today with the latest edition of the same book.

Book: Murach ASP.NET 4.5 web programming with C# 2012

Issue: During set up of book files the grant_access script returns this error:

Windows NT user or group 'IIS APPPOOL\ASP.NET v4.5' not found. Check the name again.

My setup: laptop is Windows 8.1 running IIS 8.5 and VS2013 Premium.

To solve this problem I updated the grant_access script.

Change all occurances of: 'IIS APPPOOL\ASP.NET v4.5' To: 'IIS APPPOOL\.NET v4.5'

This is the correct spelling in the App Pool, found in the IIS manager. (You should verify the spelling on your machine if your versions are different from mine)

Now run the script again, it should work.

Prerequisite: I had to register asp.net on my particular set up; used the below link.

ASP.NET 4.5 has not been registered on the Web server

dism.exe /Online /Enable-Feature /all /FeatureName:IIS-ASPNET45

Answer 3

Just change the sql script to eliminate the IIS APPPOOL\ASP.NET v4.0 grant commands and change the connnection string to an existing user. Don't beat your head against the wall.