Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "OraOLEDB.Oracle" for linked server xxxx

今天遇到了一個關於LINKED SERVER查詢報錯的案例，鏈接伺服器鏈接ORACLE資料庫，測試沒有錯誤，但是執行腳本的時候，報如下錯誤：

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "xxxx" reported an error. Access denied.

Msg 7301, Level 16, State 2, Line 1

Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "OraOLEDB.Oracle" for linked server "xxxx".

其實以前遇到過類似的案例，但是這次案例發生在SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)下，LINKED SERVER使用 Oracle Provider for OLE DB驅動，跟之前遇到的案例Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "xxxxxxx".有一些區別。解決方案相同，需要在Oracle Provider for OLE DB驅動的選項裡面勾選“Allow inporcess",或者修改註冊表，具體參考下麵Metalink官方文檔。（另外，今天還遇到了很奇葩的事情，設置後，SQL 語句在其他資料庫執行OK，但是在master庫下麵就一直報這個錯誤，但是一段時間後又OK了。十分奇怪，暫時不清楚具體原因）

Metalink上Using Oracle OLE DB Provider and MS SQL Server To Acccess Oracle Database Raise Errors 7399 And 7301 (文檔 ID 396648.1)的詳細介紹

SYMPTOMS

You are unable to connect to the Oracle database when using Microsoft SQL Server's Linked Server and the Oracle Provider for OLE DB and receive errors messages like

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "TEST" reported an error. The provider reported an unexpected catastrophic failure.

Msg 7301, Level 16, State 2, Line 1

Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "OraOLEDB.Oracle" for linked server "TEST".

CAUSE

The Oracle Provider for OLE DB has been configured to run out-of-process (in a separate process than the SQL Server process, typically DLLHOST.EXE) but it is mandatory to run the Oracle Provider for OLE DB as in-process to function properly with SQL Server.

SOLUTION

Please apply solution from

Note:333327.1 Error "Could not execute query against OLE DB provider 'OraOLEDB.Oracle'" when Querying Against an Oracle Database using Microsoft SQL Server Linked Server and the Oracle Provider for OLE DB

which describes the same problem but with different symptoms.

REFERENCES

NOTE:333327.1 - Error "Could not execute query against OLE DB provider 'OraOLEDB.Oracle'" when Querying Against an Oracle Database using Microsoft SQL Server Linked Server and the Oracle Provider for OLE DB

另外，關於文檔333327.1 ——Error "Could not execute query against OLE DB provider 'OraOLEDB.Oracle'" when Querying Against an Oracle Database using Microsoft SQL Server Linked Server and the Oracle Provider for OLE DB (文檔 ID 333327.1)的具體內容如下：

APPLIES TO:

SYMPTOMS

You are unable to connect to the Oracle database when using Microsoft SQL Server's Linked Server and the Oracle Provider for OLE DB. When issuing the following query from Microsoft's SQL Query Analyzer  

SELECT * FROM DEV..SCOTT.EMP

You receive the following error

Server: Msg 7320, Level 16, State 2, Line 1
  Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
  OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute returned 0x80040155].

If you change the query so that it will not return any rows it runs successfully

SELECT * FROM DEV..SCOTT.EMP where 1=0

CAUSE

The Oracle Provider for OLE DB has been configured to run out-of-process (in a separate process than the SQL Server process, typically DLLHOST.EXE).  The Oracle Provider for OLE DB must run in-process to function properly with SQL Server.

By SQL*Net tracing the failing query you can look at the TNS information inside of a SQL*Net trace you can see the difference between a provider running IN and OUT of process:

  In-Process Trace:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=gbednars-pc)(PORT=1521)))
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl.rmmslang.us.oracle.com)
(CID=(PROGRAM=C:\PROGRA~1\MI6841~1\MSSQL\binn\sqlservr.exe)(HOST=GBEDNARS-PC)
(USER=SYSTEM))))

  Out-Of-Process Trace:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=gbednars-pc)(PORT=1521)))
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl.rmmslang.us.oracle.com)
(CID=(PROGRAM=C:\WINDOWS\System32\DllHost.exe)(HOST=GBEDNARS-PC)(USER=SYSTEM))))

In the In-Process trace the TNS information shows us that the Oracle Provider for OLE DB is running under the sqlservr.exe process.  In the Out-Of-Processtrace we see that the same provider is running under the DllHost.exe process.  DllHost is used as a surrogate process in place of SQL Server to host out-of-process executions and clearly shows us that the Oracle OLE DB provider has been configured this way.

SOLUTION

1. Open the registry and check the value of the AllowInProcess key being used by the Oracle Provider for OLE DB
   

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\OraOLEDB.Oracle
   
        AllowInProcess     REG_DWORD     0x00000000 (0)

2. If the AllowInProcess key has been set to a value of 0 then it is configured to run out-of-process.  Change the value from 0 to 1 or if the key does not exist, create it as a DWORD with a value of 1.  The value 1 is also the default setting signifying in-process.
   

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\OraOLEDB.Oracle
   
        AllowInProcess     REG_DWORD     0x00000001 (1)
   
   OR
   
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\OraOLEDB.Oracle
        AllowInProcess     REG_DWORD     0x00000001 (1)        
           

NOTE:  Microsoft states that the out-of-process setting AllowInProcess=0, is not to be used with any provider other than SQLOLEDB (Microsoft's OLEDB Provider for SQL Server).  See the following link for more information:

  Microsoft Knowledge Base Article ID 833388
       You cannot create out-of-process providers with MDAC OLE DB components

Additionally, the Oracle Provider for OLE DB Developer's Guide states that the Oracle Provider for OLE DB (OraOLEDB) is an in-process server.

參考資料

Using Oracle OLE DB Provider and MS SQL Server To Acccess Oracle Database Raise Errors 7399 And 7301 (文檔 ID 396648.1)

Error "Could not execute query against OLE DB provider 'OraOLEDB.Oracle'" when Querying Against an Oracle Database using Microsoft SQL Server Linked Server and the Oracle Provider for OLE DB (文檔 ID 333327.1)