We recently needed to create a linked server from Microsoft SQL Server 2008 to an Oracle 9.0.1 database on a Windows Server 2008 x64 server.

For your reference, here is an outline of the steps used to successfully create the linked server connection. When trying to establish the linked server in SQL Management Studio the Oracle OLE Provider would never seem to work successfully.

Instead, we used ODBC with a System DSN to create the connection and this seemed to work reliably and the overhead of the DSN and slight performance hit were acceptable in our case. Download and Install the Oracle Client

1) Download the Oracle Database 10g Client Release 2 (10.2.0.4) from http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10204_winx64_vista_win2k8.html .

2) Unzip the archive and run setup.exe in the client folder.

3) When prompted for the installation type, choose Administrator (InstantClient did not work). Configure the Service Name I got the steps for this section from Sid Atkinson’s Blog at: http://www.sidatkinson.com/post/2007/09/27/One-way-to-create-a-linked-Server.aspx

4) Upon completion of the install the Net Configuration Assistant should automatically launch. If it does not, you can run it from Start à All Programs à Oracle – OraClient10g_homeX à Configuration and Migration Tools à Net Configuration Assistant.

5) In the Select naming methods dialog, select the default – Local Naming and click Next.

6) When prompted for a service name, enter the desired service name.

7) Choose the protocol, most likely TCP and click Next.

8) Enter the host name or IP address – we used the IP address, and enter the port information. When ready, click Next.

9) I recommend that you perform a test, by selecting Yes, perform a test and click Next. If credentials are needed a change Login button will be presented after Next is clicked.

10) When prompted for the net service name just click Next.

11) Keep clicking Next until you get to the configuration complete screen and then click Finish. Create an ODBC System DSN

12) Open the Start menu and navigate to All Programs à Oracle – OraClient10g_homeX à Configuration and Migration Tools à Microsoft ODBC Administrator.

13) Select the System DSN tab.

14) Click the Add… button.

15) Select the driver that has the name of the Oracle instance in which you installed earlier, this will be something likeOracle in OraClient10g_homeX and click Finish.

16) Enter a Data Source Name for your new system DSN. 17) Select the TNS Service name that you entered in step 6 above.

18) Click the Test Connection button and enter credentials as necessary. Establish the Linked Server

19) Open SQL Management Studio and log into the database engine for the instance you want to establish a linked server.

20) In the Object Explorer, expand the Server Objects Node, then right-click Linked Servers and select New Linked Server …

21) On the General tab, create a meaningful name for the linked server in the Linked server prompt.

22) Make sure the radio button for Other data source is selected and in the provider drop-down, select Microsoft OLE DB Provider for OBDC Drivers – * NOT * Oracle Provider for OLE DB, I could not get this to work and it would often cause SSMS to freeze just by selecting it.

23) Enter something like Oracle for the Product name.

24) In the Data source box enter the name of the System DSN that you gave in step 16 above. 25) On the left, select the Security link.

26) Configure your security configuration – in our case we selected the option for Be made using this security context and entered the credentials so that all connections used the same credential.

27) On the Server Options page, the only change that was made was Collation Compatible was set to True.

28) Click Ok and the new linked server should be configured.