Cannot Open Database "xxx" requested by login. Login failed

  • CJ-234774

    SSC-Addicted

    Points: 411

    I created a DTS package using remote desktop to the server.  I'm logged in as me and I'm an administrator. 

    If I try to edit the dts package or run it when using Enterprise Manager on my own PC, I get this error. 

    Package error

    Error Source:  Microsoft OLE DB Provider SQL Server

    Error Description:  Cannot Open Database "xxx" requested by login.  The login failed.

    I had something like this with linked servers once (where I couldn't access it from my PC, but could from the server) and I had to add some remote server login mappings to the linked server.  I don't see how this is related though...

    Thanks in advance for your help!

    CJ

  • Antares686

    SSC Guru

    Points: 125444

    How is the connection made? Specific user account or Windows Authentication.

    If Windows Authentication keep in mind SQL Agent uses it's service account as the login and thus if it is not your user name and password that is the account the SQL Agent service is running under it won't be the same as you logging in.

    However, I would never suggest setting up the server to run under your credentials, I would suggest setting up a SQL user account with proper permissions and use SL authentication instead with that info so the DTS package can successfully make the connection.

  • CJ-234774

    SSC-Addicted

    Points: 411

    Thanks for the reply.

    I'm connecting using Windows Authentication.  The problem is not actually running the DTS package as a scheduled job, but even just modifying it or running it from within the designer that I'm having problems.  I'm logged into my computer as me or logged into the server as me, open EM, then on my computer, if I right click the DTS package to run it or open the designer and try to run it from in the designer, I get this error.  If doing the same thing on the server it runs fine.

     

  • Antares686

    SSC Guru

    Points: 125444

    Did you verify the connection object is setup correctly? It sounds like the connection object has the issue from what you say. Might be defined as server = localhost or something similar which would be why fails on your machine and not on the actual server. I have do this to myself on occassions even thou I know about this.

  • CJ-234774

    SSC-Addicted

    Points: 411

    That was it! 

    Thanks so much!  Now I just gotta remember this next time...

     

    CJ

  • Antares686

    SSC Guru

    Points: 125444

    You'll forget, lord knows I do almost everytime.

  • DesertBreeze

    SSC Enthusiast

    Points: 119

    That helped me too. Thanks for the post!

  • clearlymichele

    SSC Rookie

    Points: 48

    Antares686 - Wednesday, March 28, 2007 10:06 AM

    Did you verify the connection object is setup correctly? It sounds like the connection object has the issue from what you say. Might be defined as server = localhost or something similar which would be why fails on your machine and not on the actual server. I have do this to myself on occassions even thou I know about this.

    I searched my VS project and it does refer to localhost.  If I'm testing on my desk pc, what would I change that to?  I'm new to this all.  Where would I define it, other than where I see localhost in the code?

  • Jeff Moden

    SSC Guru

    Points: 996832

    clearlymichele - Wednesday, May 16, 2018 2:16 PM

    Antares686 - Wednesday, March 28, 2007 10:06 AM

    Did you verify the connection object is setup correctly? It sounds like the connection object has the issue from what you say. Might be defined as server = localhost or something similar which would be why fails on your machine and not on the actual server. I have do this to myself on occassions even thou I know about this.

    I searched my VS project and it does refer to localhost.  If I'm testing on my desk pc, what would I change that to?  I'm new to this all.  Where would I define it, other than where I see localhost in the code?

    Change it to the actual server instance name.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Rick-153145

    SSCrazy

    Points: 2713

    clearlymichele - Wednesday, May 16, 2018 2:16 PM

    I searched my VS project and it does refer to localhost.  If I'm testing on my desk pc, what would I change that to?  I'm new to this all.  Where would I define it, other than where I see localhost in the code?

    I doubt you are using DTS still, but if SSIS, you can change it on the connection or if its running as a job in the Data Sources tab on the job step.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply