Auditing security changes in SQL Server (4214765)

When it comes to SQL Server security, it’s important to note that there are server and database security levels. All work done by a user is performed on a database, but in order to access the database and do the work, the user first needs to access the server, and afterwards the database - the server security level affects the database security level

To access the server, an appropriate login entity must be set for the user. A SQL Server login entity determines user credentials for accessing SQL Server. Security on SQL Server initially depends on properly set logins since they actually represent an entrance to SQL Server and its databases. Also, logins are an important realm of any compliance regulation – confidentiality, consistency, and accuracy of data can be easily jeopardized if a user is granted sufficient permissions within his login.

There are two SQL Server login properties related to SQL Server security:

• Server Roles – “SQL Server provides server-level roles to help you manage the permissions on a server. These roles are security principals that group other principals. Server-level roles are server-wide in their permissions scope. (Roles are like groups in the Windows operating system.)” [1]
• Securables - the resources to which the SQL Server Database Engine authorization system regulates access (e.g. the View any database permission)

These properties can be changed using either SQL Server Management Studio or T-SQL. In both cases the GRANT, REVOKE, or DENY operations are actually applied on the SQL Server login entity that is being changed.

To meet compliance regulations and maintain SQL Server secured it’s required to audit security changes, whether the changes were intentional or accidental.

Audit SQL Server security changes using DDL triggers

Changes applied to SQL Server logins related to the Securables and Server Roles properties can be audited using DDL triggers on server level. In the following example, we’ll use the ADD_SERVER_ROLE_MEMBER, DDL_GDR_SERVER_EVENTS, and DROP_SERVER_ROLE_MEMBER SQL Server security events in the DDL trigger, which will fire and capture login entities altering operations.

CREATE TRIGGER DDL_AUDIT_Logins ON ALL SERVER
FOR ADD_SERVER_ROLE_MEMBER
	,DDL_GDR_SERVER_EVENTS
	,DROP_SERVER_ROLE_MEMBER AS

SET NOCOUNT ON;

DECLARE @EventsTable TABLE (
	EType NVARCHAR(max)
	,EObject VARCHAR(100)
	,EDate DATETIME
	,EUser VARCHAR(100)
	,ECommand NVARCHAR(max)
	);
DECLARE @EType NVARCHAR(max);
DECLARE @ESchema NVARCHAR(max);
DECLARE @DBName VARCHAR(100);
DECLARE @Subject VARCHAR(200);
DECLARE @EObject VARCHAR(100);
DECLARE @EObjectType VARCHAR(100);
DECLARE @EMessage NVARCHAR(max);
DECLARE @ETSQL NVARCHAR(max);

SELECT @EType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',
 'nvarchar(max)')
,@ESchema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]',
 'nvarchar(max)')
,@EObject = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',
 'nvarchar(max)')
,@EObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]',
'nvarchar(max)')
,@DBName = EVENTDATA().value('
(/EVENT_INSTANCE/DatabaseName)[1]',
 'nvarchar(max)')
,@ETSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 
'nvarchar(max)');

INSERT INTO @EventsTable
SELECT @EType
	,@EObject
	,GETDATE()
	,SUSER_SNAME()
	,@ETSQL;

SET @EMessage = 'Login_Event: ' + @EType + CHAR(10) + 'Event Occured at: '
 + Convert(VARCHAR, GETDATE()) + CHAR(10) + 'Changed Login: ' + @EObject + 
CHAR(10) + 'Changed by: ' + SUSER_SNAME() + CHAR(10) + 'Executed T-SQL: ' + 
@ETSQL

SELECT @Subject = 'SQL Server Login changed on ' + @@servername;

EXEC msdb.dbo.sp_send_dbmail @recipients = 'DDL_Alert@companydomain.com'
	,@body = @EMessage
	,@subject = @Subject
	,@body_format = 'HTML';

SET NOCOUNT OFF;
GO

Trigger auditing provides viable login changes tracking. On the other hand, it requires certain level of T-SQL knowledge, and the trigger can be disabled easily by a user with sufficient permissions

Track security changes using SQL Server Audit feature

The SQL Server Audit feature tracks both server and database events, utilizing the Extended Events technology. The feature can be setup either using T-SQL or SQL Server Management Studio. We’ll use SQL Server Management Studio in this article; the corresponding T-SQL scripts can be easily generated using the CREATE To option

To track and document login entity changes on a specific SQL Server instance:

1. Expand the Security folder
2. Select New Audit and, using the Create Audit dialog, set the Audit name (e.g. AuditLoginChangesSpecification) and the File path (a .sqlaudit file with corresponding name will be created in the folder and used as an audit repository)
   

   

3. Click OK to confirm the SQL Server audit object creation
4. Right-click the AuditLoginChangesSpecification audit and select the Enable Audit option
   

   Using the following steps we’ll create a server audit specification, which requires a previously created SQL Server audit object (AuditLoginChangesSpecification). The server audit specification will be used to fine-tune and specify exactly what will be tracked - changes on login entities

5. Right-click the Server Audit Specification folder and select New Server Audit Specification
6. Type in the name of the new server audit specification (e.g. AuditLoginChangesServerSpecification) and select the audit object previously created using the Audit drop-down menu. In order to fine-tune the auditing, set the following:
1. Audit Action Type: SERVER_ROLE_MEMBER_CHANGE_GROUP – “This event is raised whenever a login is added or removed from a fixed server role. This event is raised for the sp_addsrvrolemember and sp_dropsrvrolemember stored procedures.” [2]
2. Audit Action Type: SERVER_PERMISSION_CHANGE_GROUP – “This event is raised when a GRANT, REVOKE, or DENY is issued for permissions in the server scope, such as creating a login.” [2]
   

   

7. Click OK to confirm the server audit specification creation
8. To complete the process of setting up the SQL Server audit feature, right-click the created audit and select the Enable Server Audit Specification option

After the audit object and server audit specification are set and enabled, the SQL Server security related changes executed on login entities will be tracked and documented in the .sqlaudit file

To view the audited login changes on SQL Server:

1. To start the audit log viewer dialog right click the AuditLoginChangesSpecification audit and select the View Audit Logs option
2. The dialog will show the captured data about the login changes with statements executed on the login entities

The other way to export and provide captured data is via the fn_get_audit_file function. The function reads the *.sqlaudit repository files created by the SQL Server Audit feature

The following script queries the captured information related to SQL Server login changes:

SELECT event_time
	,session_server_principal_name AS Changed_by
	,target_server_principal_name AS LoginName
	,server_instance_name
	,statement
FROM sys.fn_get_audit_file('C:\AUDITs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id = 'G'
	OR action_id = 'APRL';

Unlike the database level auditing, the SQL Server Audit native feature on the server level is supported on all SQL Server editions, starting from SQL Server 2008. However, for viable reporting and querying of captured information, T-SQL knowledge is required. And again, a user with sufficient permissions can disable the audit specification, apply the login changes, and enable back the audit specification without a trace

Audit SQL Server login changes – the out-of-the-box solution

Any security related changes applied to SQL Server login entities are potential security issue. Moreover, any compliance regulation requires login changes to be captured, properly documented, and made available on request

ApexSQL Audit is a SQL Server auditing tool, with a range of built-in and custom reports, which monitors changes and access on multiple SQL Server instances and their objects (databases, tables, stored procedures, functions, and views)

To audit login changes on a particular SQL Server instance:

1. Start ApexSQL Audit GUI
2. Select the SQL Server instance you want to audit and check the DDL and Security filter options under operations in the Simple filter
   

   

   Or by using the Advanced filter:

   

3. Confirm the settings change by selecting the Apply option on the yellow pop-up ribbon

   

To audit security changes on multiple SQL Server instances, repeat the first two steps on each of SQL Server instance

Captured data about changes on login entities are reported within the Security configuration history report. The report provides all changes on the security entities (logins, users, and roles) including information about logins being created or dropped, permissions granted, revoked, or denied. Each event is documented with a time of execution, SQL Server instance name, login name, application (e.g. Microsoft SQL Server Management Studio), client computer name, operation, T-SQL that was executed, class (e.g. Audit add member to DB role event), and event sub class

In order to track and document changes on SQL Server login entities ApexSQL Audit provides:

• Centralized point-and-click audit configuration set up for multiple SQL Server instances
• Monitoring and reporting process without use of T-SQL
• Automatic auditing of events related to the SQL Server security, without excluding administrative stuff actions, and identification of potential security risks
• One central repository database with the archiving feature
• Comprehensive, accurate, and tamper evident reports for reviews

To audit login changes on SQL Server, you can use either native SQL Server features and methods, or products like ApexSQL Audit. Even the native methods are built-in, they do not offer an out-of-the-box solution, and require advanced knowledge for configuring and reporting captured information. On the other hand, ApexSQL Audit provides value added features like centralized tamper-evident design, an out-of-the box solution for a number of compliance requirements, multiple SQL Server instances auditing, and more via user friendly interface

Downloads

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

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

References

[1] Server-Level Roles
[2] SQL Server Audit Action Groups and Actions

Useful resources

Microsoft SQL Server 2012 Security Cookbook
Audit Login Change Property Event Class
Login Auditing (SQL Server Management Studio)
DDL_SERVER_SECURITY_EVENTS