SQL Server / Linked Server – Oracle ODAC

Background

Need to do something that comes up every couple of years.

And, that it is to get SQL Server to interoperate with Oracle.

 

Options

There are a couple of Oracle Client tools that we can use.  For this particular exercise we will use “Oracle Data Access Components (ODAC)“.

 

Oracle Data Access Components (ODAC)

Download

64-bit ODAC is available here.

It was released on 2017-June-1st.

Image

Tabulate

Item 64-bit ODAC 12.2c Release 1 (12.2.0.1.0) Xcopy for Windows x64 64-bit ODAC 12.2c Release 1 (12.2.0.1.0) for Windows x64
Artifacts

 

ODP.NET_Managed_ODAC122cR1.zip – 2.98 MB
ODAC122010Xcopy_x64.zip – 77.0 MB (77 MB)
ODAC122010_x64.zip – 415 MB

 

Choice

We chose “64-bit ODAC 12.2c Release 1 (12.2.0.1.0) for Windows x64“.

Not in the mood for Xcopy and install notes.

Download File Extract

Using 7-Zip extract the compressed (zip) file.

 

Install

Install Steps

Outline

  1. Select Product Languages
    • Selected Languages
      • English
  2. Oracle Home User Selection
    • Use Windows Built-In Account
      • Implicitly chosen as “Local Service
  3. Specify Installation Location
    • Oracle Base
      • E:\app\oracle\client
    • Software Location
      • E:\app\oracle\client\product\12.2.0\client_1
  4. Available Product Components
    • Here are the components with the ones chosen checked
      • Oracle Data provider for .Net
      • Oracle Providers for ASP.Net
      • Oracle Provider for OLE DB 
      • Oracle Services for Microsoft Transaction Server 
      • Oracle Data Access Components for Visual Studio
      • Oracle Data Access Component Samples
  5. DB Connection Configuration
    • Skipped for later
    • Will manually update the tnsnames.ora file once install is done
  6. Perform prerequisite checks
    • Nothing to do the system performs check
  7. Summary
    • Review Settings
      • Source Locaton
      • Install Type
      • Oracle Home Location
      • Oracle Home User Selection
  8. Install Product
  9. Finish

 

Image

Select Product Languages

Oracle Home User Selection

Specify Installation Location
Original

Revised

 

Available Product Components
Original

Revised

 

DB Connection Configuration

 

Perform Prerequisite Checks

Summary

 

Install Product

 

Finish

 

Configuration

TNSNAMES.ORA


hrdb =
(
	DESCRIPTION =
	(ADDRESS_LIST =
		(
                   ADDRESS = 
                           (PROTOCOL = TCP)
                           (HOST = hrdbORCLProd)
                           (PORT = 1521)
                )
	)
	(CONNECT_DATA =
		(SERVICE_NAME =hrdb)
	)
)

 

Configuration

Component Services

There are two levels of Component Services that we need to review and configure.

Those two areas are computer and individual component(s).

 

Definitions

 

Group Item Definition
Authentication Level
Connect The normal authentication handshake occurs between the client and server, and a session key is established but that key is never used for communication between the client and server. All communication after the handshake is nonsecure.
Impersonation Level
Identify The system default level. The server can obtain the client’s identity, and the server can impersonate the client to do ACL checks.
Connect The normal authentication handshake occurs between the client and server, and a session key is established but that key is never used for communication between the client and server. All communication after the handshake is nonsecure.
Permission Types
Launch & Activation Permission Launch and Activation permissions governs who can launch and activate DCOM Objects.
Access Data from the sourced provider needs to be marshalled into SQL Server.
This is performed through the SQL Server Process Account.

 

 

Launch Component Services

Please launch “Component Services” from Control Panel \ Administrators group.

 

Configuration – Computer

Objective

This section deals with the computer’s configuration and the default configuration for components.

And, will serve as the component’s setting unless otherwise over-written for the specific component.

Steps
  1. Tab – Default Properties
    • Enable Distributed COM on this computer
      • Enabled
    • Default Distributed COM Communication Properties
      • Default Authentication Level
        • Connect
          • Connect (RPC_C_AUTHN_LEVEL_CONNECT) ( Link )
            • The normal authentication handshake occurs between the client and server, and a session key is established but that key is never used for communication between the client and server. All communication after the handshake is nonsecure.
      • Default Impersonation Level
        • Identify
          • identify (RPC_C_IMP_LEVEL_IDENTIFY) ( Link )
            • The system default level. The server can obtain the client’s identity, and the server can impersonate the client to do ACL checks.
        • Impersonate
          • impersonate (RPC_C_IMP_LEVEL_IMPERSONATE) ( Link )
            • The server can impersonate the client’s security context while acting on behalf of the client. The server can access local resources as the client. If the server is local, it can access network resources as the client. If the server is remote, it can access only resources that are on the same computer as the server.
  2. Tab – COM Security
    • This area governs limits and defaults for components where they have not been explicitly defined for specific components
    • Permission Types
      • Launch and Activation Permission
        • Launch and Activation permissions governs who can launch and activate DCOM Objects
      • Access Permissions
        • Data from the sourced provider needs to be marshalled into SQL Server.
          This is performed through the SQL Server Process Account.
    • Permission Properties
      • Limits
        • Defines Limits for “Access” and define limits for “Launch and Activation
      • Default
        • Defines Default for “Access” and define default for “Launch and Activation

 

Image
Image – Tab – Default Properties

 

Configuration – Component – MSDAInitialize

Objective

In this section we cover our principal component, MSDAInitialize.

What is MSDAInitialize?
Snehadeep Chowdhury

Let us roll with Snehadeep Chowdhury on this one:

Permissions needed to set up linked server with out-of-process provider
Link

MSDAINITIALIZE is a COM class that is provided by OLE DB. This class can parse OLE DB connection strings and load/initialize the provider based on property values in the connection string.

MSDAINITILIAZE is initiated by users connected to SQL Server. If windows authentication is used to connect to SQL Server, then the provider is initialized under the logged in user account. If the logged in user is a SQL login, then provider is initialized under SQL Server service account.

 

Permission Set
Snehadeep Chowdhury

He continues…

Based on the type of login used, permissions on MSDAINITIALIZE have to be provided accordingly.

There are certain permissions that have to be set on MSDAINITIALIZE to be able to initialize the provider out-of-process and run linked server queries successfully locally and remotely.

Steps

Again, launch Component Services and from the “DCOM Config” list, please choose MSDAInitialize.

Outline
  1. Tab – General
    • Application Name :- MSDAINITIALIZE
    • Application ID :- 2206CDB0-19C1-11D1-89E0-00C04FD7A829
    • Application Type :- Local Server
  2. Tab – Security
    • Group Boxes
      • Launch and Activation Permissions
        • Initial
          • System ( Local Launch, Remote Launch, Local Activation, and Remote Activation )
          • Administrators ( Local Launch, Remote Launch, Local Activation, and Remote Activation )
          • Interactive ( Local Launch, Remote Launch, Local Activation, and Remote Activation )
        • Augment
          • Add specially crafted Active Directory Group
            • Grant
              • Local Launch
              • Local Activation
            • Leave as is
              • Remote Launch
              • Remote Activation
          • Add SQL Server Engine Account
            • Grant
              • Local Launch
              • Local Activation
            • Leave as is
              • Remote Launch
              • Remote Activation
      • Access Permissions
        • Initial
          • Self ( Local Access and Remote Access )
          • System ( Local Access )
          • Administrators ( Local Access and Remote Access )
        • Augment
          • Account running SQL Server Services
            • SQL Server Engine
            • SQL Server Analysis Services
      • Configuration Permissions
        • Left as is
Images
Image  – Tab – General

 

Image  – Tab – Security – Original

The original setting is to use default settings set for the computer.

Image  – Tab – Security – Launch And Activation permissions
Image  – Tab – Security – Launch And Activation permissions ( Original )

SYSTEM, Administrators, and Interactive have access.

Image  – Tab – Security – Launch And Activation permissions ( Add Users & Groups )

Here we are adding users from our local “Distributed COM Users” group.

 

Image  – Tab – Security – Launch And Activation permissions ( Revised )

Local “Distributed COM Users” group granted Local Launch and Local Activation permission.

SQL Server Engine Account granted Local Launch and Local Activation permission.

 

Image  – Tab – Security – Access permissions
Image  – Tab – Security – Access permissions ( Original )

SELF, SYSTEM, and Administrators have full Local and Remote Access.

 

Image  – Tab – Security – Launch And Activation permissions ( Add Users & Groups )

Here we are adding the account that is running the SQL Server Service…

 

Image  – Tab – Security – Access Permissions ( Revised )

Granted the SQL Server Engine Service Account, “Local Access” permission to the MSDAInitialize Object.

SQL Server Management Studio ( SSMS )

Linked Server

Linked Server Providers

Reviewed the providers under Linked Server.

Objective

We want to make sure that OraOLEDB.Oracle is listed

Image

Objective – Configure Provider – Configuration

We want to make sure that the “Oracle Provider for OLE DB” provider option is configured as follow:

  1. Allow in-process
    • Enable

 

Image – Before

Image – After

 

Linked Servers
Tab – General

Tab – Security
Tab – Security –  Image

Tab – Security – Code – SQL

declare @server sysname
declare @remoteAccount varchar(30)
declare @remoteAccountPassword varchar(30)

set @server = 'PRD'
set @remoteAccount = 'mssql'
set @remoteAccountPassword = 'helloPWD'

EXEC sp_addlinkedsrvlogin @server
		, 'false'
		, 'LAB\dadeniji'
		,  @remoteAccount
		,  @remoteAccountPassword

 

Tab – Server Options
Tab – Server Options – Image

Linked Server – Test Connectivity
sp_testlinkedsever
Syntax

declare @server sysname

set @server = 'HRDB';

exec sp_testlinkedserver 
		@server = @server

Output

Linked Server – Data Dictionary
sp_tables_ex

List objects on remote server.

Syntax
declare @server sysname
declare @tableType sysname

set @server = 'HRDB';
set @tableType = 'TABLE'

-- exec sp_help sp_tables_ex
exec sp_tables_ex
@table_server = @server
, @table_type = @tableType

Output

TroubleShooting

ODBC

Here ODBC is your friend.

Please launch ODBC Administrator from Control Panel Administrator Applet

Review

Tab – Drivers

Review list of drivers

You want to make sure that:

  1. Name : – OracleinOraClient12
    • Based on version installed
  2. Version :-
    • Version :- 12.02.00.01
      • Based on the version we installed
  3. File :- SQORA32.dll
  4. Date
    • 3/7/2017
      • Based on the version we installed

 

Configure

System Data Sources
Add / Manage/ Test Data Source

Test Connection

Test Connection – Connection Successful

Test Connection – Connection Failed
Image

Textual

ORA-01017 : invalid username/password; logon denied

 

Oracle

TNSPING

TNSPING is not installed with ODAC, but if able to get and install it, it is invaluable.

Syntax


tnsping HRDB

 

Output

Output – Image

Output – Successful

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 16-FEB-2
018 14:53:25

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
E:\app\Oracle\product\11.2.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = hrdbprod.lab)(PORT = 1521))) (CONNECT_DATA = (S
ERVICE_NAME = hrdbprod)))
OK (160 msec)

C:\>

Microsoft

Modules Loaded

Let us make sure that the Oracle components are loaded into the SQL Server Memory Address Space.

tasklist
syntax

tasklist /m /fi "Imagename eq sqlservr.exe"

output

Explanation

We see the Oracle specific dlls coming up at the rear of our list.

The file names being:

  1. OraOLEDB12.DLL
  2. OraOLEDBgmr12.dll
  3. OraOLEDBrst12.dll
  4. OraOLEDButl12.dll
  5. OCI.dll
  6. OraOCIEI12.dll
  7. oraons.dll
  8. OraOLEDB12us.dll
  9. OraOLEDBpus12.dll
Sysinternals – process Explorer

Summary

There is a lot here.

Most of all it unnecessary.

But, since it is something I only do every few years, it is good to be open minded and see how things have changed; especially in terms of commentary by “Do Good” bloggers.

 

Reference

  1. Microsoft
    • Microsoft Developer
      • SQL BI / Data Access Technologies
        ( SSIS, SSRS, SSAS, Data Access, SSMA, LINQ, System.Data … )

        • Sneha Deep Chowdhury ( Snehadeep )
          • Permissions needed to set up linked server with out-of-process provider
            Link
    • Windows Dev Center
      • Security in COM > Security Values > Delegation and Impersonation
        • Impersonation Levels
          Link
  2. National Instruments
    • NI Test Stand
      • DCOM Settings for the Remote Computer for Accessing Synchronization Objects Remotely
        Link
  3. OSISoft
    • Tech Support
      • KB01144 – How to set up a PI OLEDB or PI OLEDB Enterprise Linked Server to run out-of-process
        Link
  4. Cody Konior
    • MSDAINITIALIZE minimum required permissions
      Link

 

One thought on “SQL Server / Linked Server – Oracle ODAC

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s