Encrypt data in transit and at rest in SQL Server

This guide explains how to encrypt SQL Server data in transit (across network connections) and at rest (within the database) using dbForge Studio for SQL Server. Proper encryption protects sensitive data from unauthorized access and helps organizations meet security and compliance requirements.

Prerequisites

  • A SQL Server instance with administrative access to configure encryption settings.
  • A database that contains sensitive data to encrypt.

Encrypt data in transit with SSL/TLS

To encrypt data transmitted over network connections, configure SQL Server to use a certificate.

Create a certificate

1. Run PowerShell as administrator.

2. Generate a self-signed certificate for testing purposes. This script generates a self-signed certificate on the SQL Server host and installs it in Local Machine Certificate Store.

Note

To use a publicly trusted certificate, obtain it from a trusted Certificate Authority (CA) and install it on the SQL Server host.

New-SelfSignedCertificate `
-CertStoreLocation Cert:\LocalMachine\My `
-DnsName "<local_hostname>" `
-NotAfter (Get-Date).AddYears(5) `
-FriendlyName "SQL Server TLS Certificate" `
-KeyUsage DigitalSignature, KeyEncipherment `
-KeySpec KeyExchange

This script generates a self-signed certificate on a machine running SQL Server and installs it in the local machine certificate store.

Note

Run the PowerShell script as an administrator.

3. Verify the installation of the certificate on the SQL Server host machine using one of these methods:

  • Microsoft Management Console (MMC)

    1. Press Windows+R.

    2. Enter certlm.msc.

    3. Press Enter.

    4. In Microsoft Management Console (MMC), select Personal > Certificates.

Verify the installation of the certificate

  • PowerShell command

Run the following PowerShell command to verify the certificate installation:

Get-ChildItem -Path Cert:\LocalMachine\My | Format-List Subject, FriendlyName, EnhancedKeyUsageList

Verify the installation of the certificate via PowerShell

Configure SQL Server to use the certificate

1. In SQL Server Configuration Manager, select SQL Server Network Configuration > Protocols for <instance_name>.

2. Verify that TCP/IP is enabled.

TCP/IP certificate enabled

3. Right-click Protocols for <instance_name>, then select Properties.

4. On the Certificate tab, under Certificate, select SQL Server TLS Certificate, then click Apply.

5. Click OK.

6. Press Windows+R.

7. Enter certlm.msc.

8. Press Enter.

9. Select Personal > Certificates.

10. Right-click the certificate that you created earlier, then select All Tasks > Manage Private Keys.

11. In the Permissions for SQL Server TLS Certificate private keys dialog, click Add.

12. In Enter the object names to select, enter the SQL Server service name.

13. Under Permissions for <service_name>, select Full control and Read.

14. Click Apply > OK.

Export the certificate

1. Right-click the certificate, then select All Tasks > Export.

Export the certificate

2. In Certificate Export Wizard, click Next.

3. Select DER encoded (.CER), then click Next.

4. On the File to Export page, click Browse.

5. Select the folder where you want to export the certificate, then enter a file name.

6. Click Next.

7. Click Finish, then OK.

Import the certificate

To establish an encrypted connection in dbForge Studio for SQL Server, import the certificate so that the system trusts it:

1. Press Windows+R.

2. Enter certlm.msc.

3. Press Enter.

4. Select Trusted Root Certification Authorities.

5. Right-click Certificates, then select All Tasks > Import.

6. In Certificate Import Wizard, click Next.

7. On the File to Import page, click Browse, then select the exported certificate.

8. Click Next.

9. Click Finish, then click OK.

10. Restart the SQL Server service.

Verify the encrypted connection

In dbForge Studio for SQL Server, create an encrypted database connection:

1. Open dbForge Studio for SQL Server.

2. Create a database connection with encryption:

2.1. On the General tab of the Database Connection Properties dialog, configure the server connection settings.

Note

In Server, specify the local hostname and port separated by a comma.

You can find the port in SQL Server Configuration Manager:

1. Select SQL Server Configuration > Protocols for <instance_name>.

2. Click TCP/IP.

3. Right-click Properties, then select IPAll.

2.2. On the Advanced tab, select Encrypt connection.

2.3. Click Test Connection > Connect.

Verify the encrypted connection

Test the encrypted connection

Run the following query in SQL Editor to verify that the connection is encrypted.

SELECT
     session_id
    ,encrypt_option
    ,client_net_address
    ,protocol_type
    ,protocol_version
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

If the encrypt_option value is TRUE, the connection is successfully encrypted.

Test the encrypted connection

(Optional) Force encryption for all connections

1. In SQL Server Configuration Manager, right-click Protocols for <instance_name>, then select Properties.

2. On the Flags tab, select Yes for Force encryption.

3. Click Apply, then click OK.

4. Restart the SQL Server service.

5. In Database Explorer, right-click the encrypted connection, then select Modify Connection.

6. If you are using a self-signed certificate, on the Advanced tab, in Enter additional connection string properties, enter Encrypt=True;TrustServerCertificate=True.

Warning

Use a CA-issued certificate in a production environment to avoid trust warnings and compatibility issues that may occur with self-signed certificates.

7. Click Connect.

Enable Transparent Data Encryption (TDE) for data at rest

Transparent Data Encryption (TDE) encrypts the entire database at rest, including data files, backups, and transaction logs.

For more information about encrypting sensitive data by using TDE, see Security best practices.

To enable TDE:

1. In SQL Editor, create a master key in the master database.

USE master;
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master_key_password>';

2. Create a certificate.

CREATE CERTIFICATE tde_cert_crmdata
WITH SUBJECT = 'TDE Certificate for crmdata';
GO

3. Back up the certificate and master key to a secure location.

Tip

Store certificate and key backups in a secure, off-site location with access restricted to authorized personnel only.

-- Make sure the D:\Certificates folder exists and SQL Server has write access to it.
BACKUP CERTIFICATE tde_cert_crmdata
TO FILE = 'D:\Certificates\tde_cert_crmdata.cer'
WITH PRIVATE KEY (
FILE = 'D:\Certificates\tde_cert_crmdata.pvk',
ENCRYPTION BY PASSWORD = '<certificate_password>'
);
 
BACKUP MASTER KEY
TO FILE = 'D:\Certificates\MasterKey.key'
ENCRYPTION BY PASSWORD = '<master_key_password>';
GO

4. Create a database encryption key.

USE crmdata;
GO
 
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE tde_cert_crmdata;
GO

5. Enable TDE for the target database.

 
ALTER DATABASE crmdata SET ENCRYPTION ON;
GO

Note

The encryption scan can take a significant amount of time, depending on the database size.

6. Verify the TDE status.

SELECT name, is_encrypted
FROM sys.databases
WHERE name = 'crmdata';
GO

Verify the TDE status

Encrypt specific columns with column-level encryption

You can use column-level encryption to secure columns that contain sensitive information, such as credit card numbers or Social Security numbers (SSNs).

To enable column-level encryption:

1. In SQL Editor, create a symmetric key and certificate.

Tip

Use Security Manager to grant access to the symmetric key only to authorized roles, restricting decryption to those roles.

USE crmdata;
GO
 
-- Create a database master key (required for certificates)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master_key>';
GO
 
-- Certificate for column-level encryption
CREATE CERTIFICATE column_cert
WITH SUBJECT = 'Column Encryption Certificate';
GO
 
-- Symmetric key encrypted by the certificate
CREATE SYMMETRIC KEY symkey1
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE column_cert;
GO

2. In Table Editor, add an encrypted column to the target table:

2.1. In Database Explorer, right-click the table, then select Open Editor.

2.2. Add a new column.

2.3. Click Apply Changes.

Add a column in Table Editor

3. In SQL Editor, encrypt the data in the column.

USE crmdata;
GO
 
-- Open the symmetric key for encryption operations
OPEN SYMMETRIC KEY symkey1
DECRYPTION BY CERTIFICATE column_cert;
GO
 
-- Encrypt existing plain-text credit card data into the encrypted column
UPDATE customers
SET CreditCardEncrypted = ENCRYPTBYKEY(KEY_GUID('symkey1'), credit_card);
GO

-- Remove the plaintext column after encryption
ALTER TABLE customers DROP COLUMN credit_card;
 
-- Close the symmetric key after use
CLOSE SYMMETRIC KEY symkey1;
GO

4. Verify the encryption results.

USE crmdata;
GO
 
-- Open symmetric key to decrypt data
OPEN SYMMETRIC KEY symkey1
DECRYPTION BY CERTIFICATE column_cert;
GO
 
-- Decrypt and display sensitive data
SELECT
customer_id,
full_name,
email,
CAST(DECRYPTBYKEY(CreditCardEncrypted) AS NVARCHAR(50)) AS creditcard_decrypted
FROM customers;
GO
 
-- Close symmetric key after use
CLOSE SYMMETRIC KEY symkey1;
GO

5. Back up the certificate and key.

USE crmdata;
GO
 
-- Back up the column-level encryption certificate and its private key
BACKUP CERTIFICATE column_cert
TO FILE = 'D:\Certificates\Column_Cert.cer'
WITH PRIVATE KEY (
FILE = 'D:\Certificates\Column_Cert.pvk',
ENCRYPTION BY PASSWORD = '<certificate_password>'
);
GO

Secure backup files with encryption

You can encrypt database backups to protect data at rest in backup files and help prevent unauthorized access and data loss.

Validate and monitor encryption

You can verify that encryption is correctly implemented and monitor for potential issues by using SQL Editor and Security Manager to review and confirm encryption settings.

To verify the encryption settings:

1. In SQL Editor, verify the TDE status.

SELECT name, is_encrypted
FROM sys.databases
WHERE name = 'crmdata';
GO

Verify the TDE status in SQL Editor

2. Verify the encryption key details.

USE crmdata;
GO
 
-- List symmetric keys in the database
SELECT * FROM sys.symmetric_keys;
 
-- List certificates in the database
SELECT * FROM sys.certificates;
GO

Retrieve a list of certificates in the database

3. Review the session properties to confirm that SSL/TLS encryption is in use.

If the encrypt_option column is TRUE, the connection uses TLS/SSL encryption.

SELECT
    c.session_id,
    s.login_name,
    c.client_net_address,
    c.protocol_type,
    c.encrypt_option,
    c.auth_scheme,
    s.host_name,
    s.program_name,
    c.connect_time
FROM sys.dm_exec_connections AS c
    JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
    ORDER BY c.session_id;
GO

4. Configure SQL Server Audit to monitor encryption-related events.

USE master;
GO
-- Create a server audit that writes audit logs to a file
CREATE SERVER AUDIT Encryption_Audit
TO FILE (FILEPATH = 'D:\Audits\',
MAXSIZE = 10 MB,
MAX_ROLLOVER_FILES = 5)
WITH (QUEUE_DELAY = 1000,
ON_FAILURE = CONTINUE);
GO
 
-- Create a database audit specification for crmdata
-- Track execution of fn_get_audit_file by public role
CREATE DATABASE AUDIT SPECIFICATION Encryption_Audit_Spec
FOR SERVER AUDIT Encryption_Audit
ADD (EXECUTE ON OBJECT::sys.fn_get_audit_file BY public)
WITH (STATE = ON);
GO

5. Generate audit reports in Report Designer to analyze and document encryption usage.

Tip

Schedule periodic audits in SQL Server Agent to help ensure encryption keys and certificates remain secure.

Manage encryption keys and certificates

To reduce the risk of data loss, manage and back up encryption keys and certificates by using SQL Editor and Security Manager:

1. Regularly back up the master key, certificates, and symmetric keys to a secure location.

Tip

Store key backups in a secure, encrypted vault, and restrict access to authorized administrators only.

2. Restrict access to keys and certificates.

USE crmdata;
GO
 
-- Create a database role for administrators
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'Role_Admins')
    CREATE ROLE Role_Admins;
GO
 
-- Grant access only to specific roles, for example, Role_Admins
GRANT VIEW DEFINITION ON CERTIFICATE::column_cert TO Role_Admins;
GRANT CONTROL ON SYMMETRIC KEY::symkey1 TO Role_Admins;
GO

3. Document key management procedures.

4. Rotate certificates periodically (for example, every 6 months to 2 years) by creating a new certificate and updating the encryption key.

USE master;
GO
 
-- Create a new server certificate for TDE
CREATE CERTIFICATE tde_cert_new
WITH SUBJECT = 'New TDE Certificate';
GO
 
USE crmdata;
GO
 
-- Regenerate database encryption key by using the new certificate
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE tde_cert_new;
GO

Recommendations

Recommendation Details
Use strong encryption algorithms Use AES-256 for Transparent Data Encryption (TDE) and column-level encryption to protect sensitive data.
Enforce SSL/TLS for all connections Include Encrypt=True in the connection string and enable Force Encryption in SQL Server Configuration Manager.
Back up encryption keys securely Store encryption keys and certificates in a secure, access-restricted location separate from the database server.
Update certificates regularly Update certificates every 6 months to 2 years to reduce the risk of key compromise.
Restrict access to encryption keys Grant access to encryption keys only to authorized roles using Security Manager.
Test backup restoration Test backup restoration regularly to confirm that encrypted backups can be successfully recovered.
Monitor encryption usage Use SQL Server Audit to track access to encryption keys and detect unauthorized access attempts.
Use CA-issued certificates In a production environment, use SSL/TLS certificates issued by a trusted Certificate Authority (CA) to avoid trust and certificate validation issues.
Document encryption policies Use dbForge Studio Documentation feature to document encryption configurations, certificates, and key management procedures.
Minimize encrypted columns Encrypt only columns that contain sensitive data to reduce performance overhead and simplify key management.
Audit encryption keys Regularly review encryption key metadata and access permissions by querying SQL Server catalog views, such as sys.symmetric_keys and sys.certificates, in SQL Editor.
Protect connection strings Store application connection strings in encrypted configuration files or secure vaults.
Test encryption in a staging environment Verify that all encryption settings work as expected before deploying them to production.
Enable firewall rules Restrict SQL Server network access to trusted IP addresses and secure encrypted connections.
Use strong passwords for encryption keys Enforce strong, complex passwords for database master keys and certificate private keys.
Automate key backups Schedule regular backups of encryption keys and certificates, and store them in a secure location.
Avoid hardcoding keys Never hardcode encryption keys in application code, scripts, or configuration files.
Train teams on encryption Educate database administrators on encryption principles and key management. Use the Documentation feature in dbForge Studio to document encryption policies, key usage, and management procedures.