January 4, 2006 at 3:06 pm
I have a DTS package that copies data from another SQL server to mine. I used SQL Server Authentication using the same id. Everything worked fine until they moved their data to another server. I logged into the server, ran enterprise manager and edited the job to change the server name, but got the following error - sql server does not exist or access is denied, yet I can run enterprise manager from my desktop, make the same changes and it works/connects fine. Can anyone tell me why it works from my desktop and not directly from the server?
January 5, 2006 at 1:15 am
Kevin, probably the user ID which the job runs under doesn't have privileges on that remote server. I.e., if you run the package directly, it runs under your login. If the package is invoked by a scheduled job, it runs under the login which kicks off jobs (probably the SQL Server Agent) or the job owner, depending on how your server is configured.
So, you can determine what context the job runs under, then make sure that login has the right privileges on the remote server. If you aren't sure which context the job runs under, you can add an ExecuteSQL step which simply creates a local table holding this data:
SELECT SYSTEM_USER() AS FOO INTO dbo.T_FOO
After that step runs, you should have a single record showing the context which the job runs under.
As a final reminder, if you run this interactively (i.e., log into EM and click "Run" on the DTS package) you will get your own login info. If you run it under a scheduler (log into EM, go to jobs, and click "Start job") you'll get a different login. It's that 2nd login which will need privileges on the remote server to make this work.
HTH
January 5, 2006 at 5:26 pm
I used SQL Server authentication to run these jobs.
January 9, 2006 at 10:31 am
Are you able to log in to the new server to verify that it can connect to the other server? I've had this problem before and for some odd reason needed to create an alias on the new server in order to connect to the remote server. Once I create the alias, everything works fine.
I would log in to the new server to verify the connection first.
Try doing a PING against the server to see if you can talk to it at all. If you can't connect from Enterprise Manager/Query Analyzer, try creating an alias or checking the protocols enabled in the cliconfg.exe app. If that still doesn't work, may need to get the network team involved to see where the traffic is stopping between the two servers.
January 9, 2006 at 10:38 am
I have verified access to the new server. This job is now setup and working properly. I was just curious as to why I could not make these changes while I was logged into the server using enterprise manager, but was successful using enterprise manager from my desktop. It just seemed a little backwards.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply