SQL DBA Blog

Tag: Certificates & Encryption

Forcing Encryption in SQL Server
If you’re ever planning to allow external connections to a SQL Server, one of the first things to ensure is that the data is encrypted at rest and in transit.
SQL Server has an option where we can force all connections on a SQL Server to be encrypted, which ensures us that we’re achieving encryption for our SQL connectivity.
This post follows Microsoft Docs – Enable Encrypted Connections to the Database engine. It’s quite a complex piece so a full read of this would be necessary before planning this change.
# Create Certificate (PowerShell)
# Import PK to Trusted Root Certificates
# Import Certificate into SQL Server
# Force Encryption in SQL Server
# Verify SQL Server Connectivity is Encrypted
Create Certificate (PowerShell)
The certificate must be issued for Server Authentication. The name of the certificate must be the fully qualified domain name (FQDN) of the computer.
The above is a little note in amongst a bunch of pertinent info, found here; Microsoft Docs – Enable Encrypted Connections – Remarks
I’m using a New-SelfSignedCertificate for this tutorial and creating it with PowerShell.
Before running the following PowerShell script, amend .pfk output file location & DNS Names.
```
New-SelfSignedCertificate -DnsName lab-sql1.whyte.net -CertStoreLocation cert:\LocalMachine\My -FriendlyName lab-sql1-cert -KeySpec KeyExchange -NotAfter (get-date).AddYears(99)
$thumbprint = $(Get-ChildItem Cert:\LocalMachine\My).thumbprint
$Pwd = ConvertTo-SecureString -String "Str0ngePassword1!" -Force -AsPlainText
Export-PfxCertificate -Cert "Cert:\LocalMachine\My\$thumbprint" -FilePath "C:\temp_certificates\lab-sql1pk.pfx" -Password $Pwd -Force
```
Import PK to Trusted Root Certificates
Open MMC and add the Certificates (Local Computer) Snap-in.
Expand Trusted Root Certification Authorities, right-click & select All Tasks > Import…
Navigate to the .pfx file.
Enter a password & continue.
Finish up the wizard.
Have a look at the new certificate within MMC.
Import Certificate into SQL Server
Open SQL Server Configuration Manager, then right-click Protocols for MSSQLSERVER and select Properties.
Open the Certificate tab and you should be able to view & select the new certificate from the drop-down menu.
Click to Apply & Ok out of the window – I’m doing the Forcing of Encryption separate (below).
You’ll get this prompt.
Restart the SQL Services from the configuration manager. If your services don’t start back up again, then ensure the service accounts have the appropriate permissions. For this demo, my AD Service Accounts are members of the local Administrators group.
Force Encryption in SQL Server
Right click Protocols for MSSQLSERVER, select Properties and within the Flags tab enable the Force Encryption option.
Restart the SQL Services.
Verify SQL Server Connectivity is Encrypted
Open a local and/or remote query session. You may have to ensure the Encrypt connection & Trust server certificate options are checked.
Now query sys.dm_exec_connections to check the encryption_option of all SQL connections.
```
SELECT * FROM sys.dm_exec_connections
```
May 6, 2020
Backing up a SQL Server Database with Encryption
To backup a database in SQL Server with Encryption, run the BACKUP DATABASE command with the ENCRYPTION option added to the SQL statement, and specify the valid encryption certificate name.
Backing up a SQL Server Database with Encryption is the secure way of performing your SQL Server backups. If your MSSQL database backups are off-site and/or you have sensitive customer information stored on them, then you may consider encrypting your database backups.
Since SQL Server 2012, it has not been possible to create a SQL backup with a password (PASSWORD/MEIAMPASSWORD backup options).
The demo below guide was done following Microsoft Docs – Create an Encrypted Backup.
Backup Database with Encryption
Create a Database Master Key (stored in the database).
Create a Certificate to use for the database backups (gets stored in the Master Database) – Microsoft Docs; Create Certificate
Have a look at the new certificate by querying sys.certificates (think about expiry dates).
Now backup the database.
```
-- backup database with encryption mssql
BACKUP DATABASE [Computer_World] TO DISK = N'C:\Temp_MSSQL_Backups\computer_world_full.bak'  
WITH COMPRESSION, ENCRYPTION (ALGORITHM = AES_256,SERVER CERTIFICATE = AT_Backup_Cert),STATS
GO
```
The database has successfully been backed up with encryption.
The warning we see is regarding the certificate created prior to the backup.
Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.
I have another post to guide through a certificate backup here.
May 4, 2020
Backing up a SQL Server Certificate
It’s important to backup SQL Server Certificates, but only if you’re using them really.
If for example, your SQL Server database backups are encrypted and you need to restore it on another server, then you will need both the certificate and private key that was used.
This post is a guide on backing up a SQL Server certificate, ensuring we have a copy of the certificate and private keys.
Backup a SQL Server Certificate
Check your existing server certificates by querying sys.certificates, you’ll need the certificate name.
```
Use Master  
GO  
	SELECT name, pvt_key_encryption_type_desc, subject, start_date, expiry_date, key_length
	FROM sys.certificates
GO
```
Backup certificate (Microsoft Docs – Backup Options) – amending file paths & password.
```
BACKUP CERTIFICATE AT_Backup_Cert TO FILE ='C:\Temp_MSSQL_Backups\mssql_at_backup_cert.cer'  
      WITH PRIVATE KEY (   
		FILE ='C:\Temp_MSSQL_Backups\mssql_at_backup_key.pvk',  
		ENCRYPTION BY PASSWORD ='Str0ng3P4sSw0rd!' ) 
```
Then, move those files into a safe space.
May 4, 2020