1

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

Improve this question
3
  • If your tnsnames.ora is correct, then your client (the oracle database) is not using that tnsnames.ora. You can check your tnsnames.ora from the client environment with tnsping. The client can find the tnsnames.ora if it is in a standard location ($ORACLE_HOME/network/admin, /etc) or through the environment variable TNS_ADMIN. Tnsping is important, you can overlook errors, like duplicate entries, a newline missing, a bracket too many, ...
    – Gerard H. Pille
    Commented Feb 16, 2018 at 12:41
  • @GerardH.Pille - in this scenario the oracle database does not use tnsnames at all. tnsnames.ora is strictly a client-side issue and in this case the oracle database is not the client.
    – EdStevens
    Commented Feb 16, 2018 at 22:05
  • @Richard - if, as you say, "This worked fine up until last week when it just seemed to stop working." then something changed last week. What was it? When looking at tnsnames.ora, you have to keep firmly in mind which machine is acting as the client that would use the file. Then on that machine, locate ALL possible tnsnames, and any setting of TNS_ADMIN, either in the registry or the user or system environment settings. In the case of multiple copies of tnsnames, there is an order of precedence.
    – EdStevens
    Commented Feb 16, 2018 at 22:10

1 Answer 1

Reset to default
1

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.)

Improve this answer
6
  • To bypass the TNSNAMES.ORA file you need to either put a full connect identifier in the linked server definition or use EazyConnect, which looks like ``//servername/sid` see, eg blogs.msdn.microsoft.com/dbrowne/2013/10/02/…
    – David Browne - Microsoft
    Commented Aug 23, 2018 at 19:38
  • @DavidBrowne-Microsoft not sure what you mean. The full connect identifier is in the code I posted. It's how I connect to my Oracle Databases from my SQL Servers throughout the day for ETL. Were your referring to the fact that EZConnect is an alternative?
    – SQLDevDBA
    Commented Aug 23, 2018 at 21:08
  • Looks like Oracle enhanced the Eazy Connect identifiers to no longer require the leading //. docs.oracle.com/cd/E11882_01/network.112/e41945/…
    – David Browne - Microsoft
    Commented Aug 23, 2018 at 22:00
  • Yes, that sounds correct. Also, great article. Wish I'd have known it was up as it would have saved me a headache. Bookmarked for future use!
    – SQLDevDBA
    Commented Aug 23, 2018 at 22:21
  • 2
    Oracle Database documentation is often excellent, but not very easy to find.
    – David Browne - Microsoft
    Commented Aug 24, 2018 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.