Sql-server – Windows Storage Server, SQL Server, regular files — means to protect from insider threats

access-controlauditSecuritysql serversql-server-2008

This is my first post here, and I am by no means qualified as a Windows Server or Storage Server 2008 or SQL Server 2008 administrator. I am mostly a Unix guy, however tasked with a job to investigate if our firm (a small software startup), IT infrastructure, which includes Windows Server 2008, Windows Storage Server, and SQL Server used exclusively by Finance, HR, Sales, Legal and Administration depts. Devs are all on Unix.

What I need to get some ideas around is, how to protect the Windows based infrastructure from insider attacks. More precisely, against rogue system-administrators. Our current system administration role is played by a temp. In theory, having access to all (or most) passwords/login credentials, he can do whatever he likes.

Is there then some technology (s.a. audit logs, tamper-proof backups etc.), that can be setup (like onetime), whose credentials are available only with an extremely trusted person (maybe CEO himself), and all other system admin tasks can be performed by this temp chap.

In worst case, if the admin removes, deliberately corrupts/modifies certain files, or alters database content, such acts can be:

  1. Tracked down
  2. Proven
  3. Rectified

I understand that this is probably not a pure DBA question, and pardon me for willfully posting this, but I didn't find a more appropriate SE forum, but found quite a few interesting Windows Server infrastructure discussions here.

Best Answer

What you can do is restrict access to the SQL Server and the Windows server that SQL is on. There are server level roles, with sysadmin giving you the highest privileges. Review Server-Level Roles to see what you will need to give your temp. The temp should never be in the sysadmin server level role, based on what I read from your requirements.

Then there are database level roles. This is set at each database level for each DB. See Database-Level Roles to read more. Your temp may be in the db_backupoperator, or the db_datareader role for each DB. This will prevent him from deleting/modifying the DBs.

Encrypt your confidential data. You can also use passwords for your backups, but this feature is not meant for security, rather is helps maintain related backup sets.

On the Windows server part, you can further restrict access. Querying the SQL Server can be done from a laptop with SSMS installed on it, thereby not having to give your temp much rights on the Windows server.

To track changes on the DB you will need auditing. There is a plethora of information in the Auditing in SQL Server 2008 Microsoft technical article.

Remember, too much auditing comes at the cost of performance. With SQL Server 2008, there are new and much granular auditing controls, like CHANGETABLE and policy-based management. These can help with your Tracked down, Proven, and Rectified requirements.

Above is just a quick summary of the possibilities.

Related Question