Connecting my SQL Server 2000 DB to Oracle DB plus security

[css1015]

Hi,

I have a custom SQL Server 2000 database that I will be using to drive the interfaces for my ASP.Net application that will be used only on an intranet. My SQL Server 2000 database will be connecting to an Oracle database to synchronize data for a couple of tables so that the master data is always managed in one place, the Oracle side, for those two specific tables.

I will be eventually programmatically connecting via C# and ADO.Net to the Oracle DB. I have installed the Oracle client 10g and created an ODBC connection named 'HMC' to the Oracle DB using the Microsoft ODBC Driver for Oracle successfully on the development machine. Now, my question is:

1. Do I use this DSN named 'HMC' in my ADO.Net code for connecting to the Oracle DB? What is the syntax for doing that, in terms of using the correct provider and connection string?
2. When I deploy this, do I have to install the Oracle client on every user machine on the intranet and create this 'DSN'? If no, do I use a file DSN or totally disregard the DSN way of connecting to the Oracle DB and use something like a linked server to Oracle from the server machine hosting my SQL Server 2000 DB?
3. How would the DSN approach impact the security of my intranet application?

PLEASE ADVISE.

I am a relatively inexperienced ASP.Net programmer but I have programmed with C# and ADO.Net on desktop applications extensively.

Chad

 

[dvannoy]

heres your connection strings..

http://www.connectionstrings.com/

now, that will give you your correct connection strings for oracle. I am a little confused about why you would even use ODBC? since your pushing data over to your oracle server why not just hit those tables from you app when you need to using a connection string that points to your oracle server. the web config file supports multi connection strings.

 

[css1015]

Thanks. The reason I am using ODBC is because the Oracle DBA asked me to do so, that is all.

I first configured a data source named 'HMC' like this in my tnsnames.ora file on my local development machine before creating my ODBC DSN also named 'HMC'

hmc =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.xxx.xxx)(PORT = 1521))
)
(CONNECT_DATA =
(SID = DBName)
)
)

I tried configuring a linked server from SQL Server using this hmc service name/DSN name. I could not get that to work. I will give a try with the other connection strings and find out what works for me. Thanks for the help.

 

[css1015]

I was able to use the Oracle Managed Provider for .Net to connect to Oracle using the System.Data.OracleClient and this helped me access the Oracle tables just fine using the ADO.Net objects. With the Oracle Managed Provider, I used an OracleConnection object, and an OracleDataAdapter object to query Oracle data. Then I could fill my ADO.Net dataset with the Oracle adapter just like I do with a SQL adapter and it is business as usual.

Thanks,