SQL SERVER – Exploring SQL Auditing with SQL Compliance Manager

As database administrators, we are constantly quizzed by our superiors to answer questions such as: how secure is our network, how secure is our data, is data secure at rest, is data secure in transition, who is accessing my data? All these are basic yet compelling queries businesses ask. In this age of competitive businesses, organizations are becoming tech savvy in building a secure fort for their critical data. Having worked on many projects in the past, these security measures are something I know are non-negotiable from an implementation point of view for administrators. When on this topic, I am reminded of a conversation I had with a junior DBA named Siva.

Siva: Hi Pinal!

Pinal: Hi Siva, how are you dude?

Siva: I am rocking and have been put onto a spot now from my internal auditing team.

Pinal: Wow, nice. Please shoot your question. Let me see what can be done.

Siva: Our internal auditing team wants to do auditing for our mission-critical sensitive database.

Pinal: Well, it’s easy and as simple as the question you asked.

Siva: Really, are you sure?

Pinal: Yes. Use the out-of-box capability of SQL Server auditing and it is something to play with too.

Siva: Oh, yeah. That is true. But there is a twist. They do want to log everything, but want to make sure our administration/maintenance activities are not logged per se.

Pinal: That is an interesting requirement for sure. But, it is not difficult either – trust me.

Siva: Now you are getting me interested. Please guide me.

Pinal: Well, start looking for filtered audit. Have you looked into it?

Siva: Oh yeah. I do need to look into it. Thanks for the pointer. If you get a chance, please write about these on your blog someday.

Pinal: Sure.

This conversation has been on my mind for quite some time and I have been wanting to write what Siva asked since then.

Simple Auditing Filtering with SQL Server

In the above conversation, I am making an assumption that the administrative tasks are done by the local administrator account like “sa.” The first step is to know the “principal id” of our administrator account. Use the following DMV to know the same:

SELECT name, principal_id FROM sys.server_principals

In our example, the “sa” account will have the principal_id of 1. So that is what we will use in our filter section of audit definition. The next is the audit definition we will create using the filter condition. A typical TSQL construct would look like:

CREATE SERVER AUDIT [Filtered-Audit-2014-10]
TO FILE
(  FILEPATH = N'C:\Temp\'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
(  QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
-- Change server_principal_id as needed
WHERE ([server_principal_id]<>(1))
GO

This is an awesome capability introduced with SQL Server 2012 and is worth a mention today. Now we can go ahead and create a server audit for specific events. Once the audit is enabled, we can go ahead and attach our events to this using the TSQL like below:

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20141020]
FOR SERVER AUDIT [Filtered-Audit-2014-10]
ADD (FAILED_LOGIN_GROUP)
GO

In this example, we have gone ahead and created an audit for failed logins. The wish list was to automate for a broader auditing capability with SQL Server.

Advanced Auditing with SQL Compliance Manager

On the topic of auditing I would like to talk a little bit about SQL Compliance Manager from Idera. As much as we would like to customize and use the out-of-box T-SQL functionality, it is sometimes necessary to do the same functionality of auditing in an automated fashion. This is where SQL Compliance Manager comes into play. There are a number of things we can start auditing using this tool. Various configuration options you can do with this tool include:

  1. Logins
  2. Failed Logins
  3. Security Changes
  4. Database Definition
  5. Administrative Activities
  6. User Defined Events

In addition to these, we can audit based on filtered access or we can audit all the events by unchecking the checkbox too. I don’t think we can get an easier configurable option for auditing logins, DDL, admin tasks in such a user friendly format. This in my opinion is the fastest way to configure audits on important tasks.

If you are a savy DBA, then the chances are you can enable all these auditing capabilities using a similar T-SQL script. But this is where I felt there are nice additions to the tool that make it worth a second look.

I particularly want to call out the auditing threshold, which can be used to track abnormal activity and proactively alert a DBA of any suspicious activity that has been identified based on thresholds set by the organization around access, login failures, DDL activity and many more. In addition to those activities, there is also a laundry list of standard reports they generate after the data is collected. This list is pretty exhaustive to say the least.

The more I play with the product, there are more pleasant surprises with each additional screen.

Wrap up

As I wrap up, feel free to use the filtered auditing capability with SQL Server and explore how these can be controlled at a granular level. If you want a quick solution that will enable auditing, keeping security and compliance in mind, then it is worth looking into SQL Compliance Manager too. It is worthwhile to trial SQL Compliance Manager for your environment. I still look forward for plugs to the current SQL Server enhancements like filtered auditing, audit resilience capabilities in the future. I am sure these are present in some shape or form today and may be explored.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Interesting Observation of Logon Trigger On All Servers

I was recently working on security auditing for one of my clients. In this project, there was a requirement that all successful logins in the servers should be recorded. The solution for this requirement is a breeze! Just create logon triggers. I created logon trigger on server to catch all successful windows authentication as well SQL authenticated solutions. When I was done with this project, I made an interesting observation of executing logon trigger multiple times. It was absolutely unexpected for me! As I was logging only once, naturally, I was expecting the entry only once. However, it was doing it multiple times on different threads – indeed an eccentric phenomenon at first sight!

Let us first pore over our example.

Create database, table and logon trigger

/* Create Audit Database */
CREATE DATABASE AuditDb
GO
USE AuditDb
GO
/* Create Audit Table */
CREATE TABLE ServerLogonHistory
(SystemUser VARCHAR(512),
DBUser VARCHAR(512),
SPID INT,
LogonTime DATETIME)
GO
/* Create Logon Trigger */
CREATE TRIGGER Tr_ServerLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO
AuditDb.dbo.ServerLogonHistory
SELECT SYSTEM_USER,USER,@@SPID,GETDATE()
END
GO

Login using SQL Authentication and check audit table


Login using Windows Authentication and check audit table

The above example clearly demonstrates that there are multiple entries in the Audit table. Also, on close observation it is evident that there are multiple process IDs as well. Based on these two observations I can come to one conclusion. Similar actions like login to the server took place multiple times.

Question to readers:

Have you ever experienced this kind of situation? If yes, then have you received similar entries?

For those, who have not experienced this phenomenon yet, they can recreate this situation very quickly by running the above script, and then you all can post your observations and conclusions here.

I am very much interested in learning the cause that triggers multiple entries. Valid suggestions and explanations will be published on this blog with due credit.

Reference : Pinal Dave (http://blog.SQLAuthority.com)