Database security

How can sql server ensure that the data that is P&C cannot be accessed even by the dba? Is there a way to encrypt these data without comprising on the flexibility of sql?Man, that's a big question. Here's a good place to start reading:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_93u6.asp|||It should be possible to set up a role for your DBA that allows them to do basic maintenance, such as backups and restores, but does not allow them to access tables or procedures. Can't say I've done this, though.

If your DBA logs in as sa or dbo, then there is no way to hide anything that you don't encrypt yourself. DBO is database god (small g), and SA is server God (big g).

blindman|||hmm... in that case anybody that gotten hold of any of my full backup files too can just restore it and definately he/she will have sa authority and be able to view everything....|||Where are you leaving your backups? Shouldn't they enjoy the same security (file system, locked cabinet for tapes, etc) as your live database files? It's like leaving photocopies of you credit card statements around.

Did you read the article on encryption? It does provide a method of protecting your data from direct viewing, but it needs to be set up that way initially.|||bpdWork,

I don't see where in the article it talks about protecting your data from viewing within the database, just from viewing intercepted transmissions across the network. Have you done this?

debcwong,

The BACKUP command allows to supply a password that is then required in order to restore the file, although the sqlmaint Utility (used by the Maintenance Plan wizard) does not.

blindman|||No, I haven't. In fact, you can probably tell just how lazy I am by the fact that I didn't read the whole article.

Though, the second sentence says: "Encryption ensures that data remains secure by keeping the information hidden from everyone, even if the encrypted data is viewed directly," I cannot find any way of actually doing this.

I am able to encrypt Stored rocedures and Views so that their definitions are encrypted, but that doesn't help much.

In the past, I have always written an encryption routine that things such as Credit Card numbers were passed through on their way into and out of the database. .NET has an encryption class that makes that approach a lot easier, and more secure.

Sorry for being misleading there. I guess I'm the naked, following blindman around... ;-)|||..that makes me feel a little nervous... :rolleyes:|||yeah, it scares the heck out of me.|||err guys..or gals...there's always the icq or msn for those kind of thing i believe ;)

neway sometimes it's not perfectly true in the sense that most of the software developed might be for customers and usually customers will DEMAND for the rights to access to everything and also to restore it.
That's was the whole reason y I asked the question in the first place =)

Neway am thinking of the payroll system that is currrently under development stage... I'm sure you might be a bit interested to know the pay your superior's getting ...|||Sorry. Just a little crazy from the workload.

I do unserstand what you are trying to accomplish. If you use your front-end application (or middleware) to perform the encryption, that would solve your data visibility problem. If you ultimately find a way for SQL to do it for you, I would love to know about it.

Also, as for blindman's idea to password protect the backups, you could let the end-users control the backup password protection. You can even impliment code in your front end to perform the backup and restores.|||thanks to both of you.

will try that bit on the backup thingy at work tomorrow|||As far as sensitive data is concerned (such as salaries), tell your people that the same confidentiality rules apply to DBAs as to priests.

...except that the celibacy is implied rather than enforced...|||Hey guys I have been looking into this lately myself for a db that needs to be secured. I ran across this plugin, but I haven't actually tested it.

http://www.appsecinc.com/products/dbencrypt/mssql/

When I looked over the info on this product it does exactly what you are looking for. You select the users that should get access to the information, and you can set it to encrypt only a specific column.

Do you guys know of a good way to send data from one remote computer to another? I need to send credit info from an online server to the companies internal server in the most secure way possible. These two servers will have a vpn link and the online db will have an SSL cert attached to it as well. Any thoughts?

Thanks for you help...|||Thanks for the link, 6SC.

blindman|||We implemented this slightly differently:

- Put your payroll system on a separate server
- Install SQLLiteSpeed with Encryption
- Create scheduled jobs to run custom backup
- Create alerts on vital system counters
- Setup email notification
- Create notification job
- Remove Builtin Admins and Domain/Enterprise admins from sysadmin server role
- Ask your accounting boss to change SA password, because even you should not have routine access to this server
- Make sure your accounting boss shares SA password with your CIO.|||thanks rdjabarov


Probably I'll propose to have it in another database but without additional software such as sqllitespeed. Will try to implement the database backup. Probably dts can help in schedulling it since the maintainence plan doesn't have this password feature.|||You can use T-SQL fired by a SQL Server Agent job to perform your backups, and specify passwords. See the following link on MSDN (yes, I've read this one and use it quite a bit!):

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp

Using T-SQL will allow you to be more dynamic in your code. Also, you won't be going out of process (running DTSRUN.exe) which can cause it's own problems with production reliability.

Database Protection

Dear All,
I run a win2k domain. We will be bringing a bespoke SQL database system on
board in a few weeks. I want to ensure the integratity of this database by
putting some security measures in place. I have concerns that individuals
may try to take the database to a competitor by copying it on to CD or
sending it through email. I would like to put something in place that will
make the database useless if it goes outside my domain. Has anyone got any
ideas? Encryption?
Many Thanks,"John Barwell" <johnbarwell@.msmdirect.co.uk> wrote in message
news:dLYzc.15803$NK4.2608717@.stones.force9.net...
> I run a win2k domain. We will be bringing a bespoke SQL database system on
> board in a few weeks. I want to ensure the integratity of this database by
> putting some security measures in place. I have concerns that individuals
> may try to take the database to a competitor by copying it on to CD or
> sending it through email. I would like to put something in place that will
> make the database useless if it goes outside my domain. Has anyone got any
> ideas? Encryption?
>
The best way to prevent theft of your database is to ensure proper security
is in place; both at the physical server layer, SQL Server and database
access layer. Further, implement security protection to the backup's that
may be created. I know of nothing that would render the db useless outside
of a domain...
Steve|||John,
Encryption is possible (at least according to the adds) but I have not done
it. See:
http://www.netlib.com/
http://www.appsecinc.com/products/dbencrypt/mssql/
FWIW,
Russell Fields
"John Barwell" <johnbarwell@.msmdirect.co.uk> wrote in message
news:dLYzc.15803$NK4.2608717@.stones.force9.net...
> Dear All,
> I run a win2k domain. We will be bringing a bespoke SQL database system on
> board in a few weeks. I want to ensure the integratity of this database by
> putting some security measures in place. I have concerns that individuals
> may try to take the database to a competitor by copying it on to CD or
> sending it through email. I would like to put something in place that will
> make the database useless if it goes outside my domain. Has anyone got any
> ideas? Encryption?
> Many Thanks,
>|||Activecrypt Software provides encryption solution for MSSQL Server XP_CRYPT
(www.xpcrypt.com)
and protection for T-SQL code: stored procedures, user defined functions,
triggers. The code encrypted with SQL Shield (www.sql-shield.com) cannot be
decrypted with existing decryptors like dOMNAR's SQL Server SysComments
Decryptor.|||On 6/16/04 7:25 AM, in article dLYzc.15803$NK4.2608717@.stones.force9.net,
"John Barwell" <johnbarwell@.msmdirect.co.uk> wrote:

> I have concerns that individuals
> may try to take the database to a competitor by copying it on to CD or
> sending it through email.
It is important to first establish a policy that defines what data you wish
to protect from whom. In military terminology this would be the security
classifications of objects and the security clearance of the subjects. The
first step is to establish access control lists (ACLs) that obey the
security principle of least privileges when realizing your security policy.
Users should be granted privileges only to data which they need to complete
their duty. After this, you can audit access to individual records and
insure that they are accessed only on a need-to-know basis by those who do
have permissions to access them.
Once you have done the fundamentals, you can add an additional layer of
security by encrypting data both in transit (by using SSL connections) and
at rest. There are several third-party software and appliance products that
implement column-level encryption and key-management. These features are not
implemented in SQL Server 2000, but are on the way in SQL Server 2005.
I hope that helps. Please let me know if you have any other questions.
-Mark Shlimovich|||>These features are not
> implemented in SQL Server 2000, but are on the way in SQL Server 2005.
This seems to be good news. We have rejected MSSQL precisely because of its
weak security model. We spent one whole year researching and testing it.
Are you saying that the key management will allow one to lock out DBAs and
restrict access of specific tables or objects to specific keys that are
constructed outside of the DBA role or so-called sysadmin role? In other
words, if the db is detached and attached on another MS box could the
sysadmin or some phantom god-like role gain access to that db? This is the
mutli-million dollar question. Do I understand the impending functionality
correct here or I am I way off the mark?
"Mark Shlimovich" <t-marks@.microsoft.com> wrote in message
news:BCF9688E.1EBB%t-marks@.microsoft.com...
> On 6/16/04 7:25 AM, in article dLYzc.15803$NK4.2608717@.stones.force9.net,
> "John Barwell" <johnbarwell@.msmdirect.co.uk> wrote:
>
> It is important to first establish a policy that defines what data you
wish
> to protect from whom. In military terminology this would be the security
> classifications of objects and the security clearance of the subjects. The
> first step is to establish access control lists (ACLs) that obey the
> security principle of least privileges when realizing your security
policy.
> Users should be granted privileges only to data which they need to
complete
> their duty. After this, you can audit access to individual records and
> insure that they are accessed only on a need-to-know basis by those who do
> have permissions to access them.
> Once you have done the fundamentals, you can add an additional layer of
> security by encrypting data both in transit (by using SSL connections) and
> at rest. There are several third-party software and appliance products
that
> implement column-level encryption and key-management. These features are
not
> implemented in SQL Server 2000, but are on the way in SQL Server 2005.
> I hope that helps. Please let me know if you have any other questions.
> -Mark Shlimovich
>|||>if the db is detached and attached on another MS box could the
>sysadmin or some phantom god-like role gain access to that db?
Only if in addition to the database itself they had access to the keys. SQL
Server 2005 will provide a key management system.