Ever need to build out a capability and the SQL query is your blocker? Use a SQL query Plan ‘howTo’ to figure out what’s taking query so long. My thanks to Dennis Zwahlen (a Data and AI CSA – LinkedIn ) helping me figure out what was causing a SCOM DW SQL query to render data VERY slowly!
Don’t get me wrong, the sheer volume of events is definitely part of the problem. Event rules are using expressions to further restrict collected event data.
SCOM DW Events ingested for DC Security Events when SIEM is a limit, and NOT using ACS feature. Will discuss the SCOM DW Event ingestion and additional XML authoring options to turn down the pressure.
Time to use the ‘SQL query Plan howto’ blog for SQL execution plan, to help to figure out why the DW Query takes so long. Using the execution plan, similar to SQL profiler, will provide insight to possibly speed up query, allowing PowerBI app/report rendering of data.
From SSMS > View > Add Display Estimated Execution Plan
From SSMS > View > Add Display Estimated Execution Plan
SQL execution plan starting from the left documenting SQL query
SQL query plan starting from the left documenting SQL query
Sort is taking 4.5 minutes in this example of the SQL execution plan visual. You can see moving right from the Join lines documents how SQL behaves, and how each piece affects overall execution.
SQL query plan starting moving right from the left documenting SQL query
Hope this helps for another diagnostic SQL step in your tool box!
VulnID 178852 – Vulnerable to hackers – SQL OLE DB Driver update required
Got another vulnerability pop up on the last scan. ‘Vuln 178852 OLE DB driver’ has vulnerabilities and needs updated. My experience links this NOT to ODBC vuln 175441, thereby related to added capabilities and drivers installed with SSMS v19. NOTE: OLE has a pre-req of the new Visual C++ Redistributable x86 and x64 bits. Let’s mitigate Vuln 178852 OLE DB driver update!
Quick outline of steps with Vuln 178852 OLE DB driver
Download the bits (and copy to repository and servers for install)
Once downloaded, copy the OLE DB Driver and VC Redistributable EXE’s for x64 and x86 to the affected servers. Search for OLE first, to assess OLE and Redistributable versions currently installed.
Assess ‘Vuln 178852 OLE DB driver’ updates on affected servers
Log into the server(s)
From Control Panel > Programs > Programs and Features > Search for ‘ole’ to see Redistributable versions
Check Control Panel for OLE DB Version
Check Redistributable version
From Control Panel > Programs > Programs and Features > Search for ‘Red’ to see Redistributable versions
From Control Panel > Programs > Programs and Features > Search for ‘Red’ to see Redistributable versions
If you don’t upgrade Visual C++ Redistributable first, you’ll get this setup error
Executing OLE DB Driver update pre-requisite error for Visual C++ Redistrubutable update
First, we have to install the Visual C++ updates to the server before we can update the driver.
From PowerShell (as admin) on affected servers
Go to saved directory for EXE and MSI files
PowerShell as admin > go to directory > run the EXE
Click the Check box to EULA ‘I agree’
At the Visual C++ Redistributable EULA splash screen
Check agree checkbox, then click Install button lower right
Visual C++ Redistributable EULA splash screen to check agree checkbox, then click on Install
Update installing
VC_Redistributable installing screenshot
Click Restart button (when in approved change window)
Click Restart when in change window to reboot server for Visual C++ update to apply
Restart server
Update VC_Redist.x86.exe
Second part, if applicable x86 library is installed, is to update.
Install next pre-req, if server contained both x86 and x64 bits for the ‘Vuln 178852 OLE DB driver’
From PowerShell (as admin) on affected servers:
Go to saved directory for EXE and MSI files
.\VC_redist.x86.exe
Powershell as admin window initiating the Visual C++ Redistributable x86 exe
Click the Check box to EULA ‘I agree’
At the Visual C++ Redistributable EULA splash screen
Check agree checkbox, then click Install button lower right
Click on ‘I agree’ checkbox, and click Install button to begin the x86 Visual C++ Redistributable update
Update installing
Screenshot installing the x86 Visual C++ Redistributable update
Update complete
Screenshot showing successful install of the x86 Visual C++ Redistributable update
Update MSOLEDB drivers
Third, assess first if you need x64 AND x86 drivers (my example is only x64)
Start by checking the Control Panel > Programs > Programs and Features > search for ole (and hit enter)
Control Panel > Programs > Programs and Features > searching for ole, showing old v18
From PowerShell (as admin) on affected servers
Go to saved directory for EXE and MSI files
Open MSI to begin install
PowerShell as Admin running the ole MSI install
Click Next if you get the ‘User Account Control’ (UAC) prompt to initiate MSI install
OLE MSI Install – User Account Control (UAC) prompt to initiate MSI install
Click Next
OLE MSI install, click Next
Click ‘I agree’ radio button and Click Next
OLE MSI Install, EULA splash screen to check ‘I Agree’ radio button and click Next
Next, on the OLE MSI install, click next to accept default features (just the driver install)
OLE MSI install, click next to accept default features (just the driver install)
Click Install to begin driver install
OLE MSI install, click install
OLE driver install completed, click Finish
OLE driver install completed, click Finish
Verify Control Panel for OLE driver install and version
Lastly, assess server and application requirements to verify if the old OLE driver is okay to remove from system to clear vulnerability. The old OLE driver on my system was installed the day I installed SSMS v19.x
Back to your Control Panel > Programs > Programs and Features window
Change search to OLE in the top right > hit enter
Click Delete on old version
On the Warning popup window, click continue
Control Panel view showing two OLE drivers, reflecting the newly installed, and the old version
At the UAC prompt, click Yes
OLE MSI Install – User Account Control (UAC) prompt to initiate MSI install
Once complete, verify Control Panel window
Control Panel > Programs > Programs and Features > searching for ole, showing old v18
Other documentation
Security Updates for Microsoft SQL Server OLE DB Driver (June … | Tenable®
To provide the best experiences, we use technologies like cookies to store and/or access device information. Consenting to these technologies will allow us to process data such as browsing behavior or unique IDs on this site. Not consenting or withdrawing consent, may adversely affect certain features and functions.
Functional
Always active
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
The technical storage or access that is used exclusively for statistical purposes.The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
