Welcome back to SQL University.For previous SQL University posts please visit https://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
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:
- https://thomaslarock.com/2009/03/implementing-sql-2008-sql-server-audit/
- http://www.mssqltips.com/tip.asp?tip=1954
- http://www.mssqltips.com/tip.asp?tip=1959
- http://www.simple-talk.com/sql/database-administration/sql-server-audit-magic-without-a-wizard/
For more details on SQL Server Policy Based Management, you can go here:
Tom – Great post. Quick read but some great reminders to DBAs of all experience levels in there. What I mentally bolded and underlined when reading was, “Stop thinking about an audit as a painful experience and start thinking of it as a way to improve the quality of your environment.”
That is easy to forget and sometimes difficult to do but you are 100% right. If we care about our environments (if we are good DBAs, that is) we should actually embrace the audit as a double check and perhaps even a sign to management that we might be understaffed. I lost touch with this sentiment you express around the time of some recent audits. You just slapped me back on track. Thanks, coach.
Mike,
Glad i could help.