login failed for user

Discussion in 'ASP General' started by Middletree, Feb 17, 2008.

  1. Middletree

    Middletree Guest

    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.
     
    Middletree, Feb 17, 2008
    #1
    1. Advertising

  2. Middletree

    Jeff Dillon Guest

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

    Jeff

    "Middletree" <> wrote in message
    news:...
    > 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, Feb 18, 2008
    #2
    1. Advertising

  3. Middletree

    Middletree Guest

    > 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?
     
    Middletree, Feb 18, 2008
    #3
  4. Middletree

    Jeff Dillon Guest

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

    "Middletree" <> wrote in message
    news:...
    >> 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, Feb 18, 2008
    #4
  5. Middletree

    Jeff Dillon Guest

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

    "Middletree" <> wrote in message
    news:...
    >> 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, Feb 18, 2008
    #5
  6. 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.
     
    Dave Anderson, Feb 18, 2008
    #6
  7. Middletree

    Middletree Guest

    > 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, Feb 18, 2008
    #7
  8. Middletree

    Middletree Guest

    >
    > 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, Feb 18, 2008
    #8
  9. Middletree

    Middletree Guest

    > 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, Feb 25, 2008
    #9
  10. Middletree

    Middletree Guest

    > 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.
     
    Middletree, Feb 26, 2008
    #10
  11. Middletree wrote:
    >> 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.


    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.
     
    Dave Anderson, Feb 26, 2008
    #11
  12. Middletree

    Middletree Guest

    Followed those directions to the letter. Still got the same error.
    I appreciate the help.
     
    Middletree, Feb 28, 2008
    #12
  13. 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.
     
    Dave Anderson, Feb 28, 2008
    #13
  14. Middletree

    Middletree Guest

    >
    > 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.
     
    Middletree, Feb 28, 2008
    #14
  15. Middletree wrote:
    >> What is checked under Server Authentication?

    >
    > 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.
     
    Dave Anderson, Feb 28, 2008
    #15
  16. Middletree

    Middletree Guest

    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.
    >
     
    Middletree, Feb 28, 2008
    #16
  17. 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.
     
    Dave Anderson, Feb 28, 2008
    #17
  18. Middletree

    Middletree Guest

    > 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.
     
    Middletree, Feb 28, 2008
    #18
  19. "Middletree" wrote:
    >> What happens when you use those credentials to connect to the
    >> database with SQL Server Management Studio?

    >
    > 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.
     
    Dave Anderson, Feb 28, 2008
    #19
  20. Middletree

    Middletree Guest

    \>
    > 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, Feb 29, 2008
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?U3lsdmFu?=
    Replies:
    4
    Views:
    5,427
    =?Utf-8?B?U3lsdmFu?=
    Nov 26, 2005
  2. Dennis
    Replies:
    0
    Views:
    4,101
    Dennis
    Jun 26, 2006
  3. CFTK
    Replies:
    5
    Views:
    523
  4. Homer
    Replies:
    3
    Views:
    8,031
    Alexey Smirnov
    Sep 25, 2007
  5. Tony Johansson
    Replies:
    3
    Views:
    16,491
    Patrice
    Jan 2, 2010
Loading...

Share This Page