SQL Server Linked with Oracle Database
I'm using SQL Server 2017 and am trying to set up a linked server to an Oracle database. I installed ODAC and updated my PATH to include the Oracle root from ODAC installation.
Everything looks good and I am able to choose the Oracle Provider for OLE DB when setting up the server, but when I save my configuration, I get the following error:
===================================
The linked server has been created but failed a connection test. Do you want to keep the linked server?
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry) at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd, Boolean retry) at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection() at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerProperties.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)
===================================
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "UDW". (.Net SqlClient Data Provider)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.5081&EvtSrc=MSSQLServer&EvtID=7302&LinkId=20476
Server Name: localhost Error Number: 7302 Severity: 16 State: 1 Procedure: sp_testlinkedserver Line Number: 1
Program Location:
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
This error message is not exactly helpful, and if you follow the Microsoft link, it redirects you to the Microsoft homepage (thanks Microsoft!)
Is there a way to test if this is a problem of not finding TNSNAMES vs some sort of firewall issue on the Oracle server?
I am able to set up a LinkedServer on another database in the same network so I can confirm that the TNSNAMES is exactly the same and the credentials I am using are valid.
The key to this issue is this line:
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "UDW". (.Net SqlClient Data Provider)
The solution is to go to the provider options and check allow inprocess. Shown below:
I can't explain exactly what it does, but this guy does a pretty good job.
- Which of definitions of database schema is correct?
- How to get isolation level in EF for MSSQL?
- Generate SQL Create Scripts for existing tables with query
- Insert multiple rows WITHOUT repeating the "INSERT INTO ..." part of the statement?
- Index for two columns that will be used in an operation on where clause?
- How do you update a datetime field in a table every time another field in the same table gets updated?
- Pros & Cons of TRUNCATE vs DELETE FROM
- comparing and contrasting Sybase and SQL Server
- How does sql server choose values in an update statement where there are multiple options?
- How to disconnect all users in sql server except sa?
- Select Consecutive Numbers in SQL
- SQL Find consecutive numbers in groups
- Spring Quartz configuration for connecting MS SQL server
- The most elegant way to generate permutations in SQL server
- Check column has 'letter' or '-' then bring value from another column
- Reading child XML node from column
- Combine JSON objects from multiple rows into a single object
- Find all stored procedures that reference a specific column in some table
- Laravel 10 stopped working with SQL Server Database
- Using TransactionScope around a stored procedure with transaction in SQL Server 2014
- Entity storing it's status as a FK to a status table. Can I update the FK once the status changes?
- How can I convert a Sql Server 2008 DateTimeOffset to a DateTime
- I need to replace old path's images to new path with the same image
- Format date as yyyy-mm-dd hh:mm:ss.000
- Does a foreign key automatically create an index?
- How do I select only the latest entry in a table for each group so each 'latest' is a single row?
- "The certificate chain was issued by an authority that is not trusted" when connecting DB in VM Role from Azure website
- Kafka Connect SqlServer Configuration (SSL)
- SQL-Server: Is there a SQL script that I can use to determine the progress of a SQL Server backup or restore process?
- TSQL Email Validation (without regex)