Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 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 at 14:18

1 Answer 1

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.

share|improve this answer
    
Is this a sales pitch? –  georgeb Oct 25 '13 at 8:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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