* !! Change MYCOMPUTER below to the appropriate name of your computer !!
* Open a command prompt
* Type: C:
* Type: cd "C:\Program Files\Microsoft SQL Server\80\Tools\Binn" (or the exact location of the file "osql.exe" if different)
* Copy/paste the following commands
osql -E -S localhost -Q "sp_grantlogin 'MYCOMPUTER\ASPNET'"
osql -E -S localhost -d Orders -Q "sp_grantdbaccess 'MYCOMPUTER\ASPNET'"
osql -E -S localhost -d Orders -Q "sp_addrolemember 'db_owner', 'MYCOMPUTER\ASPNET'"Thanks for the "straight" approach! A side note to those of you who used named instances (like I did), I needed to change 'localhost' to 'myCompName\namedInstance'
Do you think there could be any reprocussion by having the ASPNET user as the db_owner?|||I have had some problems getting the ASPNET account to connect to SQL 2000, see following for details:
http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=8328
I have given the ASPNET account access via EM, but it didn't help any.
So I decided to try your approach and some really strange things happened which leads me to believe my SQL 2000 is a total mess. Hopefully I am wrong.
When Trying:
osql -E -S localhost -Q "sp_grantlogin 'MYCOMPUTER\ASPNET'"
I get:
Granted login access to 'MYCOMPUTER\ASPNET'.
Seems reasonable !
When Trying:
osql -E -S localhost -d Orders -Q "sp_grantdbaccess 'MYCOMPUTER\ASPNET'"
I get:
Msg 15023, Level 16, State 1, Server MYCOMPUTER, Procedure sp_grantdbaccess, Line 127
User or role 'MYCOMPUTER\ASPNET' already exists in the current database.
Seems reasonable since I allready did this from within EM
But then when trying:
osql -E -S localhost -d Orders -Q "sp_addrolemember 'db_owner', 'MYCOMPUTER\ASPNET'"
I get:
Msg 15410, Level 11, State 1, Server MYCOMPUTER, Procedure sp_addrolemember, Line 66
User or role 'MYCOMPUTER\ASPNET' does not exist in this database.
WHAT IS UP WITH THAT ?|||Here is what I know (I will illustrate with my real world example).
I have an asp.net web app deployed on the server with .net installed to run services. As you found out, when you install .net, a LOCAL user accout is created, named ASPNET.
Anyways, my web app running on .net machine A, needs to do a sql connection to machine B, running sql server 2000.
I could NOT find a sql connection string that would ALLOW this local machine user ASPNET, to connect "trusted" to the sql server on machine B.
Fix: On machine B (sql server) I created a user account ASPNET and assigned it password. I then gave this new user access to the DB & Tables I required, no others! (read, write, and ownership I recall).
Now, In my connection string I something like this:
SqlConnection sqlConn = new SqlConnection("Data Source=MACHINE_B;Initial Catalog=MY_DB;User ID=ASPNET;Pwd=PASSWORD;Connect Timeout=4800;");
So, my connection will use sql authentication, not machine authentiaction. Its the only solution I have found, I hope this is what the hell your talkin.|||Well I have tried both machine and sql authentication without luck. Obviously there is something messed up since the user is not recognized|||I finally solved my problem. It ended up being a file permission problem (NTFS), but not in a place I would have suspected. I discovered the problem file by using the filemon program from sysinternals:
http://www.sysinternals.com/ntw2k/source/filemon.shtml
The program showed me which files had access denied. I can highly recommend this program if you encounter permission issues.
The problem file was: c:\winnt\system32\com\comadmin.dll
After giving the ASPNET account read access to this file everything worked.
I am assuming that I had this problem because I have used various tools to lock down my deployed server (file permission wise etc.)|||E if I use FAT32|||Had the same problem when installing MSDE on a 2k-server with domain.
I think this is the first MSDE that installs on servers? The problem seems
to be that MSDE creates a LOCAL-USER account for 'MYCOMPUTER\ASPNET'.
This user is not available when loged in to the servers' DOMAIN? At least
not if LOCALSERVER is also DOMAIN-server (no LOCAL-users alowed)!
The user must be 'MYDOMAIN\ASPNET'
Fix DOMAIN and recompile applicaton? No problem on 2kPRO on DOMAIN...
Strange that MS don't have a distribution for SQL2k! Or is there one?
Per, Oslo, Norway.|||If you install MSDE from the Web Matrix download, it is installed under the LocalSystem account using windows integrated security by default.
Applications connecting to this msde instance will need to be running under a domain user account with administrative privileges. You can try running a domain user account with lessor privileges, but will then run into file access issues which will result in a connection failure.
Alternatively, you can install msde using sql security so you can connect that way. Use the /? setup flag to see the name=value pair for that. This will actually install in mixed mode, so either sql security or integrated security will work. be aware this installs with null sa password, so be sure and create a secure sa password before starting the sql server service.
With a named instance, you must use the machine name and instance name. so if your machine name is foo, and instance name is bar, you use foo\bar
Michael Edwards
MSDE Program Manager
Microsoft Corp
This posting is provided "AS IS" with no warranties, and confers no rights.|||> osql -E -S localhost -Q "sp_grantlogin 'MYCOMPUTER\ASPNET'"
> osql -E -S localhost -d Orders -Q "sp_grantdbaccess 'MYCOMPUTER\ASPNET'"
> osql -E -S localhost -d Orders -Q "sp_addrolemember 'db_owner', 'MYCOMPUTER\ASPNET'"
Thank you very much. I've been looking for this information for days, and these commands were exactly what I needed.|||Thank you, thank you, thank you. I can quit tearing out my hair and get back to work now.
This worked perfectly.|||How do I correct the problem when the I have the following messages:
"The RPC Server is to busy to complete this operation"
Error 1723: The RPC Server is to busy
This Snap-in display maybe in consistant with removable Storage Service. If the problem persist please restart the snap-in
The problem have disallowed access to control panel and and my connection the internet cannot be accessed.
How do I fix the problem or step by step remove ASPNEt and reinstall?
Regards
Fred|||Thanks, this worked for me as well. Only thing I had to change was localhost to hostname\INSTANCENAME.
Thanks again.|||Hate to tell you guys this, but it really isn't recommended to give the ASPNET account access to the database, especially as dbowner. This raises all kinds of security issues.
Use mixed mode, you'll make your life a lot easier. Typically in any type of large Web app, your SQL Server is on a completely different server. Windows Integrated Security won't work in this scenerio anyway because IIS does not delegate user credentials to a remote server. In other words, even if the account requesting the page from IIS has permission to your SQL Server, IIS isn't going to pass the credentials on in a way that SQL Server will allow the user to connect. So you are back at the same problem unless you run your components inside COM+ services or do other more advanced security within your components.
I'm a little frustrated that people keep asking the same questions over and over when there are plenty of good answers here in the forums if you take the time to look.
Other than using mixed mode, the other thing to remember is that MSDE is installed in Windows Integrated Security mode by default and you either have to use the switch on the install as Michael points out or connect using Enterprise Manager to MSDE and change the security mode, as Michael, I and others have pointed out repeatedly. Please people, READMEs are there for a reason. Okay, I'll get off my soapbox now.
Curtis|||Thanks to Michael and skills0!!
I've managed to figure out which property needed to be changed at setup to enable mixed mode and found a really easy way to set it up (without using Enterprise Manager).
Just add "SECURITYMODE=SQL" to your setup.ini file in the msde setup folder and then run setup.
Now, I just have to figure out how to change sa's password ;)
No comments:
Post a Comment