ASP - login failed for user

Author

login failed for user

Middletree

2008-02-17, 6:59 pm

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/databas...-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

2008-02-18, 6:57 pm

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

Jeff

"Middletree" <middletree@hottttttttmail.com> wrote in message
news:eZKIspacIHA.1132@TK2MSFTNGP06.phx.gbl...
> 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/databas...-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.
>
>
>
>
>
>

Middletree

2008-02-18, 6:57 pm

> 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

2008-02-18, 6:57 pm

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

"Middletree" <middletree@hottttttttmail.com> wrote in message
news:u498W3mcIHA.4712@TK2MSFTNGP04.phx.gbl...
>
> On my good computer, it works fine. I will try the other one when I get
> home.
>
> What should I look for?
>

Jeff Dillon

2008-02-18, 6:57 pm

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

"Middletree" <middletree@hottttttttmail.com> wrote in message
news:u498W3mcIHA.4712@TK2MSFTNGP04.phx.gbl...
>
> On my good computer, it works fine. I will try the other one when I get
> home.
>
> What should I look for?
>

Dave Anderson

2008-02-18, 6:57 pm

Middletree wrote:
> 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.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

Middletree

2008-02-18, 6:57 pm

> 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

2008-02-18, 6:57 pm

>
> 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

2008-02-25, 3:57 am

> 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

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.

Dave Anderson

2008-02-25, 3:57 am

"Middletree" wrote:
>
> I don't know if you are still looking at this old thread, but I am
>

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.

A SQL Server login is a server (or instance) principal, while a user is a
database principal. When you attached to a new server, your database stores
a reference to a user that does not match a server principal. Matching by
name is not enough.

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.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

Middletree

2008-02-26, 6:57 pm

> 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

2008-02-26, 6:57 pm

Middletree wrote:
>
> 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.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

Middletree

2008-02-27, 9:57 pm

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

Dave Anderson

2008-02-28, 7:03 pm

Middletree wrote:
> 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?

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

Middletree

2008-02-28, 7:03 pm

>
> 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

2008-02-28, 7:03 pm

Middletree wrote:
>
> 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?

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

Middletree

2008-02-28, 7:03 pm

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?

>
> 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?
>
>
>
> --
> Dave Anderson
>
> Unsolicited commercial email will be read at a cost of $500 per message.
> Use of this email address implies consent to these terms.
>

Dave Anderson

2008-02-28, 7:03 pm

Middletree wrote:
> 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.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

Middletree

2008-02-28, 7:04 pm

> 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

2008-02-28, 7:04 pm

"Middletree" wrote:
>
> I can't connect.

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

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

Middletree

2008-02-29, 3:56 am

\>
> 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.

Middletree

2008-02-29, 3:56 am

> 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?

Same error.

Middletree

2008-02-29, 3:56 am

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

Cannot connect.

Dave Anderson

2008-02-29, 6:56 pm

"Middletree" wrote:
> 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.

You say the message is similar, but I don't know what the message was.

One difference between 2000 and 2005 is password policies. When you created
the user, did you uncheck [Enforce password policy], [Enforce password
expiration], and [User must change login at next login]?

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

Middletree

2008-02-29, 6:56 pm

The message was exactly the same, but changed "asp" to "joe" Something like

"Connection failed. User 'joe' not part of a trusted SQL connection".

I'm going from memory, but that was very close.

I'm sorry, I should have mentioned that I un-checked "enforce password
policy", because it usually won't let you do what I'm doing (create a user
named joe with password of joe). When I unchecked that, the other
checkboxes are grayed out.

>
> You say the message is similar, but I don't know what the message was.
>
> One difference between 2000 and 2005 is password policies. When you
> created the user, did you uncheck [Enforce password policy], [Enforce
> password expiration], and [User must change login at next login]?
>
>
>
> --
> Dave Anderson
>
> Unsolicited commercial email will be read at a cost of $500 per message.
> Use of this email address implies consent to these terms.

Dave Anderson

2008-03-03, 7:11 pm

Middletree wrote:
> The message was exactly the same, but changed "asp" to "joe"
> Something like
> "Connection failed. User 'joe' not part of a trusted SQL connection".

You can try forcing TCP/IP by adding this to the connection string:

Network Library=DBMSSOCN;

If that fails, open the SQL Server Log and verify that the login was
rejected by the server:

- Open SQL Server Management Studio
- Connect to your server
- Expand [SQL Server Agent]
- Expand [Error Logs]
- Open one of the logs

In the left pane, uncheck [SQL Agent] and check [SQL Server]. If you cannot
locate the login failure, the problem is not at the SQL Server, but at the
client. This all, of course, depends on your login auditing:

- Open SQL Server Management Studio
- Connect to your server
- Examine properties on your server instance

Under [Security], [Login Auditing] must log failures.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

Middletree

2008-03-03, 7:11 pm

I'll try that tonight.

Thanks!

Dave Anderson

2008-03-03, 7:11 pm

Middletree wrote:
> I'll try that tonight.

I'm interested to hear how it turns out. I should reiterate that the most
likely cause of the "not associated with a trusted connection" error is that
the server is not configured for mixed mode (Windows AND SQL Server
authentication).

Also, if DBMSSOCN does not work, try DBNMPNTW for named pipes.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

Middletree

2008-03-03, 7:11 pm

> likely cause of the "not associated with a trusted connection" error is
> that the server is not configured for mixed mode (Windows AND SQL Server
> authentication).
>

Oh, I thought I ruled that out. Sorry if I didn't make it clear. It's
definitely in mixed mode.

Middletree

2008-03-04, 3:56 am

Well, this is a kick in the pants. I went to try your latest suggestions.
Before bringing up SQL Server, I chose to open the browser and point to the
site, which would, of course, throw an error. However, the dang thing
worked! The site came right up, and I was able to do some things which I
know hit the database, such as logging in with the name and password I set
up.

I didn't make any recent changes since last time. However, I did perform a
rare reboot last night. We had thunderstorms, and I chose to power off and
unplug the machine. So I guess those changes you had me make earlier did the
trick, but a reboot, or at least a restart of one of the SQL Server
services, was necessary for those changes to take effect.

At any rate, thanks for your help!

Dave Anderson

2008-03-04, 6:57 pm

Middletree wrote:
> Well, this is a kick in the pants. I went to try your latest
> suggestions. Before bringing up SQL Server, I chose to open the
> browser and point to the site, which would, of course, throw an
> error. However, the dang thing worked! The site came right up, and
> I was able to do some things which I know hit the database, such as
> logging in with the name and password I set up.
>
> I didn't make any recent changes since last time. However, I did
> perform a rare reboot last night. We had thunderstorms, and I chose
> to power off and unplug the machine. So I guess those changes you had
> me make earlier did the trick, but a reboot, or at least a restart of
> one of the SQL Server services, was necessary for those changes to
> take effect.

Perhaps you blew through the dialog box:

"In the SQL Server Management Studio dialog box, click OK,
to acknowledge the need to restart SQL Server."

http://technet.microsoft.com/en-us/...y/ms188670.aspx

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

Middletree

2008-03-04, 6:57 pm

I guess I did, but that's only to change the method of authentication, and
it was mixed to begin with.

Oh well. All's well that ends well.

thanks

> Perhaps you blew through the dialog box:
>
> "In the SQL Server Management Studio dialog box, click OK,
> to acknowledge the need to restart SQL Server."
>
> http://technet.microsoft.com/en-us/...y/ms188670.aspx
>
>
>
>
> --
> Dave Anderson
>
> Unsolicited commercial email will be read at a cost of $500 per message.
> Use of this email address implies consent to these terms.
>

Adence

2008-03-11, 2:24 pm

[url=http://www.BestG

Blog.com/PlayMovie?clip=148803]Heather Locklear and Helen Hunt Go Lesbian![/url]

Sponsored Links