Administrating HR Databases 1

[TheVillageIdiot27]

We have an HR database (for third party application) which we need to keep as secure as possible from everyone including members of our IT team (a direction from our HR department).

There is a thin web client which most users access though the intranet which has one SQL server login and a thick client which the HR team use which uses windows authentication.

On top of this obviously we have SA account which is currently known to far to many people within IT, plus domain\administrators have a role.

I plan to remove the "builtin\administrators" and change the sql logins so they are only known to one or two people and a sealed envelope.

The main problem I can envisage with this is that when the database is backed up, does anyone know if in this scenario SQL server would require authentication in order to restore a back up?

I am wondering how other organisations deal with this - maybe they have more trust in their IT team...

 

[ptheriault]

Most companies have a DBA or team of DBAs who manage all of their databases. The 'sa' password should never be given out, only the DBA should know that password. I would change it if it is public knowledge. no application should ever use it. And DBA's should have their own login with sysadmin rights. Most DBA's don't even use the 'sa' password unless they absolutly need it. You security for the application looks ok as long. Definetly remove built-in admins.

These changes will not impact your backup strategy.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[TheVillageIdiot27]

Thanks Paul

When I say the sa password is known to far to many people - that is - far to many people for the comfort of HR for this particular database.

It is only known to IT, and because of the builtin\admins role noone is using it. HR want the administation rights resticted to one person so I will remove builtin\admins change the sa password - We'll probably create another instance for this.

I am checking that after doing this anyone who is a member of the domain admins won't be able restore a backup to another database server and be able to have a look at the salary table which is what this is trying to avoid. Are you saying they can't?

 

[ptheriault]

You need to write your backups to a share that only the sql server agent account, tape backup account and your dba's can access. Of course you still have the problem of who has access to the tapes.

I always remove built-in\admin from all my sql servers. You want to control the number of people who can create, drop and restore databases. Those are tasks best left to the dba.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[TheVillageIdiot27]

Many thanks I think thats given us the idea of how we can set it up.