oracle SQL 代理作业 - 连接可能未正确配置，或者您可能没有对此连接的正确权限？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1687084/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Agent Job - Connection may not be configured correctly or you may not have the right permissions on this connection?
提问by James Wiseman
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.
通过 SQL 代理运行 SSIS 包时出现此错误无法获取连接“ORACLE ADO.NET”。连接可能未正确配置，或者您可能没有此连接的正确权限。
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.
当我以 SQL 代理用户身份登录并直接运行 ssis 包时，一切正常。当我然后通过 SQL 代理作业执行它时，它失败了。
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):
我正在使用 ADO.NET 连接登录到 Oracle 数据库。连接字符串如下（数据源、用户名和密码已更改）：
Data Source=DATASOURCE;User ID=userid;Password=password;Persist Security Info=True;Unicode=True;
数据源=数据源；用户 ID=用户 ID；密码=密码；持久安全信息=真；Unicode=真；
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.
我已经通过 PL/SQL 开发人员测试了 oracle 登录凭据，它让我可以正常登录。
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.
据我所知，由于我为 Oracle 连接使用了明确的用户名和密码，因此 SSI pacakge 以谁的身份运行并不重要。我能看到的唯一失败点是从注册表中读取信息，但这似乎很好。
I'm really quite baffled, I must confess, and would appreciate any help some of the splendid experts here can offer.
我真的很困惑，我必须承认，并且非常感谢这里的一些出色的专家可以提供的任何帮助。
Many thanks, James
非常感谢，詹姆斯
采纳答案by James Wiseman
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.
它在一个环境中运行良好，但在另一个环境中运行良好，所以我启动了 Process Monitor ( http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx) 并通过 SQL Agent 作业运行了一个包，比较每个环境中哪些系统实体受到攻击。
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.
在失败的环境中，在批量传输操作时，程序包尝试获取 Oracle 11 客户端 DLL，然后挂起。
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.
我知道这个已经安装了，而且DLL路径是系统环境设置。经过进一步调查，发现自从 Oracle 客户端安装和 SQL Server 代理进程没有被回收后，服务器没有重新启动。
Yes, can you believe it, the old helpdesk fix "Can you reboot your computer?" worked.
是的，你能相信吗，旧的帮助台修复“你能重新启动你的计算机吗？” 工作。
Sigh!
叹！
回答by rfonn
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.
我们在客户端上遇到了问题，在将包存储到我们的 sql server 实例之前连接到 Oracle 的运行包。我们发现的解决方法是将包属性、保护级别更改为“不保存敏感数据”，并且出于安全目的，我们对包配置中的用户名和密码进行了加密，该用户名和密码由 sql server 中的 udf 解密。当然，在您尝试整个加密部分之前，我建议将用户名和密码放在包配置中而不加密值，以查看更改保护级别设置是否可以解决您的特定问题。我希望这有帮助。
