SQL 代理作业 - 连接可能未正确配置，或者您可能对此连接没有正确的权限？
通过 SQL 代理运行 SSIS 包时出现此错误 无法获取连接“ORACLE ADO.NET”。连接可能未正确配置，或者您可能对此连接没有正确的权限。
当我以 SQL 代理用户身份登录并直接运行 ssis 包时，一切都很好。当我然后通过 SQL 代理作业执行它时，它失败了。
我已经广泛阅读了有关此主题的内容，似乎很多建议都涉及您如何登录、配置代理帐户等，等等，但这些都没有帮助。
我正在使用 ADO.NET 连接登录 Oracle 数据库。连接字符串如下（数据源、用户 ID 和密码已更改）：
数据源=DATASOURCE;用户 ID=用户 ID;密码=密码;Persist Security Info=True;Unicode=True;
我正在使用包配置从注册表设置加载它。为了检查我是否获得了正确的字符串，我将其写入临时日志表中。我肯定会从正确的注册表设置中获取我需要的字符串。
我已经通过 PL/SQL Developer 测试了 oracle 登录凭据，它让我可以正常登录。
据我所知，由于我对 Oracle 连接使用显式用户名和密码，因此 SSI 包以谁的身份运行并不重要。我能看到的唯一失败点是从注册表中读取信息，但这似乎很好。
我必须承认，我真的很困惑，并且非常感谢这里一些出色的专家可以提供的任何帮助。
非常感谢， 詹姆斯
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
好吧，在经历了相当多的痛苦之后找到了这个。
它在一种环境中工作正常，但在另一种环境中则不然，因此我启动了 Process Monitor (http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx）并通过 SQL 代理作业运行一个包，比较每个环境上哪些系统实体受到攻击。
在失败的环境中，在批量传输操作时，程序包尝试获取 Oracle 11 客户端 DLL，然后挂起。
我知道这个已经安装了，而且DLL路径是系统环境设置。经过进一步调查发现，自从安装了 Oracle 客户端以来，服务器就没有重新启动过，而且 SQL Server Agent 进程也没有被回收。
是的，您能相信吗，旧的帮助台修复了“您可以重新启动计算机吗？”工作了。
叹！
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!
我们在客户端上运行连接到 Oracle 的包，然后将其存储在我们的 sql 服务器实例上时遇到了问题。我们发现的解决方法是将包属性、保护级别更改为“不保存敏感数据”，并且出于安全目的，我们对包配置中的用户名和密码进行了加密，该配置由 sql server 中的 udf 解密。当然，在尝试整个加密部分之前，我建议将用户名和密码放入包配置中，而不对值进行加密，以查看更改保护级别设置是否可以解决您的特定问题。我希望这有帮助。
We had issues at a client with running packages connecting to Oracle before stored on our sql server instance. The work around we found was to change the package property, protection level, to "Dont save Sensitive Data" and for security purposes, we encrypted the username and password in the package configuration that was decrypted by a udf in sql server. Of course, before you try the whole encryption part, I would recommend putting the username and password in the package configuration without encrypting the values to see if changing the protection level setting is the solution to your specific problem. I hope this helps.
当 tnsnames.ora 文件没有环境的有效条目时，我收到此错误
I was getting this error when tnsnames.ora file did not have a valid entry for the environment