Encryption/Decryption in SQL 2000

• Shay-256753

  SSC Enthusiast

  Points: 179

  More actions

  September 6, 2005 at 7:45 am

  #110757

  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 to import data from file

   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.

• Russell Shilling

  SSCarpal Tunnel

  Points: 4399

  More actions

  September 7, 2005 at 9:06 am

  #588115

  A better way than encrypting the passwords, and sending them back and forth across the wire, would be to hash the password in your VB program, then write a stored procedure such as usp_check_password that compares the hash from VB to the hashed password stored in the table.  This way, you never send the password in clear text at all. 

  I know there are lots of examples of MD5 and SHA hashing out there.  A little googling should turn up something.

  So long, and thanks for all the fish,

  Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

• Shay-256753

  SSC Enthusiast

  Points: 179

  More actions

  September 7, 2005 at 9:29 am

  #588131

  Hi Russell,

  Thanks for your reply.

  I've come to virtually the same conclusion myself from replies I got in other forums

  My current solution is to drop the whole encryption thing altogether, (though I'll still encrypt any calling SPs etc.), and force the user to input the password at the front end. This way, the password  doesn't have to be stored in the database at all.

  This is a simple solution, (not sure why I didn't think of it before), which might meet with some resistance from the bank, but it's one among many options I can put to them.

  Thanks a lot for your help on this. I'll investigate what you said about usp_check_password etc.. and post any results here.

  Thanks again,

  sc.