As part of an internal security review, I put together the following best practices guideline to secure SQL servers. This is just an example, and is not meant to be a comprehensive list of SQL server security parameters.
- DATABASE CREATION AND CHANGES
- New databases must be requested using a SQL database request form with proper documentation including application owner, purpose, etc. Refer to my blog post (“DBA: Create A SQL Server Database Request Form For Auditing, Change Tracking And Security“) for a database request form example.
- Database changes (example: moving a database to a different SQL server) should also be requested via the SQL database request form. This will also serve to document existing databases and their purpose.
- If given the option, name new databases descriptively. (Some applications may not allow for custom names)
- NEVER use the SQL SA account (or any account with SA equivalence) as a service account for application access to a database.
- Create a unique SQL login account (“service account”) for each application with a descriptive name and a secure password (example: no dictionary words, at least 10 characters in length with a combination of lower case, upper case, numbers and symbols).
- If you do not enforce a password change policy on SQL accounts used by applications (“service accounts”) then devise a manual process of regular password changes for each account.
- Most service accounts have full database access, but if this is not necessary, limit access to the level required.
- SECURITY AUDITING
- Each SQL server should have an internal audit conducted quarterly using an internal audit procedure. Refer to my blog post (“DBA: SQL Audit Checklist For Internal Security Review“) for an example of a checklist you can use to audit your SQL servers.
- Maintain unique copies of the checklist for each SQL server and each quarterly audit for reference.
- Checklist items must be re-produced for each audit. (Do not copy and paste)
- Compare new results against old results and document reasons for changes.
- SECURING PHYSICAL SERVER
- Limit the number of employees who have physical access to the SQL server. If your SQL server is in a computer room with code access, limit the number of employees who have access to this code, and change the code on a regular basis.
- Configure alerts (example: email via HP iLO or Dell DRAC) for hardware warnings and failures.
- SECURING VIRTUAL SERVER / SERVER OS
- Limit the number of employees who have administrative access via VMware console (or other virtual console) and Remote Desktop Services.
- Consider limiting OS administrator access to only SQL admins (via AD group and Group Policy Object).
- SECURING SQL
- All SQL and Windows AD accounts that have access to SQL databases should be documented. Consider an access request form for user access with supervisor/manager approval.
- SQL login auditing should be configured to audit “Failed logins” and a procedure should be created to review failed logins and other security data regularly.
- Remove any unneeded databases (sample databases like AdventureWorks).
- Change the default SA account password to a secure password (example: no dictionary words, at least 10 characters in length with a combination of lower case, upper case, numbers and symbols).
- Consider renaming or disabling the SA account.
- Only use named user accounts for SQL administrative purposes. (No shared accounts)
- Limit SQL SA equivalent access to SQL admins only. (Use AD group mentioned in Section 4)
- Only use SQL login accounts (“service accounts”) for applications.
- Only assign permissions necessary for SQL accounts to perform their required function.
- All accounts for named user access should be controlled by AD. (Don’t create SQL logins for named users)
- Do not share any folders (especially database folders) on the network.
- Do not assign elevated security permissions to SQL installation locations or database directories.
- BUSINESS CONTINUITY / DISASTER RECOVERY
- Confirm a maintenance plan and automated schedule is in place for all databases that require backups. A typical backup plan creates a new full database copy each night and keeps a specified number of old backups (depending on disk space limitations).
- Confirm a secondary off-site backup method is configured to archive the above database copies (either tape, or copy to volume that syncs to disaster recovery location).
- Confirm backup jobs (both local SQL and off-site) complete successfully and configure some form of email alert when backup jobs fail.
- Monitor CPU/memory/disk utilization and plan upgrades accordingly.