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?
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.
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:
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)
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)
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
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