Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
COURSE of the MONTH
10 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
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:
<sessionState
mode="SQLServer"
sqlConnectionString="Serve
allowCustomSqlDatabase="tr
cookieless="false"
/>
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.
use aspstate
sp_changedbowner 'NT AUTHORITY\NETWORK SERVICE',true
4) I then go this error "SELECT permission denied on object 'ASPStateTempApplications'
use master
go
EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE
go
use master
go
EXEC sp_dboption 'ASPState' , 'db chaining', 'true'
go
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!!
- jenni
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:
<sessionState
mode="SQLServer"
sqlConnectionString="Serve
allowCustomSqlDatabase="tr
cookieless="false"
/>
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.
use aspstate
sp_changedbowner 'NT AUTHORITY\NETWORK SERVICE',true
4) I then go this error "SELECT permission denied on object 'ASPStateTempApplications'
use master
go
EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE
go
use master
go
EXEC sp_dboption 'ASPState' , 'db chaining', 'true'
go
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!!
- jenni
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
8
4
- +4
19 Comments
HEre is a little more detailed error message:
SELECT permission denied on object 'ASPStateTempApplications'
SELECT permission denied on object 'ASPStateTempApplications'
INSERT permission denied on object 'ASPStateTempApplications'
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlE
SELECT permission denied on object 'ASPStateTempApplications'
INSERT permission denied on object 'ASPStateTempApplications'
Thank you thank you thank you!
SELECT permission denied on object 'ASPStateTempApplications'
SELECT permission denied on object 'ASPStateTempApplications'
INSERT permission denied on object 'ASPStateTempApplications'
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlE
SELECT permission denied on object 'ASPStateTempApplications'
INSERT permission denied on object 'ASPStateTempApplications'
Thank you thank you thank you!
go to start-> programs-> Microsoft SQL 2005 -> SQL Server Management Studio. You should be able to connect to your database there. Check the security tab for your login, and check the databases tab and your databaes (ASPState) to make sure that user is listed as having permission, and right mouse to see the permissions granted on each table. tho DBO should give you everything you need.
I am not an ASP guy - not sure what else could cause the problem
I am not an ASP guy - not sure what else could cause the problem
Thank you so much for your post!!
OK so I log into SQL 2005 Managment Studio. Under the SERVERNAME / SECURITY / LOGINS tree node I see the account SA. I double clikc on SA and then choose the User Mapping tab to see some databases that have been checked. I put a check next to ASPState and click ok and I get the message:
Create failed for User 'sa'. (Microsoft.SqlServer.Smo)
Cannot use the special principal 'sa'. (Microsoft SQL Server, Error: 15405)
I look at the NETWORK SERVICE user, which is what ASPState is complaining about, and it is already mapped to that ASPState Database. (ASPState has DBO as the User on this page)
Next, I go to the ASPState database then go to security/users. I see DBO in this node. THere are two sections; Owned Schemas and Database Role Memebeship.
For Owned Scheam everythign is grey checked (you konw when the box is gray but there is acheck.) this includes a lot of stuff like db_denydataReader etc which I can't uncheck...
Under Database rol memebership the db_owner is checked.
The ASPState database has no tables.
THANK YOU SO MUCH in advance for your help! this problem is really killing me....
Thakn you thank you thank you!
-- Jenni
OK so I log into SQL 2005 Managment Studio. Under the SERVERNAME / SECURITY / LOGINS tree node I see the account SA. I double clikc on SA and then choose the User Mapping tab to see some databases that have been checked. I put a check next to ASPState and click ok and I get the message:
Create failed for User 'sa'. (Microsoft.SqlServer.Smo)
Cannot use the special principal 'sa'. (Microsoft SQL Server, Error: 15405)
I look at the NETWORK SERVICE user, which is what ASPState is complaining about, and it is already mapped to that ASPState Database. (ASPState has DBO as the User on this page)
Next, I go to the ASPState database then go to security/users. I see DBO in this node. THere are two sections; Owned Schemas and Database Role Memebeship.
For Owned Scheam everythign is grey checked (you konw when the box is gray but there is acheck.) this includes a lot of stuff like db_denydataReader etc which I can't uncheck...
Under Database rol memebership the db_owner is checked.
The ASPState database has no tables.
THANK YOU SO MUCH in advance for your help! this problem is really killing me....
Thakn you thank you thank you!
-- Jenni
Jenni I don't have the asp expertise to help. Let's do this. I will post another question as a pointer to this one. Sometimes when questions get old and experts see another expert helping, they go on to help other people and a question gets stale before it gets answered. In this case, it may be that not a lot of SQL guys have ASP as well. Since your question did not emphasize that, I will repost with an emphasis on that aspect of the issue.
Let's see if we can get you the help you need.
Let's see if we can get you the help you need.
Active today
>Cannot open database "ASPState" requested by the login. The login failed.
>Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
this means that the login can connect to the sql server, but not to the database.
reason: the login has not been mapped to a user in the database, ie no permissions are granted to the login in that database, hence, the error you see.
solution:
open the properties of the login, and go to the tab to grant access to the database ASPState.
>Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
this means that the login can connect to the sql server, but not to the database.
reason: the login has not been mapped to a user in the database, ie no permissions are granted to the login in that database, hence, the error you see.
solution:
open the properties of the login, and go to the tab to grant access to the database ASPState.
By the way, you will see that I asked the question by simply posting your error message "Cannot open database "ASPState" requested by the login"
Your question "major problem with permissions" suggests that the problem has to do with permissions. It is best not to assume the nature of a problem. Just post the error you are getting, since an expert who recognizes that message can help you immediately, whereas "major problem with permissions" does not necessarily help an expert determine whether a question is within their expertise.
Your question "major problem with permissions" suggests that the problem has to do with permissions. It is best not to assume the nature of a problem. Just post the error you are getting, since an expert who recognizes that message can help you immediately, whereas "major problem with permissions" does not necessarily help an expert determine whether a question is within their expertise.
hi JenniQ,
First check what is the user in IIS security tab.
Second check that whether or not that user have access in sql server on the specific database
Regards
Imran
First check what is the user in IIS security tab.
Second check that whether or not that user have access in sql server on the specific database
Regards
Imran
JenniQ,
Imran has posted some info for you... If there is no additional post by Monday 11/20 I will be closing my pointer questions. Please either close the question and ask for a refund (and maybe repost in the asp area) or follow up with Imran.
Thanks
Imran has posted some info for you... If there is no additional post by Monday 11/20 I will be closing my pointer questions. Please either close the question and ask for a refund (and maybe repost in the asp area) or follow up with Imran.
Thanks
Hey Guys,
Thanks for your response. Please let me look this question over tonight and see if I can solve it based off these answers, then I will decied if I wanna close it or open a new Q.
Thanks guys!
-- JenniQ
Thanks for your response. Please let me look this question over tonight and see if I can solve it based off these answers, then I will decied if I wanna close it or open a new Q.
Thanks guys!
-- JenniQ
Aaron. Thank you for your help posting a pointer to this question! I will take your advice when posting in the future.
Imran -- the ASPSTATE database has no IIS website associated with it.
I followed tried giving the login user full dbo permissions and am recieve this error now:
SELECT permission denied on object 'ASPStateTempApplications'
SELECT permission denied on object 'ASPStateTempApplications'
INSERT permission denied on object 'ASPStateTempApplications'
I'm just going to try to uninstalling ASPSTATE from this server and re installing. I have a feeling something is wrong wit the installation.
Thank you for your help again!!
Imran -- the ASPSTATE database has no IIS website associated with it.
I followed tried giving the login user full dbo permissions and am recieve this error now:
SELECT permission denied on object 'ASPStateTempApplications'
SELECT permission denied on object 'ASPStateTempApplications'
INSERT permission denied on object 'ASPStateTempApplications'
I'm just going to try to uninstalling ASPSTATE from this server and re installing. I have a feeling something is wrong wit the installation.
Thank you for your help again!!
There is no "Accepted Solution", but I saw a great response by Angelll (ranked Genius) that helped me.
I have copied it here:
>Cannot open database "ASPState" requested by the login. The login failed.
>Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
this means that the login can connect to the sql server, but not to the database.
reason: the login has not been mapped to a user in the database, ie no permissions are granted to the login in that database, hence, the error you see.
solution:
open the properties of the login, and go to the tab to grant access to the database ASPState.
I have copied it here:
>Cannot open database "ASPState" requested by the login. The login failed.
>Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
this means that the login can connect to the sql server, but not to the database.
reason: the login has not been mapped to a user in the database, ie no permissions are granted to the login in that database, hence, the error you see.
solution:
open the properties of the login, and go to the tab to grant access to the database ASPState.
Featured Post
In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have.
In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB.
To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month10 days, 2 hours left to enroll
762 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.