Cannot connect to password protected MS Access 2010 database from SSIS package on SQL Server

Question

A couple of my co-workers have been having trouble connecting to password protected MS Access 2010 database from SSIS using. I decided to make an attempt at troubleshooting this.

To simplify the situation I created a new MS Access 2010 database with one table and one row with no password protection. I copied that database, updated the data in the one row so that I could differentiate the source and then applied a password. Both databases on the same network path for which I am using the full UNC path.

I created two SSIS packages, one for each database. All the packages do is read from the one table and write the data to a CSV. For my connection to the Access database I am using the Microsoft Office 12.0 Access Database Engine OLE DB Provider. For the password protected database connection I am putting the password in the Jet OLEDB:Database Password field.

When running on my machine through Visual Studio both SSIS package work as expected and write the data from the Access database table to a CSV file at the same location as the database. However, when I upload the packages and create a SQL job for each of them the one without a password works, but the one with a password does not.

Here is the error I get (with the user Xed out)...

Executed as user: XXXXXX\XXXXXXXX. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.4000.0 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    
Started:  1:42:02 PM  

Error: 2016-07-11 13:42:03.94     
Code: 0xC0202009     
Source: Package1 Connection manager "TestPassword"     
Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. 

Error code: 0x80004005.  An OLE DB record is available.  
Source: "Microsoft Office Access Database Engine"  
Hresult: 0x80004005  
Description: "Cannot open database ''.  It may not be a database that your application recognizes, or the file may be corrupt.".  End Error  

Error: 2016-07-11 13:42:03.94     
Code: 0xC020801C     
Source: Data Flow Task OLE DB Source [1]     
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "TestPassword" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  

Error: 2016-07-11 13:42:03.95     
Code: 0xC0047017     
Source: Data Flow Task SSIS.Pipeline     
Description: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.  End Error  

Error: 2016-07-11 13:42:03.95     
Code: 0xC004700C     
Source: Data Flow Task SSIS.Pipeline     
Description: One or more component failed validation.  End Error  

Error: 2016-07-11 13:42:03.95     
Code: 0xC0024107     
Source: Data Flow Task      
Description: There were errors during task validation.  End Error  

DTExec: The package execution returned DTSER_FAILURE (1).  

Started:  1:42:02 PM  Finished: 1:42:03 PM  Elapsed:  1.903 seconds.  The package execution failed.  The step failed.

Any suggestions?

We believe the problem is the server have an outdated driver. I'll post a comment if that turns out to be the issue. — Isaac

SFrejofsky SFrejofsky · Accepted Answer · 2016-07-12T12:34:37

I had a very similar issue with an ADO Net connection manager trying to connect to a remote Azure DB.

You can try assigning the full connection string including the credentials to the connection manager via an expression and variable. There are more secure ways to assign this information at run time but that is a quick way to test.

One of the major reasons that this type of deployment fails is that the password is encrypted specific to your machine when you set up the connection manager. Once the package is moved the production machine it can not decrypt the password that is saved in the connection manager. Therefore the package will never have access to your protected DB.

Cannot connect to password protected MS Access 2010 database from SSIS package on SQL Server

3 Answers