accessing a remote database through SSIS

Question

accessing a remote database through SSIS

solus

Hall of Fame

Points: 3495
More actions
January 22, 2010 at 6:48 am

#218415

I have an SSIS package running under a scheduled job on my local instance of SQL server. Part of the process copies data from an access table (on my machine) to a table in a sql server 2005 database located on another machine (in the same domain)
The package start ok, copies the access files via ftp to my local machine and attempts to copy the data. As soon as the package attempts to connect to the remote database, I get this error
Failed to acquire connection "Property". Connection may not be configured correctly or you may not have the right permissions on this connection.
(errorcode: -1073573396)
**Property is the name of the database on the remote machine
Ive tried to connect using windows authentication and also through a sql server account (setup especially for this process with dbo access to the remote machine)
Ive also setup distributed transactions and set both databases to use them
SET REMOTE_PROC_TRANSACTIONS ON
In component services (on both machines) under MSDTC security Ive allowed remote clients, inbound & outbound and set communication to 'No Authentication Required'
Nothing I can do will allow the process on my database to access the remote database. Im out of options now and cant find anything else to try.
Are there any specific steps i could be missing to allow me to connect to a remote database from an SSIS package running under the sql server instance on my local machine ?
Both machines are running windows server 2003 sp2

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

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

Have you check the logs on the sql server to see what the error is?

Ive tried to connect using windows authentication and also through a sql server account (setup especially for this process with dbo access to the remote machine)

what is the account the agent runs on? does this have access to the server?

LOOKUP_BI-756009 SSCertifiable Points: 7286 More actions · Answer 2

If Im not mistaken, you need to also check what credentials are being used by the SQL Server Agent Service that is running in Configuration Mng

http://msdn.microsoft.com/en-us/library/ms191543.aspx

steveb. SSC-Forever Points: 46733 More actions · Answer 3

LOOKUP_BI-756009 (1/22/2010)

If Im not mistaken, you need to also check what credentials are being used by the SQL Server Agent Service that is running in Configuration Mng

http://msdn.microsoft.com/en-us/library/ms191543.aspx%5B/quote%5D

yes you need to ensure that the agent is running under an account that can access the sql server, ideally a domain based account

solus Hall of Fame Points: 3495 More actions · Answer 4

Its running under local system. How can i check if this account has access to the remote server ?

solus Hall of Fame Points: 3495 More actions · Answer 5

Ive accessed the SQL server 2005 configuration tool and tried to change the account that the sql server agent runs under. Ive changed it to my own account, but when I clicked apply, then 'ok' I get this error

Invalid parameter [0x80041008]

solus Hall of Fame Points: 3495 More actions · Answer 6

Ok, I changed the account to my windows login account and entered my password, this seemed to work. So now the SQL server agent is running under my windows account. Ive given this account full dbo rights to the remote database, but I still cant connect, I get exactly the same error.

steveb. SSC-Forever Points: 46733 More actions · Answer 7

have you checked the error log on the server that you are trying to connect to? there should be an error on there with a code that will give you the reason for the failure.

post the message here if you need help with it.