January 17, 2012 at 8:13 am
I am new to SSIS and have been given an assignment to create a project.
I'm am trying to build a package in BI Studios. I have a OLE DB Source in my Data Flow Tasks' connection manager. I use the SQL Command from variable to set pulling data from the source DB using a simple call 'Exec sp_name @parameter'. I'm using SQL Server Authentication to connect to a remote DB (Windows Authentication is unavailable). I have it set to save the password and the test connection runs fine but when I attempt to save or view the columns I get SSIS Error code DTS_E_OLEDBERROR 0x80040E4D "Login Failed for [Login]"
January 18, 2012 at 2:27 am
Cause:
When you create a package on your local machine and create a connection in it, you have the option to save the password of that connection. However by default it encrypts this password so that in can only be decrypted when you' re running the package on the same machine, with the same account. This holds good only if the Connection Manager uses SQL Authentication or connects to a Database which does not support Windows Integrated Authentication (e.g. Oracle).
So, in the above scenario, if the package is deployed to a Remote Sql Server, it fails with the "Login failed.." error as it is not able to decrypt the password. (Note: It runs fine if deployed in the local Sql Server)
Resolution:
To correct the problem, you should opt between either of the three options:
1. Change all the connection managers in the package to use windows authentication.
Note: This is not an option while communicating with third party datasources which does not support windows authentication like Oracle.
2. Encrypt the package with "EncryptSensitiveWithPassword" or "EncryptAllWithPassword" and provide a package password each time User wants to edit/manipulate the package.
3. Create a configuration file to provide the connection information during Package runtime.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply