Securing a Payroll database on Public Server

Question

Securing a Payroll database on Public Server

homebrew01

SSC Guru

Points: 55548
More actions
September 24, 2007 at 7:52 am

#92763

We're considering moving our payroll database to a server that is used by several other databases (and their applications) and many users. I have a Windows Global group that has Admin rights. Of course, that will only contain 1 or 2 authorized users. The Payroll databases will have read/write access given only to the payroll application.
What type of loopholes might allow non-authorized users access to the Database ??

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736297 More actions · Answer 1

I'm guessing you mean Payroll app has R/W to the payroll db.

The admins can get in to the payroll db. No way around it. You could remove the admins from the Sysadmin roll, give them a few other rolls (serveradmin, backupoperator).

Other than that, set up a trace that goes to a flat file that the admins can't get to (it can be done) and trap their ids. Log what they access and have someone (not an admin) review them periodically to be sure they aren't accessing the payroll db.

Lowell SSC Guru Points: 323518 More actions · Answer 2

is a design change so that the fields are stored as encrypted data instead of plain text/numbers out of the question?

I recently had to do that, redid the table so that it stored mostly encrypted data instead; only the keys are un-encrypted for primary / foreign keys and dates, so that they could be indexed correctly... performance slowed down a little bit, but only two people ever hit that table; encrypt/decrypt is the same ones that came from one of the kewl DBA toolkit articles from SSC, and an application calls the procs with the proper keys.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

aureolin Hall of Fame Points: 3520 More actions · Answer 3

First, I'd recommend not doing this if you are worried about access by individuals that, while technically are "authorized", but actually have no business in the database. Given that you may be required to do this, perhaps the most secure method is to set up a second SQL Server instance on the machine. This gives you very good control over who is "SA" on that instance, and clearly divides the Payroll from the non-payroll database. That said, there's a performance penalty for having two instances on the same box - one that might be too high for a production system.

In any case, you will want to do a careful review of who is authorized as SA on your combined server, and you may find that several of those with SA rights really only need DBO on several individual databases.

Steve G.