SQL SSL connections

Net als bij webservers met het http-protocol is het bij SQL servers het geval dat data tussen de client en de server ongecodeerd wordt verstuurd. Dit is niet altijd een gewenste situatie als privé-gevoelige data heen en weer wordt gestuurd. Voor webservers is er dan het https-protocol dat gebruik maakt van SSL, voor SQL kan dit ook gebruikt worden waardoor de data versleuteld wordt verstuurd.

Voor SSL hebben we echter wel een certificaat nodig en we gebruiken hiervoor het ‘Web Server’ sjabloon om het certificaat aan te maken. Open MMC en voeg module Certificaatsjabloon toe. Zoek de ‘Web Server’ template en met rechts-klik kiezen we voor ‘Kopie van sjabloon‘. In de tab ‘Algemeen’ wijzigen we de naam naar ‘SQL Server‘.

Hierna starten we in MMC de ‘Certificeringsinstantie (CA)‘ en met dubbel-klik op de CA en rechts-klik op ‘Certificaatsjablonen‘. Kies hier voor Nieuw -> ‘Te verlenen certificaatsjablonen‘ en selecteer het ‘SQL Server‘ sjabloon.

Voeg in MMC de Certificaten module toe en selecteer het Computeraccount. Open Persoonlijk en rechts-klik op Certificaten en kies voor ‘Nieuw certificaat aanvragen…‘.

certificate01Er wordt een lijst met sjablonen getoond,we kiezen voor de zojuist toegevoegde ‘SQL Server’

certificate02Klik op ‘Er zijn meer gegevens nodig….’

certificate03Vul bij Naam de FQDN van de SQL-Cluster in en bij DNS de servernaam van de cluster.

certificate04Na een OK zal het certificaat worden toegevoegd. We gaan deze nu van de juiste rechten voorzien. Kies voor ‘Alle taken…’ -> ‘Persoonlijke sleutels beheren’certificate05 certificate06Geef het SQL-Services account lees-rechten. Hierna kiezen we ervoor om het certificaat te exporteren zodat we het straks ook eenvoudig op de andere nodes kunnen importeren.

certificate07 certificate08 certificate09Nieuw vanaf server 2012 is de mogelijkheid om een gebruikers-account mee te geven, wellicht handiger dan een wachtwoord?

certificate10 certificate11Nadat de export is weggeschreven gaan we deze op de overige node(s) importeren.

Dubbelklik op het pfx-bestand om de import-wizard te starten.

cert01 cert02 cert03 cert04Helaas zal bij het gebruik van een SQL-cluster, het certificaat niet in de lijst met mogelijke certificaten verschijnen in de ‘SQL Server Configuration Manager‘. Daarom gaan we de thumbprint via Powershell opzoeken en deze aan de registry toevoegen. Met Enter-PSSession gaan we naar de SQL-node en met het commando

Get-ChildItem cert:\LocalMachine\My

vinden we de thumbprint. Zorg er voor dat de SQL-Cluster gekozen wordt indien er meerdere cerificaten aanwezig zijn.

We zetten de inhoud van de registry path in een variable genaamd $RegistryPath, deze is in ons geval is het pad naar de SQL instance MSSQL11.MSSQLSERVER:

HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer\SuperSocketNetLib

met het commando

NewItemProperty -Path $RegistryPath -Name Certificate -Value <thumbprint> -Force

zetten we het certificaat in de registry. Hetzelfde doen we op de overige node(s).

cert05Ook met powershell kunnen we de SQL nodes forceren om een encrypted connection te gebruiken. Deze staat in hetzelfde registry-path, dus op alle SQL-Nodes:

Set-ItemProperty -Path $RegsitryPath -Name ForceEncryption -Value "1" -Force

cert06De instelling wordt geldig bij opnieuw opstarten van de SQL service dus verplaatsen we de SQL-rol even heen en weer tussen de SQL nodes:

Enter-PSSession CLSTR01
Move-ClusterGroup "SQL Server (MSSQLSERVER)" -Node SQL02
Move-ClusterGroup "SQL Server (MSSQLSERVER)" -Node SQL01

cert07Om via SSL te verbinden naar de SQL-Cluster is er in de SQL Studio connection box een Options tab waar een vinkje geplaatst kan worden bij ‘Encrypt connection‘.

encrypt