SQL Agent job failure with SSIS package to Access DB

Question

I have an SSIS package that runs a script task (mostly, and a few other things). The script task connects to an Access database using an OleDB connection. This is the Microsoft Jet 4.0 connection. I have the drivers installed. But it won't run in SQL Agent via a proxy account. It will run fine directly from Visual Studio and from the package store. In fact, it runs fine in both of those places when I log in as the special account that the proxy is tied to. But when I run via SQL Server Agent, I get the dreaded "Unspecifed Error" OleDbException.

Relevant code from script task:

// class field
private string accessConnectionStringTemplate = "Data Source=\"{0}\";Provider=Microsoft.Jet.OLEDB.4.0;";

// in method that connects to database
Print(file, "Connection string: " + string.Format(accessConnectionStringTemplate, file.FileName));
// outputs: Data Source = "\Path\To\File";Provider=Microsoft.Jet.OLEDB.4.0"
using(access = new OleDbConnection(string.Format(accessConnectionStringTemplate, file.FileName))) {
     access.Open();
     // other code
}

The error messages via SQL Agent job history:

Started:  12:35:10 PM
Error: 2016-11-03 12:35:33.51
   Code: 0x00000000
   Source: Import Files Main
   Description: Exception: Unspecified error
End Error
Error: 2016-11-03 12:35:33.51
   Code: 0x00000000
   Source: Import Files Main
   Description:    at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at ST_cc0028a4b56242909c2eae546a807995.csproj.ScriptMain.ImportFile(AccessFile file, DateTime startRecordDate, DateTime endRecordDate, List`1 accessTables, Boolean includeTransactionTables, List`1 specifiedTableList)
   at ST_cc0028a4b56242909c2eae546a807995.csproj.ScriptMain.Main()
End Error
Error: 2016-11-03 12:35:33.51
   Code: 0x00000006
   Source: Import Files 
   Description: The script returned a failure result.
End Error

Some things I've made sure of:

• The Access drivers are installed and work on the server that SQL Agent is on. I verified this by running the package in VS as both my account and the proxy's account, with no issues.
• The proxy account has access to the file in question. Again, verified by logging into the server as the proxy's account. The file is on a network share, but the path is specified as a UNC path.
• The proxy account has access to other databases that are part of this operation, to rule out any other potential sources of error.
• Running the package from the package store (via SSMS) as both my account and the proxy's account works. I did this on the database server to make sure.

In other questions I've seen on the internet about this, it's usually an issue with the drivers. In this case, I'm not sure how it could be.

I'm happy to provide additional information to help other diagnose. I myself am utterly unsure as to why this isn't working.

Answer 1

It turns out that the problem was that the Jet provider was trying to write to the SQL Agent user's temp directory, even though the task was being run with impersonation as a different user. This appears to be a feature of the Windows impersonation system, which does not change the user profile, only the user token. I ended up with this code:

var tempPath = Path.GetTempPath().Replace("\\SQLSERVERAGENT\\", "\\" + Environment.UserName + "\\");
Environment.SetEnvironmentVariable("TEMP", tempPath);
Environment.SetEnvironmentVariable("TMP", tempPath);

It's not ideal, but it works. It means that I don't have to give permissions to the SQL Agent's temp directory. Only this code has to change.

Sadly, there appears to be no way to change where the ODBC driver puts its temporary files.

EDIT: I also had this issue with a regular data flow-based package with an Excel source. In that case, I had no choice but to grant access to the SQL Agent's temp directory for my proxy user's account. If I can come up with a workaround there too, I will post it.

Answer 2

I would suggest to try few things:

1. Try to execute your package with cmd mode i.e. using the dtexce.exe syntax from SQL Agent (using both 32 bit and 64 bit option).

2. Add the Service Account (Account SQL Agent is running through) to the DCOM component for Integration Service. If you are allowed, change the SQL Agent Service account to Proxy account (for testing).

3. Do everything using Proxy account i.e. deploy the package using Proxy Account and make the job owner to Proxy Account (in SQL Agent). Create the job using Proxy Account.

4. Check the Window event viewer if you have any error related to your proxy account or SQL Agent Service account.

5. If you are using SQL Server 2012 or higher deploy the package, try it with Integration Services Catalog.