Msg 7415 Trying to Execute openrowset to Oracle

• matt.blanchette

  Ten Centuries

  Points: 1161

  More actions

  February 2, 2009 at 1:51 pm

  #199703

  Hello,

  I am trying to use openrowset to do an ad-hoc query against an Oracle (10.2.1.0) database, using the following:

  SELECT *

  FROM openrowset ('MSDAORA',

  'Server={Oracle server name}; uid={my oracle ID}; pwd={my oracle password}',

  'SELECT * FROM {schema}.{table}')

  but getting the following:

  Msg 7415, Level 16, State 1, Server {SQL Server name}, Line 1

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

  Is this denial happening on the SQL Server side of the fence, or the Oracle side? If it is happenning on the Oracle side, can anyone tell me what I need to tell my Oracle DBA to make this work?

  Thank you.

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  February 3, 2009 at 1:01 am

  #936939

  This is happening on the SQL Server side.

  You have to define a Linked server pointing to your target Oracle database.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• matt.blanchette

  Ten Centuries

  Points: 1161

  More actions

  February 4, 2009 at 11:55 am

  #938257

  Hi Paul,

  I thought I posted this yesterday, but don't see it, so I will post it again. Apologies if this is a duplicate post.

  1) To run sp_addlindserver and sp_addlinkedsvrlogin, I assume I need sa access on the server? Or is dbo access to the master database sufficient?

  2) My understanding is that openrowset should create linked server on the fly. But, if I am reading you right, something on the SQL Server side of the fence is stopping this from happening. Is their a setting I need to change to allow this to work?

  Thank you very much.

• Gift Peddie

  SSC Guru

  Points: 73570

  More actions

  February 4, 2009 at 12:18 pm

  #938293

  This could related to the Microsoft OLE DB driver for Oracle see if you can use the Oracle version instead.

  http://www.oracle.com/technology/software/tech/windows/odpnet/index.html

  Kind regards,
  Gift Peddie