Security best practices

This topic describes how to implement security best practices in SQL Server using dbForge Studio for SQL Server to protect databases from unauthorized access, data breaches, and other vulnerabilities while maintaining compliance with security standards.

Prerequisites

• dbForge Studio for SQL Server installed.
• A SQL Server instance with administrative access to configure security settings.
• A target database that you want to secure.

Configure secure authentication

You can set up secure authentication methods to control access to the SQL Server instance.

Set up authentication using Security Manager

1. Open Security Manager: Select Database > Security Manager.

2. Create a new login: Open the drop-down list next to Create User, then select Create Login.

2.1. In Name, specify the login name.

2.2. In Authentication type, select the required authentication method:

• SQL Server authentication – Allows users to connect by providing a SQL Server–specific username and password.
• Windows authentication – Uses the user’s Windows account for integrated authentication.
• External authentication – Enables connection through Azure Active Directory or other external identity providers.
• Mapped to certificate – Authenticates the user using a database certificate.
• Mapped to asymmetric key – Authenticates the user using an asymmetric key.

2.3. Configure the settings for the selected authentication method as needed.

2.4. Click Save.

Set up authentication in SQL Editor

1. Open SQL Editor: Select File > New > SQL.

2. Enter the following code to create a new SQL Server login with password policies enabled.

CREATE LOGIN your_login 
WITH PASSWORD = 'your_password', 
     CHECK_EXPIRATION = ON, 
     CHECK_POLICY = ON;

3. Click Execute to run the script.

4. Optional: Disable the sa account to enhance security.

ALTER LOGIN sa DISABLE;

Tip

Use Windows Authentication whenever possible to take advantage of centralized credential management through Active Directory.

Implement role-based access control (RBAC)

RBAC grants permissions to roles, then assigns those roles to users, so you control who can read, write, or administer database objects. You can set up users, roles, GRANT/DENY permissions, and role membership in dbForge Studio for SQL Server. For more information, see Implement and manage role-based access control in SQL Server with dbForge Studio.

Encrypt sensitive data

You can use encryption to safeguard sensitive data from unauthorized access, even if the database files are exposed or stolen. You can implement Transparent Data Encryption (TDE) to protect the whole database or apply column-level encryption to encrypt selected columns containing confidential information.

Implement Transparent Data Encryption

Transparent Data Encryption (TDE) automatically encrypts the data and log files of a database without requiring changes to applications.

1. Create a master key and certificate.

USE your_database_name;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'your_masterkey_password';
CREATE CERTIFICATE your_certificate_name 
    WITH SUBJECT = 'subject_for_your_certificate';

2. Enable TDE on the target database.

USE your_database_name;
GO
CREATE DATABASE ENCRYPTION KEY 
    WITH ALGORITHM = AES_256 
    ENCRYPTION BY SERVER CERTIFICATE your_certificate_name;
GO
ALTER DATABASE your_database_name SET ENCRYPTION ON;
GO

Note

After enabling TDE, all data written to disk is automatically encrypted. Existing data is encrypted gradually in the background.

Implement column-level encryption

Column-level encryption allows you to encrypt only specific data columns, such as credit card numbers or personally identifiable information.

1. Create a symmetric key for column-level encryption.

USE your_database_name;
GO
CREATE SYMMETRIC KEY your_key_name
    WITH ALGORITHM = AES_256 
    ENCRYPTION BY CERTIFICATE your_certificate_name;
GO

2. Open the symmetric key in the current session.

OPEN SYMMETRIC KEY your_key_name 
    DECRYPTION BY CERTIFICATE your_certificate_name;
GO

3. Add a column to store encrypted data.

ALTER TABLE your_table_name 
    ADD encrypted_credit_card VARBINARY(128);  -- Column for encrypted credit card data
GO

4. Encrypt data in the target column.

UPDATE your_table_name 
SET encrypted_credit_card = ENCRYPTBYKEY(
    KEY_GUID('your_key_name'), 
    CAST(credit_card AS NVARCHAR(100))
);
GO

Note

You must open the symmetric key in every session that performs encryption or decryption operations.

Verify encryption status

You can check the encryption status and view the existing encryption keys and certificates.

USE your_database_name;

-- Check TDE encryption status
SELECT 
    name AS database_name, 
    is_encrypted AS encryption_enabled
FROM sys.databases
WHERE name = 'your_database_name';
GO

-- encryption_enabled = 1 → TDE is ON
-- encryption_enabled = 0 → TDE is OFF

-- List all symmetric keys
SELECT name, key_algorithm, create_date 
FROM sys.symmetric_keys;
GO

-- List all certificates
SELECT name, subject 
FROM sys.certificates;
GO

Back up encryption keys and certificates

You can back up encryption keys and certificates to prevent permanent data loss.

-- Back up the TDE certificate
BACKUP CERTIFICATE your_certificate_name
TO FILE = 'C:\Backups\your_certificate_name.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Backups\your_certificate_name.pvk',
    ENCRYPTION BY PASSWORD = 'your_strong_backup_password'
);
GO

-- Open the symmetric key before backup
OPEN SYMMETRIC KEY your_key_name
    DECRYPTION BY CERTIFICATE your_certificate_name;
GO

-- Back up the symmetric key
BACKUP SYMMETRIC KEY your_key_name
TO FILE = 'C:\Backups\your_key_name.key'
ENCRYPTION BY PASSWORD = 'your_strong_backup_password';
GO

Important

Store key and certificate backups in a secure location with restricted access. Without these backups, you won’t be able to restore or access encrypted data.

Enable auditing and monitoring

You can enable auditing and monitoring to track database activity, review user actions, and identify unauthorized access attempts. Regular auditing helps maintain compliance with security policies and detect potential threats early. For more information, see Implement and manage role-based access control in SQL Server with dbForge Studio.

Encrypt backups and restrict access to them

You can encrypt database backups and restrict access to them using the Backup wizard in dbForge Studio for SQL Server or by running SQL commands in SQL Editor.

Encrypt a database backup using the wizard

1. Open the Backup Database wizard: Right-click the database in Database Explorer, then select Tasks > Back up.

2. On the Media options page, select Back up to a new media set and erase all existing backup sets.

3. On the Backup options page, select Encrypt backup, then choose a certificate or symmetric key.

Encrypt a database backup in SQL Editor

1. Open SQL Editor: Select File > New > SQL.

2. Enter the following code to create an encrypted backup.

BACKUP DATABASE your_database_name 
TO DISK = 'C:\Backups\your_database_name.bak'
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = your_certificate_name);
GO

3. Click Execute to run the script.

Restrict access to the backup folder

You can limit access to the folder holding database backups using Windows file permissions.

1. Open Windows Explorer: Select Start > File Explorer.

2. Right-click the folder you want to restrict access to and select Properties.

3. On the Security tab, remove the Everyone and Users groups.

4. Grant Full Control only to:

• The SQL Server service account (NT SERVICE\MSSQLSERVER or NT SERVICE\MSSQL$<InstanceName>)
• Database administrators (for example, DOMAIN\DBA_Team)

5. Deny or remove permissions for all other accounts.

Test backup restoration

Run the following code in a non-production environment to verify that the backup can be restored successfully.

RESTORE DATABASE your_test_database_name
FROM DISK = 'C:\Backups\your_database_name.bak'
WITH MOVE 'your_database_name' TO 'C:\Data\your_test_database_name.mdf',
     MOVE 'your_database_name_log' TO 'C:\Data\your_test_database_name.ldf',
     REPLACE;
GO

Tip

Store encrypted backups in a secure, off-site location. Limit access to authorized personnel only to ensure data confidentiality and compliance with security standards.

Monitor and audit security configurations

You can monitor and audit your SQL Server security settings to proactively manage risks, detect unauthorized activities, and maintain database integrity. For more information, see Implement and manage role-based access control in SQL Server with dbForge Studio.

Recommendations