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.

1. DATABASE CREATION AND CHANGES
   1. 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.
   2. 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.
   3. If given the option, name new databases descriptively. (Some applications may not allow for custom names)
   4. NEVER use the SQL SA account (or any account with SA equivalence) as a service account for application access to a database.
   5. 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).
   6. 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.
   7. Most service accounts have full database access, but if this is not necessary, limit access to the level required.
2. SECURITY AUDITING
   1. 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.
   2. Maintain unique copies of the checklist for each SQL server and each quarterly audit for reference.
   3. Checklist items must be re-produced for each audit. (Do not copy and paste)
   4. Compare new results against old results and document reasons for changes.
3. SECURING PHYSICAL SERVER
   1. 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.
   2. Configure alerts (example: email via HP iLO or Dell DRAC) for hardware warnings and failures.
4. SECURING VIRTUAL SERVER / SERVER OS
   1. Limit the number of employees who have administrative access via VMware console (or other virtual console) and Remote Desktop Services.
   2. Consider limiting OS administrator access to only SQL admins (via AD group and Group Policy Object).
5. SECURING SQL
   1. 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.
   2. 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.
   3. Remove any unneeded databases (sample databases like AdventureWorks).
   4. 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).
   5. Consider renaming or disabling the SA account.
   6. Only use named user accounts for SQL administrative purposes. (No shared accounts)
   7. Limit SQL SA equivalent access to SQL admins only. (Use AD group mentioned in Section 4)
   8. Only use SQL login accounts (“service accounts”) for applications.
   9. Only assign permissions necessary for SQL accounts to perform their required function.
   10. All accounts for named user access should be controlled by AD. (Don’t create SQL logins for named users)
   11. Do not share any folders (especially database folders) on the network.
   12. Do not assign elevated security permissions to SQL installation locations or database directories.
6. BUSINESS CONTINUITY / DISASTER RECOVERY
   1. 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).
   2. 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).
   3. Confirm backup jobs (both local SQL and off-site) complete successfully and configure some form of email alert when backup jobs fail.
   4. Monitor CPU/memory/disk utilization and plan upgrades accordingly.

/ photo by jimgris

Twitter

Google+

Facebook

Reddit

Pinterest

Tumblr

Digg

StumbleUpon

Linkedin

email