Solved

SQL Server Linked Server Using Oracle OPS$ Windows Authenticated Account

Posted on 2016-10-13
7
95 Views
Last Modified: 2016-10-14
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.
0
Comment
Question by:koughdur
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 41842921
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41843474
It is possible for an Oracle database having a Windows Login?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41843484
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41843490
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
 

Author Comment

by:koughdur
ID: 41844359
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
 

Author Closing Comment

by:koughdur
ID: 41844377
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41844403
Glad to help!

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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can we attach PDF to table 2 46
HIghlights of SSIS? 3 42
SQL State HYT00. Timeout expired proplem 8 42
T-SQL: need to reset a declared variable 4 31
I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question