Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

PS> 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.

share|improve this question
1  
try security.stackexchange.com also. –  garik Jul 8 '11 at 6:05

2 Answers 2

up vote 2 down vote accepted

What you can do is restrict access to the SQL server and the Windows server that SQL is on. There are database server level roles with sysadmin giving you the highest privileges. Review this link 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. Click here 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 here. Remember, too much auditing comes at the cost of performance. With SQL 2008 there are new and much granular auditing controls, like CHANGETABLE and policy based management. These can help with your 1) Tracked down 2) Proven 3) Rectified requirements.

Above is just a quick summary of the possibilities. To read more, click here. I hope this information was helpful.

share|improve this answer
    
Thanks @Stan-the-DBA. Well, we are in a very funny situation, where the temp Admin, today has all privileges. None of us in Dev (or Unix side of the co.) were aware of this, until my CEO came up to me and told me so, sharing some of his concerns. The administrative/operations function, is entirely Windows. So, today, the temp knows literally every credential, that is not at the individual file level. The only things that are safe (relatively), are the Word-docs and Excel-sheets, that are individually password protected, used mostly by our Finance and HR. Needless to say, I was scared stiff. –  icarus74 Jul 11 '11 at 4:07
    
Not sure if this is very typical of startups, but we've grown in an extremely lean mode, cutting corners on such internal safeguards here and there. Will go thru the Microsoft Security link. Thanks again. –  icarus74 Jul 11 '11 at 4:08

SQL Server 2008 includes all sorts of auditing which can be used to monitor what is done in SQL Server. I've written about this in "Securing SQL Server" which may be of some use.

share|improve this answer
    
Thanks @mrdenny. Shall be going through that site. –  icarus74 Jul 11 '11 at 4:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.