Welcome back to SQL University.For previous SQL University posts please visit http://thomaslarock.com/category/sql-university/.

This week we will be discussing the topic Securing and Auditing Servers. This is a timely topic for me since I recently did a presentation at SQL Saturday #39 on the topic of SQL Server 2008 Audit. So, this topic happens to be something I have spent some time on very recently. Instead of giving you an overload of information on the nuts and bolts of hardening your instance (besides, that’s why  keep KBK (blog | twitter) around), I am going to walk you through some general levels of awareness and leave it as an exercise for you to drill down further.

Let’s get started!

tempdb

The first level is, of course, awareness. You must be aware that your current SQL configuration may not be perfect. More importantly, you must be aware that certain viruses will attack unsecure servers. All too often I see people get complacent about their security with regards to patching and the result is often a disaster. You simply must be aware that these risks exist, that they are real, and that they could happen to you.

Once you start to understand that these risks exist the next step of awareness you need is that auditing your SQL instance is a great way to ensure they are kept secure. Yessir, that’s right. Audit = good thing. Let me explain further…

msdb

See, auditing your SQL Server is merely one piece of the entire cycle of continuous improvement.

It is as simple as the diagram spells it out for you. First, document what your standard happens to be. Then, have someone independent come in and collect data on your server instances and compare to your standards. If any changes are necessary you plan for them to happen, implement them, and then repeat the process again.

model

Use this concept when it comes to securing a SQL instance. Start with something obvious, like removal of the BUILTIN\ADMINISTRATORS login from your instances. Document your standard (the login should not allowed), collect data, find a deviation, plan and implement the change, then go back and do it all over again at a planned date in the future.

Take a good, hard look at your instances and start asking yourself questions about how you can make things better. Stop thinking about an audit as a painful experience and start thinking of it as a way to improve the quality of your environment. Make a list of the things you want to change, get them changed, and put together a series of control reports to verify that the changes are intact.

master

At this level you will put tools into place to help you secure and audit your environment. You can use SQL Server 2008 Policy-Based Management to check for, and even prevent, deviations in your standard configurations. You can also use SQL Server 2008 Audit to monitor your instances to ensure they have not been altered without your knowledge. If you get even more daring you could use the Audit Collection Services feature of Operations Manager. A combination of one or more of those three tools would give you everything you would need to help create and monitor a secure environment.

resourcedb

Here are some links you can use as a resource for more information.

For more information on SQL Server 2008 Audit, you can go here:

For more details on SQL Server Policy Based Management, you can go here: