login failed for user

[Middletree]

I'm pulling my hair out.

I am trying to set up a website on my home PC for development there. I have
been using my laptop, but want to move it to the desktop.
Both machines are running Windows XP Pro SP2.
Both have SQL Server 2005 standard.

This site works fine in Production, by the way, which is hosted by company
that I am paying monthly.

All I am doing is trying to move the development version of this site from
one machine to another, but I am getting this error:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80004005)
Login failed for user 'asp'. The user is not associated with a trusted SQL
Server connection.
/shape/includes/shapedbinc.asp, line 33


Here is the connection string, which is kept in an include file:
strDBConnection ="Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=asp;Password=xxx;Initial Catalog=SHAPE;Data Source=LUSR0594"

set conn = CreateObject("ADODB.Connection")

conn.open strDBConnection

====================================

Of course, I changed the password for this post. The machine name, which is
also the SQL Server instance name, got changed from LUSR0594 (on the machine
that works) to "HOME" for the desktop. I am sure that this information is
correct.

I have researched the heck out of this, and spent a lot of time at
aspfaq.com, particularly
http://databases.aspfaq.com/database/what-should-my-connection-string-look-like.html
and all suggestions offered are not resolving my particular issue. I have
also been to every link on that page, including KB articles.

I have the computers side by side, comparing settings in the database
itself, and can only find one difference:

In SQL Server Management Studio, I go to the Security navigation tab on the
left. It's the one outside of the list of databases. I expand Security to
Logins, and expand that. I right-click "asp" and go to Properties. On the
left, I highlight User Mapping. On the machine that works, the database
which I use (called "SHAPE") has "asp" in the User column, and db_owner
under default schema.

In the same section on the machine which doesn't work, nothing is in those
columns for that database. Obviously, I tried adding asp and db_owner in
those two respective spots, and cannot get it to save my changes. Mgmt
Studio tells me "User, group, or role 'asp' already exists in the current
database."

I am at a loss and losing tons of valuable time on this. I am not sure what
I need to do.

Any help appreciated. Thanks.

 

[Jeff Dillon]

Try launching SQL Management Studio using the "asp" login, and see what
happens

Jeff

 

[Middletree]

Try launching SQL Management Studio using the "asp" login, and see what

happens

On my good computer, it works fine. I will try the other one when I get
home.

What should I look for?

 

[Jeff Dillon]

"Access Denied" when you try to log in and access that database.

 

[Jeff Dillon]

And you need to specify the correct computer name "Home", right?

 

[Dave Anderson]

In SQL Server Management Studio, I go to the Security navigation tab
on the left. It's the one outside of the list of databases. I expand
Security to Logins, and expand that. I right-click "asp" and go to
Properties. On the left, I highlight User Mapping. On the machine
that works, the database which I use (called "SHAPE") has "asp" in
the User column, and db_owner under default schema.

In the same section on the machine which doesn't work, nothing is in
those columns for that database. Obviously, I tried adding asp and
db_owner in those two respective spots, and cannot get it to save my
changes. Mgmt Studio tells me "User, group, or role 'asp' already
exists in the current database."

It sounds like you copied the DB -- perhaps by restoring from backup. In
that case, the SQL Server logins are not really synchronized by their
internal IDs. You can remove them from the DB and re-create them, or fix
them with sp_change_users_login:

http://msdn2.microsoft.com/en-us/library/ms174378.aspx

I would look at the Auto_Fix argument.

 

[Middletree]

And you need to specify the correct computer name "Home", right?

On this laptop, I didn't have to. I'll let you know what happens at home, on
the computer I am having problems with.

I should note that many of the KBs and articles said that I need to specify
a domain, but my home PC isn't on a domain. I put the computer name, of
course, but still got the problem.

 

[Middletree]

It sounds like you copied the DB -- perhaps by restoring from backup.

Actually, I did a detach, reattach. Probably the same result, though.

In that case, the SQL Server logins are not really synchronized by their
internal IDs. You can remove them from the DB and re-create them, or fix
them with sp_change_users_login:

http://msdn2.microsoft.com/en-us/library/ms174378.aspx

I would look at the Auto_Fix argument.

I'll check it out, thanks.

 

[Middletree]

It sounds like you copied the DB -- perhaps by restoring from backup. In

that case, the SQL Server logins are not really synchronized by their
internal IDs. You can remove them from the DB and re-create them, or fix
them with sp_change_users_login:

http://msdn2.microsoft.com/en-us/library/ms174378.aspx

I would look at the Auto_Fix argument.

I don't know if you are still looking at this old thread, but I am confused
by this link. It didn't make sense to me, a non-expert when it comes to SQL
Server. I just didn't understand it.

Regarding your first paragraph, I tried removing the user and login (why do
those two things have to be separate, anyway?) and putting new ones in, but
got all kinds of errors there, too. This is beyond ridiculous. I've wasted
way too many hours on this.

 

[Middletree]

You can use sp_change_users_login to patch things up. Otherwise, clean out

the users (and their schemas, if necessary) in the new database, create
the logins on the server, assign them to the database and grant the
appropriate privileges.

I will try this. I was afraid I wouldn't be able to log into SQL Server if I
deleted all logins, but I guess we'll find out.

 

[Dave Anderson]

I will try this. I was afraid I wouldn't be able to log into SQL
Server if I deleted all logins, but I guess we'll find out.

Be careful -- I said to clean out the *users*, not the *logins*. This is
what I mean:

Connect to the server and expand [Databases]. Look under your database, and
expand [Security]:[Users]. Leave the following alone:
{dbo,guest,INFORMATION_SCHEMA,sys}. Remove the user that the web application
uses.

Next, go out to the server level and expand [Security]:[Logins]. Locate the
desired login (create it if necessary) and open it. Under [User Mapping],
map the login to your database. If you do not select a schema, it will
default to dbo. Once you click [OK], go back to the security options on your
database, and you will see the login name listed under [Users]. Assign
permissions.

 

[Middletree]

Followed those directions to the letter. Still got the same error.
I appreciate the help.

 

[Dave Anderson]

Followed those directions to the letter. Still got the same error.
I appreciate the help.

Backing up just a tiny bit, have you ensured that you are running SQL Server
in mixed authentication mode? This is found by looking at the server
properties and examining the [Security] pane. What is checked under Server
Authentication?

 

[Middletree]

Backing up just a tiny bit, have you ensured that you are running SQL
Server in mixed authentication mode? This is found by looking at the
server properties and examining the [Security] pane. What is checked under
Server Authentication?

Currently, it's Mixed. But in trying to troubleshoot, I have made it
Windows-only at times.

 

[Dave Anderson]

Currently, it's Mixed. But in trying to troubleshoot, I have
made it Windows-only at times.

OK. As it is mixed, you can create and assign SQL Server logins. Create a
new one and assign it to your database with a db_datareader role. Then close
your connection to the server and re-open it with those credentials and
confirm that you can perform SELECT against one of the tables. Is this
successful?

 

[Middletree]

Sadly, I am at work, so I can't try that till tonight.

BTW, when I followed the previous directions, you said to go to the newly
created user called asp, and assign permissions. I assigned it to DB_Owner.
Is that what I should have done?

 

[Dave Anderson]

Sadly, I am at work, so I can't try that till tonight.

BTW, when I followed the previous directions, you said to go to the
newly created user called asp, and assign permissions. I assigned it
to DB_Owner. Is that what I should have done?

In an ideal world, no. But that should have been sufficient, since it was
overkill.

I'm not sure you answered this question: What happens when you use those
credentials to connect to the database with SQL Server Management Studio?
Are you able to perform queries?

Once you can answer that question affirmatively, we can discuss any problems
your web application might have connecting.

 

[Middletree]

I'm not sure you answered this question: What happens when you use those

credentials to connect to the database with SQL Server Management Studio?

I can't connect.

 

[Dave Anderson]

I can't connect.

And when you create a new login with a different name?

 

[Middletree]

\>

OK. As it is mixed, you can create and assign SQL Server logins. Create a
new one and assign it to your database with a db_datareader role. Then
close your connection to the server and re-open it with those credentials
and confirm that you can perform SELECT against one of the tables. Is this
successful?

I created a Login named Joe, with password of joe. Got out, tried to log
into SQL Server using SQL authentication, and it gave me an error which was
worded very similarly to the error msg already quoted.