Windows Server Core is the perfect candidate for Microsoft SQL Server. While I rather spend 15 hours automating something, making SQL Server secure on Core is quit a hard / impossible task without PowerShell.
SQL Server Management Studio 2017
So the first task will be to install the latest version of Management Studio. I’ve started to upgrade all my Automation Framework PowerShell scripts to automatically download the media if it doesn’t exist in the $Version folder.
That way I just need to provide $Version and $URL when a new version is released. The rest is handled automatically. The easiest way to find the URL is just using Google Chrome Downloads and copy the link.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
Write-Verbose "Setting Arguments" -Verbose $StartDTM = (Get-Date) $Vendor = "Microsoft" $Product = "SQL Server Management Studio" $Version = "17.3" $PackageName = "SSMS-Setup-ENU" $InstallerType = "exe" $Source = "$PackageName" + "." + "$InstallerType" $LogPS = "${env:SystemRoot}" + "\Temp\$Vendor $Product $Version PS Wrapper.log" $LogApp = "${env:SystemRoot}" + "\Temp\$PackageName.log" $Destination = "${env:ChocoRepository}" + "\$Vendor\$Product\$Version\$packageName.$installerType" $UnattendedArgs='/q' $url = "https://download.microsoft.com/download/3/C/7/3C77BAD3-4E0F-4C6B-84DD-42796815AFF6/SSMS-Setup-ENU.exe" Start-Transcript $LogPS if( -Not (Test-Path -Path $Version ) ) { New-Item -ItemType directory -Path $Version } CD $Version Write-Verbose "Downloading $Vendor $Product $Version" -Verbose If (!(Test-Path -Path $Source)) { Invoke-WebRequest -Uri $url -OutFile $Source } Else { Write-Verbose "File exists. Skipping Download." -Verbose } Write-Verbose "Starting Installation of $Vendor $Product $Version" -Verbose (Start-Process "$PackageName.$InstallerType" $UnattendedArgs -Wait -Passthru).ExitCode Write-Verbose "Customization" -Verbose Write-Verbose "Stop logging" -Verbose $EndDTM = (Get-Date) Write-Verbose "Elapsed Time: $(($EndDTM-$StartDTM).TotalSeconds) Seconds" -Verbose Write-Verbose "Elapsed Time: $(($EndDTM-$StartDTM).TotalMinutes) Minutes" -Verbose Stop-Transcript |
SQL Server 2016
For this installation I’m using the good old batch script. I’ll hopefully publish a post soon using PowerShell with Microsoft SQL Server 2017.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
@echo off REM Batch Wrapper for MDT, Standalone and Chocolatey Installation - (C)2015 xenappblog.com pushd %~dp0 SET AppName=Microsoft SQL Server 2016 SET Version=2016 SET OPTIONS= SET OPTIONS=/Q SET OPTIONS=%OPTIONS% /ACTION=Install SET OPTIONS=%OPTIONS% /FEATURES=SQL SET OPTIONS=%OPTIONS% /INSTANCENAME=MSSQLSERVER SET OPTIONS=%OPTIONS% /SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE" SET OPTIONS=%OPTIONS% /SQLSYSADMINACCOUNTS="%USERDOMAIN%\Administrator" "BUILTIN\Administrators" SET OPTIONS=%OPTIONS% /AGTSVCACCOUNT="NT AUTHORITY\Network Service" SET OPTIONS=%OPTIONS% /IACCEPTSQLSERVERLICENSETERMS SET OPTIONS=%OPTIONS% /BROWSERSVCSTARTUPTYPE="Automatic" cls echo. echo Installing %AppName% echo. cd %Version% start /wait setup.exe %OPTIONS% popd endlocal |
After the Automated installation of Microsoft SQL Server 2016 you’ll want to create a Domain Certificate to Force Encryption.
cd
So again we’re using a script created by Martin Therkelsen to do so automatically. Create the following two files in a Temp folder and run the script. To make it simpler and secure, run it on any server that has IIS with Management Tools.
|
1 |
Install-WindowsFeature -Name Web-Server -IncludeManagementTools |
SSL.ini
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
[Version] Signature="$Windows NT$" [NewRequest] Subject = "CN=ServerFQDN" ; For a wildcard use "CN=*.CONTOSO.COM" for example ; For an empty subject use the following line instead or remove the Subject line entierely ; Subject = Exportable = TRUE ; Private key is not exportable KeyLength = 2048 ; Common key sizes: 512, 1024, 2048, 4096, 8192, 16384 KeySpec = 1 ; AT_KEYEXCHANGE KeyUsage = 0xA0 ; Digital Signature, Key Encipherment MachineKeySet = True ; The key belongs to the local computer account ProviderName = "Microsoft Enhanced RSA and AES Cryptographic Provider" ProviderType = 12 SMIME = FALSE RequestType = PKCS10 HashAlgorithm=Sha256 ; At least certreq.exe shipping with Windows Vista/Server 2008 is required to interpret the [Strings] and [Extensions] sections below [Strings] szOID_SUBJECT_ALT_NAME2 = "2.5.29.17" szOID_ENHANCED_KEY_USAGE = "2.5.29.37" szOID_PKIX_KP_SERVER_AUTH = "1.3.6.1.5.5.7.3.1" szOID_PKIX_KP_CLIENT_AUTH = "1.3.6.1.5.5.7.3.2" [RequestAttributes] CertificateTemplate= WebServer |
New-Certificate.ps1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
<# .Synopsis Automate the creation of wildcard certificates .DESCRIPTION This script can automate the creation of wildcard certificates from an internal PKI infrastructure. The output PFX file will not have a password and it will be placed in the folder the PS1 script is located. You will need to have the SSL.INI file in the same folder as this script and you will need to run the script as a domain users. Use the function within this script by editing the line in the buttom. .PARAMETER Path Path to where temporary files are stored .PARAMETER PFXPath Path to where the PFX file is exported .PARAMETER CAName Name of the Certificate authority .EXAMPLE New-WildcardCertificate .EXAMPLE New-WildcardCertificate -Path C:\Temp -PFXPath "\\FILE01\Certificates" .EXAMPLE New-WildcardCertificate -Path C:\Temp -PFXPath "\\FILE01\Certificates" -CAName "DC01.Domain.Com\Domain-DC01-CA" #> Function New-WildcardCertificate { [CmdletBinding()] Param( [Parameter(Mandatory=$False,Position=1)] [string]$Path = "C:\Windows\Temp", [Parameter(Mandatory=$False,Position=2)] [string]$PFXPath = ".", [Parameter(Mandatory=$False,Position=3)] [string]$CAName, [Parameter(Mandatory=$False,Position=4)] [string]$Password, [Parameter(Mandatory=$False,Position=4)] [string]$CertificateName ) Begin { If ($CertificateName -match "\*") { $CertName = "Wildcard" Write-Host "Found" } else { $CertName = $CertificateName } $Domain = (Get-ItemProperty -path hklm:\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters -Name Domain).Domain If (!(Test-Path -Path $PSScriptRoot\ssl.ini)) { Write-Host "You don't have the SSL.INI file that is required to run this script" -ForegroundColor Red Break; } If (!(Test-Path -Path $Path)) { New-Item -Path $Path -ItemType Directory } (Get-Content $PSScriptRoot\ssl.ini) | Foreach-Object {$_ -replace 'ServerFQDN',"$CertificateName"} | Out-File .\$CertName.ini } Process { If ($CAName -eq "") { Write-Verbose "Finding certificate authority" $CA = New-Object -ComObject CertificateAuthority.Config $CAName = $CA.GetConfig(0) } Write-Verbose "Requesting certificate" & c:\windows\system32\certreq.exe –new "$CertName.ini" "$Path\$CertName.req" & c:\windows\system32\certreq.exe -config "$CAName" –submit "$Path\$CertName.req" "$Path\$CertName.cer" Write-Verbose "Installing certificate" & c:\windows\system32\certreq.exe –accept "$Path\$CertName.cer" Write-Verbose "Exporting certificate and private key" $PFXPassword = ConvertTo-SecureString -String $Password -Force -AsPlainText $cert = new-object security.cryptography.x509certificates.x509certificate2 -arg "$Path\$CertName.cer" Get-item cert:\localmachine\my\$($cert.Thumbprint) | Export-PfxCertificate -FilePath "$PFXPath\$CertName.pfx" -Password $PFXPassword $cert.Thumbprint | Out-File -FilePath "\\dc-01.ctxlab.local\xa\Certificates\$CertName.txt" Write-Verbose "Certificate successfully exportert to $CertName.pfx" } End { Write-Verbose "deleting exported certificat from computer store" Remove-Item -Path cert:\localmachine\my\$($Cert.Thumbprint) -DeleteKey Remove-Item -Path $Path\$CertName.cer -Force Remove-Item -Path $Path\$CertName.req -Force Remove-Item -Path $Path\$CertName.rsp -Force } } New-WildcardCertificate -Path C:\Install -PFXPath "\\dc-01.ctxlab.local\xa\Certificates" -Password "P@ssw0rd" -CertificateName "sql-02.ctxlab.local" -Verbose |
The final part is to import the certificate, bind it to the SQL instance and set permissions on the private key. The SQL Service Account is running the Network Service so without permissions to the private key the SQL Server simply won’t start. Luckily I stumbled open this post which solved that for me.
Make sure to run this script on the SQL Server itself.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
$FQDN = "$env:Computername" + "." + "$env:USERDNSDOMAIN" $Pwd = ConvertTo-SecureString -String "P@ssw0rd" -Force -AsPlainText $serviceAccount = 'NETWORK SERVICE' $Share = "\\dc-01\xa\Certificates\" $PfxPath = "$Share" + "$FQDN" + ".pfx" $strThumb = "$Share" + "$FQDN" + ".txt" Import-PfxCertificate -CertStoreLocation Cert:\LocalMachine\My -Exportable -Password $Pwd -FilePath Filesystem::$PfxPath $certificateObject = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2 $thumbprint = $certificateObject.Import($PfxPath, $Pwd, [System.Security.Cryptography.X509Certificates.X509KeyStorageFlags]::DefaultKeySet) Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\SuperSocketNetLib" -Name "Certificate" -Type String -Value "$thumbprint" Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\SuperSocketNetLib" -Name "ForceEncryption" -Type DWord -Value "1" $certThumbprint = Get-Content $strThumb $permissionType = 'Read' try { #Clear Existing Variables $cert = '' $keyFullPath = '' Write-Host "--------------------------" Write-Host "Server: $env:ComputerName" -ForegroundColor Cyan Write-Host "Finding Certificate..." -ForegroundColor Green #Get Certificate $cert = Get-ChildItem -Path cert:\LocalMachine\My | Where-Object {$_.Thumbprint -eq ($certThumbprint -replace '\s','')} If ($cert -ne $null -and $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName -ne $null) { # Get Location of the machine related keys $keyPath = $env:ProgramData + "\Microsoft\Crypto\RSA\MachineKeys\"; $keyName = $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName; $keyFullPath = $keyPath + $keyName; Write-Host "Found Certificate..." -ForegroundColor Green Write-Host "Granting access to $serviceAccount..." -ForegroundColor Green #Grant Full Control to account listed in $serviceAccount $acl = (Get-Item $keyFullPath).GetAccessControl('Access') #Get Current Access $buildAcl = New-Object System.Security.AccessControl.FileSystemAccessRule($serviceAccount,$permissionType,"Allow") #Build Access Rule $acl.SetAccessRule($buildAcl) #Add Access Rule Set-Acl $keyFullPath $acl #Save Access Rules Write-Host "Access granted to $serviceAccount..." -ForegroundColor Green Write-Host "--------------------------" } Else { Write-Host "Unable to find Certificate that matches thumbprint $certThumbprint or the private key is missing..." -ForegroundColor Red Write-Host "--------------------------" } } catch { Write-Host "Unable to grant access to $serviceAccount..." -ForegroundColor Yellow Write-Host "--------------------------" throw $_; } Restart-Service mssqlserver |
To verify you can run the following query
|
1 2 |
SELECT session_id, encrypt_option FROM sys.dm_exec_connections |
The above query will show encryption even if you just set Force Encryption on the instance. In that case it will use a self sign certificate!
The only way to check is the SQL Server Error log. Simply search for the Certificate Thumbprint. Click here to learn how.
Hi,
I need a script to add existing ssl certificate to SQL DB.
All computers have ssl certificate and i need the same to integrate to SQL server to encrypt connection.
any help would be appreciated.