使用SSIS包到Access DB的SQL Agent作业失败

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.

我有一个运行脚本任务的SSIS包（主要是和其他一些东西）。脚本任务使用OleDB连接连接到Access数据库。这是Microsoft Jet 4.0连接。我安装了驱动程序。但它不会通过代理帐户在SQL Agent中运行。它可以直接从Visual Studio和包存储中运行。实际上，当我作为代理所绑定的特殊帐户登录时，它在这两个地方都运行良好。但是，当我通过SQL Server代理运行时，我得到了可怕的“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:

通过SQL代理作业历史记录的错误消息：

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.
Access驱动程序已安装并在SQL Agent所在的服务器上运行。我通过在VS中运行包作为我的帐户和代理的帐户来验证这一点，没有任何问题。
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.
代理帐户可以访问相关文件。再次，通过以代理的帐户登录服务器进行验证。该文件位于网络共享上，但该路径指定为UNC路径。
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.
从包存储（通过SSMS）运行包，因为我的帐户和代理的帐户都有效。我在数据库服务器上做了这个以确保。

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.

我很乐意提供其他信息以帮助其他诊断。我自己完全不确定为什么这不起作用。

2 个解决方案

#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:

事实证明，问题是Jet提供程序正在尝试写入SQL Agent用户的临时目录，即使该任务是作为其他用户模拟运行的。这似乎是Windows模拟系统的一项功能，它不会更改用户配置文件，只会更改用户令牌。我最终得到了这段代码：

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.

它并不理想，但它确实有效。这意味着我不必为SQL Agent的临时目录授予权限。只有这个代码必须改变。

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

遗憾的是，似乎无法更改ODBC驱动程序放置其临时文件的位置。

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.

编辑：我也有一个带有Excel源的常规基于数据流的包的问题。在这种情况下，我别无选择，只能为我的代理用户的帐户授予访问SQL Agent的临时目录的权限。如果我也可以在那里找到解决方法，我会发布它。

#2

I would suggest to try few things:

我建议尝试一些事情：

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).

尝试使用cmd模式执行包，即使用SQL Agent中的dtexce.exe语法（使用32位和64位选项）。
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).

将服务帐户（帐户SQL代理正在运行）添加到Integration Service的DCOM组件。如果允许，请将SQL代理服务帐户更改为代理帐户（用于测试）。
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.

使用代理帐户执行所有操作，即使用代理帐户部署程序包，并将作业所有者设置为代理帐户（在SQL代理中）。使用代理帐户创建作业。
Check the Window event viewer if you have any error related to your proxy account or SQL Agent Service account.

如果您有与代理帐户或SQL代理服务帐户相关的任何错误，请检查Window事件查看器。
If you are using SQL Server 2012 or higher deploy the package, try it with Integration Services Catalog.

如果您使用SQL Server 2012或更高版本部署该程序包，请使用Integration Services目录进行尝试。

#1

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