April 13, 2004 at 11:54 am
I have a DTS that extracts data from a DB on another linked SQL server that I have read-only permission to. My scheduled job runs the package fine on my Dev machine, but when I move them to our Test server, it fails with an unable to connect to the other server error message. It appears that the account used by Agent on our Test machine is not authorized to access the linked server. What do I need to do to allow my Test (and ultimately my Production) servers access to the Linked Server?
April 13, 2004 at 12:17 pm
Does the DTS package have a connection object to the other server, or do you actually use a linked server?
If it's the first case, ask to have the SQL Service account added as a login to the other server and give it appropriate permissions, or create and use a SQL login for the connection.
If it's actually a linked server, check that the linked server is set up correctly and that if you are mapping local logins to remote logins that these are set up correctly.
April 14, 2004 at 7:51 am
If the problem occurs when you schedule the job, then you are correct that it's probably due to the account that the SQL Agent runs under. You need to make sure that account has the same permissions that you have on the external server. When the job is scheduled it runs under that SQL Agent account, based on the permissions it has, so the best solution is to make sure that account is a user and has the proper permissions on the external server you are accessing. Of course, if your testing and production servers (executing the DTS package) run the agent with different accounts you'll have to make sure both of those accounts have the proper permissions on the external server in order for both situations to work properly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply