Profiling Java Apps Using Bundled Tools

Background

Lately I have found myself playing with a lot of Applications that are running inside Java’s JVM.

The responsible next step is to start profiling their consumption patterns.

 

Referenced Work

These days most of my work are begun from a Google Search.

This work started from Karun Subramanian’s well thought effort.

Here are his blog posts:

  1. 5 not so easy ways to monitor the Heap Usage of your Java Application
    Link

 

Constraints

The tools that we will be evaluating have the following constraints:

  1. They should be run under the same account as the Java Processes that will be monitored

 

Tools

In this exercise we will look at our tools from the prism of Interface; Command Line  Interface ( CLI ) or Graphical User Interface.

Outline

  1. Interface
    • Command Line Tools ( CLI )
      • jstat
      • jmap
    • GUI
      • visualvm
      • jconsole

Preparation

jps

Outline

jps is analogous to the ps command in Linux.

While ps list all processes; jps lists all running Java Processes.

 

Syntax


jps

 

Sample


jps | find /I /V "Jps"

 

Output

 

Explanation

  1. The first column is the LVMDID ( process ID)
  2. The second column is the Process Name

 

Command Line Interface ( CLI )

jstat

Artifacts

Upon installing Java’s JDK, jstat is available in the JDK’s HOME bin folder.

Code

syntax

jstat -gc [process-id]

Sample

jstat -gc 13800

Output

jstat_20180811_0913AM

Explanation
Column Meaning Sample
EC Eden Capacity (KB) 69952
EU Eden Space Utilization (KB) 19112.8
OU Old Space Utilization (KB) 120747.8
OC Old Space Capacity (KB) 174784.0

jmap

Artifacts

jmap is available in the JDK’s HOME bin folder.

Code

syntax

jmap -heap [process-id]

Sample

jmap -heap 13800

Output

es.20180811.0943AM

Explanation
Attribute Meaning Sample
MaxHeapSize Maximum Heap Size 256 MB
NewSize Eden Space Utilization 85 MB
MaxNewSize Eden Space Capacity 85 MB
OldSize Old Space Utilization 170 MB

jhsdb

Artifacts

From Java JDK v9, jhsdb is available in the JDK’s HOME bin folder.

Code

syntax

jhsdb jmap -heap --pid [process-id]

Sample

jhsdb jmap --heap --pid 1960

Output

Explanation
Attribute Meaning Sample
G1
Regions 2020
Capacity 2020 MB ( 2 GB )
Used 98 MB
Free 1921 MB ( 1.92 GB)
Used % 4.90%
G1 – Eden Space
Regions 62
Capacity 74 MB
Used 62 MB
Free 12 MB
Used % 83.78%
G1 – Survivor Space
Regions 4
Capacity 4 MB
Used 4 MB
Free 0
Used % 100 %
G1 Old Generation
Regions 34
Capacity 50 MB
Used 32 MB
Free 17 MB
Used % 65.79 %

 

Graphical User Interface ( GUI )

visualVM

Artifacts

Java Version
Java Version v1.8 and Before

Up to Java v1.8 Visual M was bundled with Java JDK.

Once Java’s JDK is installed, please access visualvm.exe from the JDK’s HOME bin folder.

Java Version  9 and Above

Please download VisualVM from here.

Extract the zip file and run visualvm.exe from the bin folder.

Visual

Tab – Overview
Image

es_Tab_Overview_20180811_1004AM.PNG

Textual
  1. JDK
    1. Bitness :- x64
    2. v1.8
  2. JVM Arguments
    • -XX:+UseConcMarkSweepGC
      • Garbage Collection Algorithm
      • Web Links
    • HeapDumpPath = data
    • ErrorFile :- logs/hs_err_pid%p.log
Tab – Monitor
Image

es_Tab_Monitor_20180811_1005AM.PNG

Textual
  1. Heap
    • Max :- 256 MB
    • In use :-  120 MB

 

Tab – Threads
Image

es_Tab_Threads_20180811_1004AM

 

jconsole

Artifacts

JDK is accessible from the bin folder of Java’s JDK HOME folder.

Visual

Tab – Overview
Image

jconsole_Tab_Overview_20180811_1121AM

Textual
  1. Heap Memory Usage :- 137 MB
  2. Live Threads :- 60
  3. Classes :- 16,000
Tab – Memory
Image

jconsole_Tab_Memory_20180811_1112AM

Textual
  1. Used :- 156 MB
  2. Committed :- 256 MB

 

Tab – Threads
Image

jconsole_Tab_Threads_20180811_1128AM

Textual
  1. Live Threads :- 60
  2. Peak :- 63

 

 

References

  • Oracle
    • Home / Java / Oracle JDK 9 Documentation
      • Java Platform, Standard Edition Tools Reference
    • Java
      • jps
        • jps – Java Virtual Machine Process Status Tool
          Link
  • Karun Subramanian
    • 5 not so easy ways to monitor the Heap Usage of your Java Application
      Link
  • Dustin Marx
    • Dzone
      • jhsdb: A New Tool for JDK 9
        Link

SQL Server – Linked Server – TroubleShooting – SysInternals / Process Monitor

Background

Though better minds would disagree, I am lured into running OLE/DB providers out of process when configuring Linked Servers.

 

SysInternals/Process Monitor

For one, it is a bit easier to debug and troubleshoot via SysInternals/Process Monitor.

 

Filter

Here is what we will be filtering on…

Image

Explanation

  1. Process Name
    • is
      • dllhost.exe

 

Capture

Image

Explanation

  1. sqlservr.exe
    • TCP*
      • TCP Accept
      • TCP Receive
      • TCP Send
  2. DLLhost.exe
    • Process Create
    • Thread Create
  3. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Windows )
    • dll
      • combase.dll
      • sechost.dll
      • rpcss.dll
  4. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( OLE-DB )
    • dll
      • C:\Program Files\Common Files\System\Ole DB\oledb32.dll
      • C:\Windows\System32\MSDART.dll
      • C:\Windows\System32\ole32.dll
      • C:\Program Files\Common Files\System\Ole DB\oledb32r.dll
  5. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Windows )
    • dll
      • C:\Windows\System32\comsvcs.dll
      • C:\Windows\System32\authz.dll
      • C:\Program Files\Common Files\System\Ole DB\msdaps.dll
      • C:\Program Files\Common Files\System\Ole DB\msdaps.dll
  6. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Oracle / Client )
    • dll
      • C:\Program Files\Common Files\System\Ole DB\msdaps.dll
      • E:\app\oracle\client\product\12.2.0\client_1\bin\OraOLEDB12.dll
      • E:\app\oracle\client\product\12.2.0\client_1\bin\VERSION.dll
      • E:\app\oracle\client\product\12.2.0\client_1\bin\OraOLEDBgmr12.dll
      • OCI
        • E:\app\oracle\client\product\12.2.0\client_1\bin\OCI.dll
        • C:\Windows\System32\OCI.dll
        • E:\app\oracle\client\product\12.2.0\client_1\oci.dll
      • E:\app\oracle\client\product\12.2.0\client_1\bin\MSVCP120.dll
  7. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Windows – MSVCP* )
    • dll
      • Visual C++ Redistributable Packages for Visual Studio 2013
        • C:\Windows\System32\msvcp120.dll
        • E:\app\oracle\client\product\12.2.0\client_1\bin\MSVCR120.dll
  8. Oracle.key
    • E:\app\oracle\client\product\12.2.0\client_1\bin\Oracle.Key
  9. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Oracle / Client )
    • dll
      • E:\app\oracle\client\product\12.2.0\client_1\oraociei12.dll
        • The oraociei12.dll file is the main binary for Instant Client
      • E:\app\oracle\client\product\12.2.0\client_1\oraons.dll
        • Oracle Basic Instant Client & Oracle Basic Light Instant Client
  10. Oracle Key & OLEDB DLLS
    • E:\app\oracle\client\product\12.2.0\client_1\Oracle.Key
    • E:\app\oracle\client\product\12.2.0\client_1\Oraoledbic12.Dll
    • E:\app\oracle\client\product\12.2.0\client_1\OraOLEDB12us.dll
    • E:\app\oracle\client\product\12.2.0\client_1\OraOLEDBpus12.dll
  11. Oracle Network
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\oraaccess.xml
    • C:\Windows\System32\mswsock.dll
      • MS Windows WinSock
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\sqlnet.ora
    • E:\app\oracle\client\product\12.2.0\client_1\log\diag\clients
    • C:\Windows\System32\SHCore.dll
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\intchg.ora
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\tnsnav.ora
    • E:\app\oracle\client\product\12.2.0\client_1\oraociei12.dll
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\tnsnames.ora
    • E:\app\oracle\client\product\12.2.0\client_1\NETWORK\mesg\tnsus.msb
  12.  Network
    • TCP Reconnect
      • :

        -> [destination/hostname]:[destination/port-number (1521) ]

  13. Oracle Client Logs
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user\host_[####_###]\trace
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user\host_[####_###]\trace\sqlnet.log
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_[####_###]\incident
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_[####_###]\metadata
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_[####_###]\alert\log.xml
  14. Network
    • TCP Send/Receive/Disconnect
      • [destination/hostname]:[destination/port-number] ->

        :

  15. Thread Exit/Process Exit/Close File

 

 

Oracle Log files

User_host_trace

C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_####_###\trace

Image

Textual


***********************************************************************

Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 12.2.0.1.0 - Production
Time: 21-FEB-2018 10:46:59
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: Message 12535 not found; No message file for product=NETWORK, facility=TNS
ns secondary err code: 12560
nt main err code: 505

TNS-00505: Message 505 not found; No message file for product=NETWORK, facility=TNS
nt secondary err code: 60
nt OS err code: 0
Client address: 

Tabulated

  1. Errors Recorded
    • Main
      • Fatal NI connect error 12170
    • Detail
      • TNS-12535
        • ns secondary err code :- 12560
        • nt main err code :- 505
      • TNS-00505
        • ns secondary err code :- 60
        • nt OS err code :- 0

User_host_trace

C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_##\alert\log.xml

Image

Explanation

XML file which contains error messages from the Oracle Connectivity Client.

 

Summary

Our error is “Fatal NI connect error 12170” and that translates to “ORA-12170: TNS:Connect timeout occurred“.

Basic firewall issues.

Though DCOM itself as a container does not expose a lot of errors, client applications themselves are free to do so.

Using SysInternals’ Process Monitor, we are able to quickly familiarize ourselves with our OLE-DB Provider.

 

References

  1. Native Instruments
    • Native Instruments > General > OS and Computer Tuning
      • Windows Error Message: Missing MSVCP120.dll File
        Link
  2. Oracle Technology Network / Database / Database Features / Oracle Call Interface
    • Oracle Instant Client ODBC Release Notes
      Link
  3. Burleson Consulting
    • Donald Burleson
      • fatal ni connect error 12170
        Link

SQL Server – Linked Server – TroubleShooting

Background

There are a variety of avenues for diagnosing Linked Server related connectivity issues.

 

Follow Up

As a quick follow up to earlier conversations on SQL Server and heterogeneous data sources:

  1. SQL Server / Linked Server – Oracle ODAC
    Link

 

Outline

Here are some of the tools for diagnosis heterogenous data related issues:

  1. Query
    • DBCC
      • Trace Flag 7300
  2. SQL Server Profiler
  3. Event Viewer
  4. SysInternals
    • Process Explorer

 

 

Query

DBCC

Trace Flag 7300

Query
Syntax

dbcc traceon(7300) 
	with no_infomsgs
	;
go

dbcc traceoff(7300)
	with no_infomsgs
	;
go


Sample

dbcc traceon(7300) 
	with no_infomsgs
	;
go

declare @linkedServer sysname

set @linkedServer = 'HRDB';

exec sp_tables_ex
		@table_server = @linkedServer

go

dbcc traceoff(7300)
	with no_infomsgs
	;
go


Sample – Grid
Sample – Textual

OLE DB provider "OraOLEDB.Oracle" for linked server "PRD" returned message "ORA-12170: TNS:Connect timeout occurred".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41 [Batch Start Line 4]
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "PRD".


Explanation
  1. Error Message
    • OLE DB provider “OraOLEDB.Oracle” for linked server “PRD” returned message
      • “ORA-12170: TNS:Connect timeout occurred”.
    • Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server “PRD”.
  2. Using DBCC TraceOn (7300)
    • Allows us to see that we are experiencing connection timeout to the Oracle Server
    • Likely means network or TNS configuation error

 

 

SQL Server Profiler

Trace Properties

Trace Event Selections

Here are the events that we will be tracing on…

Event Category Event Event Description
Errors and Warnings
ErrorLog  Indicates error events logged in the SQL Server error log.
EventLog  Indicates events logged in the Windows application event log.
Exception  Indicates that an exception has occurred in SQL Server.
Execution Warnings  Indicates any warnings that occurred during the execution of a SQL Server statement or stored procedure.
User Error Message  Displays the error message as seen by the user in the case or an error or exception. The error message text appears in the TextData field.
OLEDB
OLEDB Call Event Includes event classes produced by the execution of stored procedures.
OLEDB Data Read Event Displays OLE DB IRowset::GetData calls made by SQL Server for fetching row data for distributed queries and remote stored procedures.
OLEDB Errors Indicates that an OLE DB error has occurred.
OLEDB Provider Information Occurs when a distributed query is run and collects information corresponding to the provider connection. This event class contains all the properties collected from the remote provider using various property sets such as DBPROPSET_DATASOURCEINFO, SQLPROPSET_OPTHINTS, DBPROPSET_SQLSERVERDATASOURCEINFO (SQL Server only), DBPROPSET_SQLSERVERDBINIT (SQL Server only) and DBPROPSET_ROWSET and interface IDBInfo.
OLEDB Query Interface Event Displays OLE DB IUnknown::QueryInterface calls made by SQL Server for distributed queries and remote stored procedures.
Security Audit
Audit Login Collects all new connection events since the trace was started, such as when a client requests a connection to a server running an instance of SQL Server.
Audit Logout Collects all new disconnect events since the trace was started, such as when a client issues a disconnect command.
Stored Procedures
RPC:Completed Occurs when a remote procedure call has been completed.
TSQL
SQL:BatchCompleted Occurs when the Transact-SQL statement has completed.
SQL:BatchStarting Occurs when a Transact-SQL batch is starting.

 

 

Traces

Sample Traces

Sample Trace #01
Image – Full Conversation

Image – OLEDB Call Event
Explanation
  1. SQL:BatchStarting
    • Issues sp_tables_ex
    • Query Linked Server asking for a listing of tables
  2. OLEDB QueryInterface Event
    • IID_IDBProperties
      • <ppunk>0x0000003C47F941D8</ppunk>
        • Set ppunk to 0x0000003C47F941D8
  3. OLEDB Call Event
    • Property
      • DBPROP_INIT_TIMEOUT
      • DBPROP_INIT_GENERALTIMEOUT
      • DBPROP_INIT_DATASOURCE
      • DBPROP_AUTH_USERID
      • DBPROP_AUTH_PASSWORD
    • Result
      • hresult
        • 265946
          • ???
  4. OLEDB QueryInterface Event
    • IID_IDBInitialize
      • input
        • IID_IDBInitialize
      • hresult
        • 0
      • ppunk
        • <ppunk>0x0000003C47F941E0</ppunk>
          • returns 0x0000003C47F941E0
    • IID_ISSAsynchStatus
      • input
        • IID_ISSAsynchStatus
      • hresult
        • -2147467262
  5. OLEDB Errors
    • hresult
      • -2147467259
  6. OLEDB Query Interface Event
    • input
      • IID_ISupportErrorInfo
    • hresult
      • 0
    • outputs
      • 0x00000044686EBFC0
  7. OLEDB Call Event
    • input
      • IID_IDBInitialize
    • hresult
      • 0
  8. User Error Message
    • OLE DB provider “OraOLEDB.Oracle” for linked server “PRD” returned message “ORA-12170: TNS:Connect timeout occurred“.
  9. Exception
    • Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server “PRD”.
  10. User Error Message
    • Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server “PRD”.

 

Event Viewer

Security

Filter

Tabulate

Courtesy of SANS.org:

SANS Institute
InfoSec Reading Room
Windows Logon Forensics
Link

Event  ID = Windows XP / Windows 2000 Windows 2008/2012/2014, etc Event Description
 528 4624 Successful logon: A user successfully logged on to a computer. For
information about the type of logon, see the next section
 529 4625  Logon failure. A logon attempt was made with an unknown user name
or a known user name with a bad password. For Windows 2008 and
above, event ID 4625 logs every failed logon attempt with failure status
code regardless of logon type or type of account
530 4625 Logon failure for a logon attempt to log on outside of the allowed time
 531 4625  Logon failure for a logon attempt using a disabled account.
 532 4625  Displays the error message as seen by the user in the case or an error or exception. The error message text appears in the TextData field.
 533  4625 Logon failure. A logon attempt was made by a user who is not allowed
to log on at this computer.
 534 4625 Logon failure. The user attempted to log on with a type that is not allowed.
 535 4625 Logon failure. The password for the specified account has expired.
 536 4625 Logon failure. The Net Logon service is not active
 537 4634 Logon failure. The logon attempt failed for other reasons. In some
cases, the reason for the logon failure may not be known.
 538 4634 The logoff process was completed for a user.
 538/551 4647 A user initiated the logoff process. It is logged for Interactive and
RemoteInteractive logons in place of logoff event 538/4634.
 539 4625 Logon failure. The account was locked out at the logon
 540 4624 Successful network logon: A user successfully logged on over a
network.
 552 4648 A user successfully logged on to a computer using explicit credentials while already logged on as a different user
 638 4778 A user has reconnected to a disconnected terminal session.
 683 4625 A user disconnected a terminal session without logging off.

 

Image

 

Sample
Sample 01
Image – Top

Image – Bottom
Explanation
  1. Security ID
    • NULL SID
  2. Logon Type
    • Logon Type 3 is Network
  3. Impersonation Level
    • Impersonation
  4. New Logon
    • Security ID
      • LABdadeniji
    • Account Name
      • dadeniji
    • Account Domain
      • LAB
  5. Network Information
    • Workstation Name
      • DADENIJI
    • Source Network Address
      • w.x.y.z
    • Source Network Port
      • #####
  6. Detailed Authentication
    • Logon Type
      • NtLmSsp
    • Authentication Package
      • NTLM
    • Package Name (NTLM only)
      • NTLM V2
    • Key Length
      • 128

SysInternals

Process Explorer

Application Context

OLE-DB Provider can be configured to run in process or out of process.

If run in process we need to track the sql server service.

When ran out of process, we need to track via dllhost.exe

Sample

Sample – 01
Image

Explanation
  1. dllhost.exe
    • COM Class
      • MSDAINITIALIZE Class
      • c:program filescommon filessystem32ole dboledb32.dll
      • OLEDB Core Services

tasklist

Preface

The OLE-DB Provider can either be configured to run as in-process or out-of process.

When in process it runs within the sqlservr.exe address space.

When out of process, it runs within a surrogate process, dllhost.exe.

out of Process – dllhost.exe

syntax

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

Output
Output # 01

Explanation
  1. We can see that Oracle dlls are loaded within the dllhost.exe

 

References

  1. Microsoft
    • Microsoft Developer
      • SQL BI / Data Access Technologies
        • Snehadeep
          • Troubleshooting “Cannot create an instance of OLE DB provider”
            Link
    • CSS SQL Server Engineers
      • pssql
        • How to get up and running with Oracle and Linked Servers
          Link
  2. Gianluca Sartori
    • Setting up linked servers with an out-of-process OLEDB provider.
      Link
  3. Sans Institute
    • Windows Login Forensics
      Link

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

 

Apache – jMeter – Preparation – Java JRE

Background

Pardon the pun, but measuring out jMeter appropriateness for a task we have at hand.

Error

image

NotAbleToFindJavaExecutableOrVersion_20171023_0536PM

 

textual

jmeter
Not able to find Java executable or version. Please check your Java installation.
errorlevel=2
Press any key to continue . . .

 

Troubleshooting

Validation

JRE

Outline

Let us see if we have JRE installed, and if so what version.

 

Steps

  1. Launch MS Windows Control Panel
  2. If Java Applet is not present, then we do not have Java installed, and we can skip the rest of our validation steps
  3. If Java Applet is present, access the Java Applet
  4. The “Java Control Panel” window appears
  5. Access the Java Tab
  6. Click the View button
  7. The “Java Runtime Environment Settings” window appears
    • There are two tabs, User and System
      • User Tab
        • In our User tab, we noticed that we have JRE 1.7 installed
        • We also noticed that its path indicates an install path of “C:\Program Files (x86)\Java\jre7\bin\javaw.exe”
        • Having it in x86 is ominous for us, as our OS in 64-bit

 

Image

javaRuntimeEnvironmentSettings_20171023_0539PM

 

Remediation

Download and install 64-bit Java JRE if your OS is 64-bit

 

Validation

Image

javaRuntimeEnvironmentSettings_20171023_0548PM

 

Tabulated

  1. We have two versions of JRE
    • v1.8
      • Path :- C:\Program Files\java\jre1.8.0_131\bin\javaw.exe
      • Architecture :- x86_64
    • v1.7
      • C:\Program Files (x86) \ Java\jre7\bin\javaw.exe
      • Architecture :- x86

 

Explanation

It looks like we can rumble with x86_64.

 

Success

Ran jmeter.bat again and we are good!

jmeter_20171024_0903AM

DBeaver – Database Connection Configuration – Oracle – Oracle JDBC Driver

Background

Towards a couple of sample Oracle Database sample exercises that we have in mind wanted to make sure that our all purpose database client, DBeaver, is able to communicate with an Oracle Database.

 

Preparation

Oracle

Java Version

Objective

The version of JDBC Client that we need will be based on the version of JRE that is installed or will be installed on the computer.

And, so let us establish that first; that is determine the JRE Version Number.

Outline

Using Control Panel, find and launch the Java applet.

Once Java Control Panel window appears, please access the “General” tab.

Within the “General” tab, click the “About” button

Review the Version Numbers listed and exit the “About” panel, once satisfied.

Next access the “Java” tab and review the System and User settings.

Within the Java tab, we are able to enable and disable each installed JRE package.  They are listed by Version #.

Image

Image – About

controlPanel_java_about__20171013__0831PM

 

Explanation
  1. The version number is “Version 8 Update 40” ( build 1.8.0_40)
    • We have Version 8
    • Build 1.8
Image – Java Runtime Environment Settings
Image – Java Runtime Environment Settings – System

Image – Java Runtime Environment Settings – User

Image – Java Runtime Environment Settings – System

Download

Download URL

Please visit Oracle’s JDBC download web site.

The URL is Link.

And, the current version# is 12.1.0.2.

 

Image

Matrix

Jar Package Description Detail
ojdbc7.jar JDBC driver classes except classes for NLS support in Oracle Object and Collection types. NLS Support which enables Internalization are excluded
ojdbc7_g.jar Same as ojdbc7.jar except compiled with “javac -g” and contains tracing code. Tracing & Debugging support included
ojdbc7dms.jar Same as ojdbc7.jar, except that it contains instrumentation to support DMS and limited java.util.logging calls. Instrumentation & Logging Supported included
ojdbc7dms_g.jar Same as ojdbc7_g.jar except that it contains instrumentation to support DMS. Instrumentation included

 

Explanation

We do not need support for NLS, National Language, english is sufficient.

And, we do not need tracing and logging as will not be developing code and thus need to capture code related errors and instrumentation.

In short, the base package, ojdbc7.jar, is sufficient.

Download

Please download the Jar file and move it to a location where you will like to have it available to our Client, DBeaver.

Configuration

Please launch DBeaver and let us review and configure the list of JDBC Drivers.  And, also configure connections to our various Oracle Database hosts.

Outline

  1. Driver Manager
  2. Connection

 

Driver Manager

The list of Drivers currently availed is accessible through the menu items Database \ Driver Manager.

Outline

  1. Please click the menu item Database / Driver Manager
  2. In the “Driver Manager” window
    • Choose Oracle
    • Click the “Edit” button
  3. In the “Edit Driver Oracle” window
    • Tab – Libraries
      • Review the list of libraries registered
      • Add
        • If the ojdbc driver is missing
          • Please click the “Add File” button
          • Navigate to the folder where the JDBC Driver is kept
          • And, select the jdbc jar file
          • Ensure that jar file is listed
      • Driver Class
        • Click the “Find Class” button
        • In the “Driver Class” dropdown, the Driver classes are availed
          • Driver Class
            • oracle.jdbc.OracleDriver
            • oracle.jdbc.driver.OracleDriver
          • The original driver class is oracle.jdbc.driver.OracleDriver
          • And, the modern one is oracle.jdbc.OracleDriver
          • Please choose the modern one
      • Please press the OK button once you are satisfied

Images

Driver Manager – Oracle

Edit Driver “Oracle” – Before Adding Jar File

Edit Driver “Oracle” – After adding Jar file

Edit Driver “Oracle” – Choose Driver Class – Choosing

Edit Driver “Oracle” – Choose Driver Class – Chosen

 

Connection

New Connection

Please click the “Database” / “New Connection” menu item to create a new connection.

Outline

  1. In the “Select new connection type” panel
    • Please select the Oracle driver
  2. In the “Oracle Connection Settings” panel
    • Tabs
      • Tab – General
        • Group Box
          • Group Box – Connection Type
            • In the “Basic” group box
              • Host
                • Please enter the Oracle DB Server Hostname
                  • In our case localhost
              • Port
                • Please enter the Port Number for the Oracle Instance
                  • In our case 1521
              • Database
                • Please enter the database name
                  • As we are local, we clicked on the database name scroll bar and we chose XE from the discovered local instances
              • Service or SID
                • Chose SID
            • Test Connection
              • Please click the “Test Connection” button to validate your connection settings
                • If connection attempt fails, you will get an error message similar to
                  • I/O error :- The Network adapter could not establish a connection
      • Tab – Oracle Properties
        • Group Box
          • Group Box – Session Settings
            • Language
              • Please choose Default from the list of languages
            • Territory
              • Please choose Default from the list of territories
            • NLS Date Format
              • We left the “NLS Date Format” textbox empty
                • As there is no need for Internalization
          • Group Box – Content
            • Hide empty schemas
              • Please unclick the default setting of hiding empty schemas
                • Hiding empty schemas could create potential for not seeing the entire schema footprint
            • Always Show DBA Objects
              • Left unchecked as we will are not currently interested in seeing metadata information availed through the DBA schema
          • Tab – Driver Properties
            • Will not expand on this tab at this time
    • Click the Next button
  3. In the “Network” panel
    • No need for SSH Tunnel / Proxy

Image

Select new connection type

Oracle Connection Settings
Oracle Connection Settings – General – Initial

Oracle Connection Settings – General – Completed – Incorrect

Oracle Connection Settings – General – Completed – Correct

 

Create new Connection – Network

Create new Connection – Test Connection

When test connection fails…

Create new Connection – Test Connection – IO Error : The Network Adapter could not establish the connection – Image

Explanation
  1. Oracle services not started
Create new Connection – Test Connection – ORA-12514, TNS:listener does not currently know of service requested in connect descriptor – Image

 


Create new Connection – Test Connection – ORA-12514, TNS:listener does not currently know of service requested in connect descriptor – Textual

Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

Explanation

When test connection fails…

Create new Connection – Test Connection – When things are good

Create new Connection – Finish Connection Creation
Create new Connection – Finish Connection Creation – Initial

Create new Connection – Finish Connection Creation – Completed

 

Usage

Sample Query

Session Information

Query


SELECT 
         SYSDATE AS currentDate 
       , SYS_CONTEXT ('USERENV', 'SESSION_USER') AS currentUser
       , SYS_CONTEXT('USERENV','HOST') AS host       
       , SYS_CONTEXT('USERENV','TERMINAL') AS terminal
       
FROM SYS.DUAL

Output

References

  1. Oracle Docs
  2.  Oracle-Base
    • Identifying Host Names and IP Addresses
      Link

Oracle Database Client on Windows

Background

In the last couple of posts we touched on installing Oracle XE to avail a small footprint Oracle DB Server.

Later we used SQLPlus to validate connectivity and add a new ‘regular‘ user.

 

Lineage

  1. “Oracle Database 11g Express Edition” – Installation on Windows
    Link
  2. Oracle – SQLPlus – Create new User
    Link

Download Area

The current version of the Oracle Database Engine Client is Oracle Database 11g Release 2 (11.2.0.1.0).

It is available for download here.

Which App

We will be using the Oracle Client DB Connectivity layer on MS Windows x64 bit.

Unfortunately, the client tool is Microsoft’s Business Intelligence Development and that client is 32 bit, we will have to take the Oracle 32-bit Client.

Here it is….

Size wise It is 700 MB.

Install

Select Installation Type

Image

Details

  1. InstantClient ( 174 MB )

 

Specify Installation Location ( Step 2 )

Image

Before

After

Details

  1. Changed from user specific folder to generic folder

Perform Prerequisite Checks ( Step 3 )

Image

Before

After

Details

  1. Path
    • Path’s Length
      • Value
        • Expected Value :- 1023
        • Actual Value :- 1239
      • Oracle will like for the path length to be less than 1024
      • Chose to Ignore

 

Summary ( Step 4 )

Image

Details

  1. Global Settings
    • Disk Space :-  174 MB
    • Install Type :- Instant Client
    • Oracle Home Location :- D:\app\oracle\product\11.2.0\client_1
  2. Inventory Information
    • Inventory Location :-  C:\Program Files (x86)\Oracle\Inventory

 

Install Product ( Step 5 )

Image

Validation

tnsping

Let us tnsping to see if we can connect to our locally installed Oracle XE ( Oracle Express Engine ).

Command


tnsping XE

Output

tnsnames.ora

Please review and modify tnsnames.ora to add new & modify existing alias.

Commendation

Please review Tyler Chessman very generous write-up for a good, solid, write-up on connectivity installation & configuration between SQL Server and Oracle.

 

References

  1. Tyler Chessman
    • Connecting to an Oracle Database from SQL Server and Microsoft BI Tools
      Avoid common pitfalls
      Published On :- 2014-May-14th
      Link