SQL Server: Security Practices and Checklist March 8th, 2013

Vinod Kumar

Now that you did get a chance to see my previous post around Database designs there have been few who have asked more of these posts. In continuation to that concept, this blog post will concentrate on highlighting the SQL Server security practices that I often call out and recommend to customers in our Architecture and design sessions. As always the disclaimer remains the same – these are generic practices / suggestions and might not be the same in your environment and deployment scenarios.

Security is a topic that is always seen as a after thought and I start reiterating to our every single customer how critical and important this is. Most of these are to be done by the database administrator of the environment. It is also left to us to keep track and constantly audit if any of them have been deviated or broken.

Other Practices posts have been:

SQL Server: Database Design Practices

This by no means is not the end-all list of security tips I suggest. But this is a great starting point for you.

General Suggestions

  1. The SQL Server service pack level is current.
  2. Ensure physical security of your server and only authorized people get access to the datacenter.
  3. Make sure SQL is installed with Windows Authentication as much as possible.
  4. Cross database ownership chaining is disabled.
  5. Always block TCP port 1433 and UDP port 1434 on your perimeter firewall. If named instances are listening on additional ports, block those too.
  6. Database Engine Ad Hoc distributed queries is disabled.
  7. Make sure xp_cmdshell is disabled. Don’t use it.
  8. The default trace option is enabled by default. Though non-intrusive, check the need if the same needs to be on production environment.
  9. The DB software installation account is restricted to authorized users only.
  10. Automated tools are used to provide audit trail reports using System Center or any other tool.
  11. SQL Server Agent jobs are monitored in accordance with procedures for failures and success.
  12. Database data files are stored in the same logical storage partition and permissions are proper.
  13. Never install SQL Server on a domain controller.
  14. The ports which the DB uses should be configured appropriately and must not be left to default (1433).
  15. SQL Server registry keys should be properly secured.
  16. Plan for an upgrade/migration plan to address an unsupported DB software version.
  17. Remove sample databases from production servers.

Logins Related

  1. Make sure DB password complexity has not been disabled.
  2. DB account passwords are set to expire every 60 days or more frequently based on your environment.
  3. The sa password has been changed from the default. Disable "sa" and make sure to rename them.
  4. Make sure the ‘remote admin connections’ (also referred to as the Dedicated Administrator Connection (DAC)) option is not disabled.
  5. Dedicated accounts have to be designated for SQL Server Agent proxies.
  6. The Database installation account is separate from the logged in user on the server.
  7. Do review changes to DBA role assignments and the logins who have DBA role access.
  8. Make sure no unnecessary privileges granted to the Windows Service accounts or to Windows Users on the local machine.
  9. The SQL Server service uses a least-privileged local or domain user account.
  10. The SQL Server service account is not assigned excess host system privileges.

Audit Related

  1. Audit trail data is not maintained for one year.
  2. The auditing of logins is configured for failed (or all) logins.
  3. Database executable and configuration files are being monitored for unauthorized modifications.
  4. Transaction logs have to be reviewed for unauthorized modification of classified data.
  5. Audit trail data is reviewed regularly to detect database access by unauthorized applications.
  6. Audit records are restricted only to authorized individuals.
  7. Make sure audit logs are included in backup operations regularly.
  8. Auditing attempts to bypass access controls must be logged.
  9. Trace Rollover should be enabled for audit traces that have a maximum trace file size.

Encryption related

  1. Database passwords used by batch and/or job processes should be in encrypted format.
  2. Sensitive information stored in the database has been identified and protected using encryption.
  3. The Service Master Key is backed up and stored offline and off site.
  4. Make sure the procedures and restrictions for import of production data to development databases are implemented. If obfuscation of data needs to be done while importing, this needs to be adhered to.
  5. Access to Master Key is restricted, controlled and audited.
  6. Symmetric keys should use a master key, certificate, or asymmetric key to encrypt the key.

Permission related

  1. Permissions assigned to PUBLIC or GUEST for a database must be reviewed monthly.
  2. Application user privilege has to be reviewed monthly or more frequently to ensure compliance with least privilege and documented policy of your organization.
  3. Track who has permissions in using the WITH GRANT OPTION.
  4. Never grant permissions to the public database role.
  5. Do not enable the guest account.
  6. Periodically scan fixed server and database roles to ensure that membership is only granted to trusted individuals.
  7. Ensure that the mapping between database users and logins at the server level is correct.
  8. Assign permissions to roles rather than directly to users.
  9. Objects may be owned by roles / schemas, rather than directly by users, if you want to avoid application changes when the owning user is dropped.

Common Security Lapses

  1. DB backup and restoration files are not protected from unauthorized access.
  2. Make sure the Database Master Key passwords are not stored in credentials within the database.
  3. Database backup procedures are defined and implemented properly.
  4. Backup and recover procedures have been implemented/tested.
  5. Check for unapproved inactive or expired database accounts on the database/server.
  6. The DB is periodically tested for vulnerability management and compliance.
  7. Make sure the database is NOT accessible to internet users and is located in a DMZ.
  8. Replication snapshot folders are protected from unauthorized access.
    Linked server providers should not allow ad hoc access.

Miscellaneous Points

  1. Developers are not assigned excess privileges on production databases.
  2. The Named Pipes network protocol is disabled.
  3. The Analysis Services ad hoc data mining queries configuration option has been disabled.
  4. Analysis Services Anonymous Connections are disabled.
  5. Track and audit stored procedures executing at startup.
  6. Error log retention should be set to meet log retention policy. Else increase number of Log files.
  7. Database TRUSTWORTHY status should be authorized and documented or set to off.
  8. The SMO and DMO SPs option should be set to disabled if not required. Try not to use this.
  9. The Agent XPs option should be set to disabled if not required.
  10. Database Engine Ad Hoc distributed queries should be disabled.
  11. Run separate SQL Server services under separate Windows accounts.
  12. Assign static ports to named instances of SQL Server.
  13. Keep an inventory of all versions, editions, and languages of SQL Server for which you are responsible. Include instances of Express Editions in your inventory as it will help in patching.

Do tell me if you found this useful and I am sure there are areas of Development and coding that I will write in future blog posts.

Tags: , , , , , , , , ,

This entry was posted on Friday, March 8th, 2013 at 08:00 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “SQL Server: Security Practices and Checklist”

  1. solomon says:

    Very Good. Thanks!!

  2. Ambati Siva says:

    Very important points and noted with thanks..:)

  3. very effective points.

Leave a Reply