database SSIS 连接管理器登录失败

SSIS connection manager login fails

提问by BaseBallBatBoy

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 项目，我在其中定义了一个数据源（提供程序：Native OLE DB/Microsoft OLE DB Provider for SQL Server）。当我手动打开它并点击“测试连接”按钮时，一切正常。连接管理器可以使用连接字符串、用户和密码访问数据库。

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

现在我有一个 SSIS 包，我在其中创建了一个基于此数据源的连接管理器（“来自数据源的新连接...”）。

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.

在包控制流中，我有一个 SQL 任务，它的连接类型为 OLE DB，并且连接设置为我在这个包中的连接管理器。该任务向数据库发出一些更新语句，一点也不奇怪。

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'."

错误：SSIS 错误代码 DTS_E_OLEDBERROR。发生 OLE DB 错误。错误代码：0x80040E4D。OLE DB 记录可用。来源：“Microsoft OLE DB Provider for SQL Server” Hresult：0x80040E4D 描述：“用户“myUser”登录失败。

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

仅供参考：myUser 等于我的数据库的用户名。

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!

所以我的问题是：我在这里错过了什么？我真的看不出这里有什么问题。我真的被困在这里了。任何提示非常感谢！

采纳答案by criticalfix

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.

ProtectionLevel“DontSaveSensitive”意味着密码根本不会与 SSIS 包一起保存。SSIS 这样做的原因是密码不会在其他人可以获得的地方浮动。

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.

您为连接字符串创建了一个配置文件，但密码也不会保存到配置文件中。如果您希望它包含密码，则必须手动编辑配置文件。但最好的方法是在安排执行 SSIS 包的作业时配置密码。这将密码保存在一个安全的地方，并且它不会随 SSIS 包到处浮动。

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.

当然，在调试时，您需要一个配置文件，其中手动输入了密码。但是，在部署到生产环境时，该配置文件不会随包一起使用。生产配置文件应该有一个空密码。密码应该存在于执行包的预定作业中。

回答by bilalraja

I have worked around that problem in BIDS.

我已经在 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.

1) 首先，如果您没有配置文件并且在您的连接管理器中您使用的是 sql server 身份验证，那么您将在 ole db 源或目标任务中得到一个红叉。它不会在执行 sql 任务中发生，因为 ole db 源和目标任务实际上必须运行 select * from table 查询才能获得要从中选择的表列表。由于没有保存密码，它会给你一个错误。

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.

2) 如果你有一个配置文件，你仍然会面临类似的问题。但是使用配置文件，您可以编辑和手动插入密码。它对我有用。ole db 源和目标任务没有显示任何红叉。