Server in DTS Package not found

Question

Server in DTS Package not found

dw-386578

SSC Veteran

Points: 224
More actions
January 11, 2007 at 2:34 pm

#171036

Hi!
I am running quite normal DTS pacakges with a normal SQL Server connection. For no apparent reason, suddenly most of the packages, when executed, report the error:
" [Connection Open(connect()) SQL Server does not exist or access denied"
I am using Windows Authentication. The packages were moved to a new server recently, but the referenced server name has been changed to the new server name.
Any ideas on this one??
All help appreciated!

Doug

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

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

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 1

Are you executing these packages manually? If so, has the account you've logged on to your network been granted access to the database on the new server?

dw-386578 SSC Veteran Points: 224 More actions · Answer 2

Thanks for the reply!

I am running them manually and via a Job. Same result. what is the difference when you run them manually or not with respect to the access permissions?

The Plot thickens:

I created a test user with sysadmin priveldges and access to the database. In the package-in the connection- I chose SQL Authentication and used this test user as the login.

Result: Same message as before: "...SQL Server does not exist or access denied"

Any idea why?

Thanks again!

Doug

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 3

Try deleting all instances of the existing SQL connection object in your package and adding a new connection back.

MohammedU SSCoach Points: 19644 More actions · Answer 4

When you create a job to execute the dts package you have to specify the ServerName with -S switch...

Make sure you used the correct server name in the job.

MohammedU
Microsoft SQL Server MVP

Martin Schoombee SSCoach Points: 19128 More actions · Answer 5

We have had similar issues sometimes when using Windows Authentication and our domain passwords expired and subsequently changed. For some reason it seems like the domain controller have not yet filtered everything through to the servers.

This usually happened when we ran the DTS packages locally, or did not close our remote connections after changing domain passwords.

If you are working via remote desktop connection, try logging off and then on again. If that does not work, try to delete and recreate the server registrations (using windows authentication). If you still have issues after this I would suggest you look at recreating the individual connections in your DTS package.

Hope this helps,

Martin.

dw-386578 SSC Veteran Points: 224 More actions · Answer 6

Thanks Guys for the Answers!

I did indeed delete the connections and recreate them. The package runs manually until I close the package and reopen it- then the same old problem!! It does not run from a job in any case.

Forgive my ignorance, but what are domain passwords, and how do I delete and recreate the server registrations??

It seems clear that this problem has to do with a confusion between permissions/logins- but I am still stumped.

Thanks again, Doug

dw-386578 SSC Veteran Points: 224 More actions · Answer 7

some new info on the problem:

I created a new SQL login with Sysadmin permissions

I changed the SQL Agent Connection login to this new login

I deleted the connection in the package that did not run, then recreated it using this new login via SQL Authentication.

It runs exactly once! Then gives the usual message:

" [Connection Open(connect()) SQL Server does not exist or access denied"

Does anyone have an idea what is causing this?

Thanx!

Martin Schoombee SSCoach Points: 19128 More actions · Answer 8

Hi,

Your domain password is the password you use to login to your network, or your local pc for that matter (if it is on a network).

To delete the SQL Server registration:

Right-click on the server name in Enterprise manager, and select the "Delete SQL Server Registration". Do this on the box you are executing the DTS package on.

Right-click on "SQL Sever Group" in the left pane of Enterprise Manager, select "New SQL Server Registration..." and recreate the connection to the server. This will hopefully sort out the login problems.

Martin.