January 18, 2010 at 10:02 am
I have a SSIS package that runs on a local instance of sql server. Due to problems with my hardware, Ive set it up (using a config file) to access a database on another machine on our network (its actually a colleagues development pc). The package starts and performs the first stages ok (downloads & unzips a file containing some access data tables)
When it gets the part where it copies data from an access table to a sql server table (on the other machine) I get an 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)
The package is running from a job in the instance of sql server on my local PC. The sql server database being updated is on the remote pc.
this is the connection string in my config file
<Configuration ConfiguredType="Property" Path="\Package.Connections[Property].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=IP address of other machine;User ID=My user account;Initial Catalog=Property;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>
</Configuration>
Ive been setup with public & sysadmin access on the remote database, and its also been setup to allow remote connections, but the SSIS process cant connect. The remote database also has my login mapped to dbo.
Ive also tested the connection in the package in visual studio and it checked out ok.
Can anyone tell me why this is not connecting ?
January 18, 2010 at 10:20 pm
How is the job running? Typically this isn't under a user account. When you run it interactively, the package runs under your account. Those credentials might not work when a job executes it.
January 20, 2010 at 2:01 am
the sql server agent is running under the local system account
January 21, 2010 at 4:27 am
anyone ?
January 21, 2010 at 6:12 pm
This part of the connection string in your config file
\Package.Connections[Property].
implies that you have a connection called Property. Is this the case?
January 22, 2010 at 3:02 am
Property is the name of the database
January 22, 2010 at 3:04 am
\Package.Connections[Property].
refers to the actual property, not the database name (confusing I know, but I inherited this database, and thats what its called)
January 26, 2010 at 3:52 am
Hi,
If the package is running locally, but accessing a database on a remote server then it should probably require a logon on the remote machine.LOCAL SYSTEM ACCOUNT is equivalent to a local administrator, it wouldn't have any privileges on the remote machine.
Is the remote logon in the config file? I could only see an Userid, no password. Maybe it was removed for the example.
Can you logon to the remote server with that id and access the relevant components in the database? Maybe permissions haven't been granted at that level!
I hope this helps.
Paul
January 26, 2010 at 4:18 am
I changed the account that the sql agent runs under to my local windows account (this does have access to the remote machine) and it works.
But I now have a new machine, so im back to running everything locally.
Now im having a different problem, the package no longer connects to my ftp server, despite working perfectly on my old machine
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply