Integration – time to integrate data sources to data lake
Ready for a single pane of glass? Ready to have your insights in a common location? Let’s discuss Data Integration with SQL2022.
Let’s start with some background on SQL2022 and similarly SQL2025, start with the learn site link. SQL2022 by design is Azure enabled with multiple capabilities like ‘Bi-directional HA/DR to Azure SQL’ and ‘Azure Synapse Link’. Basically, Synapse link is the key.
SQL2022 by design is Azure Enabled
Utilize the PowerBI Cloud Service with today’s hybrid environments. SQL2022 allows integration with other Azure capabilities like Azure Data factory/data lake, and Azure Synapse. Another reason to upgrade SQL 2022, is design simplification. However, PowerBI data gateway adds a potential break point (single point of failure). While PowerBI data gateway centralizes all premise data to a central location. In the same way, consolidating data sent to the cloud. When PowerBI data gateway fails, insights and visualizations have stale data (i.e. data NOT transferred for a near real-time display).
Why SQL2022 then?
Connect insights and visualization to justify ‘Data Integration with SQL2022’ scenarios.
SQL2022 built in capability to Azure Synapse Analytics
Use SQL2022 to configure SQL agent jobs which pull SQL scripts from your cloud environment. DevOps and common Azure Storage repository are great advantages for speed of execution.
Ah – ‘Setting up PowerBI Report Server SPN’s for PowerBI and SQL to help securely communicate and authenticate.
‘Setting up PowerBI Report Server SPN’ in hybrid environments when the PowerBI cloud service is not <yet> an option in an organization. This article will go through SPN commands, to secure via Kerberos authentication and/or smart card usage for Security requirements (i.e. STIG, CCRI, SOX, HIPAA, PCI, Security Scans, <insert other regulatory requirements here>). Lastly, PowerBI Report Server can be setup to run parallel to SSRS SQL instance. Refer to SPN commands below which helped me setup SmartCards authentication based on SPN setup.
Download the ‘AD insights pack’ for new capabilities to audit users, svc/MSA accounts, password last set, expiring, last login AD insights. Includes AD group audit alert capability.
Time to provide key ‘AD insight reports’ into users and groups. Delve into different AD audit capabilities for users and groups. The pack also gathers DC Security events (rules), and lastly, on demand tasks for reports.
The question is what determines a problem?
Every domain admin has a different experience and perspective, whether cyber (hack) focused or not. Audit standards differ, from HIPAA, SOX, CCRI, STIG, etc.
Groups – Choose your OU structure to audit WA in DA, SA in DA, WA in SA etc.
NOTE: Take caution on the OU group audit, to limit the output, as events have a size limitation
Configure ‘AD insight reports’
Now we can configure the user pack for applicable standards, like password age, last set, or AppOwners. The AppOwners is an array, so you can add whatever Application, system owners/teams in your organization. The password datasource (DS) rule runs weekly.
Configure the Password Time, last set, month, week and AppOwners to build out actionable svc/msa accounts failing audit artifacts.
Break out the regular expressions of whatever accounts each team uses, to tailor relevant data into the report alert. Find/Replace (Control-H) might be more effective, as the DS/WA repeat the logic for the on-demand task report, vs. the rule and monitor.
App Owner relevant service accounts by SamAccountName
Update patterns ID naming conventions
Tailor account names to environment to match ingested DC Security events.
Tailor the DC Security Events to account naming conventions.
Configure OU to environment
Configure OU structure to audit based on domain canonical names, groups, DC, etc.
Time to make the doughnuts again, new Security ODBC Vuln 175441 that needs to be mitigated. Not sure if you ever saw the commercials, but this is where my mind goes sarcastic humor and all. Whether you’re using ACAS/Tenable/Nessus for security scans, this may show up with your SCOM servers (MS, DB), and PowerBI Report Servers.
Let’s get started to upgrade ODBC
Action: Security scan shows a new ODBC Vuln 175441, that may impact SCOM or PowerBI Report Server talking with SQL servers.
Start with some documentation, to understand what and why…
Have Security run additional scan to verify resolved
What servers are vulnerable?
We’re focused on the ‘Security – ODBC Vuln 175441’
Begin by looking at your Security scanning tool output (PowerBI report pictured). I am also showcasing the PowerBI report, as this streamlines what the Security Admin has to provide when System Administrators (sysAdmin) reach out for debug/details.
ACAS/Tenable/Nessus scan PowerBI Report
In my case, I wanted to see what servers are impacted. The PowerBI Report has a built-in ‘Deep Dive’ tab to see the details from the scan/check. Click on the Deep Dive Tab, enter the PlugIn ID (175441 for ODBC) and hit enter. This breaks out what servers are vulnerable. Assess what servers are yours (my output simplified to show what I own with SCOM and PowerBI 🙂 Looking at the ‘NetBIOS Name’ column. Alternatively, the admin typically has the scan tool email XLS files.
Access your ACAS/Tenable/Nessus scan deep dive tab (or PowerBI Report) to see how many systems are vulnerable.
Save to share or common path to put file on affected server(s).
Once moved, login to affected server(s), typically RDP with Local Administrator equivalent admin ID
Open Windows Explorer > Copy ODBC MSI to server
Open PowerShell (as Admin) window > Go to path > Run ODBCMSI
PowerShell as Administrator steps
Now the ODBC popup window for install
Note the screenshots and progress prompts
Click ‘I accept’ radio button and then click ‘Next’
ODBC EULA splash screen
Click Next to move beyond the ODBC features screen
ODBC Features screen
Click on Install
ODBC Install prompt
Watch progress bar (maybe 1-2 minutes)
ODBC Install Progress bar
Click Finished
ODBC Install finished
Once the MSI installer window closes, it’s time to verify server Control Panel.
Verify server Control Panel shows update
Click on Start > Control Panel > Programs > Programs and Features
In the top right search bar, type ‘ODBC’ and hit enter to filter results.
Snapshot of Control Panel before
Control Panel with ODBC as the search string
Snapshot of Control Panel after
Hit F5 to refresh screen output
ODBC Control Panel after install
The one question is if version 17 has to be removed to clear vulnerability. Ran into this scenario with Java, as the update left old versions.
I typically reboot the server to reinitialize server to assess any impacts, as well as boot on the new drivers. For this instance, I coordinated my July server updates were installed to simplify my admin (as both require reboot!)
Have Security run additional scan to verify resolved
Typically SME has scheduled scans that run weekly, and can run scans on-demand. Depending on urgency, you can decide whether or not waiting is relevant.
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.
