October 12, 2011 at 4:04 am
I have a strange issue which has suddenly hit my SSIS server.
We have a number of packages that connect to other SQL Servers using SQL Authentication.
Everything was running fine until this morning; the packages ran every morning without fail.
Now we are getting the error:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "..." failed with error code 0xC0202009.
component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0:900404E4D. An OLE DB record is available. Source "Microsoft SQL Server Native Client 10.0" Hresult: 0x800404E4D Description "Lofin failed for user '...'.".
The bizarre thing is that I can run the run a simple query using the same sql authentication direct from the sql management studio on the SSIS server the package is running from.
I have even created two identical packages to make a connection using sql authentication, one directly on the server, the other on my local machine, and published both. the one published from the studio on the server connects, the one published from my studio does not - and they both use the same login and password.
I have tried connecting to other SQL Servers with the same error
I have tried using ADO instead of OLD DB as my connection with the same problems.
But bizarrely if I try using a trusted connection, it works fine regardless of where I publish the package from.
The other bizarre thing is that between the last successful run, the packages haven't been republished, neither server has been rebooted (they have since been but this hasn't fixed the issue).
I desperately need help - can anyone shed any light on what I can do or test?
Thanks
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
October 12, 2011 at 5:15 am
We have resolved the issue - somehow - though we aren't sure how or why.
The packages in question were using Trusted Authentication (I thought ehy were using sql authentication) - but were failing with the errors mentioned above (ignoring the login failure obviously).
We tried republishing the packages but with no success, they would publish but not run.
We spotted something on other forums about the protection level. These were set to ProtectSensitiveWithUserKey. We couldn't see why this would affect TrustedConnections, but for the hell of it we changed it to ProtectSensitiveWithPassword and supplied a password.
We republished the packages and lo and behold they just ran. They never asked for the password when they ran, we never had to change the scheduled tasks, they just .... worked!
Now if someone could answer that one ..... :crazy:
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
October 17, 2011 at 4:32 pm
EncryptSensitiveWithUserKey
Uses a key that is based on the current user profile to encrypt only the values of sensitive properties in the package. Only the same user who uses the same profile can load the package. If a different user opens the package, the sensitive information is replaced with blanks and the current user must provide new values for the sensitive data. If the user attempts to execute the package, package execution fails. DPAPI is used for this encryption.
http://msdn.microsoft.com/en-us/library/ms141747.aspx
It could be inferred that someone else attempted to open the package and the credentials were invalidated despite being trusted authentication. Stranger things have happened.
Just throwing something out there.
I would advise not using User Encryption at all, password encryption is ok but I prefer to use a database configuration for all connection information (encrypted to your required level) and use windows authentication to connect to said database.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply