SSIS连接管理器登录失败 [英] SSIS connection manager login fails

问题描述

我有一个SSIS项目，其中我定义了一个数据源（提供程序：本地OLE DB / Microsoft OLE DB提供程序的SQL Server）。当我手动打开这个，点击按钮测试连接一切正常。连接管理器可以使用连接字符串，用户和密码访问数据库。

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.

现在我有一个SSIS包，其中我创建了一个基于此数据源从数据源的新连接...）。

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

在包控制流程中，我有一个SQL任务，其连接类型为OLE DB，连接设置为我的连接管理器。

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:

错误：SSIS错误代码DTS_E_OLEDBERROR。发生OLE DB错误。
错误代码：0x80040E4D。可以使用OLE DB记录。来源：
Microsoft OLE DB提供程序的SQL ServerHresult：0x80040E4D
描述：用户'myUser'登录失败。

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等于我对数据库的用户名。

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!

推荐答案

ProtectionLevelDontSaveSensitive表示密码不会保存与SSIS包。 SSIS这样做的原因是密码不会浮动在其他人可以得到的地方。

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.

所以当你输入密码并点击按钮test连接一切工作正常。但是当您在调试模式（或生产环境）中运行时，您没有密码。因此，当然登录失败。

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:

您为连接字符串创建一个配置文件，但是
密码也不会保存到配置文件中。您将
必须手动编辑配置文件，如果你想要包括
一个密码。但是最好的方法是在计划执行SSIS包的作业时配置密码
。这保证
的密码在一个安全的地方，它不是浮动在整个
SSIS包的地方。

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.

这篇关于SSIS连接管理器登录失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！