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

 

Sql Server Agent – Step – “SQL Server Integration Services Package” – Error – “Errors were detected in the command line arguments…”

Background

Trying to edit the parameters on a SQL Server Agent Job, but ran into a hard wall.

 

Error Message

Error

Image

Textual

TITLE: Microsoft SQL Server Management Studio

Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)

 

Advanced Information

Upon clicking the “Advanced Information” we will click the message pasted below.

Image

Textual

===================================

Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)

——————————
Program Location:

at Microsoft.SqlServer.Management.SqlManagerUI.DTSJobSubSystemDefinition.Microsoft.SqlServer.Management.SqlManagerUI.IJobStepPropertiesControl.Save(JobStepData data, Boolean isSwitching)
at Microsoft.SqlServer.Management.SqlManagerUI.JobStepProperties.OnGatherUiInformation(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.OnGatherUiInformation(RunType runType)

Scenario

This problem is reproducible this way.

  1. Start Sql Server Management Studio ( SSMS )
  2. Connect to SQL Server Instance
  3. Access SQL Server Agent \ Jobs \ [Job Name]
  4. Edit a Job Step whose type is “SQL Server Integration Services Package
  5. There are two tabs, Package and Configuration
  6. Access the “Configuration” Tab
  7. Within the “Configuration” Tab there are three Tabs.  The Tabs are “Parameters”, “Connection Managers”, and Advanced
  8. Select the “ExcelConnectionManager_ConnectionString” parameter
    • Click on “ellipse” button
    • The “Edit Literal Value for Execution” window appears
    • Access the “value” textbox and make cosmetic changes
    • Click the OK button
  9. The error, “Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)“, aforementioned appears

 

Quick Points

  1. Other Parameters can be modified
    • This error only occurs when we tried amending the Excel File Connection String
    • We were able to modify other parameters; such as SQL Server and SMTP Server
  2. Connection Manager
    • All Connections ( Excel, SQL Server, and SMTP) can be successfully modified via the Connection Manager Tab

 

Honorable Mention

  1. Cody Konior
    • Agent job errors when running an SSIS package
      Published On :- 2015-August-2nd
      Link

 

SQL Server Versions

Here are the versions that exhibit this issue.

SQL Server Engine

Tabulated

Version Version Literal Version#
SQL Server v2014
Microsoft SQL Server 2014 (SP2-CU4) (KB4010394) – 12.0.5540.0 (X64) 12.0.5540.0

 

SQL Server Client

SQL Server Management Studio ( SSMS )

Tabulated

Version Version Literal Version#
SQL Server v2017
v 17.2 14.0.17177.0
SQL Server v2014
SQL Server 2014 SP2 CU10 12.0.5571.0

 

Image

SQL Server v2017

SQL Server v2014

Powershell Modules – AzureAD – “Get User’s Information “

Background

Now that we have “AzureAD” Powershell modules installed, let us play around with it.

First off, the let us review what type of information is returned on a Member Account.

 

Code

Outline

  1. Using CmdletBinding define our script argument list
  2. To connect to our “Tenants Domain“, Issue “Connect-AzureAD
  3. To get specific AD Accounts, call get-azureaduser and filter appropriately
    • Get the returned object type by issuing GetType().Fullname
    • Get the first item in the result list by issuing Select-Object -first 1
    • Get Object’s Property list by issuing Select-Object -Property *
    • Display PropertyList

 

Actual Code

 


[CmdletBinding()]
Param(
  [Parameter(Mandatory=$True,Position=1)]
   [string]$personName
	
)
Set-StrictMode -Version Latest;

#Connect to Azure AD
Connect-AzureAD

# get list of AD users that matches the name passed in
$objListofPerson = get-azureaduser -Filter "startswith(displayName,'$personName')"


# if List is empty, then say so
if (!$objListofPerson)
{
     "Object (objListofPerson) is null (empty)"
     return
}
 
# Keith Hill - Get Type name
# the-typename-and-inheritance-chain/
# http://rkeithhill.wordpress.com/2007/10/28/powershell-quicktip-using-pstypenames-to-see-# 
$strLog = "Type name is " + $objListofPerson.GetType().Fullname;
$strLog
 
# Get top item in list
$objPerson = $objListofPerson | Select-Object -first 1

# if List is empty, then say so
if (!$objPerson)
{
     "Object (objPerson) is null (empty)"
     return
}
 
#https://www.codykonior.com/2013/03/26/powershell-how-to-show-all-of-an-objects-properties-and-values/
$objPersonPropList = $objPerson | Select-Object -Property *

if (!$objPersonPropList)
{
	 "Object has no properties"
	 return
}

$objPersonPropList

Invoke

Syntax


powershell -file ./Office365AzureAccount.ps1 -personName "[personName]"

Sample


powershell -file ./Office365AzureAccount.ps1 -personName "Daniel Adeniji"

Output

Images

Image – 01

Image – 02

Image – 03

Image – 04

 

Tabulate

  1. Type
    • Type name is Microsoft.Open.AzureAD.Model.User

Source Code

GitHub

DanielAdeniji/Office365AzureADPowerShell
Link

 

References

  1. Microsoft Azure
    • Azure/Azure PowerShell
      • Connect-Azure
        Link
      • GetAzureADUser
        • GetAzureADUser
          Link
  2. PowerShell
    • PowerShell Parameters
      • Don Jones
        • Windows PowerShell: Defining Parameters
          Link
    • PowerShell Properties
      • Cody Konior
        • PowerShell: How to show all of an object’s properties and values
          Link
    • PowerShell List
      • Don Jones
        • How can I get just the first entry from a list of entries?
          Link
  3. AzureAD Powershell
    • GetAzureADUser
      • StackOverflow
        • Example of Get-AzureADUser [-Filter <String>] command
          Link