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

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