Sql server to oracle
-
Hi all,
We have an app server connecting to sqlserverdatabase and oracle database. The application calls a stored procedure that exists in sql server database, when the procedure runs it has to selects/inserts into table from the oracle database.The stored procedure in sql server database should be able to access the tables in oracle database. I'm trying to find methods to implement this.. I just researched a few links and suggest to use linked server but our network team doesnt want to use linked server due to some security issues which it makes it complicated.
Can we use Db links to do this? I'm hoping if we use dblink it will be at the database level and we can do it using ODBC connection to the oracle database? Please give me your thoughts.
Thank you very much.
-
pretty sure a db link is an Oracle-to-SQL server ODBC connection.(well to many other db systems, but say SQL for now)
a linked server is the equivalent: a SQL-to-Oracle ODBC Connection.(SQL can connect to lots of other db systems as well)
an application could open multiple connections to either SQL or Oracle. or Access or MySQL for that matter.
If the work is going to be done in Oracle, then you'd create a db link to SQL.
If the work is going to be done in SQL, then you'd create a linked server to Oracle.
Based on what you described, you'd need a linked server(The stored procedure in sql server database should be able to access the tables in oracle database). It sounds like your team is not familiar with linked servers, hence the knee jerk reaction that would not be secure. it can be as secure or as wide open as you want it to be, since you define the oracle username and password that would be used to connect with.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank you so much Lowell.
Is linked server, the only option to access Oracle database from SQL server stored Procedure? Is linked server, basically like a database object like a dblink in Oracle? SO far from my research, I'm thinking linked server is only at database level and not at server level? If this is right, I'm hoping to make the network management understand how it works...Please give me your suggestions.
Thanks again
-
newbieuser (10/3/2009)
Thank you so much Lowell.Is linked server, the only option to access Oracle database from SQL server stored Procedure? Is linked server, basically like a database object like a dblink in Oracle? SO far from my research, I'm thinking linked server is only at database level and not at server level? If this is right, I'm hoping to make the network management understand how it works...Please give me your suggestions.
Thanks again
a linked server for oracle would be to a specific database; using the classic Scott/Tiger Analogy, a linked server would be set up to go you your Oracle SID, and using the username Scott be able to access all the objects(table/View/Procs, including sysviews like USER_TABLES, etc) that SCOTT would have access to.
select * from MyLinkedServer.SCOTT..SomeTable
to get to a different database, you'd need a different linked server, and the linked server would use a different username and password for connecting.
in SQL, when you create the linked server, you can decide which credentials will be used on this screen:
in the circled section, that is saying that the connection would always use a specific username and password.
but in the section above, you can make it so the SQL user "webdev", if it uses the linked server, would connect/impersonate as SCOTT with the password TIGER, and if the user "production" connects (still to the SCOTT schema/database), it us a different users credentials. that Oracle user may only have access only to a subset of the objects in the SCOTT database.
hope that helps, let me know if i muddied the waters or clarified things for you.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
a better example: in Oracle in whatever database, you might add a new user "SQLPassThru", and give that user access to 3 specific tables, and nothing else.
that Oracle user would be what is used in the SQL linked server.
that's about as tight as the security should be, no access to anything it doesn't need access to.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks so much Lowell. You sure made it clear. I greatly appreciate it.
I need more help from you experts.. I'm following this doc to find out about how to setup the linked server.. Is there a better method to follow?
http://support.microsoft.com/kb/280106
Do I need to download the drivers mentioned in step 2? Step 3 has some registry changes for MDAC 2.5? Is this something to do with configuring the server access?
Thank you so much
-
Please help me , anyone?
Just trying to get more information on this.. If there could be any possible security issues, as far as the setup is concerned I only see two things: exec sp_addlinkedserver and exec sp_addlinkedsrvlogin. It does not talk about windows authentication on connecting to the Oracle server?
On Management Studio new linked server option on the security tab I see this option:
Be made using this security context:
Remote login:
With password:
Is this the Oracle server's windows credential?
Please give me your thoughts on this. Thank you so much.
-
newbieuser (10/6/2009)
Be made using this security context:Remote login:
With password:
Is this the Oracle server's windows credential?
Please give me your thoughts on this. Thank you so much.
No, the Remote Login would be the Oracle equivalent of a standard SQL login (database user name/password). If your Oracle instanc(es) are set up to use Windows Auth then you should be good to go using "Be made using the login's current security context". If they are not (which ours aren't), you'll need to have your Oracle admin team set up a user account that is secured to the point you need it.
With Windows Authentication you would not need to set up or record a user name/password in the Linked Server itself, as the connection would be made at runtime. Much like when connecting to SQL Server through ODBC; when Windows Auth is used you do not enter credentials.
Hope that clears things up a little for you.
-
Thanks Mark. That was clear enough. Hope I got that right.. the windows authentication will be the set up with the ODBC connection to the Oracle server and the linked server setup uses only Oracle Database's user and password.
Another doubt, in the EXEC sp_addlinkedserver- I see the product_name is the linkedservername, can you please tell me what the 'server' , 'data_source', location,provider_string and catalog is?
/* sp_addlinkedserver [@server =] 'server'
[, [@srvproduct =] 'product_name']
[, [@provider =] 'provider_name']
[, [@datasrc =] 'data_source']
[, [@location =] 'location'] [, [@provstr =] 'provider_string']
[, [@catalog =] 'catalog']
*/
EXEC sp_addlinkedserver 'Ora817Link', 'Oracle', 'MSDAORA', 'oracle817'
/* sp_addlinkedsrvlogin [@rmtsrvname =] 'rmtsrvname'
[,[@useself =] 'useself']
[,[@locallogin =] 'locallogin']
[,[@rmtuser =] 'rmtuser']
[,[@rmtpassword =] 'rmtpassword']
*/
Is rmtsrvname ,the linked server name? What is useself , locallogin?
Thanks for your help
-
newbieuser (10/6/2009)
Thanks Mark. That was clear enough. Hope I got that right.. the windows authentication will be the set up with the ODBC connection to the Oracle server and the linked server setup uses only Oracle Database's user and password.Another doubt, in the EXEC sp_addlinkedserver- I see the product_name is the linkedservername, can you please tell me what the 'server' , 'data_source', location,provider_string and catalog is?
/* sp_addlinkedserver [@server =] 'server'
[, [@srvproduct =] 'product_name']
[, [@provider =] 'provider_name']
[, [@datasrc =] 'data_source']
[, [@location =] 'location'] [, [@provstr =] 'provider_string']
[, [@catalog =] 'catalog']
*/
EXEC sp_addlinkedserver 'Ora817Link', 'Oracle', 'MSDAORA', 'oracle817'
/* sp_addlinkedsrvlogin [@rmtsrvname =] 'rmtsrvname'
[,[@useself =] 'useself']
[,[@locallogin =] 'locallogin']
[,[@rmtuser =] 'rmtuser']
[,[@rmtpassword =] 'rmtpassword']
*/
Is rmtsrvname ,the linked server name? What is useself , locallogin?
Thanks for your help
Question - what Data Provider are you using in the ODBC connection? Depending what Oracle client you have on the SQL Server may limit/expand your options as far as what kind of security can be used.
In my Oracle connection, I am using "Oracle Provider for OLE DB", and my settings are as follows:
Product Name: Oracle
Data Source: Oracle Instance in TNSNAMES.ORA
Provider String: Oracle Instance in TNSNAMES.ORA (same as preceeding line)
Location: Left blank
Catalog: Left blank
For perspective, my Oracle client is Oracle 10 running on an x64 SQL Server.
-
for a scripted login, this will probably help.
the remote user and the remote password IS the Oracle username and password you want..SCOTT/Tiger in our previous examples:
--#################################################################################################
--Linked server Syntax for Oracle 10G
--#################################################################################################
DECLARE @server sysname,
@srvproduct nvarchar(256),
@provider nvarchar(256),
@datasrc nvarchar(100),
@location nvarchar(100),
@provstr nvarchar(100),
@catalog sysname,
@sql varchar(1000)
--add an access Database as a linked server
SET @server = N'MyOracle' --this is your Alias SQL server will nickname the Instance
SET @srvproduct = N'Oracle'
SET @provider = N'ORAOLEDB.Oracle' --optionally 'MSDAORA' to use the MS driver
SET @datasrc = N'SFMN10G' --this is the oracle isnatance straight from tnsnames.ora
set @provstr = ''
EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr
-- exec sp_dropserver AccessDb
exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle',
@useself = N'FALSE',
@locallogin = 'sa', --on the SQL side, you'll use local credentials "sa" for the user
@rmtuser = N'CHANGE_ME', --oracle username
@rmtpassword = 'NotARealPassword' --oracle password
--list all the tables and their names
EXEC sp_tables_ex 'MyOracle'
GO
--EXEC dbo.sp_DropServer 'MyOracle', 'DropLogins'
--list all the tables and their names
EXEC sp_tables_ex MyOracle
GO
select * from MyOracle..CHANGE_ME.GMACT
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks Mark. We are proposing to use OLE DB provider for Oracle as well on ORacle 10g. So would be a similar setup..
Can you please tell me what the location and catalog is? - in the sp_addlinkedserver?
Thanks again
-
newbieuser (10/6/2009)
Thanks Mark. We are proposing to use OLE DB provider for Oracle as well on ORacle 10g. So would be a similar setup..Can you please tell me what the location and catalog is? - in the sp_addlinkedserver?
Thanks again
Ours are not specified. The arguments from sp_addlinkedserver were omitted. Here's the result if I script the LS out:
EXEC master.dbo.sp_addlinkedserver@server = N'LINKED_SERVER_NAME',
@srvproduct = N'Oracle',
@provider = N'OraOLEDB.Oracle',
@datasrc = N'FROM_TNSNAMES.ORA',
@provstr = N'FROM_TNSNAMES.ORA'
-
Thank you both.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply