SQL SERVER – How to Chang Audit Location?

The auditing capability inside SQL Server is a hidden gem and not known to many in my opinion. If you have a requirement to audit your SQL Server environment and want to keep track of the activities such as DBCC commands on the server, backup, restore, failed logins, login creations, database access, database permission changes, user password changes, trace changes and much more can be audited at the server node. A similar longer list is available at the database audit node too which we can use. In this blog post let me talk about a simple task of changing the location of the audit log after it was created.

This post is inspired by one of the sessions I attended where one of the attendees had asked, if we can audit and store the information of audit on a network share. What will happen when the network share is unavailable because of network issues? Is there a seamless way to switch or change the network location anyhow? This was an interesting question and I do know from SQL Server 2012, the concept of audit resilience is available. Moreover we can also change the share which holds the Audit information using TSQL.

In this blog post we will go in sequence below:

  1. Create an Audit on a network share
  2. Enable the Audit
  3. Create an Server Audit Specification
  4. Enable the Server Audit Specification
  5. Look at the metadata and access the Logs
  6. Create an failure by removing sharing the Network drive
  7. Change the Audit location to a local drive
  8. Access the Audit logs

So let us go through the scripts for the above steps:

-- Step 1 - Create an Audit on a network share
CREATE SERVER AUDIT [NetworkAudit]
TO FILE(FILEPATH='\\SQLAuthority\Audit\Network') /* substitute in here network drive */
WITH (ON_FAILURE=FAIL_OPERATION, QUEUE_DELAY=0);

-- Step 2 - Enable the Audit
ALTER SERVER AUDIT [NetworkAudit] WITH (STATE=ON);

-- Step 3 - Create a Server Audit Specification
CREATE SERVER AUDIT SPECIFICATION [complianceServerSpec] FOR SERVER AUDIT [NetworkAudit]
ADD (SCHEMA_OBJECT_ACCESS_GROUP);

-- Step 4 - Enable the Server Audit Specification
ALTER SERVER AUDIT SPECIFICATION [complianceServerSpec] WITH (STATE=ON);

If you ask me these are the most common steps we will take to create ANY audit. As outlined above, we have created a server audit where we are auditing any Schema Object Access.

-- Step 5 - Look at the metadata and access the Logs
SELECT * FROM sys.server_file_audits
SELECT * FROM sys.fn_get_audit_file('\\SQLAuthority\Audit\Network\*', NULL, NULL);
SELECT * FROM sys.dm_server_audit_status

Let us simulate an error on the network path where the audit files are located. Check the metadata where the filesize if 0. That indicates we have a lost network location.

-- Step 6 - Create an failure by unsharing the Network drive
SELECT * FROM sys.dm_server_audit_status

If we try to read the file location we will get the following error:

SELECT * FROM sys.fn_get_audit_file('\\SQLAuthority\Audit\Network\*', NULL, NULL);

Msg 33224, Level 16, State 4, Line 20
The specified pattern did not return any files or does not represent a valid file share. Verify the pattern parameter and rerun the command. 

Since this network drive is unavailable, we need to move the auditing to a local drive. Here is how we can do the same.

-- Step 7 - Change the Audit location to a local drive
ALTER SERVER AUDIT [NetworkAudit] WITH (STATE=OFF);
ALTER SERVER AUDIT [NetworkAudit] TO FILE(FILEPATH='D:\Audit');
ALTER SERVER AUDIT [NetworkAudit] WITH (STATE=ON);

In this example I am moving the drive to a local D:\ drive. At many times it is quite possible we might not have access or permission to write to a local folder on the server. If that is the case, we are likely to get the following error when we try to enable the STATE=ON.

Msg 33222, Level 16, State 1, Line 29
Audit 'NetworkAudit' failed to start. For more information, see the SQL Server error log. You can also query sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_XE_LOG'.

If we query the dm_os_ring_buffers, it will mention the file creation error. This is because we do not have enough permissions to write on the folder. Go ahead and change the permissions appropriately and rerun the command.

<Record id = “443” type =”RING_BUFFER_XE_LOG” time =”4084184″><XE_LogRecord message=”file: file create or open failed (last error: 5)”></XE_LogRecord></Record>

There is yet another error that can come when configuring the location to a local server. If the folder specified in the ALTER SERVER AUDIT is not, accessible or not available, SQL Server will raise an error as:

Msg 33072, Level 16, State 1, Line 2
The audit log file path is invalid.

So make sure the folder specified exists before issuing the command. Let us check the metadata to see if the changes have taken effect:

-- Step 8 - Access the Audit logs
SELECT * FROM sys.dm_server_audit_status
SELECT * FROM sys.fn_get_audit_file('D:\Audit\*', NULL, NULL);

The output would look like, take a note of file size to be non-zero:

This concludes the logical steps one needs to do to setup and change the audit file location inside SQL Server. As a final step, here are the steps to clean up the objects created in this blog post.

-- Cleanup
ALTER SERVER AUDIT SPECIFICATION [complianceServerSpec] WITH (STATE=OFF);
ALTER SERVER AUDIT [NetworkAudit] WITH (STATE=OFF);
DROP SERVER AUDIT SPECIFICATION [complianceServerSpec];
DROP SERVER AUDIT [NetworkAudit];

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

About these ads

SQL SERVER – User Defined Audit with SQL Server

Talk to any financial institution or bank they will be super paranoid when it comes to security and auditing policies applied to their organizations. In a recent session to one of our customers, I had to enter their premises and I had to go through a number of screening. From the entrance, car parking, reception, elevators and everywhere there was someone asking us for some information. The more I think about it, the more frustrated I become. After returning home, I thought through the complete incident with my family. I was pleasantly surprised the calmness at which they were talking to me about process.

Wow, it is a great way to learn patience and why this is important. Organizations have a reason, they need data secure, they want to have procedures so that there is no data loss, no theft of data and many more. In this competitive world this is super critical. Hence these procedures are important, critical for survival. And hence this blog is inspired to the Auditing capability with SQL Server 2012 and what I found interesting.

Creating our Audit

Creating an audit is as simple as going through a series of wizard with some basic data. Go to SQL Server Management Studio -> Security -> Audits and create a new audit. Go ahead and add the FilePath, in our example I have added it as “C:\Audit”.

The TSQL equivalent for the same is:

CREATE SERVER AUDIT [Audit-20141115-213944]
TO FILE
(  FILEPATH = N'C:\Audit'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF)
WITH
(  QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE)
GO

After creating the same, we will make sure this Audit is enabled. This ensures where our Auditing data will get into. In our example it is configured to a File as shown above.

Once we enable the Audit node, the next step is to add the Audit operation. Here we are going to add the “User Defined Audit Group”

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20141115-214433]
FOR SERVER AUDIT [Audit-20141115-213944]
ADD (USER_DEFINED_AUDIT_GROUP)
GO

Creating User defined Audit entries

This is a capability that was introduced with SQL Server 2012 and I personally feel this is quite powerful for a couple of reasons. Now, apart from standard auditing capability now applications can raise specific auditing into the audit log so that we can track or audit logically from an applications point of view.

The simplest way to raise an audit record from an application is using the stored procedure sp_audit_write command. A typical command looks like:

EXEC sys.sp_audit_write 1, 0, N'This is an audit from Pinal'

This command will now put an entry into my audit, we just defined in the previous. To view the entry, select the “View Audit Log” option from the Audit we just created. The Log viewer looks like below:

In the example above, we can see the details. Please note the Audit “Succeeded as False” because we sent the second parameter to sys.sp_audit_write as 0 hence it is False. If you want to enter it as True, pass the value as 0. Also the statement that got us this information is available as part of “Statements” line item.

If you want to view all the entries into our audit file, we can use the sys.fn_get_audit_file function.

SELECT * FROM sys.fn_get_audit_file('c:\Audit\*', NULL, NULL)

I hope you got a flavor of how custom user defined audits can be defined. This is a great way to use the auditing feature from your application’s point of view. Do let me know if you found this useful and if you are planning to use the same in your environments.

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

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)

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)