SSIS connection manager login fails

Question

I have an SSIS project wherein I defined a Data Source (provider: Native OLE DB/Microsoft OLE DB Provider for SQL Server). When I open this up manually and hit the button "test connection" all works fine. The connection manager can access the DB using the connection string, user and password.

Now I have an SSIS package where I created a connection manager based on this data source ("new connection from data source...").

In the package control flow I have an SQL task which has connection type OLE DB and connection is set to my connection manager within this very package. The task fires some update statement to the database, noting fancy at all.

Now when I debug the whole thing I always get the same error:

Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'myUser'."

FYI: myUser equals the user name I have for my database.

So my question is: what am I missing here? I really can't see what's wrong here. I'm really stuck here. Any hints greatly appreciated!

Answer 1

ProtectionLevel "DontSaveSensitive" means the password won't get saved with the SSIS package at all. The reason SSIS does this is so that the password isn't floating around where someone else could get it.

So when you type in the password and hit the button "test connection" it all works fine. But when you run in debug mode (or in production), you don't have a password. Therefore, of course, the login fails.

This is why you need a configuration file. See my answer here:

You create a configuration file for the connection string, but the password won't get saved to the configuration file either. You will have to edit the configuration file manually if you want it to include a password. But the best way to do this is to configure the password when you schedule the job that executes the SSIS package. That keeps the password in a safe place, and it isn't floating around all over the place with the SSIS package.

While you are debugging, of course, you need a configuration file that has the password manually typed into it. But that configuration file doesn't go with the package when it's deployed to production. The production config file should have a blank password. The password should live in the scheduled job that executes the package.

Answer 2

I have worked around that problem in BIDS.

1) First of all if you don't have configuration file and in your connection manager you are using a sql server authentication then you will get a red cross in ole db source or destination tasks. It won't happen in execute sql tasks because the ole db source and destination tasks actually have to run a select * from table query to get you the list of tables to select from. Since password is not saved it will throw you an error.

2) If you do have a configuration file you will still face the similar problem. But with configuration file you can edit and manually insert password. It worked for me. The ole db source and destination tasks were not showing any red cross.