linked server problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

linked server problem

Author

Topic

andyhallam
Starting Member

9 Posts

Posted - 2011-01-25 : 08:05:47

I am running 64bit windows vista with SQL SERVER 2008 SP1.
11.2 Oracle on windows (all on the same machine)

I am trying to set up a linked server to Oracle.

Using OraOLEDB.Oracle as MSDAORA is not shown in the SQL SERVER Providers list???

Am doing -

exec sp_addlinkedserver @server='andyh', @srvproduct='Oracle', @provider='OraOLEDB.Oracle', @datasrc='MYORACLE'

exec sp_addlinkedsrvlogin @rmtsrvname='andyh', @useself='false', @locallogin='sa', @rmtuser='myuser', @rmtpassword='xxxx'

tnsping MYORACLE works fine.

- I execute the below
select * from andyh..SCOTT.EMP

- I get
OLE DB provider "OraOLEDB.Oracle" for linked server "andyh" returned message "ORA-01017: invalid username/password; logon denied".

The @rmtuser and @rmtpassword are correct - I can connect via sqlplus using these values no problem.

Help

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-01-25 : 08:31:15

You need to install the 32 bit Oracle client as well as far as I can remember. This is due to support issues, will try to find a kb article for you..

Can't find one straight up, but read this:

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/8cea4b0f-28b4-4224-809c-6935a624205a/

andyhallam
Starting Member

9 Posts

Posted - 2011-01-25 : 09:37:59

I have got both the 11.2 32bit & 64bit clients installed.
I have also installed 11.2 ODAC.

The thing that puzzles me is the "invalid username/password" - or is this just a red herring?

The full error message I get is:

OLE DB provider "OraOLEDB.Oracle" for linked server "andyh" returned message "ORA-01017: invalid username/password; logon denied".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "andyh".

thanks

andyhallam
Starting Member

9 Posts

Posted - 2011-01-25 : 12:12:48

I now have a 32bit windows XP server on which to test and I get the same problem?
On this I have installed -
SQL SERVER 2005
11.2 Oracle client

I have created a 'testls' user on the Oracle database -
CREATE USER testls IDENTIFIED BY testls DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT CONNECT, RESOURCE, SELECT ANY TABLE TO testls;

I can connect from my 32bit client via SQL plus with no problems -
C:\temp>sqlplus testls/testls@MYORACLE

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 25 17:02:04 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> select count(*) from SCOTT.EMP;

COUNT(*)
----------
14
SQL>

In SQL Server Management Studio I execute -
exec sp_addlinkedserver @server='andyh', @srvproduct='Oracle', @provider='MSDAORA', @datasrc='MYORACLE'
exec sp_addlinkedsrvlogin @rmtsrvname='andyh', @useself='false', @locallogin='sa', @rmtuser='testls', @rmtpassword='testls'
select * from andyh..SCOTT.EMP

OLE DB provider "MSDAORA" for linked server "andyh" returned message "ORA-01017: invalid username/password; logon denied
".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "andyh" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "andyh".

I then try using OraOLEDB.Oracle –
exec sp_addlinkedserver @server='andyh', @srvproduct='Oracle', @provider='OraOLEDB.Oracle', @datasrc='MYORACLE'
exec sp_addlinkedsrvlogin @rmtsrvname='andyh', @useself='false', @locallogin='sa', @rmtuser='testls', @rmtpassword='testls'
select * from andyh..SCOTT.EMP

OLE DB provider "OraOLEDB.Oracle" for linked server "andyh" returned message "ORA-01017: invalid username/password; logon denied".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "andyh".

I then tried both methods again changing ‘MYORACLE’ to ‘ANDYH:1521/MYORACLE’ – same result…

What am I doing wrong / not doing???

Any help is greatly appreciated.

Andy

andyhallam
Starting Member

9 Posts

Posted - 2011-01-26 : 06:19:58

I have found the problem.

It was because of the @locallogin='sa' parameter.

Removing this and the "select * from andyh..SCOTT.EMP" works.
Phew!

Hang on though - I've now got a problem with TIMESTAMP incompatibility.
All of our tables on Oracle have a "createddate" column of type TIMESTAMP (equivalent to SQL SERVER's DATETIME).

select * from andyh..TRAINDB.LINKEDSERVERTEST

Msg 7354, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "andyh" supplied invalid metadata for column "CREATEDDATE". The data type is not supported.

I will create a new thread for this on the Transact-SQL (2008) forum.

If you have any comments on this new issue please post on the new thread.

thanks and regards,

Andy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources