SQL Server to Oracle Connection

Question

I'm trying to access an Oracle Database from MS SQL Server. I installed a local Oracle DB and tried this and everything worked OK.

But when I try to connect to a remote system (ie. an Oracle DB on another computer) it shows an error saying "ORA-01017 invalid username/password; logon denied".

I can connect to that computer with SQL Developer (with same user/pass).

What am I missing?

====================

Update 1:

Results of execute_sp_helplinkedsrvlogin :

Linked Server     LocalLogin     Is self Mapping     Remote Login
--------------    ----------     ---------------     -------------
192.168.0.56      sa             0                   sa

which is correct (target username is sa).

==========================

Update 2 :

I can connect to that database with sqlplus with same username and password, even add and remove data.

Have you checked to see if your Oracle TNS service name points to the correct server and instance name? — FutbolFan
I did and I will again tommorow , but if that was the problem then the IDE (sql developer) would have the same problem too, right? — Amir M
Perhaps...It might be very well be a permission issue. But, without looking at how your linked server is setup, it would be hard to pinpoint the issue. — FutbolFan
You can try to execute the sp_helplinkedsrvlogin stored proc to ensure the login information is setup correctly. — FutbolFan
I had a initial thought that it might be something to do with the tnsnames.ora file. Well, that wasn't too bad, was it? :) — FutbolFan

FutbolFan FutbolFan · Accepted Answer · 2015-04-20T17:07:02

Try running this in your SSMS:

execute sp_helplinkedsrvlogin

This should return a result indicating if a remote login has been setup for each linked server. If a remote login is not setup for your linked server, go to properties page for that linked server to add a remote login.

addremotelogin_linkedserver

SQL Server to Oracle Connection

1 Answers