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 – Idera SQL XEvent Profiler – a Free Tool for XEvent Monitoring

Download Extended Event Profile FREE

We often resist the change, we do not like change and we keep on using the same old technology which we have been using it to resolve the new challenges which we face in our daily life. The Extended Event is such a technology as well. It was added to SQL Server in SQL 2008 and expanded upon greatly in SQL 2012 is a powerful mechanism for monitoring activity in SQL Server. It has huge implications for tools like SQL Diagnostic Manager, which switched over to XEvent technology for wait monitoring, and for users of all stripes who need to see what is happening on their servers.

Though the product was well thought of it was yet not quite ready for average users due to many reasons. It offered a reduced set of events and could only be manipulated through TSQL. In SQL 2012 the number of events went up dramatically and Microsoft released an XEvent GUI as part of Management Studio. However, lots of users think that the XEvent tool is a bit overkill for a lot of purposes. Therefore, a lot of users continue to use SQL Server Profiler, which uses trace technology. It’s easy to see why – it’s familiar, it’s easy to use, and it takes only a few clicks to get a diagnostic session started.

Team Idera has put together a new free tool called SQL XE Event Profiler, which offers the simplicity of Profiler but uses the powerful and lightweight XEvent technology on the back end. The idea was to provide the simple 2 click behavior that users were looking for, along with the familiar SQL Profiler templates that users are already accustomed to using. The application has the familiar start, stop, and pause buttons, the ability to turn auto-scroll on and off, and the ability to clear the grid. While we were at it, we added some nice grouping functionality to the event grid, along with text searching and export.

I am downloading this tool this weekend and going to play with this tool and see how I can use it on my various servers. I encourage all of you to do the same.  Here is the link to the original blog.

Download Extended Event Profile FREE

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

SQL SERVER – Retrieve and Explore Database Backup without Restoring Database – Idera virtual database

I recently downloaded Idera’s SQL virtual database, and tested it. There are a few things about this tool which caught my attention.

My Scenario

It is quite common in real life that sometimes observing or retrieving older data is necessary; however, it had changed as time passed by. The full database backup was 40 GB in size, and, to restore it on our production server, it usually takes around 16 to 22 minutes, depending on the load server that is usually present. This range in time varies from one server to another as per the configuration of the computer. Some other issues we used to have are the following:

  1. When we try to restore a large 40-GB database, we needed at least that much space on our production server.
  2. Once in a while, we even had to make changes in the restored database, and use the said changed and restored database for our purpose, making it more time-consuming.

My Solution

I have heard a lot about the Idera’s SQL virtual database tool.. Well, right after we started to test this tool, we found out that it really delivers what it promises. Using this software was very easy and we were able to restore our database from backup in less than 2 minutes, sparing us from the usual longer time of 16–22 minutes. The needful was finished in a total of 10 minutes. Another interesting observation is that there is no need to have an additional space for restoring the database. For complete database restoration, the single additional MB on the drive is not required anymore. We can use the database in the same way as our regular database, and there is no need for any additional configuration and setup.

Let us look at the most relevant points of this product based on my initial experience:

  • Quick restoration of the database backup
  • No additional space required for database restoration
  • virtual database has no physical .MDF or .LDF
  • The database which is restored is, in fact, the backup file converted in the virtual database. DDL and DML queries can be executed against this virtually restored database.
  • Regular backup operation can be implemented against virtual database, creating a physical .bak file that can be used for future use.
  • There was no observed degradation in performance on the original database as well the restored virtual database.
  • Additional T-SQL queries can be let off on the virtual database.

Well, this summarizes my quick review. And, as I was saying, I am very impressed with the product and I plan to explore it more. There are many features that I have noticed in this tool, which I think can be very useful if properly understood.

I had taken a few screenshots using my demo database afterwards. Let us see what other things this tool can do besides the mentioned activities. I am surprised with its performance so I want to know how exactly this feature works, specifically in the matter of why it does not create any additional files and yet, it still allows update on the virtually restored database. I guess I will have to send an e-mail to the developers of Idera and try to figure this out from them.

I think this tool is very useful, and it delivers a high level of performance way more than what I expected. Soon, I will write a review for additional uses of SQL virtual database.. If you are using SQL virtual database in your production environment, I am eager to learn more about it and your experience while using it.

The ‘Virtual’ Part of virtual database

When I set out to test this software, I thought virtual database had something to do with Hyper-V or visualization. In fact, the virtual database is a kind of database which shows up in your SQL Server Management Studio without actually restoring or even creating it. This tool creates a database in SSMS from the backup of the same database. The backup, however, works virtually the same way as original database.

Potential Usage of virtual database:

As soon as I described this tool to my teammate, I think his very first reaction was, “hey, if we have this then there is no need for log shipping.” I find his comment very interesting as log shipping is something where logs are moved to another server. In fact, there are no updates on the database from log; I would rather compare it with Snapshot Replication. In fact, whatever we use, snapshot replicated database can be similarly used and configured with virtual database. I totally believe that we can use it for reporting purpose. In fact, after this database was configured, I think the uses of this tool are unlimited. I will have to spend some more time studying it and will get back to you.

Click on images to see larger images.

virtual database Console
Harddrive Space before virtual database Setup
Attach Full Backup Screen
Backup on Harddrive
Attach Full Backup Screen with Settings
virtual database Setup – less than 60 sec
virtual database Setup – Online
Harddrive Space after virtual database Setup
Point in Time Recovery Option – Timeline View
virtual database Summary
No Performance Difference between Regular DB vs Virtual DB

Please note that all SQL Server MVP gets free license of this software.

Reference: Pinal Dave (http://blog.SQLAuthority.com), Idera (virtual database)