Anyway to connect to Oracle on the fly without tnsnames entry?

  • Mr Corn Man

    Ten Centuries

    Points: 1268

    As the title indicates, I'm curious if anyone knows of a way to connect to an Oracle database from SQL 2008 SSMS, on the fly, using code logic as opposed to a tnsnames entry?

    It is a ridiculous process here to get a simple change made to the tnsnames file on the SQL server, it can take a week to get done for work that takes 30 seconds. Therefore, I'm looking for a way to fill the void between requesting a change to be made and the change being implemented.

    I'd like to be able to create a temporary Oracle connection directly within the SQL code to be used just for the current query being written.

    Thanks for your help!

  • Eric M Russell

    SSC Guru

    Points: 124933

    Regardless of method, first the Oracle client, at least the Instant Client, must be installed on the SQL Server box.

    For a linked server connection, you'll need a TNSNAME. However, have you investigated either of these two options? This would be my first attempt, but I'm not sure if it will work.

    Reply back if you find a solution and get it working. I could use it myself on occasion, but am too busy to dick around with it.

    OPENROWSET

    ( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'

    | 'provider_string' }

    , { [ catalog. ] [ schema. ] object

    | 'query'

    }

    | BULK 'data_file' ,

    { FORMATFILE = 'format_file_path' [ <bulk_options> ]

    | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }

    } )

    https://msdn.microsoft.com/en-us/library/ms190312(v=sql.105).aspx

    OPENDATASOURCE ( provider_name, init_string )

    https://msdn.microsoft.com/en-us/library/ms179856(v=sql.105).aspx

    "Do Not Seek To Follow In The Footsteps Of The Wise. Seek What They Sought." - Matsuo Basho

  • Mr Corn Man

    Ten Centuries

    Points: 1268

    Here is what ended up working:

    SELECT *

    FROM OPENROWSET('OraOLEDB.Oracle'

    ,'server_ip_address:port_number/database'

    ;'your_user_name'

    ;'your_password'

    ,'your_query, for example, SELECT * FROM DB.SCHEMA.TABLE')

    In addition to this query, part of the solution required me to add a registry DWORD value for the OraOLEDB.Oracle provider. That registry value was DisallowAdHocAccess with a value of '0'. Without this registry addition non-SA logins received an error:

    Msg 7415, Level 16, State 1, Line 1

    Ad hoc access to OLE DB provider 'OraOLEDB.Oracle' has been denied. You must access this provider through a linked server.

    Once the registry was updated, everything works like a charm.

    Thanks!

  • Eric M Russell

    SSC Guru

    Points: 124933

    Mr Corn Man (5/20/2015)


    ...

    In addition to this query, part of the solution required me to add a registry DWORD value for the OraOLEDB.Oracle provider. That registry value was DisallowAdHocAccess with a value of '0'.

    ...

    You had to make this registry entry on the Windows server box? How so without being an Admin ?

    "Do Not Seek To Follow In The Footsteps Of The Wise. Seek What They Sought." - Matsuo Basho

  • Mr Corn Man

    Ten Centuries

    Points: 1268

    Yes, the registry change was on the server. I have access to do that, but not to the tnsnames. Go figure.

  • Eric M Russell

    SSC Guru

    Points: 124933

    Mr Corn Man (5/20/2015)


    Yes, the registry change was on the server. I have access to do that, but not to the tnsnames. Go figure.

    It's neat that you got openrowset on Oracle working without going through TNSNAMES. You should submit an article here on sqlservercentral describing how to setup Oracle client connectivity, required server settings, and everything, so those of us who occasionally need to pull data from Oracle can ramp up quickly.

    "Do Not Seek To Follow In The Footsteps Of The Wise. Seek What They Sought." - Matsuo Basho

  • net.flux

    SSC Journeyman

    Points: 75

    I just logged in to say, thank you. This is the only place I found on the internet with the correct answer. I used to connect to a Oracle instance on AWS. I ran a, 10 minute, complicated query and dropped the results neatly into MS Sql server, without having to learn SSDT.

  • kevaburg

    SSCoach

    Points: 17910

    Eric M Russell (5/20/2015)


    Mr Corn Man (5/20/2015)


    ...

    In addition to this query, part of the solution required me to add a registry DWORD value for the OraOLEDB.Oracle provider. That registry value was DisallowAdHocAccess with a value of '0'.

    ...

    You had to make this registry entry on the Windows server box? How so without being an Admin ?

    Hi,

    to be honest this answer and solution is more complicated than the normal solution of creating a TNS_ADMIN System variable and installing the instant Client.

    All that needs to be done in addition to that is to create a linked Server in SQL Server and run the query with OPENQUERY.

    Regards,

    Kev

  • maudzedunjp

    Old Hand

    Points: 312

    Good advices, thx for help!

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply