MSDN Documentation: SQL Security Samples

Introduction to SQL Security

This section provides foundational knowledge and best practices for securing your SQL Server instances and databases. Understanding these concepts is crucial for protecting sensitive data from unauthorized access and malicious attacks.

• Authentication Mechanisms
• Authorization and Permissions
• Data Encryption Strategies
• Auditing and Logging

Sample Scripts: Authentication

Explore sample T-SQL scripts demonstrating how to implement and manage different authentication methods for SQL Server. This includes Windows Authentication and SQL Server Authentication.

Creating SQL Logins

-- Create a SQL Server login with a strong password
CREATE LOGIN [MyNewUser] WITH PASSWORD = N'VeryStrongP@ssw0rd123!',
    DEFAULT_DATABASE = [MyDatabase],
    CHECK_EXPIRATION = ON,
    CHECK_POLICY = ON;
GO
                

Enabling Mixed Mode Authentication

To use SQL Server Authentication, ensure Mixed Mode is enabled in SQL Server Configuration Manager.

Sample Scripts: Authorization

Learn how to grant, deny, and revoke permissions on database objects like tables, views, and stored procedures to control user access effectively.

Granting Permissions

-- Grant SELECT permission on a table to a SQL login
GRANT SELECT ON SCHEMA::[dbo] TO [MyNewUser];
GO

-- Grant EXECUTE permission on a stored procedure
GRANT EXECUTE ON OBJECT::[dbo].[MyStoredProcedure] TO [MyNewUser];
GO
                

Creating Roles

Use roles to group permissions and assign them to users for easier management.

-- Create a database role
CREATE ROLE [DataReaderRole];
GO

-- Add a user to the role
ALTER ROLE [DataReaderRole] ADD MEMBER [MyNewUser];
GO

-- Grant permissions to the role
GRANT SELECT ON SCHEMA::[dbo] TO [DataReaderRole];
GO
                

Sample Scripts: Encryption

Implement data encryption at rest and in transit to protect sensitive information. This includes Transparent Data Encryption (TDE) and Always Encrypted.

Enabling Transparent Data Encryption (TDE)

TDE encrypts data files and transaction log files.

-- Create a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'MasterKeyP@ssw0rd!';
GO

-- Create a certificate for TDE
CREATE CERTIFICATE [TDE_Cert]
WITH SUBJECT = N'TDE Encryption Certificate',
    EXPIRY_DATE = N'2025-12-31';
GO

-- Create a symmetric key using the certificate
CREATE SYMMETRIC KEY [TDE_Symmetric_Key]
WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE [TDE_Cert];
GO

-- Enable TDE for a database
ALTER DATABASE [MyDatabase] SET ENCRYPTION ON;
GO
                

Using Always Encrypted

Always Encrypted allows applications to encrypt sensitive data inside SQL Server. Configuration involves SQL Server, client drivers, and key management.

Sample Scripts: Auditing

Set up SQL Server Audit to track database events, providing a log of who did what and when. This is essential for compliance and security monitoring.

Creating a Server Audit

-- Create a server audit specification
CREATE SERVER AUDIT [SQLSecurityAudit]
TO FILE (
    FILEPATH = N'C:\SQLAudits\' ,
    MAXSIZE = 5 MB ,
    MAX_ROLLOVER_FILES = 5 ,
    RESERVE_DISK_SPACE = OFF
)
WITH ( QUEUE_DELAY = 1000 ,
    ON_FAILURE = CONTINUE );
GO

ALTER SERVER AUDIT [SQLSecurityAudit] WITH ( STATE = ON );
GO
                

Creating a Database Audit Specification

-- Create a database audit specification
CREATE DATABASE AUDIT SPECIFICATION [DBAccessAudit]
FOR SERVER AUDIT [SQLSecurityAudit]
ADD (SELECT ON DATABASE::[MyDatabase] BY [dbo]),
ADD (INSERT ON DATABASE::[MyDatabase] BY [dbo]),
ADD (UPDATE ON DATABASE::[MyDatabase] BY [dbo]),
ADD (DELETE ON DATABASE::[MyDatabase] BY [dbo])
WITH (STATE = ON);
GO