Using a linked server in SQL Server to access the Oracle database

First, install the configuration Oracle Client

To access Orcale data, the Oracle client must be installed on the client machine that is accessed.

Orcale has two forms of client:

L Complete Client

The Microsoft OLE DB provider that includes basic Oracle access to the server-side database requires Oracle Client software support file and sql*net. Also includes tools for configuring client settings, Sqlplus, Enterprise Manager, and a range of tools.

L Immediate Clients (instant client)

This client is small in size, but includes only the most basic support drivers for accessing the Orcale server, no management tools, and no graphical interface available. The settings for the client need to be done manually.

Currently Orcale is newer than the Oracle 10g version, for example.

1. Download the Oracle 10 client

can go to orcale official website to download, need to register first, then download.

Download a Client for Oracle Database 10g client Release 2 for Microsoft Windows (32-bit): http://download.oracle.com/otn/nt/oracle10g /10201/10201_client_win32.zip

2. Installing the Oracle 10 client

Download good, unzip, install.

There are four options at the time of installation:

L Instantclient, equivalent to minimal installation.

L Administrator, Complete installation.

L Run time

L Custom

For ease of management, it is generally installed as an administrator.

3. Configuring the Oracle 10 Client

In the client main need to configure two settings, naming methods and server aliases, there is a listener is required for the service side, to listen to client access, the client does not have to set the listener.

These settings are available in the Net Manager tool, in the "net Manager" in the configuration and migration tools in the Oracle program group. ：

Profiles are used to set up naming methods and validation methods.

3.1. Naming methods

Select "Name" in the upper drop-down list at the left of the window, and the left window shows the currently available methods, and the right is the named method that has been selected, and several main naming methods are:

L TNSNames represents the use of Tnsnames.ora files to parse

L Onames that Oracle uses its own name server (Oracle name servers) to resolve, and currently Oracle recommends using Lightweight Directory Access Protocol LDAP instead of Onames

L hostname means use host file, Dns,nis, etc. to parse

You typically use local naming to resolve the server name, which is the server name set in Tnsnames.ora. TNSNames. The server name in Ora is set in the server alias.

3.2. Verification method

Select Orcale Advanced Security in the drop-down list to set which authentication method users use when connecting to the Oracle server. In the left-hand window below, the available authentication methods are shown, the right is the selected authentication method, and the main authentication methods are:

L NTS indicates operating system authentication

L None, nothing. Indicates Oracle database authentication

L KERBEROS5, using the Kerberos 5 authentication method

These authentication methods can be used at the same time, the general use of Oracle database authentication, that is, there is nothing to set up here, the choice of other authentication method does not affect the authentication of Oracle database.

3.3. Server aliases

When the TNSNames local naming method is selected in the naming method above, the alias of the server is set here.

You can add and edit local server aliases by selecting "Service naming" in the left window. The server aliases generated here are reflected in the Tnsnames.ora file.

The server naming setting for the local server actually sets the server's four main parameters: the server hostname (which can be either the host name or the host IP), the server port number (default 1521), the Access Protocol, and the database service name to access the server.

Exchange here is the client's server alias, which is used by the client to access the service-side database.

The service name DatabaseName is the service name of the database that the server needs to access.

The following is the IP, port, and protocol for this server (typically TCP/IP).

After setting the server alias, this is reflected in the Tnsnames.ora file:

EXCHANGE =

(DESCRIPTION =

(Address_list =

(ADDRESS = (PROTOCOL = TCP) (HOST = 203.175.156.82) (PORT = 1521))

)

(Connect_data =

(service_name = Exchang)

)

)

Second, create a new linked server in SQL Server that points to orcacle

Above the client set the necessary settings to access the Oracle server, the client can access the database of the Orcale service through the Oracle client.

This is only a case where a linked server using SQL Server links the Oracle server to access data.

Settings are as follows:

L Create a new linked server in SQL Server, such as a linked server named: Oracle_exchange

L Data Access Interface selection: Microsoft OLE DB Provider for Oracle

L Product Name: Oracle

L Data Source: is the server alias established in the previous step, which represents the database to be accessed on the Oracle server to be accessed.

In security, select Establish a connection using this security context, and then enter the user name and password to access Oracle.

Ok, other settings not mentioned above do not need to be set. The following can be passed.

Third, access Orcale through the linked server in SQL Server

Since the service name of Orcale is directed directly to a database on the Orcale server, all data accessed Orcale through the linked server is in this form (the location of the specified database is not required):

Server aliases: Owner. Database objects

Oracle_exchange. Username.tablename

Using a linked server in SQL Server to access the Oracle database