In the SQL Server Security Ground to Cloud workshop you'll cover the basics of securing SQL Server installations and databases, from on-premises systems to Microsoft Azure deployments. After completing that workshop, you will know the basics of securing your system along with the other teams in your organization. This checklist should be used as a guide to form the basis of your own security posture, after you have completed the workshop and fully understand each section's implementation.
This checklist covers both the Microsoft Azure SQL Database platform as well as physical installations of SQL Server on bare-metal or Virtual Machine Environments. SQL Server Installations are the responsibility of the data professional and their larger security team, and Microsoft Azure SQL Database security is a shared responsibility between Microsoft and your organization. A more complete description of these responsibilities is here.
Some of these items are checked on installation or initial configuration, others are periodic tasks based on your organization's requirements and the general use of your installation. You should augment the steps in this guide to fit your organization and installations. You should review this checklist periodically to ensure compliance and to update the teams in your organization for changes or additions.
Security Checklist by "Defense in Depth" Area
The following sections should be answered as "Complete", "Checked", or "Implemented". If any area is not marked with one of these designations, you should complete the tasks to secure the area using the knowledge gained in the SQL Server Security course. You should also use the "Zero-Trust" model for your deployments.
Physical
Physical security involves restricting and controlling access to your datacenter and computing assets to only allow authorized access.
SQL Server Installations
The physical server hosting SQL Server is kept in a secure location, with alarm and monitoring systems implemented, which are documented and periodically tested.
Only authorized personnel have access to the physical server that hosts SQL Server.
All access to the physical system hosting SQL Server is audited and periodically reviewed.
Encryption at rest (such as Transparent Data Encryption) has been evaluated and implemented wherever possible.
All Database Backups are encrypted, or stored on Encrypted media.
Server Master Keys and Database Master Keys, along with other Certificate mechanisms, are backed up and secured. Ideally in a different location.
Microsoft Azure SQL Database environments
The following reference has been reviewed and approved by appropriate security auditing teams: https://docs.microsoft.com/en-us/azure/security/fundamentals/physical-security
All Database Backups are encrypted, or stored on Encrypted media, especially if exported from Microsoft Azure.
Perimeter
Perimeter security entails creating defenses at the network level for distributed denial of service (DDoS) attacks, and using network access controls, Network Security Groups, and other network segmentation strategies to limit communication between systems, avoiding spoofing, man-in-the-middle attacks, and other network-related issues.
SQL Server Installations
Only required network protocols are enabled using the SQL Server Configuration Manager utility.
Default SQL Server ports have been changed to non-standard values.
The SQL Server Browser Service has been disabled using the SQL Server Configuration Manager utility.
Where possible, Always Encrypted is enabled to ensure encryption of data on the wire and at-rest.
Strict connection encryption enabled for SQL Server 2022 and higher applications.
TLS 1.2 should be enforced when possible.
Where applicable, the Extended Protection for Authentication feature is configured using channel binding and service binding.
Microsoft Azure SQL Database environments
The following reference has been reviewed and approved by appropriate security auditing teams: https://docs.microsoft.com/en-us/azure/security/fundamentals/infrastructure-sql
Microsoft Azure Server-level IP Firewall configured for appropriate access based on application patterns.
Microsoft Azure Database-level IP Firewall configured for appropriate access based on application patterns.
Microsoft Azure Virtual network service endpoints configured for appropriate access based on application patterns.
Microsoft Azure Virtual network rules configured for appropriate access based on application patterns.
Compute
The Compute security area requires creating a "strong system for controlling access to physical and virtual machines, and implementing strong Cloud controls.
SQL Server Installations
All Windows Operating System Service Packs and Linux updates evaluated and applied after testing to the system(s) hosting SQL Server binaries and files.
Firewalls are configured to allow only specific access from authorized systems.
A process is in place for auditing and reviewing all Operating System users to check for administrative or elevated access.
A process is in place for auditing and reviewing all Operating System users to check for unusual access to resources.
The Server that hosts SQL Server Instances does not also provide file shares, print spools, applications or other functions.
SQL Server is not installed on a Domain Controller.
Binary and file locations for the SQL Server are secured against unauthorized access.
Only required Operating System components, utilities, and features are installed required for operating the SQL Server installation.
System lock after timeout is enabled.
Virus and other malware scans and tools are enabled, run, and results are periodically evaluated. The following reference has been reviewed and approved by appropriate security and auditing teams: https://support.microsoft.com/en-us/topic/how-to-choose-antivirus-software-to-run-on-computers-that-are-running-sql-server-feda079b-3e24-186b-945a-3051f6f3a95b
Only required SQL Server components, utilities and features are installed required for securely servicing authorized data and programmatic requests.
SQL Server is at a currently supported version.
SQL Server latest Service Packs and/or Cumulative Updates are tested, installed and configuration documents updated.
SQL Server CLR feature evaluated for necessary and proper use, and disabled if not.
SQL Server Application Roles documented with applicable applications that use them.
SQL Server Guest user disabled.
The SQL Vulnerability Assessment Tool has been run and a baseline report has been created, and a schedule is in place for it to be run periodcially.
The Default Trace Enabled Instance option is enabled.
A Server Audit, Server Audit Specification and Database Audit Specifications have been evaluated and implemented to the level required by the organization.
A full monitoring and alerting systems has been implemented on the server, and has a review process and team appointed.
Unless required, and after testing, the SQLWriter and SQLBrowser services are disabled.
If the CLR feature is required, the clr strict security Instance option is enabled.
The Maximum number of error log files Instance option is set to 10 or higher.
A full configuration audit of the Operating System and SQL Server Instances has been created, and is updated with Delta reports on a periodic basis. These documents are reviewed by both the Security and Data teams.
Microsoft Azure SQL Database environments
Database backup and other off-DB file storage reviewed for access only by authorised personnel.
Microsoft Azure Defender for SQL is implemented and Threat Detection is enabled, with a group alias for incident reporting.
Identitiy and Authorization
Identity and Authorization security defines the appropriate *Principals* and checking that they are who they claim using multifactor authentication[]() and other conditional access systems for infrastructure, code, and change tracking systems.
SQL Server Installations
The sa SQL Server account has been disabled or renamed, after testing.
Integrated Authentication is implemented wherever possible. Active Directory or Azure Active Directory is used wherever possible for that integration.
If SQL Server Authentication is used, the Password and Account settings have been strengthened with Password History, length, complexity, age, and lockout settings to the most restrictive possible.
The SQL Server Configuration Manager utility is used for all Service Account changes.
All SQL Server Services evaluated and disabled where not required for servicing authorised data requests.
All SQL Server Services use specific, low-privilege accounts for each service operation, and are periodically reivewed for activity.
A strong audit and review process is in place for evaluating user access to data and programmatic objects, such as using SQL Server Audit and other utilities.
A specific audit and review rule is in place for evaluating elevated Principal accounts (such as Instance or Database Administrator effective permissions), and to ensure no applications use an elevated account.
A specific audit and review rule is in place for evaluating Failed Login attempts.
The public group has no execute access to unnecessary stored procedures, such as extended stored procedures.
The xp_cmdshell is disabled.
DBA accounts have been removed from the sysadmin role, and CONTROL SERVER has been granted to DBA accounts.
There is a strict, documented and reviewed process for Operating System, Instance, and Database accounts that are no longer active, and access to data-bearing assets are revoked as soon as the user leaves the position or organization.
Unless required by auditing or Replication, the Scan For Startup Procs Instance option is disabled.
Unless required, the Database Mail XPs Instance option is disabled.
Unless required, the Cross DB Ownership Chaining Instance option is disabled.
Unless required, the Remote Access Instance option is disabled.
Unless required, the Remote Admin Connections Instance option is disabled.
Unless required, the Trustworthy Database Property is disabled. Note: The msdb System Database requires this configuration be enabled.
The SQL Agent Proxies have been audited to establish least privilege.
Microsoft Azure SQL Database environments
The highest level of authentication (Azure Active Directory and/or Role-Based Acccess Control) is implemented wherever possible.
If Kerberos (Active Directory Integration) is used, accounts are regularly reviewed for proper controls and RBAC acccess.
If SQL Server authentication is used, accounts are regularly reviewed for proper controls, strength, rotation, and object acccess.
Application
The Application security area involves implementing Secure Code practices and policies to prevent security vulnerabilities.
SQL Server Installations
Applications do not use a hard-coded password in the application for database access.
Applications are developed on a Development Instance, tested on a Testing Instance, and Developers do not have general rights to access Production Instances.
Development databases do not contain sensitive production data.
Unless required, the CLR Enabled Instance option is disabled.
Unless required, the OlE Automation Procedures Instance option is disabled
Unless required, the Ad Hoc Distributed Queries Instance option is disabled.
Each application's input for database access is sanitized to prevent SQL Injection and other attacks.
Microsoft Azure SQL Database environments
Azure SQL Database Audit is run and reviewed periodically.
Azure SQL Database Vulnerability Report is run and reviewed periodically.
Azure logs are monitored and reviewed for each Azure SQL Database environment.
Data
The Data Security Area involves ensuring that business and customer data is encrypted and protected against unwanted access at rest, in-transit, in-memory and in-code processes.
SQL Server Installations
Only system and user databases are installed and configured. All demonstration or sample databases have been removed.
All databases have been evaluated for encryption requirements, and mechanisms such as Transparent Database Encryption, Always Encrypted, and other technologies are implemented where appropriate.
The SQL Data Discovery and Classification feature has been evaluated and used to identify and classify sensitive data.
If Always Encrypted is not enabled, Dynamic Data Masking is configured for applicable columns.
Row Level Encryption has been evaluated and implemeneted for all sensitive data.
Any specifically sensitive databases are identified, documented in the configuration documents, and reviewed with the Security and Data teams. If any database is subject to higher restrictions, such as government or organization regulations, the applicable teams are also included in this review.
Least-privilege role-based security has been implemented for all data access.
SQL Server Audit features such as Change Tracking, Change Data Capture, and SQL Server Audit have been evaluated and implemented per-database as required.
Microsoft Azure SQL Database environments
Row-Level Security evaluated and implemented for sensitive data elements.
Data Masking evaluated and implemented for sensitive data elements.
For Further Study