SQL Server – BulkCopy ( BCP ) – Which data file?

Background

Using BCP, we are churning though quite a bit of files.

Unfortunately, the DOS batch file that I quickly stitched together missed an importantly functionality.

And, that functionality is to log the current file being processed.

Diagnostic

Microsoft

Resource Monitor

I am a big fan of Microsoft’s Resource Monitor.

Let us use it.

Preparation

We remote connect to the source computer and launched task Manager; from Task Manager accessed Resource Monitor.

Resource Monitor – Tab – Memory

Image

ResourceMonitor_Tab_CPU_ProcessesAndAssociatedHandles_20180706_0850AM.png

Explanation
  1. When we filter on the bcp.exe process, we see our data file as one of the files mentioned in the “Associated Handles” tab

 

Resource Monitor – Tab – Memory

Image

ResourceMonitor_Tab_Memory_20180706_0852AM.png

Explanation
  1. The BCP process is using about 14 MB

 

Resource Monitor – Tab – Disk

Image

ResourceMonitor_Tab_Disk_20180706_0907AM.png

Explanation
  1. sqlserver,exe is mentioned
  2. But, not our bcp.exe file

 

Summary

To get a reliable insight into which files are being accessed, please consider Microsoft’s own Resource Monitor; specifically the CPU tab.

 

 

Error: “The requested operation could not be completed due to a file system limitation (mscorlib”

Background

Experiencing an error when trying to run an embedded process.  Part of the functionality that is bundled in the process, is a file copying module.

Error Message

Here is the error message :-

*** Error: The requested operation could not be completed due to a file system limitation (mscorlib) ***

Trouble Shooting

Application Message

Thankfully the particular file that is copied is logged, as well.


Copying log backup file to temporary work file. Source: '\\LABDB\Backup\hrdb_20180703104507.trn', Destination: '\\LABMirror\backup\HRDB_20180703104507.wrk'

Fast Copy

Attempted same file copy in FastCopy.

And, again thankfully it reported an error message; along with an error number.

Image

FastCopy_20180705_0712PM_v2.png

Text


WriteFileWait(The requested operation could not be completed due to a file system limitation665) : \\LABMirror\backup\HRDB_20180703104507.wrk
TotalRead  = 40,414 MB
TotalWrite = 40,289 MB
TotalFiles = 0 (0)
TotalTime  = 26:50
TransRate  = 25.0 MB/s
FileRate   = 0.00 files/s 

Explanation

  1. Error
    • Error Operation :- WriteFileWait
    • Error Description :- The requested operation could not be completed due to a file system limitation
    • Error Number :- 665

 

Error Causation

There are a few probably causes for error number 665.

Compression?

File

Finally, we checked the compression setting on others files in the targeted folder.

File Property

Image

file_20180706020000_trn_brushedup.png

Textual
  1. Size :- 2.07 GB
  2. Size on Disk :- 760 MB
Explanation

We can see that our file is compressed.

Folder Settings

Reviewed the Folder’s Advanced Attributes.

Image

AdvancedSettings_20180705_0717PM.png

Explanation

  1. Compress Contents to save disk space ( is enabled )

 

Remediation

Compression

Setting

We turned off compression on the targeted folder.

Fast Copy

Retried Fast Copy and it worked successfully.

NoError__20180705_1014PM_v2.png

 

Summary

Wished the original application displayed the OS’s error number and not just a simple error text that read “Error: The requested operation could not be completed due to a file system limitation (mscorlib)“.

Thankfully, there was enough diagnostic data in regards to the original file and destination folder that allowed us to replay via a more illuminating tool.

 

 

 

 

 

Windows Subsystem for Linux ( WSL ) – Error – “The app that you are trying to run is not supported on this version of Windows”

 

Background

If you try to enable “Windows Subsystem for Linux” ( WSL ) on a Windows 10 machine, you may unfortunately run into the error noted in our title message.

 

Error

Error Message

Image

NotSupportedOnThisVersionOfWindows_20180612_1012PM.png

Textual


The app that you are trying to run is not supported on this version of Windows.

 

Steps to reproduce

Currently, I am able to reproduce this error very easily.

The pathway towards reproducing are simple and direct.

And, they are:

  1. lxrun.exe /install
  2. bash

The commands above are meant to enable Linux if not currently installed.

Why?

Why am I not able to enable Linux on my Windows 10 system.

Requirements

The basis requirements are :-

  1. Bitness
    • 32-bit ( NO )
    • 43-bit ( YES )
  2. MS Windows 10 – Marketing
    • Anniversary Update  ( 2016-August-2nd )
    • Creator Update ( 2017-April-5th )

 

Versioning

Version & Build

Let us go get our Version Number

There are a couple of ways to get our Version Number.

Outline

  1. Windows System
    • About
  2. Winver

Steps

Windows System – About

  1. Access Windows System
  2. In Windows System, click on the menu Item ( Help \ About )

Image

controlPanel_System_Help_About_20180613_1050PM

Explanation

  1. Windows Edition
    • Windows 10 Enterprise 2016 LTSB

 

winver

Image

winver_20180613_1058PM

Explanation

  1. Version :- 1607
  2. OS Build :- 14393.2312

Marketing

Let us use Wikipedia to map Version Number to the Marketing Name.

Image

Windows 10 version history
Link

wikipedia_20180613_1106PM

Explanation

Our version # is 1607.

1607 maps to “Anniversary Update”.

Can we get Linux ?

Can we enable Linux Sub-system?

MSFT’s documentation:

Install the Windows Subsystem for Linux
Link

Based on the doc referenced above, here are the Marketing Versions Supported:

  1. Anniversary Update  ( 2016-August-2nd )
  2. Creator Update ( 2017-April-5th )

But, we are still stuck!

Why Stuck

It appears that though we are able to take all the necessary steps :

  1. Enable Developer Mode
  2. Enable Linux Subsystem feature

but, because we are on LTSB, we are in a tight mud.

LTSB?

DUKE

TO LTSB or Not

Link

With the release of Windows 10 in 2015, Microsoft introduced a new sub-edition of Windows 10 Enterprise called “Long Term Servicing Branch” or “LTSB”. Each release of Windows 10 Enterprise LTSB will remain relatively unchanged–receiving only security updates and bug fixes, but no feature updates–through a 10-year lifespan.

To date, Microsoft has delivered two releases of Windows 10 Enterprise LTSB (2015 and 2016) with the next expected in 2019. While, according to Microsoft, LTSB was “designed for special-purpose PCs such as those used in point-of-sale systems or controlling factory or medical equipment”, some in IT have deployed it to common end-user computers, citing the benefit of having no Windows Store apps (which includes Microsoft Edge and Cortana) and no semi-annual feature updates to deal with.

However, recent articles and an updated Microsoft FAQ point out that, as released versions of Windows 10 Enterprise LTSB will not receive newer features, they will also not be supported on newer computer processors (such as Intel’s eighth-generation “Kaby Lake Refresh” architecture, released in August, 2017) . This introduces a potential down-side to deploying LTSB, but it’s not a new concept, as both Windows 7 and Windows 8.1, both still fully supported by Microsoft on older hardware, are only partially supported on Intel’s sixth-generation “Skylake” processors and are not supported on the seventh-generation “Kaby Lake” processors.

Windows 10 – Marketing Name via PowerShell

Background

Reading through MSFT’s documentation on installing the “Linux Subsystem on MS Windows” brought to light the need to familiarize oneself with the various marketing moniker for each Released build of MS Windows 10.

Literature

Here is the specific document…

Install the Windows Subsystem for Linux
Link

And, here are the specific texts that juiced my appetite:

Image

Falls Creator Update

linuxPlatform_FallCreatorUpdate_20180613_1150AM.png

Anniversary Update and Creators Update

linuxPlatform_AnniversaryAndCreatorsUpdate_20180613_1152AM.png

Explanation

So the question is which MS Windows 10 Update am I running ….

Referenced Work

Thanks goodness there are various credible sources doing the heavy work of lining up MSFT’s version #, Marketing Version, and Build Number  low & high bars.

Here are the sources we used for this post:

  1. Microsoft
    • Windows 10
      • Windows 10 release information
        Link
  2.  Wikipedia
    • Windows 10 version history
      Link
  3. PureInfotech
    • Mauro Huculak
      • Windows Update History information helps you know exactly what’s the latest version of Windows 10 available.
        Link

Image

Wikipedia – Windows 10 – Version History

wikipedia_Windows10VersionHistory_20170613_1225PM

Code

Text file

Text file – UpdateList.txt

Outline

  1. Here is our file’s content
    • Version
    • Marketing
    • Build Number – Min
    • Build Number – Max

1809, Windows 10 version 1809 (Late 2018), 17686.00, 17686.00
1803, Windows 10 version 1803 (April 2018 Update) history, 17133.73, 17134.83
1709, Windows 10 version 1709 (Fall Creators Update) history, 16299.15, 16299.461
1703, Windows 10 version 1703 (Creators Update) history, 15063.11, 15063.1112
1607, Windows 10 version 1607 (Anniversary Update) history, 14393, 14393.2189
1511, Windows 10 version 1511 (November Update) history, 10586.104, 10586.1176
1507, Windows 10 version 1507 (Initial Release) history, 10240.16683, 10240.17831

PowerShell

PowerShell – getWindows10MarketingName.ps1

Outline

  1. Read flat file ( updateList.txt)
  2. Get MS Windows Version via calling [Environment]::OSVersion
    • Build Number
  3. Get MS Windows by issuing “Get-WmiObject Win32_OperatingSystem
    • Returns
      • Caption
      • OSArchitecture
  4. Registry Access – Get-ItemProperty
    • Argument :- HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion
    • Returns
      • ProductName
      • ReleaseID
      • CurrentBuildNumber
      • CompositionEditionID
  5. Iterate read file
    • Condition to indicate match
      • If ReleaseID from registry Path HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion matches read record
      • If Build Number falls into range of read record
    • Save Pointer
  6. If Pointer Saved
    • Display OS Version from table

Preparatory

Registry
Registry – HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion

registry_20180614_0700AM.png

 

Actual Code


Set-StrictMode -Version 1
  

[boolean]$debug=$false;
[string] $objOSVerNumberAsString = $null;
[double] $versionNumberMinAsNumber = 0;
[double] $versionNumberMaxAsNumber = 0;
[boolean] $bConverted = $false;
[string] $strLog = $null;
#[string] $CHAR_EMPTY='';
#PowerTip: Create Empty String with PowerShell
[string] $CHAR_EMPTY=[string]::Empty;

[string] $productName =$null;
[string] $releaseID =$null;
[string] $currentBuild =$null
[string] $edition =$null;

[string] $releaseIDInList = $null;

$debug=$false;
#$debug=$true;

$updateListFilename = 'updateList.txt';

#read file updateList.txt
$updateList = Get-Content $updateListFilename;

#Display Update List
#$updateList;

#Get OS version [Environment]::OSVersion
$objOSVer =[Environment]::OSVersion;

#Display OS version
Write-Host('OS Version');
Write-Host('----------');
Write-Host($objOSVer);


$objOSVerPlatform = $objOSVer.Platform;
$objOSVerServicePack = $objOSVer.ServicePack;
$objOSVerNumber = $objOSVer.Version;
$objOSVerString = $objOSVer.VersionString;


$objOSVerNumberAsString = [system.String]::Join($CHAR_EMPTY, $objOSVerNumber);


$objOSVerNumber0 = $objOSVerNumberAsString.split('.')[0];
$objOSVerNumber1 = $objOSVerNumberAsString.split('.')[1];
$objOSVerNumber2 = $objOSVerNumberAsString.split('.')[2];
$objOSVerNumber3 = $objOSVerNumberAsString.split('.')[3];


$objOSVerNumberBase = $objOSVerNumber0;
$objOSVerNumberSP = $objOSVerNumber1;
$objOSVerNumberBuild = $objOSVerNumber2;
$objOSVerNumberMinor = $objOSVerNumber3;

if ( $debug)
{

    Write-Host('');

    Write-Host('OS Version ( broken into pieces)');
    Write-Host('--------------------------------')  

    Write-Host('OSVersionPlatform      :- {0}' -f $objOSVerPlatform);
    Write-Host('OSVersionServicePack   :- {0}' -f $objOSVerServicePack);
    Write-Host('OSVersionNumberAsArray :- {0}' -f $objOSVerNumber);
    Write-Host('objOSVerNumberAsString :- {0}' -f $objOSVerNumberAsString);
    Write-Host('OSVersionString        :- {0}' -f $objOSVerString);

    Write-Host('objOSVerNumberSP       :- {0}' -f $objOSVerNumberSP);
    Write-Host('objOSVerNumberBuild    :- {0}' -f $objOSVerNumberBuild);

    Write-Host('');
}

  

$objWMIOS = (Get-WmiObject Win32_OperatingSystem);

if ($objWMIOS -ne $null)
{
	Write-Host('');

    Write-Host("WMI - Win32_OperatingSystem");
	#$objWMIOS;

	$WMIOSCaption = $objWMIOS.caption;
	$WMIOSArchitecture = $objWMIOS.OSArchitecture;

	$strLog = "`t WMI OS - Caption        :- {0}" -f $WMIOSCaption;
	Write-Host $strLog;

	$strLog = "`t WMI OS - OSArchitecture :- {0}" -f $WMIOSArchitecture;
	Write-Host $strLog;	

	Write-Host('');
}


$strRegPath = "HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion";
$objRegWinOS = (Get-ItemProperty $strRegPath);

if ($objRegWinOS -ne $null)
{

	Write-Host('');
    Write-Host("Registry Path $strRegPath");
	#$objRegWinOS;

	$productName = $objRegWinOS.ProductName;
	$releaseID = $objRegWinOS.ReleaseID;
	$currentBuild = $objRegWinOS.CurrentBuildNumber;
	$edition = $objRegWinOS.CompositionEditionID;

	$strLog = "`t WinOS Registry - productName :- {0}"`
				-f $productName;
	Write-Host $strLog;

	$strLog = "`t WinOS Registry - ReleaseID :- {0}" `
				-f $releaseID;
	Write-Host $strLog;

	$strLog = "`t WinOS Registry - Current Build :- {0}" `
				-f $currentBuild;
	Write-Host $strLog;

	$strLog = "`t WinOS Registry - Edition :- {0}" `
				-f $edition;
	Write-Host $strLog;

	Write-Host('');
}


$i = 0;
$entry = $null;
$entrySaved = $null;


$iNumberofUpdates = $updateList.Length;

Write-Host '';
Write-Host 'Iterating Build List looking to match OS Build Number ... ';
Write-Host '';


foreach ( $entry in $updateList )
{

  $releaseIDInList =$entry.split(',')[0];
  $update=$entry.split(',')[1];
  $versionNumberMin=$entry.split(',')[2];
  $versionNumberMax=$entry.split(',')[3];

  
  $bConverted = [double]::TryParse(`
                      $versionNumberMin`
                    , [ref] $versionNumberMinAsNumber);

  $bConverted = [double]::TryParse(`
                    $versionNumberMax`
                      , [ref] $versionNumberMaxAsNumber);                               

  if ($debug)
  {

    Write-Host('Build {0} -f $objOSVerNumberBuild ');

    $strLog = 'versionNumberMin {0}' `
				-f $versionNumberMinAsNumber;
	Write-Host($strLog);

    $strLog = 'versionNumberMax {0}' `
				-f $versionNumberMaxAsNumber;
	Write-Host($strLog);	

  }     

  if (
			( $releaseID -eq $releaseIDInList) `
		-or `
			( `
				     ( $objOSVerNumberBuild -ge $versionNumberMinAsNumber) `
				-and ( $objOSVerNumberBuild -le $versionNumberMaxAsNumber) `
			)
	 )
    {
        # save entry
        $entrySaved = $entry;

        break;
    }

  $i = $i + 1;  

} # foreach ( $entry in $updateList )   


if ($entrySaved -ne $null)
{

    # Get fields
    $matchVersion = $entrySaved.split(',')[0];
    $matchMarketing = $entrySaved.split(',')[1];
    $matchBuildMin = $entrySaved.split(',')[2];
    $matchBuildMax = $entrySaved.split(',')[3];

    Write-Host("Version Matched");

    Write-Host("`t Version   :-  $matchVersion");

    $strLog = "`t Build     :-  {0} through {1}" `
                -f $matchBuildMin.Trim() `
				 , $matchBuildMax.Trim();

    Write-Host($strLog);

    Write-Host("`t Marketing :- $matchMarketing");

}
else
{

    $strLog = 'Version Number ({0}) not found!' -f `
                $objOSVerNumberBuild;

    Write-Host($strLog);            

}

Invoke

Syntax

powershell -file ./getWindows10MarketingName.ps1

Output

getMSWindows10UpdateName_Work_20180613_0742PM.png

Source Code Control

GitHub

DanielAdeniji/WinOSMarketingNameUsingPS
Link

Listening

Listening to the song I first heard during yesterday’s parade:

Drake – Nice for What
Link

Lyrics

You got to be nice for what to these …..

Windows 10 – Installing Linux SubSystem

Background

Availing Linux on MS Windows 10.

Here is the guide that we will be using:

Windows 10 Installation Guide
Install the Windows Subsystem for Linux
Link

Outline

  1. Enable Windows Feature
    • Microsoft Windows Subsystem Linux
  2. Review OS Version
    • Issue SystemInfo and get OS Version and Build Number
  3. Choose Install Location, based on Build Number
    • If Build Number > 16215
      • We can install from Windows Store
    • Else
      • Enable Windows Developer Mode
      • Start Bash or use “lxrun /install
  4. Register Product
  5. Create Linux User
  6. Set root user’s password

 

Install Steps

Enable Windows Optional Features

Microsoft-Windows-Subsystem-Linux

Code

powershell -C "Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Windows-Subsystem-Linux"

System Info

Code


powershell -C "systeminfo | Select-String 'OS' "

Output

version_20180610_0902PM

Explanation

As our Build Number ( 17134 ) is greater than 16215, we can install via “Microsoft Store“.

Microsoft Store

Visit Microsoft Store and search for Linux.

https://www.microsoft.com/en-us/search/result.aspx?q=linux

Here is the result of that search:

MicrosoftStore_Linux.PNG

 

SUSE Linux Enterprise Server 12

We chose to go with SE Linux.

SUSE Linux Enterprise Server 12 – Get

SUSELinuxEnterprise_20180610_0905PM.PNG

Use across your devices

UseAcrossYourDevices_20180610_0906PM.PNG

Add your Microsoft account to Microsoft Store

AddYourMicrosoftAccountToMicrosoftStore_20180610_0921PM [brushedup].PNG

Enter Password

AddYourMicrosoftAccountToMicrosoftStore_EnterPassword_20180610_0923PM.PNG

Installing

Installing_20180610_0911PM

SUSE Linux Enterprise Server 12 – Registration +User

registration_20180610_0926PM.PNG

SUSE Linux Enterprise Server 12 – Registration +User +Password

registrationUserAndPassword_20180610_0927PM.PNG

Validation

Bash

uname

Get Linux Version

Code


uname -r

Output

bash_uname_20180610_0938PM

List Users

Code


cat /etc/passwd

Output

security_users_20180610_0947PM.PNG

References

  1. Microsoft
    • Install the Windows Subsystem for Linux
      • Install the Windows Subsystem for Linux
        Link
    • Rich Turner
      • Bash on Ubuntu on Windows – Download Now!
        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