SSIS Sql Server配置登录错误 [英] SSIS Sql Server Configuration Logon Error

问题描述

我一直在努力打开和关闭几个月，真的需要一个解决方案。

我们通过Visual Studio 2008在SQL Server 2008 R2上使用SSIS并配置了我们的使用SQL Server配置的包。 DEV上的一切都很好但是当我们去UAT时，包裹会在我们的UAT帐户下执行，正如您所期望的那样。



当包通过dtexec运行时，SSIS尝试从DEV数据库加载配置值（因为这是连接管理器保存在包中的方式）尽管连接到UAT传递给dtexec。这会导致错误，因为UAT帐户无权访问DEV数据库。记录为：

 已开始：  09:44:52  
 错误：  2013-10-17   09:44:53 。 29  
 代码：  0xC0202009  
 来源：< packagename> xxxxxx 连接  manager xxxxx< CMName> xx
 描述：  SSIS  错误 代码  DTS_E_OLEDBERROR 。    OLE   DB  错误 已发生  。 错误 代码：  0x80040E4D 。 
    OLE   DB  < span class =code-leadattribute> record     available 。 来源： Microsoft   SQL  服务器  Native  客户端  10  .0 Hresult：  0x80040E4D  说明： 登录 失败 
  for   user 'xxx< UserName>'。。 
 结束 错误 

然后包继续连接到UAT数据库，从那里成功加载配置并正常执行。然而，日志正在引起支持，不断认为这是任何后来的非相关错误的原因。

解决方案

您应该使用适当的部署策略文件系统部署或SQL Server部署。



您需要适当设置包的保护级别。然后在.dtsconfig文件中正确指定

配置值。在配置文件中设置密码。然后你可以尝试。



如果你在sql代理中安排工作，那么你应该使用sql代理帐户来运行这个包。





让我知道你是否还有任何困惑。



问候，

Mubin

您好



我通过查看一些SQL Server配置示例找到了答案最好的程序。



通过向包中添加单独的xml配置（它也适用于环境变量）我可以为用于SQL的连接管理器设置连接字符串服务器配置。



这样，配置的ConnectionString所需的任何更改都在配置阶段完成，因为包已加载。



这个链接给出了一个例子，使用环境变量 31天的-SSIS-SQL服务器的配置

I have been struggling with this on and off for a couple of months and really need to get a solution.
We are using SSIS on SQL Server 2008 R2 via Visual Studio 2008 and have configured our packages using SQL Server configuration. Everything is fine on DEV but when we go to UAT the packages are executed under our UAT account as you would expect.

When the package is run via dtexec, SSIS attempts to load configuration values from the DEV database (because this is how the connection manager is saved in the package) despite the connection to UAT being passed in to dtexec. This causes an error because the UAT account does not have access to the DEV database. This is logged as:

Started:  09:44:52
Error: 2013-10-17 09:44:53.29
   Code: 0xC0202009
   Source: <packagename>xxxxxx Connection manager "xxxxx<CMName>xx"
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed
 for user 'xxx<UserName>'.".
End Error

The package then continues to connect to the UAT database, successfully load the config from there and execute normally. However the log is causing support to constantly think that this is the reason for any later non-related errors.

解决方案

Hi,

You should use proper deployment strategy either File System Deployment or SQL Server Deployment.

You need to set Protection level of your package appropriately. then specify
Configuration values in .dtsconfig file properly. set password in configuration files. and then you can try.

if you are scheduling job in sql agent then you should use sql proxy account to run this package.


let me know if you still have any confusion.

Regards,
Mubin

Hi

I've managed to find an answer by going over some of the SQL Server Configuration examples and best procedures.

By adding a separate xml configuration to the package (it works with an environment variable too) I can set the connection string for the connection manager that is used for the SQL Server configuration.

That way any changes required to the ConnectionString for the configuration are done at the configuration stage as the package is loaded.

This linked gives an example, using an environment variable 31-days-of-ssis-sql-server-configuration

这篇关于SSIS Sql Server配置登录错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！