[Oracle][ODBC][Ora]ORA-12170: TNS:Connect timeout发生ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr fai

[Oracle][ODBC][Ora]ORA-12170: TNS:Connect timeout occurred ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

提问by Coco

I created a SSIS package to pull data from Oracle database to SQL server database. I have set up ODBC connection successfully on my local machine and on the server, I can connect to Oracle database using sqlplus on both environments. And my package run fine on my local machine. it also executed successfully when I run it from Package store on the server but the problem is when I run the package as sql job I got this error:

我创建了一个 SSIS 包来将数据从 Oracle 数据库提取到 SQL 服务器数据库。我已经在本地机器和服务器上成功设置了 ODBC 连接，我可以在两种环境中使用 sqlplus 连接到 Oracle 数据库。我的包在我的本地机器上运行良好。当我从服务器上的包存储中运行它时，它也成功执行，但问题是当我将包作为 sql 作业运行时，出现此错误：

Description:

描述：

System.Data.Odbc.OdbcException: ERROR [HY000] [Oracle][ODBC][Ora]ORA-12170: TNS:Connect timeout occurred ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [HY000] [Oracle][ODBC][Ora]ORA-12170: TNS:Connect timeout occurred at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection OdbcConnectionString constr OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection OdbcConnectionString connectionOptions) at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options Object poolGroupProviderInfo DbConnectionPool pool DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection DbConnectionFactory connectionFactory) at System.Data.Odbc.OdbcConnection.Open()

System.Data.Odbc.OdbcException: ERROR [HY000] [Oracle][ODBC][Ora]ORA-12170: TNS:Connect timeout发生ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [HY000] [ Oracle][ODBC][Ora]ORA-12170：TNS：连接超时发生在 System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection OdbcConnectionString constr OdbcEnvironmentHandle environmentHandle) 在 System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection externalConnection OdbcConnectionString connectionOptions) 在 System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options Object poolGroupProviderInfo DbConnectionPool pool DbConnection owningObject) 在 System.Data.ProviderBase.DbConnectionFactory。CreateNonPooledConnection(DbConnection owningConnection DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection externalConnection DbConnectionFactory connectionFactory) at System.Data.Odbc.OdbcConnection.Open()

Does it make any senses?

它有任何意义吗？

回答by Cameron Kerr

ORA-12170 indicates that the TCP connection to the database listener failed.

ORA-12170 表示与数据库侦听器的 TCP 连接失败。

If this is an error that happens all the time, then check your connection strings (particularly port numbers), firewalls and network ACLs.

如果这是一直发生的错误，请检查您的连接字符串（特别是端口号）、防火墙和网络 ACL。

If this is a transient error that doesn't always occur, or it the firewalls and network ACLs seen okay, then check for correct ARP behaviour. I've recently had this problem myself, and have blogged about it at http://distracted-it.blogspot.co.nz/2014/04/ora-12170-tnsconnect-timeout-resolved.html

如果这是一个并不总是发生的暂时性错误，或者防火墙和网络 ACL 没有问题，那么请检查 ARP 行为是否正确。我最近自己也遇到了这个问题，并在http://distracted-it.blogspot.co.nz/2014/04/ora-12170-tnsconnect-timeout-resolved.html 上写了博客

Hope it helps.

希望能帮助到你。

回答by Fordy

ORA-12170 can result due to a lack of permissions. The Sql Server Agent Service Account must have access to the Oracle Client folder. You can test if this is the problem by logging in to the server as an administrator and trying to run the job manually, if it works this way, then it could be permissions. To change the permissions:

由于缺乏权限，可能会导致 ORA-12170。Sql Server 代理服务帐户必须有权访问 Oracle 客户端文件夹。您可以通过以管理员身份登录服务器并尝试手动运行作业来测试这是否是问题，如果它以这种方式工作，则可能是权限问题。要更改权限：

• Browse to the Oracle folder, e.g. C:\Oracle64
• Right Click the folder, click Properties, Security Tab, Click Edit, Click Add
• Change "Locations" to be the machine name that you are on.
• Enter the name “NT Service\SqlServerAgent” (this must be exactly right) and click Check Names.
• Click OK, Apply, OK to get back to the security tab, you should see that “SQLSERVERAGENT” has a tick for Read & Allow, Read, List folder contents .

• 浏览到 Oracle 文件夹，例如 C:\Oracle64
• 右键单击文件夹，单击属性，安全选项卡，单击编辑，单击添加
• 将“位置”更改为您所在的机器名称。
• 输入名称“NT Service\SqlServerAgent”（必须完全正确）并单击“检查名称”。
• 单击“确定”、“应用”、“确定”返回安全选项卡，您应该看到“SQLSERVERAGENT”对 Read & Allow, Read, List folder contents 打勾 。

回答by Samuel Bulux

Sometimes this happens when you are accesing to a DBLINK in one of your querys, and the database pointed on the dblink is down, so you have to check that too.

有时，当您在查询之一中访问 DBLINK 时会发生这种情况，并且该 dblink 上指向的数据库已关闭，因此您也必须检查这一点。