Major problems with Microsoft SQL Server and Permissions!
Posted on 2006-10-30
I am trying to set up SQL state for ASP.NET on a Windows 2003 server with MS SQL server 2005. I am getting this error:
Cannot open database "ASPState" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
1) I have created the ASPSstate database using the prefered Microsoft Methods.
2) This is my connection string in the web.config:
3) Then just to get this puppy working I ran this query to give the permission to NT AUTHORITY\NETWORK SERVICE, which is what i thought sql wanted.
sp_changedbowner 'NT AUTHORITY\NETWORK SERVICE',true
4) I then go this error "SELECT permission denied on object 'ASPStateTempApplications'" which I reseached and found that this is the fix (beause of SQL and SP3 apparently you have to jump through this hoop)
EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE
EXEC sp_dboption 'ASPState' , 'db chaining', 'true'
5) OK restarted SQL server and this didn't work at all. I also tried assigned permissions to SA but sql didnt like that and wouldn't let me login.
The goal here is to use SSI or integrated security so i dont have to have the SA password in the web.config. PLEASE HELP ME BEFORE I TURN A SHOTGUN ON MYSELF!!