blocks|key|70650|text|当您打开/运行包时，将触发一个OnInformation事件，该事件如下所示|type|unstyled|depth|inlineStyleRanges|entityRanges|data|70651|70652|程序包尝试从XML文件"c:\ssisdata\so_56776576.dtsconfig“进行配置。|blockquote|70653|70654|70655|当Visual+Studio/SSDT打开/运行一个程序包时，它说它使用了配置，但由于某些原因，无法获取它们，然后您应该会看到如下消息|70656|加载so_56776576.dtsx时出现|70657|警告:导入配置文件失败："c:\ssisdata\so_56776576.dtsconfig“|70658|70659|70660|和|70661|70662|警告:找不到配置文件"c:\ssisdata\so_56776576.dtsconfig“。检查目录和文件名。|70663|70664|70665|70666|70667|警告:未能加载包的至少一个配置项。检查"Configuration+1“的配置条目和之前的警告，查看失败的配置的描述。|70668|70669|70670|如果有人手动编辑了配置文件并破坏了XML，您将看到如下警告|70671|70672|无法加载XML配置文件。XML配置文件的格式可能不正确或无效|70673|70674|70675|关于configuration+-需要注意的重要一点是，如果找不到配置，SSIS将继续显示设计时的值。这就是为什么检查包运行时发出的警告是至关重要的。如果您正在手动运行，请确保指定了/rep+ew，以便报告错误和警告。|offset|length|style|BOLD|CODE|70676|关于根本原因的猜测|70677|包的保护级别为EncryptSensitiveWithUserKey，这意味着包创建者的AD凭据用于对可能包含敏感信息的内容进行哈希处理。我可以在我的连接字符串中使用AD身份验证，并指定连接应该是可信的，但整个区块仍将根据我的Active+Directory帐户进行加密。当你出现并试图维护包时，它将无法解密敏感数据，因为你不是我。|70678|解决这个问题的两种方法是使用共享密钥(EncryptSensitiveWithPassword/EncryptPackageWithPassword)，它处理起来很麻烦，而且它违背了整个保密精神，因为每个人都知道这个秘密。您已经确定的另一种方法是DontSaveSensitive，这就是我要解决的所有问题。|70679|要克服的问题是，使用DontSaveSensitive时，您每次保存时，SSIS都会从可能保留用户名和密码的位置清除任何有关用户名和密码的信息-比如连接管理器。2005/2008年避免这种情况的策略是在运行时使用配置或显式覆盖来提供用户名和密码。我的典型方法是使用基于表的配置，而不是基于XML的配置，因为我更擅长保护表中的敏感数据，而不是在文件系统上处理ACL。对于多个开发人员和基于文件的配置，我们遇到的另一个挑战是，要么每个人都必须设置相同的文件系统(而我们开发人员是独特的彩虹雪花，所以这是不太可能的)，要么我们需要使用网络共享文件，这是很棒的，直到有人给它添加自己的值并破坏它，或者删除您的更改或任何其他挑战。|70680|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|H|X|2J|7|0|0|7|R|0|3F|H|0|A|H|0^^$0|@$1|2|3|4|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|1R|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|1S|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|1T|8|@]|9|@]|A|$]]|$1|G|3|-4|5|6|7|1U|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|1V|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|1W|8|@]|9|@]|A|$]]|$1|L|3|M|5|E|7|1X|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|1Y|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|1Z|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|20|8|@]|9|@]|A|$]]|$1|R|3|K|5|6|7|21|8|@]|9|@]|A|$]]|$1|S|3|T|5|E|7|22|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|23|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|24|8|@]|9|@]|A|$]]|$1|W|3|Q|5|6|7|25|8|@]|9|@]|A|$]]|$1|X|3|K|5|6|7|26|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|E|7|27|8|@]|9|@]|A|$]]|$1|10|3|-4|5|6|7|28|8|@]|9|@]|A|$]]|$1|11|3|-4|5|6|7|29|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|2A|8|@]|9|@]|A|$]]|$1|14|3|-4|5|6|7|2B|8|@]|9|@]|A|$]]|$1|15|3|16|5|E|7|2C|8|@]|9|@]|A|$]]|$1|17|3|-4|5|6|7|2D|8|@]|9|@]|A|$]]|$1|18|3|-4|5|6|7|2E|8|@]|9|@]|A|$]]|$1|19|3|1A|5|6|7|2F|8|@$1B|2G|1C|2H|1D|1E]|$1B|2I|1C|2J|1D|1F]]|9|@]|A|$]]|$1|1G|3|1H|5|6|7|2K|8|@]|9|@]|A|$]]|$1|1I|3|1J|5|6|7|2L|8|@$1B|2M|1C|2N|1D|1F]]|9|@]|A|$]]|$1|1K|3|1L|5|6|7|2O|8|@$1B|2P|1C|2Q|1D|1F]]|9|@]|A|$]]|$1|1M|3|1N|5|6|7|2R|8|@$1B|2S|1C|2T|1D|1F]]|9|@]|A|$]]|$1|1O|3|-4|5|6|7|2U|8|@]|9|@]|A|$]]]|1P|$]]

When you open/run a package, an OnInformation event is fired that says something like 

<blockquote>
 The package is attempting to configure from the XML file "c:\ssisdata\so_56776576.dtsconfig".
</blockquote>

When Visual Studio/SSDT opens/runs a package which says it uses configuration but for reasons, cannot get them, you should then see messages like

<blockquote>
 Warning loading so_56776576.dtsx: Failure importing configuration file: "c:\ssisdata\so_56776576.dtsconfig"
</blockquote>

and 

<blockquote>
 Warning loading so_56776576.dtsx: The configuration file "c:\ssisdata\so_56776576.dtsconfig" cannot be found. Check the directory and file name.
</blockquote>

and 

<blockquote>
 Warning loading so_56776576.dtsx: Failed to load at least one of the configuration entries for the package. Check configuration entries for "Configuration 1" and previous warnings to see descriptions of which configuration failed.
</blockquote>

If someone has manually edited the config file and broken the XML, you'd see a warning like

<blockquote>
 Cannot load the XML configuration file. The XML configuration file may be malformed or not valid
</blockquote>

The important thing to note with regard to configuration - if a configuration cannot be found, SSIS will continue along with the design time values. That is why it is crucial to check the warnings emitted when your package runs. If you are running manually, ensure that you have <code>/rep ew</code> specified so you report Errors and Warnings.

<h2>Guesses as to root cause</h2>

The package has the protection level of <code>EncryptSensitiveWithUserKey</code> which means the AD credentials of the package creator are used to hash things that might have sensitive information in them. I could be using AD authentication in my connection string and specify that the connection should be trusted but that entire block is still going to get encrypted against my Active Directory account. When you come along and attempt to maintain the package, it's not going to be able to decrypt the sensitive data as you are not me. 

The two ways around that are to use a shared key (EncryptSensitiveWithPassword/EncryptPackageWithPassword) which is cumbersome to deal with plus it goes against the whole spirit of secrecy since everyone knows the secret. The other approach as you've identified is <code>DontSaveSensitive</code> and that's my go to for all of this.

The problem to be overcome is that with <code>DontSaveSensitive</code> is that every time you save, SSIS is going to wipe out any knowledge of user name and password from places that might be holding on to it - like a connection manager. The 2005/2008 strategy to hedge against this was to use Configuration or explicit overrides at run time to supply user name and password. My typical approach was to use configuration based on a table instead of XML as I was better at securing sensitive data in a table than I was mucking with ACL on the file system. The other challenge we had with multiple developers and file based configuration was that either everyone had to set their file systems up the same (and we developers are unique rainbow snowflakes so that's unlikely) or we need to use a network shared file which is great until someone adds their own values to it and breaks it or removes your changes or any of a host of other challenges.

I have created a SSIS package and I am trying to run it locally. We use package configurations that point to sql tables and a XML config file. The package ran successfully for about a week, even when deployed to a SQL Server Agent Job in our STAGE environment.

Now, the only way I can get the package to run is by not using the Package Configurations and choosing EncryptSensitivewithPassword. If I change the package to DontSaveSensitive, I continuously get the error below:

<blockquote>
 An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'Test_User'.".
 Error: 0xC020801C at AgentCompany, Lookup [37]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Test" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
</blockquote>

It is so strange that about a week ago, this package ran fine with the configurations and the DontSaveSensitive Option.

I have updated the config file to ensure that it is establishing the connection string to the appropriate database. I also test the connectivity on the connection managers and they all test successfully.

I also double checked the SQL Database where the user is trying to connect to ensure that it has permissions there and it does.

I am very confused. Please Help!

Updating dtsconfig file
Re-creating the connection managers
Making some DFT task DelayValidation to true
Changing the RunTime to 32 bit
EncrpytPasswordSensitive with package configs removed---This works but this is not the standard at my company and this is not how I developed and tested the package before

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER- have tried everything

Visual Studio

身份验证

文件系统

SQL数据库

SQL Server

数据库

我已经创建了一个SSIS包，并且正在尝试在本地运行它。我们使用指向sql表和XML配置文件的包配置。该包成功运行了大约一周，即使在我们的STAGE环境中部署到SQL Server代理作业时也是如此。现在，让包运行的唯一方法是不使用包配置并选择EncryptSensitivewithPassword。如果我将程序包更改为...

问SSIS错误代码DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER-已尝试所有方法
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SSIS错误代码DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER-已尝试所有方法EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SSIS错误代码DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER-已尝试所有方法
EN