SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Three A’s: Auditing

Authentication and Authorization, the first two of the three A’s of security, control who gets access to what. However, at some point we’ll need to do who is accessing that what and when it happened. That brings us to the third A: Auditing.

Auditing isn’t strictly required on all systems. Going back to our example from yesterday of the almost 21 year-old, the doorman at the club likely isn’t keeping track of the names of who entered and who was rejected. There is no auditing. In that case, it’s not important. What is important is controlling who gets into the club. In a lot of cases, it may be in the club’s best interest to not know who went in.

But within information technology, auditing is a big deal. It becomes a bigger and bigger deal as we want to know about when data is being viewed and when it’s being changed. SQL Server has received better and better auditing options as we’ve received newer versions. However, the very least bit of auditing that most folks recommend is to audit failed logins. You can set this up in the SQL Server properties like so:

This setting is read at start-up, so SQL Server will need to be restarted anything we make a change here. Once it’s on, whether we’re just auditing failed logins or we’re capturing both successful and failed logins, we’ll see entries in the SQL Server error log and the OS’ Application Event Log.

As I said, this is the minimal level. SQL Server gives us a bunch more. However, for the purposes of detailing the three A’s of security, this is all that needs to be said for now.


Databases – Infrastructure – Security

Brian Kelley is an author, columnist, and Microsoft SQL Server MVP focusing primarily on SQL Server security. He is a contributing author for How to Cheat at Securing SQL Server 2005 (Syngress), Professional SQL Server 2008 Administration (Wrox), and Introduction to SQL Server (Texas Publishing). Brian currently serves as an infrastructure and security architect. He has also served as a senior Microsoft SQL Server DBA, database architect, developer, and incident response team lead.

Comments

Leave a comment on the original post [truthsolutions.wordpress.com, opens in a new window]

Loading comments...