How to fix ASPNET "Login failed" problem with MSDE

I've searched all over the web trying to find a goddamn fix for this problem and no one has it right or they have the commands misspelled (!!!), so in spite of all those idiots, in spite of the makers of the Web Matrix guided tour for not pointing out this problem, and in spite of Microsoft who has not posted a solution themselves, here it goes:

* !! 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 ;)

How to find the IP of someone repeatedly trying to login as SA

I'm running a Windows 2003 Server, with MS SQL Server 2000 which is using
mixed mode security. The SQL server is being used for development and
testing purposes.
The server needs to be exposed to the internet as it is also accessed off
site
When checking the windows logs, the application log showed repeated failed
login attempts to the sa SQL account and new entries were being created at a
rate for a few a minuet.
How can I find out the IP address of where these login attempts were coming
from?
and also how can I get the SQL server to ignore requests for this address or
maybe ip range?
To temporarily stop this I just disabled the TCP/IP network protocol for the
SQL server.
Thanks for any help,
GrantGreat questions:
How can I find out the IP address of where these login attempts were coming
from?
Answer: Best ways are either running Microsoft Network Monitor on the
server & make a capture of the traffic.
Or, netstat -an to capture the sessions connected via IP. To send a login
the client first needs to make
a tcp session using the 3 way handshake.
and also how can I get the SQL server to ignore requests for this address or
maybe ip range?
Answer: By default there is no way to do this from SQL. You'd need to
configure a firewall in front of SQL to allow connection requests from only
one machine.
I've requested that we allow auding for failed login request & log the IP
address for the next release of SQL.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin McDonnell [MSFT] wrote:

> and also how can I get the SQL server to ignore requests for this address
or
> maybe ip range?
> Answer: By default there is no way to do this from SQL. You'd need to
> configure a firewall in front of SQL to allow connection requests from onl
y
> one machine.
If you are familiar with IPSec you could set up a custom IP Sec policy
to only allow connections from machines that you trust. You don't want
to specify who you deny, but instead specify who you trust.

> I've requested that we allow auding for failed login request & log the IP
> address for the next release of SQL.
>
It would be nice if the auditing was built to use windows auditing,
however I'm sure that this is only available if using windows
authentication, and in this case it was sql auth that was tried.
Aaron Weiker
http://blogs.sqladvice.com/aweiker
http://aaronweiker.com/sql

How to find particular login has access to which databases?

Is there a way to get the list of database to which a particular login name has access to and also how find what access that particular login has in each database?
Appreciate your comments and suggestionsGive sp_helplogins a try|||Thanks! for the reply.. I had figured that out.. sp_helplogin is helpful.

How to find group user login Name

Our system administrator set up an NT server group in order to allow
our users to login to our application via https to our sql server.
The group appears as a User in SQL Server when you look at it in
Enterprise Manager. That said, I can not see the users associated
with the group from Enterprise Manager, but know they can login to the
database.
The problem is this. When we login via the web we get access to the
database without problem, but when you look at the current_user what
you see is the login Name the user entered and NOT the name of the
group/User. That is to say, I can see a UserID which is not listed as
a User in SQL Server and can't see the name of the group, which is
listed as a user in SQL Server.
I need to know who's logging in order to direct them to the
appropriate web page via their role. Before the admin set up the
group, I was using sp_helpuser to get the role, but then again I had
the userID to do this.
The question I have now, is there any way to see what the
group/user is who logged in i.e. the goup listed as the User in
Enterprise Manager? Otherwise I have to build a table of userIDs and
their group/User name, which seems to defeat the purpose of having he
server authenticate users.

Thanks,
Tom"Tom Loach" <tcl4p@.virginia.edu> wrote in message
news:o7vttvktkbdbib39pqklga3f8s9lgdsksc@.4ax.com...
> Our system administrator set up an NT server group in order to allow
> our users to login to our application via https to our sql server.
> The group appears as a User in SQL Server when you look at it in
> Enterprise Manager. That said, I can not see the users associated
> with the group from Enterprise Manager, but know they can login to the
> database.
> The problem is this. When we login via the web we get access to the
> database without problem, but when you look at the current_user what
> you see is the login Name the user entered and NOT the name of the
> group/User. That is to say, I can see a UserID which is not listed as
> a User in SQL Server and can't see the name of the group, which is
> listed as a user in SQL Server.
> I need to know who's logging in order to direct them to the
> appropriate web page via their role. Before the admin set up the
> group, I was using sp_helpuser to get the role, but then again I had
> the userID to do this.
> The question I have now, is there any way to see what the
> group/user is who logged in i.e. the goup listed as the User in
> Enterprise Manager? Otherwise I have to build a table of userIDs and
> their group/User name, which seems to defeat the purpose of having he
> server authenticate users.
> Thanks,
> Tom

I'm not sure I understand your setup completely, but if you use CURRENT_USER
you will see the user name in the current database, not the login name. If
you use SYSTEM_USER instead, you should see the user's login - does this
give the result you expect?

If not, then perhaps you could clarify the sequence of events used to set up
the group, espeically the difference between a server login and a database
user - this is what's not entirely clear from your post above. If you can
provide the actual SQL commands used to set up server access and permissions
for the Windows group, that would be the least ambiguous description,
although of course you may well have done it through Enterprise Manager
instead.

Simon|||Simon,
thanks for your suggestion, but alas it did not work. Iam sorrry
I was not able to make the situation more clear, but perhaps another
try.
We have a group account set up, which if you look at in Enterprise
manager appears as a User i.e. a_Group. Obviously, since this is a
group account it is comprised of multiple users who need access to SQL
Server. The problem we're having is, if you login to the system and
if I run a stored procedure that returns the Current_User, what I get
is not the name of the User i.e. a_Group that appears in Enterprise
Manager, but the UserID i.e. h3tcl that was used to login to the
Windows server.
When I tried to exec sp_helpuser using Current_User I get an error
since SQL Server does not know who the user is (it sees h3tcl). If I
exec sp_helpuser and enter the user name (a_Group) found in
Enterprise manager sp_helpuser works fine and returns the appropriate
data from SQL Server.
So I'm back to the problem is how and where do I look for the group
name? If you have any other ideas I'm more than open. Thanks again
for your input.

Regards,
Tom

On Tue, 16 Dec 2003 16:44:21 +0100, "Simon Hayes" <sql@.hayes.ch>
wrote:

>"Tom Loach" <tcl4p@.virginia.edu> wrote in message
>news:o7vttvktkbdbib39pqklga3f8s9lgdsksc@.4ax.com...
>> Our system administrator set up an NT server group in order to allow
>> our users to login to our application via https to our sql server.
>> The group appears as a User in SQL Server when you look at it in
>> Enterprise Manager. That said, I can not see the users associated
>> with the group from Enterprise Manager, but know they can login to the
>> database.
>> The problem is this. When we login via the web we get access to the
>> database without problem, but when you look at the current_user what
>> you see is the login Name the user entered and NOT the name of the
>> group/User. That is to say, I can see a UserID which is not listed as
>> a User in SQL Server and can't see the name of the group, which is
>> listed as a user in SQL Server.
>> I need to know who's logging in order to direct them to the
>> appropriate web page via their role. Before the admin set up the
>> group, I was using sp_helpuser to get the role, but then again I had
>> the userID to do this.
>> The question I have now, is there any way to see what the
>> group/user is who logged in i.e. the goup listed as the User in
>> Enterprise Manager? Otherwise I have to build a table of userIDs and
>> their group/User name, which seems to defeat the purpose of having he
>> server authenticate users.
>>
>> Thanks,
>> Tom
>I'm not sure I understand your setup completely, but if you use CURRENT_USER
>you will see the user name in the current database, not the login name. If
>you use SYSTEM_USER instead, you should see the user's login - does this
>give the result you expect?
>If not, then perhaps you could clarify the sequence of events used to set up
>the group, espeically the difference between a server login and a database
>user - this is what's not entirely clear from your post above. If you can
>provide the actual SQL commands used to set up server access and permissions
>for the Windows group, that would be the least ambiguous description,
>although of course you may well have done it through Enterprise Manager
>instead.
>Simon