Sql-server – Linked server from MSSQL to Oracle Issue

linked-serveroraclesql server

Last week we began having an issue with a Linked Server to an Oracle database on one of our MSSQL Servers. If I use the linked server on the MSSQL server it works fine. However, if i try to use it remotely through SSMS it throws the below error. But, if i log in to the SQL Server remotely with SSMS and an SQL sysadm account it does work. Server is on mixed mode and normally we connect using window credentials.

OLE DB provider "OraOLEDB.Oracle" for linked server "*****" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "*******".

This worked fine up until last week when it just seemed to stop working.

The same server on our DR site with the same linked server works fine.

We have checked the provider settings against a working Linked Server and they are fine. We have rebooted the Server. Checked TNSNAMES is matching and correct. I am now completely out of ideas of what to try next and wondered if anybody out there has had a similar problem and has been able to resolve the issue. I could try to reinstall the Oracle ODAC but trying to avoid this.

Thanks for any help or advice you can give.

Richard

Best Answer

If you're able to select the 'Oracle Provider for OLE DB' option in the "Provider" dropdown, then I'd suggest trying the following (via the GUI):

Product Name: ###.###.#.###:####/SRV1

Data Source: ###.###.#.###:####/SRV1

Where ###.###.#.###:### is your Oracle Instance's IP Address (with standard 1521 or 152# port), and SRV1 is your Service name.

Just make sure the ORACLE user you're using to get in is correct in the "Security" section.

This option allows you to bypass the TNSNames file if you want to confirm that there's a connection (TNSPing is my go to, just like Gerald H. Pille said in the comments above).

Or if you want to go about it via T-SQL:

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName'
                             , @srvproduct=N'###.###.#.###:####/SRV1'
                             , @provider=N'OraOLEDB.Oracle'
                             , @datasrc=N'###.###.#.###:####/SRV1'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedServerName'
                                ,@useself=N'False'
                                ,@locallogin=NULL
                                ,@rmtuser=N'OracleUserHere'
                                ,@rmtpassword='passwordhere'
GO

Then go back in and change your options via the GUI (RPC, RPC Out, etc.)

Related Question