Linked Server - Oracle - sys as sysdba

Question

Linked Server - Oracle - sys as sysdba

niquem1974

Right there with Babe

Points: 773
More actions
November 6, 2015 at 7:41 am

#399289

Hey gang
I've been googling for ages now and haven't got to the bottom of this.
Essentially I just need to create a Linked Server to a few Oracle instances.
My system = Win10 x64, SSMS Express 2014, Oracle Client 12102 x64
So, I was able to set up a connection using the UN & PW I was given.
Once established I noticed that I was only seeing a few tables in the catalog (as I have a direct connection through PL/SQL dev)
I then realised that I needed to connect using the SYS credentials to get sight of all tables....which I quickly found didn't work.
I kept receiving Username/PW errors from the Linked server dialogue.
I then went to test the connection in the Oracle client directly. I eventually discovered that I could connect using this syntax:
username
sys
PW
<sys password> as sysdba
However when putting this into the Linked Server/Security/Be made using this secruity context boxes I couldnt fatham how to get it to connect
Surely someone has managed to conquer this issue?
Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

a lot of the time, the GUI might not show you all possible datasets...you sometimes have to "KNOW" that a database exists.

you can run sp_tables_ex in SSMS to get details about specific catalogs, i don't use the GUI much for the navigation of data in a linked server.

if you are having trouble with mapping user to remote user, i've got my notes below:

Well you had to install the 64 bit version of the oracle driver on the Server that SQL is also installed; you had configure your TNS names correctly and prove you can connect via SQLPlus or Toad or whatever your local tool of choice is.

If you've got that installed on the sever, the examples below can help you ;

here's both the script and a screenshot of the config in SSMS's linked server.

--#################################################################################################

--Linked server Syntax for Oracle 10G

--#################################################################################################

DECLARE @server sysname,

@srvproduct nvarchar(256),

@provider nvarchar(256),

@datasrc nvarchar(100),

@location nvarchar(100),

@provstr nvarchar(100),

@catalog sysname,

@sql varchar(1000)

--add an access Database as a linked server

SET @server = N'MyOracle' --this is your ALias

SET @srvproduct = N'Oracle'

SET @provider = N'ORAOLEDB.Oracle' --optionally 'MSDAORA' to use the MS driver

SET @datasrc = N'SFMN10G' --this is the SID

set @provstr = ''

EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr

-- exec sp_dropserver AccessDb

exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle',

@useself = N'FALSE',

@locallogin = 'sa',

@rmtuser = N'CHANGE_ME',--oracle username

@rmtpassword = 'NotARealPassword' --oracle password

--list all the tables and their names

EXEC sp_tables_ex 'MyOracle'

GO

EXEC dbo.sp_DropServer 'MyOracle', 'DropLogins'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

niquem1974 Right there with Babe Points: 773 More actions · Answer 2

Hi thanks

Unfortunately, that doesn't answer my point

I dont have a problem connecting as a known user (in your example scott/tiger), the problem comes when attempting to connect using the "as sysdba" context; hence my post

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 3

Haven't tried it yet, but why would you want to access Oracle as sysdba through linked server?

Isn't it possible to grant the user the specific rights for their needs?

niquem1974 Right there with Babe Points: 773 More actions · Answer 4

Hi Jo

In a word No 🙂

The situation I have is that I am from a SQL server dev background

I have been given the log on details to an Oracle environment, one user only has visibility of a few tables whereas the SYS has visibility of them all ( I am logged into PL/SQL dev as the SYS @ SYSDBA)

I am wishing to connect to Oracle from SQL to try and get an understanding of the different concepts within a familiar environment.

There is no Oracle DBA btw (and the SQL DBAs have just shrugged when Ive asked) and obviously I dont want to go fiddling with User accounts on something I dont know how to use yet

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 5

I kept receiving Username/PW errors from the Linked server dialogue.

What errors were they, and who is the authority for them? This being a SQL Server forum, you might be seeking guidance from the wrong authority :).

I couldnt fatham how to get it to connect

A client must first be able to connect to a server - only after connectivity has been established can the server verify a client's credentials. Did you mean "get it to authenticate"? Was a different error raised?

You may want to review Oracle's documentation for setting up OLEDB or ODBC clients. It has been over a decade since I last worked an Oracle connectivity or authenitcation issue. But at that time, one didn't just need Oracle's ODBC (or OLE DB) driver. One also needed Oracle's SQL*Net, which was not free....

Try creating an empty file named test.udl, double-click it, enter required parameters, and test the connection. If that connection test fails with the same error(s), this forum is unlikely to have the droids you are looking for 🙂 == you have a repro that is independent of SQL Server :).

niquem1974 Right there with Babe Points: 773 More actions · Answer 6

I think the point of my question maybe getting lost a bit.

Essentially, all Im seeking is an answer as to whether I can connect to a Linked server (Oracle) using the SYS as SYSDBA credentials. It's suspect its something to do with the syntax of the connection string with the SSMS Linked Server dialogue.

I can successfully connect using the Oracle ODBC Client using

UN : SYS

PW: <password> AS SYSDBA

It errors if I use the same criteria in the SSMS Linked Server connection manager

NB I can successfully connect using the restricted User account as previously mentioned.

I think this is the right forum to pose the question as I can actually conncet to the Oracle instance fine using the SYS login and I can also perform a successful connection test to the Oracle instance on the server that the SSMS in on using the above mentioned method.

If its not possible, that's fine it'd be helpful to understand why, Im just trying to expand my understanding.

Thanks in advance

dweil SSCrazy Points: 2408 More actions · Answer 7

Log on to sqlplus as sys

Create user sqluser identified by 'newpassword';

Grant connect to sqluser;

Grant dba to sqluser;

For the linked server log on as sqluser password newpassword and you will be able to see all the tables.

Would not do on a production because the linked user would have dba (sa) privileges on the Oracle Database;