SSMS – v2008 – Error – “Index was outside the bounds of the array. (Microsoft.SqlServer.Smo)”

Background

Drew a quick error running Sql Server Management Studio (SSMS).

Error

Text

The error reads :-

Index was outside the bounds of the array. (Microsoft.SqlServer.Smo)

Image

SSSM.v2008.IndexWasOutsideTheBoundsOfTheArray.20190203.0652AM.PNG

Explanation

  1. Error reads
    • Microsoft.SqlServer.Smo
    • Index was outside the bounds of the array

Recreate

Tried connecting to Microsoft SQL Server v2017 instance with Sql Server Management Studio v2008.

 

TroubleShooting

Microsoft.SqlServer.Smo

SysInternals / Process Explorer

Version

Outline
  1. Download SysInternals \ Process Explorer
  2. Launched SysInternal’s Process Explorer
  3. Selected the ssms.exe
  4. View process modules
    • Click on menu “View”\”Show Lower Pane”
  5. Find
    • Click on menu and choose Find
    • Enter smo
    • Reviewed found modules
  6. Launched Windows Explorer
  7. Accessed the found DLL
  8. Noted version number as 10.0.1600.22
Images
Image – Process Explorer

processExplore.20190203.0957AM.PNG

Image – Handle or DLL substring

findModule.smo.PNG

Windows Explorer

ssmo.sysInternals.processExplorer.20190203.0720AM.PNG

Remediation

Microsoft SQL Server 2008 Service Pack 4 Feature Pack

SQL Server SMO is bundled as part of SQL Server Feature Pack.

The latest version is Service Pack 4 and it is available here.

Packages

  1. Shared Management Objects
    • ENU\X86\SharedManagementObjects.msi
  2. Clr Types
    • ENU\X86\SQLSysClrTypes.msi

Outline

  1. Download
    • Download ENU\X86\SharedManagementObjects.msi
    • Download ENU\X86\SQLSysClrTypes.msi
  2. Install
    • Install SQLSysClrTypes.msi
    • Install SharedManagementObjects.msi

Screenshot

Download

SQL System CLR Types

clrtypes.20190203.0732AM.PNG

Management Objects

microsoft.smo.20190203.0728AM.PNG

Installation

Management Objects
Prerequisite – Missing Microsoft SQL Server 2008 System CLR Types

clrtypes.20190203.0730AM.PNG

Welcome

microsoft.smo.welcome.20190203.0733AM.PNG

System CLR Types
Installation – SQL Server 2008 System CLR Types

clrtypes.welcome.20190203.0732AM.PNG

Post Installation Review

Outline

  1. Post Installation
    • Microsoft.SqlServer.Smo.dll
    • Version
      • Moved from 10.0.1600.22 to 10.0.6000.29

Images

WindowsExplorer.Microsoft.SqlServer.smo.dll.20190203.1028AM.PNG

Acknowledged

Acknowledged Giles D Middleton.

GilesDMiddleton.20190203.1041AM

 

References

  1. stack overflow
    • Index was outside the bounds of the Array. (Microsoft.SqlServer.smo)
      Link
  2.  Microsoft
    • Microsoft SQL Server 2008 Service Pack 4 Feature Pack
      Link

 

SQL Server – v2017 – Install – “Oracle JRE 7 Update 51 (64-bit) is required for Polybase”

Background

Installing SQL Server v2017 and ran into an error that I have seen before.

Error

Error Image

OracleJRE7Update51_x64.2018109.1111AM.PNG

Error Textual

Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase

Tackled Before

History

Already tackled before here:

SQL Server (v2016) – Installation – Blocked – “Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase”
here

History

The fix applied was to install Java JRE downloaded from here:

https://www.oracle.com/technetwork/java/javase/downloads/index.html
Link

But, unfortunately even though install applied no help this time.

Trouble Shooting

Let us dig a bit deeper.

SysInternals

Process Monitor

Image

sysInternals.processMonitor.20181009.1010AM.PNG

Explanation

  1. Identified component used by SQL Server Setup
    • Component is ScenarioEngine
  2. ScenarioEngine
    • Log
      • C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\20181009_092126\Detail.txt

Tail

Tail For Win32

Download

Downloaded Tail For Win32 from here:

Tail for Win32
Link

Usage

Launched “Tail For Win32” and loaded the identified SQL Server Log file ( Detail.txt )

Results
Image
TailforWin32_Usage_20181009_1151AM
Textual

13) 2018-10-09 10:13:06 Slp: Init rule target object: Microsoft.SqlServer.Configuration.Polybase.Polybase_IsMinJavaVersionInstalledFacet
(13) 2018-10-09 10:13:06 SQLPolyBase: Could not find registry setting HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion.
(13) 2018-10-09 10:13:06 SQLPolyBase: Minimum version expected: 1.7.51. Java not found.
(13) 2018-10-09 10:13:06 SQLPolyBase: Rule 'Polybase_IsMinJavaVersionInstalled' detection result: IsMinJavaVersionInstalled= False
(13) 2018-10-09 10:13:06 Slp: Evaluating rule        : Polybase_IsMinJavaVersionInstalled
(13) 2018-10-09 10:13:06 Slp: Rule running on machine:
(13) 2018-10-09 10:13:06 Slp: Rule evaluation done   : Failed
(13) 2018-10-09 10:13:06 Slp: Rule evaluation message: This computer does not have the Oracle Java SE Runtime Environment Version 7 Update 51 (64-bit) or higher installed. The Oracle Java SE Runtime Environment is software provided by a third party. Microsoft grants you no rights for such third-party software. You are responsible for and must separately locate, read and accept applicable third-party license terms. To continue, download the Oracle SE Java Runtime Environment from https://go.microsoft.com/fwlink/?LinkId=526030.
(13) 2018-10-09 10:13:06 Slp: Send result to channel : RulesEngineNotificationChannel

Textual
  1. QLPolyBase: Could not find registry setting HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion.
    (13) 2018-10-09 10:13:06 SQLPolyBase: Minimum version expected: 1.7.51. Java not found.

Registry

regedit

Images

Regedit – Image – Before
Image

registry_20181009_1038AM.PNG

Textual
  1. Computer\HKEY_LOCAL_MACHINE\Software\JavaSoft
    • Java Plug-in
    • Java Update
    • Java Web Start
    • Java Web Start Caps
    • JRE
Explanation
  1. HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion.
    • Java Runtime Environment
      • Missing

Remediation

Outline

Please download Java JRE 8 and install it.

Download

Please download artifacts from here :-

Java SE Runtime Environment 8 Downloads
https://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.html
Link

Artifacts

Image

Java SE Runtime Environment 8u181

download_JavaSERuntimeEnvironment_20181009.1221PM.PNG

download_20181009.1026AM.PNG
Explanation
  1. Chose to download Windows X64

 

Install

Images

Destination Folder

download_DestinationFolder_20181009.1031AM.PNG

Installing
Installing -01

install_20181009.1032AM.PNG

java Setup – Complete

install_JavaSetupComplete_20181009.1033AM.PNG

Change in License Terms

download_JavaSERuntimeEnvironment_Roadmap_20181009.1030AM.PNG

Registry

Using regedit, please review the Java’s registry structure.

regedit

Images
Regedit – Image – After
Image

registry_java_jre_1Dot8_20181009_1243PM.PNG

Explanation
  1. HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion is now present

 

Summary

It appears that Oracle has changed the folder structure for Java in the Windows Registry.

Specifically what used to be

HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion.

is now

HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\JRE\CurrentVersion.

Please be sure to have Java JRE v1.8 ( Java 8) installed for SQL Server v2016\v2017 Polybase.

DataStore.edb

Background

An alarm was raised by our monitoring software.

An alarm is raised whenever a drive free space falls below 10%.

Combed the drive using SpaceSniffer and found out that the DataStore.edb file on C:\Windows\SoftwareDistribution\DataStore is larger than usual.

 

Image

Here it is clocking in at 1.3 GB

 

TroubleShooting

SysInternals

Process Monitor

Overview

Let us see if we can use SysInternal’s Process Monitor and determine which processes are accessing the DataStore.edb file.

Filter

Clause
  1. Path
    • Begins with C:\Windows\SoftwareDistribution\DataStore
Image

 

Capture

Image
 
Event Properties
Event Properties – Create File – Event

Event Properties – Create File – Event – Property
  1. Desired Access :- Read Attributes, Synchronize
  2. ShareMode :- Read, Write
Event Properties – Create File – Event

Image

Details

  1. Path :- C:\Windows\System32\svchost.exe
  2. Command Line :- C:\Windows\System32\svchost.exe -k netsvcs
  3. User :- NT AUTHORITY\SYSTEM

 

Services

Knowing that svchost.exe is a host for many services, which one is netsvcs?

Services Applet

Image

Explanation

We see it is the “Windows Update” service.

 

Process Explorer

Overview

Which program has datastore.edb opened?

Process Explorer Search

Menu Find

Using the menu item”Find Handle or DLL…”, sought for datastore.edb

Handle or DLL substring

 

Process Explorer Results

Here is the result from searching for DataStore.edb

What process is is using the marked PID

Our marked PID is 1012

WIthin Process Explorer ordered by Process ID, PID, and looked for our identified process ID, 1012.

What process is is using the marked PID

Right clicked on that Process and from the drop down menu chose the Properties item.

Here are the services that are using that running within the identified process.

 

Summary

Though DataStore.edb is principally used by the Windows Update Service, because svchost.exe is a shared process, it is going to take more than stopping Windows Update Service to prune / clean out the DataStore.edb file.

SQL Server – Files In use – Day 1

Background

One of the many areas that that one needs to keep an eye one when monitoring database engines  is which files are opened, how they are opened ( exclusively, read only), what other processes are competing for them, etc.

Day 1

This is Day 1 and so we will start off with the basic tools.

Microsoft

Lineage

Microsoft really did a very job with Resource Monitor.

Prior to Resource Monitor, Task manager was the go to quick tool.

Task Manager

Here is what Task Manager exposes:

unfortunately, it only exposes information at process level.

Resource Monitor

Tab – Disk

Here we see the active Disk Activities.

Explanation

  1. We are able to filter by Process
  2. And, we can order by
    • Process Name
    • File name
    • Reads/Writes/Totals

Tab – CPU

Tab – CPU – Original

When we switch over to the CPU Tab, here is what we see

Explanation

We are forced to choose a process.

Tab – CPU – Process = sqlservr.exe

Once we chose sqlservr.exe from the list of processes:

Tab – CPU – Process = sqlservr.exe = Drive C:

Explanation:
  1. Unfortunately, we were getting really bad storage utilization a few months ago, and had to move to System Drive C: till more storage was allocated
  2. Will come back and move the rollover data and log files
Tab – CPU – Process = sqlservr.exe = Drive D & E:

Explanation:
  1. Most of our SQL Server Data and log files are on Drive D & E:
  2. Will come back and segregate them

SysInternals

I real like the straightforwardness of SysInternals tools.

Handle

Here is how to use handle.

Scripts

Look for file handles

Here we ask for a specific process:

  1. -p = sqlservr
  2. type = file
Code

handle -nobanner -p sqlservr | find "File" | more

Output

Look for file handles – File  – Extension [ mdf, ndf, ldf]

Here we ask for files that have have df in their names.

  1. -p = sqlservr
  2. type = file
Code

handle -nobanner -p sqlservr | find "File" | find "df" | more

Output

SQL Server

Sql Server Management Studio ( SSMS )

You can also use SSMS Activity Monitor.

But, to me it is a big hammer to what one really needs.

Dedicated

Can’t go anywhere else but to SysInternals, Mark Russinovich & Bryce Cogswell.

To me they made it cool to want to look into things, Windows Internals anyone.

SysInternals – AccessCheck – Querying Folder\File Permissions

Background

Wanted to document how to use SysInternal’s accesschk to review NTFS permissions.

 

Premise

This effort was brought to bear by a question asked by desmando on the Windows SysInternals forum.

Question

The question is here

Image

accesschk64-desmando

Textual

I’m trying to look for files and folders and I don’t have access to. To test, I created a folder on my desktop and removed all rights to it. I then ran the following command:

accesschk64.exe -nsd "domain\username" c:\Users\username\Desktop\

It came back saying “No matching objects found.”

Is this not the right tool? Am I not using it right?

Environment Provisioning

File Security Assignment

We have three files.  And, they are in E:\BAK folder.

Our file names are File1.txt, File2.txt, and File3.txt

  1. For files 1 and 2, we are the owner
  2. On File 3
    1. Unchained it from the Folder permission set
    2. And, assigned ownerships to someone else

File List

File2.txt

File2.txt – Properties

securitysettings-security-file2

 

File2.txt – Advanced Setting Properties

advancedsecuritysettings-file2

 

File3.txt

File3.txt – Properties

securitysettings-security-file3

 

File3.txt – Advanced Security Settings

securitysettings-file3-advancedsecuritysettings

 

Script

Logged On user

Code


rem **********************************************************
rem -q Omit Banner
rem -nobanner No banner
rem -v Verbose
rem **********************************************************


set _folder=E:\BAK

set _principalSelf=%USERDOMAIN%\%USERNAME%

rem List all permissions on this folder
AccessChk -nobanner -d "%_folder%"

REM User me has access
AccessChk -nobanner "%_principalSelf%" "%_folder%"

REM User me has no access ( -n )
AccessChk -nobanner -n "%_principalSelf%" "%_folder%"



Output

 

filepermissionforself-20170126-0109pm

Explanation

  1. Used %USERDOMAIN%\%USERNAME% to get current logon’s Domain and User
  2. Retrieve permissions at folder level using -d
  3. Retrieve permission for user against all files
    • Listed 3 files
    • file1.txt and file2.txt we have permissions ( RW :- Read and Write )
    • file3.txt we do not have permission ( File name still listed but without permission set )
  4. Listed files that we do not have permission ( E:\BAK\file3.txt )

 

Another User

This other user does not have access

Script


rem **********************************************************
rem -q Omit Banner
rem -nobanner No banner
rem -v Verbose
rem **********************************************************


set _folder=E:\BAK

set _principal=AD\bpolakam

rem List all permissions on this folder
AccessChk -nobanner -d "%_folder%"

REM User me has access
AccessChk -nobanner "%_principal%" "%_folder%"

REM User me has no access ( -n )
AccessChk -nobanner -n "%_principal%" "%_folder%"



Output

filepermissionforanother-20170126-0119pm

Explanation

  1. Specified full domain name using Domain\Principal syntax
  2. Retrieve permissions at folder level using -d
  3. Retrieve permission for user against all files
    • Listed 3 files
    • file1.txt, file2.txt, and file3.txt  are all listed
      • File names still listed but without permission set
  4. Listed all 3 files as user does not have permission to any of the files

 

References

  1. By Aaron Margosis and Mark E. Russinovich – Windows Sysinternals Administrator’s Reference: Security Utilities
    Link

Web Downloaded Code and Hidden Characters

Background

Stole one line from the Web and can not even get it to work

 

SMH Moment

Another SMH moment. What good is to steal something and yet not be able to use it.

 

Error

Here is the stupid error, I am getting

Error Message

Image

executed-with-special-characters-20170120_1242pm

Textual


>accessChk_sqlServer.cmd

>..\AccessChk ûp sqlservr.exe ûf ûq ûv

Explanation

  1. Basically the – operator is being substituted with û

Review Code

Here is the code when I type it out in the Console

Image

view-with-special-characters

Textual

>type accessChk_sqlServer.cmd
..\AccessChk ûp sqlservr.exe ûf ûq ûv

Explanation

  1. Basically the – operator is being substituted with û

 

Clean Up

Notepad++

Launch Notepad++ and opened the file

Original Display

notepadplusplus-display-20160120-0108pm

Change Encoding to OEM-US

Let us change Encoding to OEM-US.

Here are our steps:

  1. Click on menu items
    • Encoding
    • Character sets
    • Western European
    • OEM-US

 

changeencodingtooem_us

 

Post Change Encoding

notepadplusplus-display-20160120-0113pm

 

Replace & Strip Out Special Characters

Find & Replace

Using Find & Replace to replace û with –

notepadplusplus-display-20160120-0115pm

 

Find & Replace – Post

Post “Find & Replace

notepadplusplus-display-20160120-0116pm

Explanation
  1. Please attention to the count of how many replacements was done

 

Save

Please save the revised document

Thankfully notepad++ color codes changed documents.

BTW, by Color Code I mean a red disk icon by the file name.

notepadplusplus-display-20160120-0120pm

 

Run Script

With the saved changes, took back to the console and re-ran the script.

executed-without-special-characters-20170120_0122pm

Ran successfully.

 

Dedicated

Dedicated to Mark Russinovich & Notepad++.