Encryption/Decryption in SQL 2000

[Shaymoh]

Hi,

I wonder if anyone can help me.

I am a software developer writing code, (VB6 front end, SQL 2000 back end). for one of our clients, (a bank). Security is a major consideration.

I needed to import large amounts of data from a file, and post this data to customer accounts. Using the Front End was way too slow, so, I decided to use the "xp_cmdshell" extended SP.

My data import SP does, among other things, the following:

1. enable "xp_cmdshell" EXEC permission to a small group of users, using the "sp_set_sqlagent_properties" SP;

2. setup a proxy account using the "xp_sqlagent_proxy_account" SP;

3. import the data using the "xp_cmdshell" SP.

4. disable "xp_cmdshell" EXEC permission for non dba's, (this automatically closes the proxy account).

My code, which works OK, looks something like the following:

CREATE PROCEDURE Test
AS

DECLARE

@Domain VARCHAR(10), @NTUser VARCHAR(120), @Password VARCHAR(10)
.....
.....

SELECT @Domain = Value FROM TestTable (NoLock)

WHERE ModuleName = 'DomainName'

SELECT @NTUser = Value FROM TestTable (NoLock)

WHERE ModuleName = 'UserName'

SELECT @Password = Value FROM TestTable (NoLock)

WHERE ModuleName = 'Password'

--Give non dba users CmdEXEC permission on xp_cmdshell
EXEC msdb..sp_set_sqlagent_properties @sysadmin_only = 0

--Setup the Proxy Account
EXEC master..xp_sqlagent_proxy_account N'SET'
, N'@Domain'
, N'@NTUser'
, N'@Password'

--Run the xp_cmdshell code
EXEC master..xp_cmdshell .....

--Remove CmdEXEC permission on xp_cmdshell for non dba users
EXEC msdb..sp_set_sqlagent_properties @sysadmin_only = 1

.....
.....

My question is this. Is there any way of encrypting just the Password value in the TestTable above, then decrypting it within the SP?

Encrypting the entire Value column in TestTable is not an option since lots of other SPs use this table.

Adding my own Extended SPs to the database is also not an option.

I suppose I could write my own encryption into the calling SP, using Caesar Shift or ROT13 or something, but I was hoping for a more elegant/builtIn solution.

Can anyone PLEASE, PLEASE, help me.

Thanks in advance,

sc.

 

[damber]

Unfortunately, current SQL Server versions don't have a way to encrypt data content (AFAIK.. but you can ask again in the SQL Server Forum).

Here's a useful link with some commercial encryption tools:

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22

Though, as you state, you may not be able to use these..?

Also, why can't you use GRANT or DENY to restrict access to the table? Or is this a shared/hosted SQL Server ?

You may be able to write your own, (if you want it for *free*) and use OSS algorithms to build your own encryption tool - but this is not really free - it will still take you some time to develop it.

But there is, as far as I know, no built in method.. check out this link:

http://builder.com.com/5100-6388-5083541.html

Try the SQL Server Forum for more detailed info, and possible workarounds.

A smile is worth a thousand kind words. So smile, it's easy!

 

[Shaymoh]

Thanks damber,

You've confirmed what I've heard in other forums, there's no way of encrypting data items in SQL server.
I can't restrict access to the TestTable as it needs to be available to al the users in the bank.
The links you sent me were very useful though.
I think my best solution might be to write an encrypted SP that takes an input number, rotates and shifts it a few times, and outputs the result. I don't think this needs to be very sophisticated decryption since my only concern is to hide the Password value stored in the TestTable.

Thanks again for your reply,

SC.

 

[damber]

No problem - it really depends on who you are protecting it from and what it is for - in most cases simple obfuscation is fine to imply encryption and deter chancers/meddlers - but be warned that it is soooo easy to brute force simple methods like this, that it is of no use to protect against determined users.

Good Luck in whatever you choose.


A smile is worth a thousand kind words. So smile, it's easy!