I know there are quite a few people out there struggling with just the basics using Oracle in a Microsoft world. There are several ways to configure your environment whether you plan on doing an MS SQL project, .NET, SSIS or any or all of the above, however the basic requirement is having the Oracle service setup and configured on your machine. Below are the steps to configure that service with one of the recent .NET packages posted by Oracle (You'll have to create a login with them to download it -- I don't make the rules); the latter half of this post shows how to utilize the Oracle client service to then create a linked server in SQL 2005 to connect to an Oracle server.
Software Pre-Requisites:Oracle Data Provider for .NET 10.2.0.2.20
After this package is installed, you should getsomething similar added to your menu: Programs> Oracle - ODACHome1 > Configuration and Migration Tools > Net Configuration Assistant
- Start the Net Configuration Assistant
- On the Welcome screen, choose Local Net Service Name configuration
- After clicking next, choose Add on the next screen
- In the Service Name box, type in the name of the service that the Oracle database runs under (usually something like OracleDB.world)
- After clicking next, confirm the protocol you will be using; it is usually TCP if you are unsure
- Enter the name of the server and the port number you will use to connect on the next screen
- Confirm that you will do a test and then change the user name and password to a valid user name and password for your target database
- After testing, give your new Oracle service a name
Part Two: Setting the Properties on the SQL Server
Your first step will be to open Microsoft SQL Server Management Studio and connect to the SQL Database server and instance that you will be creating the Linked Server on.
-
-
Navigate to the Server Objects folder and then to Linked Servers. Right Click on the Linked Servers folder and click “New Linked Server”
-
You will receive this dialogue screen. Note that you can create multiple linked server connections to the same server and database by giving different names in the “Linked Server” box. One reason for doing this would be to utilize the same TNS entry, but use different logins given the schemas/objects those logins will have access to.
-
For Provider, select Microsoft OLE DB Provider for Oracle. Product Name will be Oracle. Your Data Source will be the TNS name you saved from the Section One steps.
-
Next go to the security tab and fill in the login you have been granted to the Oracle server
-
Go to the server options and set the properties to the following values:
- Collation Compatible = True
- Data Access = True
- Rpc = False
- Rpc Out = False
- Use Remote Collation = True
- Collation Name leave blank
- Connection Timeout leave default value
- Query Timeout leave default value
And that’s it! You have now done the basic steps necessary to configure SQL Server 2005 to connect to an Oracle database via a linked server.
Tags: data migration
Categories: SQL Server