SQL Server Linked Server Using Oracle OPS$ Windows Authenticated Account

I would like to create a Linked Server from a SQL Server 2008 Database to an Oracle 11g Database using my OPS$ Windows Authenticated account.

However, no matter what I try it doesn't want to work.  It seems to expect a password, but neither leaving the Password blank, nor setting it to slash (/) seems to work.

Is this not allowed?  If it is allowed, how do I go about establishing the link?

Thanks.
koughdurAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I'm strictly an Oracle guy and know nothing about SQL Server and how it all works.

I assume you can connect directly to Oracle from your Windows user using / using sqlplus or similar Oracle tool?

If so, that tells me that the SQL Server link uses some other user.  Likely the service owner running SQL Server.  It would probably need to be that user that needs an OPS$ account.

You can have the Oracle DBA check the listener's log file or v$session to see what user SQL Server is using to connect with.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It is possible for an Oracle database having a Windows Login?
0
 
slightwv (䄆 Netminder) Commented:
Oracle can use OS Authentication.  Basically if the user has been authenticated in the OS, Oracle doesn't challenge the connection.

It is a security issue but it has its place at times.  It can save people from hard-coding usernames and passwords in things like scripts.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Oracle can use OS Authentication.
Then it's only depending on the Linked Server configuration. In the Linked Server properties, go to Security and enable the option for "Be made using this security context" and provide the correct credentials.
0
 
koughdurAuthor Commented:
Vitor,

I tried all of the different ways to connect and everyone failed.  The SQL Server interface appears to support having a different user establish the connection other than the sys admin or the user who owns the schema.  However, it seems like Microsoft forgot to support Oracle Windows Authentication, because they don't understand a blank or '/' password entry.
0
 
koughdurAuthor Commented:
It turns out I'm a complete idiot.  I use a different login for that computer which doesn't have a windows authenticated account on the Oracle database.

Duh.

Your answer got the light bulb to go off and make me realize that.

Thanks.
0
 
slightwv (䄆 Netminder) Commented:
Glad to help!

and we ALL have "duh" moments.....  ;)
0
All Courses

From novice to tech pro — start learning today.