4

I'm trying to make the linked server from SQL Server 2005 to Oracle more secure by not having user/pass on "Be made using this security context:" fields so only a specific user can access the data from linked server.

So I tried mapping SQL Security User to Oracle user/pass by inserting it in "Local server login to remote server login mappings:" area then "For a login not defined in the list above, connection will:" set to "Not be made".

When I click OK, I get:

Access to the remote server is denied because no login-mapping exists

Is it not possible to map SQL login to a Oracle login? Is there any way to get this to work?

Improve this question
2
  • It sounds like it wasn't executed as the login you wanted. If your sysadmin on the box you can impersonate the login you mapped the creds to and try the connection. you can impersonate by using "execute as login = 'login'" then running a query. To use "revert" to revert back to your security context
    – Nabil Becker
    Feb 23, 2014 at 2:45
  • Its is possible. I am able SELECT from my SQL 2005 server to my Oracle server while mapping a local SQL 2005 login to a remote Oracle login without issue. I assume that you can successfully connect from your SQL 2005 server to your Oracle server via Oracle Net Manager?
    – jl01
    Feb 28, 2014 at 14:18

2 Answers 2

Reset to default
1

If the user that you added to use the Linked Server is not for the login you are currently using (and it is the only one you've set up), when you click OK, the connection test will fail because the current user will have no valid mapping to use the Linked Server.

To fix this simply create a valid mapping for your login as well. That way when you click OK the test will succeed because there is a valid mapping for the current user - if you use the same settings for the Oracle target in both mappings you'll have some confidence that it will be working for the other user(s). Either that or ignore the error from the test and check specifically that the user you created the mapping for can use the linked server successfully.

Improve this answer
0

The issue here is that you have the concept of "users" in two distinct RDBMS systems. Unfortunately, RDBMS products use literal (as opposed to Reference) style identifiers to denote their domain entities (users, tables, views, procedures, roles etc..).

The solution requires middleware that's endowed with the following capabilities:

  1. concept or domain entity denotation (naming) using Reference style of identifiers (e.g., HTTP URIs where <#OracleUserID> and <#SQLServerUserID> denote Oracle and SQL Server users, respectively)

  2. ability to express and comprehend entity relationship semantics (e.g., that two entities are participants in a coreference relation whereby <#SQLServerUserID> <#sameAs> <#OracleUserID> where <#sameAs> denotes the aforementioned relation)

  3. ability to create data access policies driven by entity relationship semantics thereby enabling mapping of DBMS roles and associated privileges between SQL Server and Oracle

  4. ODBC of JDBC connectivity such that your clients access the capabilities described above via ODBC or JDBC while also communicating with Oracle and SQL Server via ODBC or JDBC.

To conclude, if you are happy to apply middleware to this solution, you can take a look at OpenLink Virtuoso which offers the capabilities described.

Disclaimer: I'm the founder/CEO of openlink software

Improve this answer
1
  • 1
    Is this a sales pitch?
    – georgeb
    Oct 25, 2013 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.