SCCM Data Warehouse – SQL SSL certificates on AlwaysOn cluster

I was setting up the new Data Warehouse feature of SCCM on our corporate SQL 2016 AlwaysOn cluster.

You will notice that after you have installed the DW and try to run a report, you may get this error:

An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source ‘AutoGen__39B693BB_524B_47DF_9FDB_9000C3118E82_’. (rsErrorOpeningConnection) A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.)

The Data Warehouse reporting data source has this parameter: TrustServerCertificate=false

You can change this to true – but it will get set back to false by SCCM 🙂

The correct way forward – in my my opinion, is to install trusted certificates from your root CA on the SQL Instances hosting the DW database. There are other posts on installing the self signed certificate as a trusted root CA onto the reporting server, but I don’t like using self signed certs as they are difficult to track and revoke.

This solution was tested on a 3 node AlwaysOn SQL 2016 cluster running on Server 2016 VMs in Azure.

The certificates are requested from the internal publishing CA’s using powershell.

The SQL instance where the Data Warehouse database exists will likely have >1 Availability Group and Availability Group Listener (AGL), therefore when requesting the certificate, ALL the AGLs netbios and fqdn’s should be included in the Subject Alternative Name (SAN) properties, as well as the hostname.

I ran this PowerShell on each of the 3 SQL nodes to request a unique certificate.

# Create and submit a request
$Hostname = [System.Net.Dns]::GetHostByName(($env:computerName)).Hostname
$template = "WebServer2008"
$SAN = $Hostname,"AGL1-INSTANCE1","AGL1-INSTANCE1.domain.local","AGL2-INSTANCE1","AGL2-INSTANCE1.domain.local","AGL3-INSTANCE1","AGL3-INSTANCE1.domain.local","AGL4-INSTANCE1","AGL4-INSTANCE1.domain.local"
Get-Certificate -Template $template -DnsName $SAN -CertStoreLocation cert:\LocalMachine\My

Now, hop onto your issuing CA and approve the pending request for the certificate.

Back on the SQL nodes, retrieve the requested certificate from the CA. It will be installed into the machines Personal store.

# Now the certificate is approved, you can retrieve the request:
Get-Certificate -Request (Get-ChildItem -Path cert:\LocalMachine\Request)

Open an MMC and add the Certificates snap-in targeted at the local computer store. Find the newly installed certificate (The one with the above SANs in it) and retrieve the certificate thumbprint.

On each of the SQL Nodes, change the following registry value so that it matches the thumbprint of the newly approved certificate:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.INSTANCE1\MSSQLServer\SuperSocketNetLib]
"Certificate"="25615102f32108599d89555678e7fcdabb0db491"

The SQL Server service for the instance will need restarting following this Registry update.

Before testing the SCCM Data Warehouse reports, you can try in SQL Server Management Studio using these options:

SQLServerSSL

Advertisements

Azure Automation Account Runbook – Update VM tags from SQL Server table

As you can probably see from previous posts, I spend a lot more time these days automating tasks with PowerShell using Azure Automation hybrid workers.

In this post, I wanted to show you a way of reading from a SQL Server database the opening hours of a remote site/office and inputting these times into Azure VMs tags.

This ensures that Azure VMs are available during office hours and also helps to keeps Azure running costs down.


# Get office opening times from SQL database

param
    (    
        [Parameter(Mandatory = $false)]
        [string] $SQLServer = "SQL1.test.local",

        [Parameter(Mandatory = $false)]
        [string] $Database = "Sites",

		[Parameter(Mandatory = $false)]
        [string] $RGbaseName = "RG-WIN10-",

        [Parameter(Mandatory = $false)]
        [string] $AzureSubscriptionName = "azuresub",

		[Parameter(Mandatory = $false)]
        [string] $Filter = "*WIN7*"
		
    )


$ErrorActionPreference = "Stop"


Next is a simple function that calls a separate runbook that sends an email report on all the output of the last job run of the specified runbook (let me know if you want more details on this):


Function Send-EmailReport
{
    #Send email report of this runbook job
    $params = @{AutomationRunBookName="OpeningTimesToTags";SendReportByEmail=$true;SendToAddress="ITSupport@domain.gov.uk","ITSupport2@domain.gov.uk"}
    Start-AzureRmAutomationRunbook -AutomationAccountName "AA-WE-SUB-01" -Name "Report-AutomationJobOutput" -ResourceGroupName "RG-AA-01" –Parameters $params -RunOn "AA-HybridWorker" >$null 2>&1 | out-null
}

Grab the credentials needed to run the remainder of the script from the AA credentials vault:

# Azure Global Admin
$AzureOrgIdCredential = Get-AutomationPSCredential -Name 'sv-azure-automation'

# Database Read Only account
$DB_RO = Get-AutomationPSCredential -Name 'DB Read Only'

# Login to Azure
Login-AzureRmAccount -Credential $AzureOrgIdCredential >$null 2>&1 | out-null

#Select the specified subscription now
Select-AzureRMSubscription -SubscriptionName $AzureSubscriptionName >$null 2>&1 | out-null

Load the SQLServer PowerShell module or install if not currently. This one is great as we no longer need SSMS:

# SqlServer PoSh Module
If(-not(get-module -ListAvailable -Name SqlServer)){
    write-output "Could not find module 'SqlServer', installing now..."
    Find-Module SqlServer | Install-Module
}

import-module -Name SqlServer -ErrorAction SilentlyContinue

If(Get-Module -Name SqlServer){
    write-output "Module 'SqlServer' loaded, continue"
} else {
    Send-EmailReport
    write-error "Could not load or install 'SqlServer' PoSh Module"
}

Get all the VMs from Azure where the Resource Group name is like $RGbaseName:

write-output "`nLooping through all RGs like $RGbaseName to find specific VMs"
$RGs = Get-AzureRmResourceGroup | ? ResourceGroupName -like "$RGbaseName*"
If(!$RGs){write-error "Could not find any Resource Groups with a name like $RGbaseName*";return} else
{
	write-output "Retrieved $($RGs.count) RGs"
}

Filter the VMs so we are only working with the ones we want, based on $Filter:

write-output "`nGetting filtered VMs from $($RGs.count) RGs using filter $Filter"
$FilteredVMs = $RGs | Get-AzureRmVM | ? Name -like $Filter
If(!$FilteredVMs){write-error "Could not find any VMs with a name like $Filter";return} else
{
	write-output "Retrieved $($FilteredVMs.count) filtered VMs"
}

If the days of the week in the database are using a custom format or start the week starts on a different day, we can convert with this simple function:

# 1 = "Sunday"
# 2 = "Monday"
# 3 = "Tuesday"
# 4 = "Wednesday"
# 5 = "Thursday"
# 6 = "Friday"
# 7 = "Saturday"
Function Convert-DBWeekday {
	param
	(
		[Parameter(Mandatory = $true)]
		[string] $DBweekday
	)
	Switch ($DBweekday) {
		
				1 {
					$OutputDay = "Sunday"
				}

				2 {
					$OutputDay = "Monday"
				}

				3 {
					$OutputDay = "Tuesday"
				}

				4 {
					$OutputDay = "Wednesday"
				}

				5 {
					$OutputDay = "Thursday"
				}

				6 {
					$OutputDay = "Friday"
				}

				7 {
					$OutputDay = "Saturday"
				}

			}

	return $OutputDay

}

Finally, we are looping through all the filtered VMs to get a site specific code in the VM name – this is how we match the opening times to the office/site location stored in the SQL database.

Once we have the site and opening times, we subtract 60 mins from the opening time and add 30 mins to the close time. This provides a small maintenance window for the VM to complete any updates or SCCM software installs.

Then apply the VM Tags. Schedule_$Weekday
Obviously a separate Automation runbook is required to read the tags and shutdown/startup the VMs at the time specified in the tags.

ForEach($FilteredVM in $FilteredVMs){
	write-output "`nLooping through each filtered VM. Now using: $($FilteredVM.Name)"

	write-output "`nDetermine site code of VM $($FilteredVM.Name)"
	$Site = ($($FilteredVM.Name) -split "-")[2] -replace '[a-zA-Z]',''
	If(($Site.length -eq 3)-and($Site -match "\d{3}")){
		write-output "Site code determined as $Site"
	} else {
		write-error "Site code determined as $Site which does not match 3 digits, quitting"
		return
	}

	# Connect to SQL database and get Site info
	Write-Output "Getting side ID from SQL database for site $Site"
	$Site = Read-SqlTableData -ServerInstance $SQLServer -DatabaseName $Database -Credential $DB_RO -TableName site -SchemaName dbo | ? sitename -like "*$Site"
	If($Site.ID){
		Write-Output "Site ID identified as: $($Site.id)"
	} else {
		Send-EmailReport
		write-error "Could not retrieve $Site from the $Database database."
	}

	Write-Output "`nGetting opening hours for site $($Site.sitename)...."
	$DefaultHours = Read-SqlTableData -ServerInstance $SQLServer -DatabaseName $Database -Credential $DB_RO -TableName tblopenhours -SchemaName dbo | ? {$_.site_id -eq $($Site.id) -and $_.usertypecode -eq "Default"}

	

	write-output "`nGetting VMTags from $($FilteredVM.Name)"
	$VMTags = (Get-AzureRmResource -ResourceId ($FilteredVM).Id -ErrorAction Stop).Tags

	ForEach($Day in $DefaultHours){
    

		$StartDate = [datetime]$($Day.starttime)
		$EndDate = [datetime]$($Day.endtime)
		$StartTime = get-date $StartDate -Format HH:mm
		$EndTime = get-date $EndDate -Format HH:mm
		$WeekDay = Convert-DBWeekday -DBweekday $($Day.weekday)
		write-output "`nOpening hours for $WeekDay are $StartTime - $EndTime"

		write-output "-60 mins from $StartTime and +30 mins to $EndTime"
		$StartTime = get-date (Get-Date $StartTime).AddMinutes(-60) -Format HH:mm
		$EndTime = get-date (Get-Date $EndTime).AddMinutes(30) -Format HH:mm
		
		$SchedWeekday = "Schedule_$Weekday"
		write-output "Setting tag $SchedWeekday to value: $StartTime->$EndTime"
		$VMTags.$SchedWeekday = "$StartTime->$EndTime"
		

	}

	write-output "`nWriting VM Tags back to Azure..."
	Set-AzureRmResource -ResourceGroupName $($FilteredVM.ResourceGroupName) -Name $($FilteredVM.Name) -ResourceType "Microsoft.Compute/VirtualMachines" -Tag $VMTags -Force -Confirm:$false

}

# Send report of job output by email
Send-EmailReport

SQL Backup to URL (Azure Blob) behind authenticated proxy server

If you want to use SQL 2012 or above to backup directly to a URL and your servers are behind a corporate proxy that also requires auth, create the following .net config file so that the sql service will make the connection correctly:

c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\BackuptoURL.exe.config

Add the following code with your proxy address to the new file:

<?xml version ="1.0"?>
<configuration>
            <system.net>
                        <defaultProxy>
                                    <proxy  proxyaddress="http://proxylb.domain.local:8080" bypassonlocal="true" usesystemdefault="True" />
                                    <bypasslist>
                                                <add address="[a-z]+.domain.local$" />
                                    </bypasslist>
                        </defaultProxy>
            </system.net>
</configuration>

Detecting and dropping orphaned SQL database logins

Sometimes it might be useful to find all SQL and Windows logins inside all databases within an  instance of SQL that do not have associated logins. If the database is not in Contained mode, then instance level logins will probably be used to access the database.

This scripts finds all orphaned users in all databases in the instance and then drops those users accounts from the respective database. Note, use with caution and perform a database backup before testing.

CREATE TABLE ##ORPHANUSER
(
RowID int not null primary key identity(1,1) ,
DBNAME VARCHAR(100),
USERNAME VARCHAR(100),
CREATEDATE VARCHAR(100),
USERTYPE VARCHAR(100)
)

USE Test_Database
INSERT INTO ##ORPHANUSER
SELECT DB_NAME() DBNAME, NAME,CREATEDATE,
(CASE
WHEN ISNTGROUP = 0 AND ISNTUSER = 0 THEN 'SQL LOGIN'
WHEN ISNTGROUP = 1 THEN 'NT GROUP'
WHEN ISNTGROUP = 0 AND ISNTUSER = 1 THEN 'NT LOGIN'
END) [LOGIN TYPE] FROM sys.sysusers
WHERE SID IS NOT NULL AND SID 0X0 AND ISLOGIN =1 AND
SID NOT IN (SELECT SID FROM sys.syslogins) AND sys.sysusers .name != 'dbo'

SELECT * FROM ##ORPHANUSER

Declare @SQL as varchar (200)
Declare @DDBName varchar (100)
Declare @Orphanname varchar (100)
Declare @DBSysSchema varchar (100)
Declare @From int
Declare @To int
Select @From = 0, @To = @@ROWCOUNT
from ##ORPHANUSER
--Print @From
--Print @To
While @From < @To
Begin
Set @From = @From + 1

Select @DDBName = DBNAME, @Orphanname = USERNAME from ##ORPHANUSER
Where RowID = @From

Set @DBSysSchema = '[' + @DDBName + ']' + '.[sys].[schemas]'
print @DBsysSchema
Print @DDBname
Print @Orphanname
set @SQL = 'If Exists (Select * from ' + @DBSysSchema
+ ' where name = ''' + @Orphanname + ''')
Begin
Use ' + @DDBName
+ ' Drop Schema [' + @Orphanname + ']
End'
print @SQL
Exec (@SQL)

Begin Try
Set @SQL = 'Use ' + @DDBName
+ ' Drop User [' + @Orphanname + ']'
Exec (@SQL)
End Try
Begin Catch
End Catch

End

DROP TABLE ##ORPHANUSER