SQL Server database security and compliance

When discussing SQL Server security, one of the most important terms is principal. Principals are SQL Server entities, arranged in a hierarchy, which can request specific SQL Server resources. There are various principals in SQL Server, and in this article, we’ll focus on a database user entity. Unlike SQL Server login entities, which are used for accessing a SQL Server instance (a server-level principal), a database user entity (a database-level principal) is used for defining access to a particular database that belongs to the SQL Server instance

Along with general SQL Server security concerns, it’s very important to track down and document any changes applied to a database user regarding compliance regulations. All compliance regulations (e.g. Basel II, HIPAA, GDPR, PCI, FERPA, GLBA, and SOX) require auditing of any changes on data and user entities that have access to the data. Auditing solutions must provide proper documentation, as an evidence trail that shows whether the accuracy and confidentiality of important data is not jeopardized

The following user entity properties are related to SQL Server database security:

• Database role membership – SQL Server provides several roles as security principals to easily manage the permissions on databases
• Securables – a list of the securables on which specific permissions have been granted or denied to this user entity, such as various alter, create, delete, and view permissions

Either of the properties can be modified via T-SQL (using the GRANT, REVOKE, or DENY operation) or SQL Server Management Studio

In order to meet SQL Server compliance requirements and maintain a SQL Server database security, it’s necessary to audit changes on user entities and their properties

Audit SQL Server database security changes using DDL triggers

As a native solution, available in all SQL Server editions, DDL triggers can be used to audit security changes on database user entities. To show how to do that, we’ll track the CREATE_USER, ALTER_USER, and DROP_USER database-level events

CREATE TRIGGER DatabaseUserChange
ON DATABASE
    FOR CREATE_USER, ALTER_USER, DROP_USER
AS
     SET NOCOUNT ON;
     DECLARE
        @AuditTable TABLE (
                          AType nvarchar(max),
                          AObject varchar(100),
                          ADate datetime,
                          AWho varchar(100),
                          ACommand nvarchar(max)
        );
     DECLARE
        @AType nvarchar(max);
     DECLARE
        @AObject varchar(100);
     DECLARE
        @ATSQL nvarchar(max);
     SELECT
            @AType = EVENTDATA().value(
            '(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(max)')
            , @AObject = EVENTDATA().value(
            '(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)')
            , @ATSQL = EVENTDATA().value(
            '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
            'nvarchar(max)');
      INSERT INTO @AuditTable
      SELECT
            @AType, @AObject, GETDATE(), SUSER_SNAME(), @ATSQL;
      SET NOCOUNT OFF;
GO

SQL Server DDL triggers can be a very efficient and cost-effective solution to track changes on database user entities. However, they are easy to bypass – a malicious user with sufficient permissions can easily disable the trigger, apply changes on user entity, and enable back the trigger afterwards. Additional downsides of this auditing method are no tamper evident auditing repository and a T-SQL knowledge requirement. T-SQL is required not for setting up the auditing only, but for providing captured information for any kind of compliance or auditing revision

Track changes on SQL Server database user entities using the SQL Server Audit feature

Another native SQL Server solution to track changes on database user entities is using the SQL Server Audit feature. Auditing on a database level is supported by SQL Server Enterprise and Developer editions only

To set up the SQL Server Audit feature, both T-SQL and SQL Server Management Studio can be used. In the following example, we’ll use SQL Server Management Studio. Note that the corresponding T-SQL for each step can be created using the CREATE To option in the Object Explorer tree view pane

To set up the SQL Server Audit feature for tracking changes on database user entities:

1. Expand the Security folder in the Object Explorer pane

2. Select New Audit from the Audits folder context menu and set Audit name (e.g. AuditDatabaseUsers) and File path (where captured information will be saved in a .sqlaudit file)

   

3. Click OK to confirm the SQL Server audit object creation
4. Select the Enable Audit option from the created audit object context menu

After the audit object is created and enabled, it is necessary do define auditing on the database by creating a database audit specification. The following steps must be performed for each database that will be monitored for database user entity changes

1. Expand the database in Object Explorer
2. Expand the Security folder
3. Select the New Database Audit Specification in the context menu of the Database Audit Specification folder

4. Set the name of the new database audit specification (e.g. DatabaseAuditUsersSpecification) and using the Audit drop-down menu, select the previously created SQL Server audit. To fine-tune the auditing, in our case to specify auditing changes on database user entities, set the following Audit Action Type rows: DATABASE_ROLE_MEMBER_CHANGE_GROUP, DATABASE_PERMISSION_CHANGE_GROUP, and DATABASE_PRINCIPAL_CHANGE_GROUP

   

5. Save the database audit specification, and enable it from its context menu by selecting the Enable Database Audit Specification option

After the audit object and database audit specification(s) were set, every change on user entities will be tracked and recorded in the .sqlaudit file

Although the .sqlaudit file can be opened from the context menu of the AuditDatabaseUsers audit object (the View Audit Logs option), that is not a convenient method, nor a good format for providing audited data

Another way to provide captured information is by using the fn_get_file_audit function which reads the *.sqlaudit files created by the SQL Server Audit feature. The following script retrieves the information related to DDL statements and actions that changed user entities:

SELECT
       event_time ,
       session_server_principal_name AS UserName ,
       server_instance_name AS ServerName,
       database_name ,
       object_name ,
       statement
  FROM sys.fn_get_audit_file('C:\AUDITs\AuditDatabaseUsers*.sqlaudit',
DEFAULT, DEFAULT);

Similar to DDL triggers, the SQL Server Audit feature provides no tamper evident repository, can be easily disabled by a user with sufficient permissions, and requires T-SQL for comprehensive captured information reporting and analysis. Moreover, it’s available in the Developer and Enterprise editions only

The out-of-the-box audit solution for SQL Server database security changes

ApexSQL Audit is a SQL Server compliance tool that provides a wide range of monitoring and reporting features in a point-and-click manner. It ensures SQL Server security and compliance requirements by auditing access and changes to multiple SQL Server instance and their objects

To audit changes on database user entities on a specific SQL Server database:

1. Start the ApexSQL Audit GUI
2. Select the SQL Server instance and check the database you want to track for changes

3. To fine-tune the tracking towards capturing changes on database user entities, check the DDL and Security options in the Operation types section

   

   If you need to audit multiple SQL Server instances or databases, repeat steps 2 and 3 accordingly on desired instances and databases

4. To confirm the settings selection, click the Apply option in the yellow pop-up ribbon

   

The built-in reports that provide information about database user entity changes are the Security configuration history and the Permission changes reports. These report templates differ in parameter fields used for filtering captured information and the column types used for presenting the information

To see the information, select the Report pane in the left menu, or click on the View reports button in the ApexSQL Audit main toolbar and use any of the previously listed reports. Also, in case of additional requirements on providing auditing documentation, which is common in case of compliance revisions, ApexSQL Audit provides a built-in custom report designer

The process of tracking SQL Server database security via ApexSQL Audit provides:

• Monitoring and reporting without T-SQL use and knowledge
• Point-and-click configuration set up
• Automatic auditing of all security changes, regardless of the SQL role that the user who did the change belongs to
• Identification of security risks and potential compliance problems
• One centralized repository with the archiving feature
• Accurate, tamper evident, and comprehensive customizable reports for analysis and reviews
• Support for all SQL Server editions, except the SQL Server Express edition

SQL Server database security and compliance can be done via native SQL Server features or using 3rd party tools like ApexSQL Audit. Even the native methods don’t provide tamper evident repositories and require advanced T-SQL knowledge for setting up monitoring and reporting. ApexSQL Audit provides a user friendly and efficient auditing and reporting method, along with all-in-one solution for multiple SQL Server instances and their databases

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.

Useful resources:

Principals (Database Engine)
Database-Level Roles
Microsoft SQL Server 2012 Security Cookbook
SQL Server Audit Action Groups and Actions