SQL Agent作业-连接可能配置不正确，或者您对此连接没有正确的权限？ [英] SQL Agent Job - Connection may not be configured correctly or you may not have the right permissions on this connection?

问题描述

通过SQL代理
运行SSIS程序包时出现此错误，无法获取连接 ORACLE ADO.NET。连接可能配置不正确，或者您对此连接没有正确的权限。

当我以SQL Agent用户身份登录并直接运行ssis软件包时，精细。然后，当我通过SQL代理作业执行它时，它将失败。

我已经广泛阅读了有关该主题的内容，并且似乎有很多建议涉及到如何登录，配置代理帐户等，等等。

我正在使用ADO.NET连接登录到Oracle数据库。连接字符串如下（数据源，用户标识和密码已更改）：

数据源= DATASOURCE；用户ID = userid；密码=密码；持久安全信息= True; Unicode = True;

我正在使用程序包配置从注册表设置中加载它。为了检查我得到的字符串是否正确，我将其写入临时日志表中。我一定会从正确的注册表设置中获取所需的字符串。

我已经通过PL / SQL开发人员测试了oracle登录凭据，这使我可以很好地登录。

<据我所知，由于我为Oracle连接使用了明确的用户名和密码，所以运行SSI pacakge的用户无关紧要。我能看到的唯一失败点是从注册表中读取信息，但这看起来还不错。

我真的很困惑，我必须承认，

非常感谢，
James

解决方案

好吧，在经历了很多痛苦之后，找到了它。

在一种环境下运行正常，但在另一种环境下运行不正常，因此我启动了Process Monitor（ http://technet.microsoft.com/zh-cn/sysinternals/bb896645.aspx ），并通过SQL Agent作业运行了一个程序包，比较在每个环境中遭受攻击的系统实体。

在出现故障的环境中，在进行批量传输操作时，程序包尝试获取Oracle 11客户端DLL，然后挂

我知道已经安装了该文件，而且DLL路径是系统环境设置。经过进一步调查，发现该服务器自从Oracle Client安装以来一直没有重新启动，并且SQL Server Agent进程没有被回收。

是的，您能相信吗？ ，旧的帮助台修复程序您可以重新启动计算机吗？

叹息！

I'm getting this error when running an SSIS package through SQL Agent Failed to acquire connection "ORACLE ADO.NET". Connection may not be configured correctly or you may not have the right permissions on this connection.

When I log on as the SQL Agent User and run the ssis package directly it is fine. When I then execute it through the SQL agent job, it fails.

I've read around extensively on this topic, and it seems a lot of the advise concerns how you are logged in, configuring of proxy accounts, etc, etc, etc, none of which has been helpful.

I am logging onto an Oracle database with an ADO.NET conncetion. The connection string is as follows (datasource, userid and password have been changed):

Data Source=DATASOURCE;User ID=userid;Password=password;Persist Security Info=True;Unicode=True;

I'm loading this from a registry setting using package configuration. To check that I am getting the correct string, I am writing it into a temporary log table. I am definately getting the string I need from the correct registry setting.

I've tested the oracle login credentials though PL/SQL developer, and it lets me login just fine.

As far as I can tell, as I'm using an explicit user name and password for the Oracle connection it just shouldn't matter who the SSIs pacakge is run as. The only point of failure that Ican see would be the reading of the information from the registry, but that seems fine.

I'm really quite baffled, I must confess, and would appreciate any help some of the splendid experts here can offer.

Many thanks, James

解决方案

Ok, tracked this one down after quite a lot of pain.

It was working fine on one environment, but not another, so I fired up Process Monitor (http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx) and ran a package through the SQL Agent job, comparing which system entities were hit on each enviroment.

On the failing environment, at the point of the bulk transfer operation, the package attempted to get the Oracle 11 client DLL, and then hung.

I knew that this was installed, and, moreoever, the DLL path was a system environment setting. After further investigation it was revealed that the server had not been rebooted since the Oracle Client install and the SQL Server Agent process had not bee recycled.

Yes, can you believe it, the old helpdesk fix "Can you reboot your computer?" worked.

Sigh!

这篇关于SQL Agent作业-连接可能配置不正确，或者您对此连接没有正确的权限？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！