x

Database security access and auditing

Can someone guide me on how I could start to implement database security access and auditing policies throughout SQL Server 2008 R2, 2008, 2005? The sysadmins and the securityadmin list are unbelievable. Please help.
more ▼

asked Apr 16 '11 at 07:58 PM in Default

SqlAdv gravatar image

SqlAdv
274 9 11 13

@SQLAdventure - make sure to vote for the answers that help you by clicking the "Thumb Up" icon and when you have the answer you need click the tick/check icon to show other visitors which answer was the one that solved your problem.
Apr 18 '11 at 06:12 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Here's a list of steps I would take:

  1. Identify all members of sysadmin and securityadmin roles.
  2. Determine which of those belong to real people and which are service accounts.
  3. Take the list of real people to management and have them decide who really needs access.
  4. Write your own server-side trace (don't use C2) to monitor for logins and try to determine if and when any of the service accounts are used.
  5. For service accounts that have no activity over an extended period of time (one week, one month), take the list to management with a recommendation to disable but not delete. This way, if the account is actually used, but only rarely, it's a simple matter to reactivate.
  6. After a specified period of time agreed upon with management, remove the service accounts that were inactive.
  7. After the list of valid service accounts are identified, determine what applications they are tied to. Work with the teams responsible for managing those applications to determine what the actual permissions needed are. If some of these are 3rd party applications, prepare to deal with the fact that some companies will not support their application unless you give out the permissions they demand, even if they don't need it.
  8. Build queries to run daily and weekly to monitor changes to the environment, especially with regards to login creation/deletion and role membership changes.
  9. Now attack the operating system the same way you did SQL Server: determine who are members of the local Administrators and Power Users groups and take a similar methodology.
more ▼

answered Apr 18 '11 at 10:25 AM

K. Brian Kelley gravatar image

K. Brian Kelley
933 2

@K. Brian Kelley - Brilliant, thanks for popping in and add an answer on this one. I almost added you as a security guru in my answer but ended up just linking to Denny and his book.

@SqlAdventure - This is gold standard advice from a SQL Server security expert.
Apr 18 '11 at 12:58 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

What do you mean by "The sysadmins and the securityadmin list are unbelievable"?

I would recommend that if your question is so broad that you would be best off getting a test server set up and then purchasing a couple of books Denny Cherry (@MrDenny) has a book for 2008 that I would recommend - http://www.amazon.co.uk/Securing-SQL-Server-Protecting-Attackers/dp/1597496251/ref=sr_1_1?ie=UTF8&qid=1303034839&sr=8-1 and there are similar for the previous versions.
more ▼

answered Apr 17 '11 at 02:55 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

(comments are locked)
10|1200 characters needed characters left

I take that to mean there are accounts showing up under sysadmins and securityadmin fixed server roles that you are asking yourself: Why do they have that access?

I've been there and still deal with that one.

The top two choices you have for auditing that are quick and easy to implement are default trace (which should be on by default) and C2 Auditing. The default trace captures mostly changes to configuration options. Then C2 auditing will capture everything done on the instance of SQL Server. Caution, when you enable C2 auditing if your instance is very active you will need to manage the storage space where the trace files are created, because it can get out of hand very quickly.

The first step I would take though is run these two commands:

EXEC sp_helpsrvrolemember 'sysadmin'
EXEC sp_helpsrvrolemember 'securityadmin'

Take those list of accounts to your upper management, or whoever your supervisor might be, and ask them who in this list ACTUALLY needs this permission. That is a legitmate concern and should be addressed by management.

more ▼

answered Apr 17 '11 at 07:43 PM

Shawn_Melton gravatar image

Shawn_Melton
5.3k 17 21 29

Thanks so much for your response. Would you disable or change the permission for these 2 accounts if they aren't being used by SQL Agent service? NT AUTHORITY\SYSTEM (sysadmin), NT SERVICE\MSSQLSERVER (sysadmin)? Thanks again for your kindness.
Apr 18 '11 at 04:33 AM SqlAdv
I can't answer that for you. You would need to determine if the accounts are being used for any applications. The "NT AUTHORITY\SYSTEM" is the local system account for the operating system. In my environment we actually use that account. The "NT SERVICEMSSQLSERVER" reads like it might be a service account for your instance of SQL Server. If so it requires sysadmin rights on the instance. You can view the service account being used for your SQL services in SQL Server Configuration Manager.
Apr 18 '11 at 04:44 AM Shawn_Melton
None of these accounts are being used by service account.
Apr 18 '11 at 05:02 AM SqlAdv
If you know they are not being used, then you can probably drop them. However on the safe side I would just disable the logins for a few days/weeks/months to ensure they are not. They cannot hurt anything if they are disabled.
Apr 18 '11 at 06:21 AM Shawn_Melton
NT Authority\System is used by the SQL Server VSS Writer at the minimum in 2005/2008. It's used by Full Text Search (and the service that goes with that) in 2000.
Apr 18 '11 at 10:20 AM K. Brian Kelley
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x158

asked: Apr 16 '11 at 07:58 PM

Seen: 1156 times

Last Updated: Apr 16 '11 at 07:58 PM