SQL Server Database Security Audit (Part 1): What to expect?

By: Svetlana Golovko   |   Updated: 2012-11-21   |   Comments (10)   |   Related: > Auditing and Compliance

Problem

I am new to Database Administration and have been asked to prepare data for an audit. I know there are a lot of different metrics I can pull from SQL Server, but I am not exactly sure where to begin.  What items would you suggest to look into to make sure that we have information for the audit?

Solution

A SQL Server security review should be part of the DBAs regular activity. Some people do this once a month and others more often. You can also have this completely or partially automated (using server level DDL triggers, alerts, third party tools etc.).

The checklist below will help you to be ready to answer some security related questions for your next audit.

What do you need to check in first place?

• Logins
  • You should enable at least the failed logins audit. This will help you to identify brute force or unauthorized login attempts. 
  • Refer to this tip for more information.
• Members of "Local Administrators" group on SQL Server:
  • This check is required to make sure that only authorized users are members of the group.
  • Refer to this tip for more information.
• Sysadmin server role members (and other server level roles):
  • This check is required to make sure that only authorized logins are members of the server groups (you may need to justify each case).
  • Refer to this tip for more information.
• Db_owner database role members:
  • This check is required to make sure that only authorized users are in this role. This role should be used for the database administration only. Developers, application accounts should be given only permissions required to perform their tasks.
  • Refer to this tip for more information.
• Password policies and expiration for the SQL logins are enabled:
  • This check is required to make sure that the passwords are strong and cannot be easily compromised.
  • Refer to this tip for more information.
• Builtin/Adminstrators group removed from sysadmin's server role (not this is removed by default in the latest versions of SQL Server):
  • One of the reasons to check this is that DBAs don't always have control of this group's membership. Another reason for this is separation of duties. Only DBAs should be members of the sysadmin's role.
  • Refer to this tip for more information.
• Production and Test databases are segregated (on different SQL Servers):
  • One of the reasons is potential access of the production database due to hard coded database names (cross-database queries). Production database could be accidentally updated during the testing if database names have not been updated in test stored procedures. Also, testing can be resource intensive and may affect production databases.
• Sample databases (AdventureWorks, Pubs etc.) are not present on Production SQL Servers:
  • These databases are not production databases and should not be installed on production SQL Servers. A security review is required for each of these databases if they are used.
• Set strong password for "sa" login, disable "sa" login, or possibly rename "sa" login if there are no legacy applications that depend on it (change database connection to use less privileged account):
  • Most attackers will try to use "sa" login first.
  • Refer to this tip for more information.
• Review initialization parameters (sp_configure settings) -
  • Ad Hoc Distributed Queries
    • Disable if not required. There is a possibility of somebody using SQL Server authentication with it where the provider string will contain an embedded password.
  • clr enabled
    • Disable if not used to reduce number of the enabled features and therefore narrow down patching and potential attack surface.
  • cross db ownership chaining:
    • Disable if not required. This option allows for the bypassing of permissions between databases.
  • Database Mail XPs
    • Disable if not required. To reduce the number of the enabled features and therefore narrow down patching and potential attack surface.
  • xp_cmdshell
    • To avoid network and OS related attacks through the OS commands. This is disabled by default.
• CONNECT permissions to the guest user
  • Make sure that CONNECT permissions are denied on all databases for this user except master and tempdb (especially if you have SQL Server 2012 with contained databases).
• Restrict access to backup files to System Administrators and/or DBAs:
  • This has to be done to make sure that company's sensitive data is not available to anybody else.
  • Refer to this tip for more information.
• SQL Server Security settings:
  • Set to Windows Authentication (which is more secure) where possible. Justify your SQL security mode and prove that it has to be mixed if it is set to mixed (for example, legacy applications requirements).
  • Refer to this tip for more information.
  • SQL Server build:
    • Be ready to answer the questions about your database system patch level and patching procedures. Make sure that the latest security fixes have been applied.
    • Refer to this tip for more information.

What else can you expect?

• Auditors may ask you for documentation for the following processes:
  • Review of databases users
  • When accounts are created and deleted
  • List of permissions via GRANT
• You may need to provide the following information:
  • Where data and transaction logs are located
    • best practice - make sure they are on separate partitions
  • What network protocols are enabled
    • best practice - disable Named Pipes if not used
  • SQL Server services startup mode
    • best practice - make sure it is automatic
  • List of Application roles
  • List of remote and linked servers (including security configuration)
    • best practice - replace remote servers with linked servers and use Windows authentication
• Make sure you have database diagrams for the most important databases and/or list the names of the most important tables.
• You may need to provide the evidence for the following:
• Successful and/or failed backups and related notifications
• Server/database/objects audit (all or some of the events, for all databases or just for critical databases):
  • Audit Add DB User Event
  • Audit Add Role Event
  • Audit Add Login to Server Role
  • Audit Addlogin Event
  • Audit Add Member to DB Role
  • Audit App Role Change Password
  • Audit Change Audit
  • Audit Change Database Owner
  • Audit Database Scope GDR
  • Audit Database Management
  • Audit Database Object GDR
  • Audit Database Operation
  • Audit Database Object Management
  • Audit Database Principal Impersonation
  • Audit Database Principal Management
  • Audit Database Scope GDR
  • Audit Login Change Password
  • Audit Login Change Property
  • Audit Login GDR Event
  • Audit Object Derived Permission Event
  • Audit Schema Object GDR
  • Audit Schema Object Management
  • Audit Schema Object Take Ownership
  • Audit Server Alter Trace
  • Audit Server Object GDR
  • Audit Server Object Management
  • Audit Server Object Take Ownership
  • Audit Server Operation
  • Audit Server Principal Impersonation
  • Audit Server Principal Management
  • Audit Server Scope GDR
  • Audit Database Object Take Ownership
• You may need to review SQL Server folders permissions, but be careful here. First of all make sure that service account and DBAs have access to the folders. To get a list of the permissions that are granted by SQL Server setup use this URL: http://msdn.microsoft.com/en-us/library/ms143504(v=sql.105).aspx#Reviewing_ACLs.
• You may need to provide the list of logins with specific OS rights (for example, "Log on as a service").
• Remove logins without permissions.
• Remove orphaned database users (where there is no SQL Server logins exist).
• Fix broken database users (SQL logins mapping) with "ALTER USER" statement or with sp_change_users_login stored procedure.
  • Refer to this tip for more information.

Every company has different requirements for the audit, so the list could be longer or shorter. Make sure you know these requirements, know what tables you need to audit, know where sensitive data is stored and how it accessed. Also, find out what are your company's industry standards. I hope this will help you to be ready for your next audit.

Note: All security changes you are planning to make should be tested first on your test servers to make sure that there are no dependencies (applications specific requirements, legacy applications etc.) and that applications are still functioning properly.

Next Steps

• Build a checklist that works for your environment
• Have your scripts ready and start collecting data.
• Check your servers and databases.
• Read more tips on SQL Server Auditing and Compliance.

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips