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

Question

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

CJ-234774

SSC-Addicted

Points: 411
More actions
March 28, 2007 at 8:07 am

#172608

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

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

That was it!

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

CJ

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Antares686

SSC Guru

Points: 125444

March 28, 2007 at 2:04 pm

#697532

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

DesertBreeze SSC Enthusiast Points: 119 More actions · Answer 6

DesertBreeze

SSC Enthusiast

Points: 119

December 24, 2009 at 2:40 pm

#1096400

That helped me too. Thanks for the post!

clearlymichele SSC Rookie Points: 48 More actions · Answer 7

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: 1004407 More actions · Answer 8

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.

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

Rick-153145 SSCrazy Points: 2823 More actions · Answer 9

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.