This discussion is archived
8 Replies Latest reply: May 16, 2012 2:49 AM by Anil Pinto RSS

Oracle linked server on SQL Server 2005; cannot access from remote

Em Newbie
Currently Being Moderated
I'm a newbie, forced into being a database query-writer by necessity, so please bear with my horrible explanations. I have google-searched this to the end of google, and cannot find an answer for my particular problem, though I've been able to solve many issues to get here -- so, I am trying.

I have a SQL Server database set up on a SQL 2005 server, and I need to link Oracle (11g) data to it.

I've successfully installed the necessary Oracle software onto that Windows 2003 server housing my SQL 2005 database. I set up my linked server, and can query when I remote desktop into the Win2003 server and run SSMS from there.

HOWEVER, I have SSMS 2008 installed on MY PC. This PC can also (independent of SQL) access the same Oracle database - I have client software set up on it. But when I try to access the Linked Server, remotely, from my PC, using SSMS 2008, I get errors:

OLE DB provider "OraOLEDB.Oracle" for linked server "eplive" 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 "eplive".

I can tnsping the database just fine from my PC.

I can, as a workaround, continue to Remote Desktop to the 2005 server and use SSMS there -- it's just a PITA. And I hate when things that seem like they should work, don't work.

Any things I can try, that maybe I haven't, already?

Thank you!
Em
  • 1. Re: Oracle linked server on SQL Server 2005; cannot access from remote
    orafad Oracle ACE
    Currently Being Moderated
    Em wrote:

    HOWEVER, I have SSMS 2008 installed on MY PC. This PC can also (independent of SQL) access the same Oracle database - I have client software set up on it. But when I try to access the Linked Server, remotely, from my PC, using SSMS 2008, I get errors:
    Just to understand your setup, before trying to fix anything -

    So you've only got SSMS, as a client tool to access and manage the 2005 Server on Win 2003, on your PC. Not a second MS Sql Database service, right?

    In that case, you don't need the Oracle client software on that pc, in a Linked server context. You'd only need it on the MS Sql (2005) host.

    How are you accesing the Linked server from SSMS 2008? First connected to the remote 2005 db engine?

    Where is the Oracle server running? On a third machine?

    OLE DB provider "OraOLEDB.Oracle" for linked server "eplive" 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 "eplive".
    12154 means that, using configured naming methods, the connect id (aka. tns alias) could not be resolved to a connect descriptor i.e. address parameters to be used in next step of connection.

    To simplify, you could try entering <hostname>:<port>/<servicename> (fill in Oracle related details) for Data source in the Linked server properties.

    >
    I can tnsping the database just fine from my PC.
    In this case, tnsping from the Win 2003 instead. That's where Linked server does the lookup.

    Edited by: orafad on Feb 1, 2012 2:37 AM
  • 2. Re: Oracle linked server on SQL Server 2005; cannot access from remote
    Em Newbie
    Currently Being Moderated
    Sorry -- I didn't see your reply until now. I hope I can answer your questions.

    orafad wrote:

    Just to understand your setup, before trying to fix anything -

    So you've only got SSMS, as a client tool to access and manage the 2005 Server on Win 2003, on your PC. Not a second MS Sql Database service, right?

    In that case, you don't need the Oracle client software on that pc, in a Linked server context. You'd only need it on the MS Sql (2005) host.

    How are you accesing the Linked server from SSMS 2008? First connected to the remote 2005 db engine?

    Where is the Oracle server running? On a third machine?
    I have SSMS 2008 as a client tool on my pc. I have SSMS 2005 on the server on which my 2005 SQL databases (and the link to my Oracle Linked Server).

    I have the Oracle 11g client on both - it's on MY pc because I need it for other things; it's on the SQL database server because I'm pretty sure I needed it to create the linked server connection in SSMS, there.
    To simplify, you could try entering <hostname>:<port>/<servicename> (fill in Oracle related details) for Data source in the Linked server properties.
    I don't know the port #!
    In this case, tnsping from the Win 2003 instead. That's where Linked server does the lookup.
    There is NO tnsping installed, apparently, with Oracle 11g client? I cannot find tnsping.exe on that server. The client is there, my entries pointing to my TNS_ADMIN are in my registry...

    Any help is appreciated -- not sure why I didn't get an email notification of your previous reply, but I'll keep my eyes open, now...

    Thanks
  • 3. Re: Oracle linked server on SQL Server 2005; cannot access from remote
    Em Newbie
    Currently Being Moderated
    I have now set up my linked (Oracle) server on an SQL Server 2008 server, and can use SSMS 2008 on that server to run OPENQUERY queries against my Oracle database, successfully.

    I still cannot, however, use the SSMS 2008 that I have on MY DESKTOP PC to connect to that linked server.

    I can TNSPING the Oracle service just fine from my PC (as I can from the SQL Server).

    There's no reason that I can think of that the linked server, when being accessed from my desktop, should NOT be able to find this database/service.

    Any ideas?
  • 4. Re: Oracle linked server on SQL Server 2005; cannot access from remote
    Em Newbie
    Currently Being Moderated
    I've been successful in setting up a linked server on our SQL Server 2008 server to an Oracle 11g database, but still cannot access this linked server when I run SSMS from a PC other than the SQL Server.

    Let me re-hash, as I've done some changing:

    1) I have administrative rights on all of our servers;

    2) SISDB2 is a 64-bit Windows 2008 R2 server, running SQL Server 2008.

    3) I have set up a linked server called EPLIVE on SISDB2, using provider OraOLEDB.Oracle. I was somehow successful in making this connection by installing both the 32-bit and 64-bit Oracle clients (11.2.0.1.0). Relevant (I believe) options installed are Oracle Net, Oracle Net Listener, Oracle Netca Client, Oracle ODBC Driver, Oracle Services for MTS, Oracle Provider for OLE DB, Oracle Data Provider for .NET, Oracle Providers for ASP.NET. Not sure I needed all those options, but that's where I am.

    4) There are no local copies of TNSNAMES.ORA or SQLNET.ORA on SISDB2\C:. There is a registry entry called TNS_ADMIN that points to a SHARED tnsnames.ora on our network (UNC addressing) in both the HKLM\SOFTWARE\ORACLE and HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home1 branches of the registry.

    5) I can TNSPING my eplive SID/Service (sorry -- I get confused at the difference between SID and SERVICE) from a command prompt on SISDB2.

    6) I can sqlplus into eplive and query the database, from a command prompt on SISDB2.

    7) On SISDB2, in SSMS, I can test the connection to EPLIVE, and run OPENQUERies against it.

    8) Under the Provider properties, Allow InProcess is enabled, on SISDB2.

    On my 32-bit Windows 7 desktop, I run SSMS 2008 locally.
    1) I see the linked server when I connect to SISDB2.

    2) Numbers 4, 5 and 6 from above are all also true on my Desktop PC.

    3) Number 7 from above is NOT true -- I get an ORA-12154 error when I try to test the connection to the linked server EPLIVE on SISDB2 from a remote copy of SSMS 2008.

    I've undoubtedly left off some information about my configuration, but I do not understand why, if EPLIVE is a recognized SID on the desktop, I cannot use the linked server that is functional in a "remote" copy of SSMS 2008.

    I would greatly appreciate any clues that can resolve this, for me. I've googled the daylights out of this problem, and find lots of things to try, but nothing that has resolved this.
  • 5. Re: Oracle linked server on SQL Server 2005; cannot access from remote
    925223 Newbie
    Currently Being Moderated
    For SQL clusters this response ...

    To simplify, you could try entering <hostname>:<port>/<servicename> (fill in Oracle related details) for Data source in the Linked server properties.

    ... should have flashing lights and a "Start here first" sign.

    It worked.

    Thank you
  • 6. Re: Oracle linked server on SQL Server 2005; cannot access from remote
    Em Newbie
    Currently Being Moderated
    Holy moly. Worked for me, too. Someone (you?) had suggested that to me earlier, but I was wrapped in stress, at the time, and couldn't figure out what to use for PORT, so I avoided trying.

    Got it now, though -- thank you very much.
  • 7. Re: Oracle linked server on SQL Server 2005; cannot access from remote
    Anil Pinto Newbie
    Currently Being Moderated
    after following above changes, not able to connect, please help me out.
    is there any changes i need to do apart from this.


    thanks in advnace
  • 8. Re: Oracle linked server on SQL Server 2005; cannot access from remote
    Anil Pinto Newbie
    Currently Being Moderated
    after including the GO at the end of the select stmt its working fine.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points