Unable to create a Oracle Linked Server in SQL 2008 Feb CTP. Is this a known issue?

Question

 

The Connection information from a Data Link file was used with both MSDAORA as well as OraOLEDB.Oracle but a linked server could not be created. The Data Link files tests for connection without a problem. The Microsoft link in the error may be neglected as it does not provide any help. For both providers the Allow inprocess option was enabled and SQL Server restarted before setting up the linked servers.

 

Is this a known issue?

 

Here is a typical message :

===================================

"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)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd)
   at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
   at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerProperties.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)

===================================

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORA10GXE".
OLE DB provider "OraOLEDB.Oracle" for linked server "ORA10GXE" returned message "ORA-01017: invalid username/password; logon denied". (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1300&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

------------------------------
Server Name: HODENTEK2
Error Number: 7303
Severity: 16
State: 1
Procedure: sp_testlinkedserver
Line Number: 1

------------------------------
Program Location:

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

 

Answer 1

The default security choice for the Linked Server is not good for establishing the Linked Server. I was able to set up the linked server using the oraOLEDB.Oracle provider with the "Connection will be made using the following security context" option.

 

I am yet to test the MSDORA provider.

 

One thing at a time.

 

Jayaram Krishnaswamy

Answer 2

Isn't MSDAORA quite outdated? I believe it it's only good for old versions like Oracle 8.

Answer 3

I don't know but it is listed as one of the Providers in the SQL Server 2008 Management Studio.

 

Answer 4

Mgmt Studio lists all providers installed on the SQL Server machine, so as long as the OS ships with
MSDAORA, it will be listed. I guess that at some point that Microsoft will stop including MSDAORA,
but as always with old software, it has to hang around for compatibiliy reasons.