Encrypt a column in SQL 2000 using code or SQL script

Question

I am using SQL 2000. The string column "Password" is in the "Users" table. It has about 3 thousand lines. My requirement is to encrypt all the values ​​in the Password column. In addition, I should be able to decrypt these encrypted password fields whenever necessary.

I know that starting with SQL 2005, there are built-in functionalities for these requirements. But I care about SQL 2000.

Please suggest if there is any way to achieve my requirement using VB code or SQL script . NOT with any third-party tools. I searched for many places, but did not succeed.

Thanks.

Answer 1

You can use the undocumented functions PWDENCRYPT and PWDCOMPARE available in SQL Server 2000 -

 CREATE TABLE #USER ( LOGIN_ID varchar(20), UserPassword nvarchar(256) ) -- Encrypt & Insert Password -- Note: You will have to write UPDATE on existing records INSERT #USER VALUES ( 'my_loginid', PWDENCRYPT('MyPassword1')) DECLARE @InputPassword VARCHAR(100) DECLARE @IsValid INT = 0 -- Test for Correct Password SET @InputPassword = 'MyPassword1' SET @IsValid = (SELECT PWDCOMPARE(@InputPassword, UserPassword, 0) FROM #USER WHERE LOGIN_ID = 'my_loginid') SELECT @IsValid AS 'Test1'; -- Test for Wrong Password SET @InputPassword = 'WrongPassword' SET @IsValid = (SELECT PWDCOMPARE(@InputPassword, UserPassword, 0) FROM #USER WHERE LOGIN_ID = 'my_loginid') SELECT @IsValid AS 'Test2' DROP TABLE #USER 

Links Links -

• PWDENCRYPT
• Pwdcompare
• SQL Server Undocumented Password Encryption Features

Answer 2

Passwords are usually stored with a type 1 hash file (for example, SHA1), that is, they are encrypted and never need to be decrypted. When a user enters a password, your code will hash it and check if the hashed value matches the hashed value in the database.

However, it looks like you have a requirement to also decrypt the password. There are several asymmetric algorithms for this (RSA, PGP, etc.), where you will have a pair of private and public keys. The private key is kept secret, while the public key can be used by others to be able to encrypt their own information before sending it to you. This seems like a bust, because only your VB6 code should encrypt the data, not third parties. Therefore, you can simply use a symmetric algorithm (such as Blowfish or TripleDES), in which you use the same passphrase (instead of a key pair) to encrypt and decrypt data. This passphrase can be stored in a configuration file on the server. Remember to protect it from unauthorized users.

Have you seen this article? It uses TripleDES with a passphrase that sounds exactly the way you need it. http://msdn.microsoft.com/en-us/library/ms172831(v=vs.80).aspx

Answer 3

It is currently considered a mistake to simply encrypt passwords yourself. Often an arbitrary line (called a salt) is added to each password, and then encryption is applied. Basically, it doesn't matter in what order you add the salt and encrypt it. All these combinations are equal in coding strength:

HASH (Pass & Salt) OR HASH (HASH (Pass)+Salt)) OR HASH (HASH (Pass) + HASH (Salt))

Salt is stored in a separate table as plain text. Another thing you can do is to encrypt the same value several times in a row. A small delay for one user will not be noticeable, but it will increase the effort required to brute force the password.

It is also recommended that tables be named so that table names cannot be guessed. This makes blind attacks more difficult when they cannot immediately get the password table.

Regarding the method of encrypting the string.

SQL Server 2000 There are no built-in symmetric functions. There are 2 asymmetric built-in functions: BINARY_CHECKSUM and CHECKSUM .

VB VB provides you with already implemented algorithms as well as tools to carry out your own implementation. The article mentioned by @SuperFunkyMonkey contains links to Secure Space for encryption . Another symmetric algorithm (which you can decode) is Rijndael.

Answer 4

Let me start by indicating that you mentioned that this is a password. Proper password protection is a complex subject, but with a minimum minimum, I suggest salting and draining them. SQL Server does include a hash function (pwdencrypt was in SQL Server 2000, but was not documented until later). Newer versions include Hashbytes, which have more options), but this hash function is not the safest, and you should look at other options.

Using a hash instead of encryption violates one of your stated requirements in order to be able to decrypt these encrypted fields, but with passwords, as a rule, it is better not to decrypt them. (You can compare the hashed value with the password entered by the user, also by hashing the password, you simply cannot decrypt it easily to restore the plain text version.)

If you really want to encrypt them, look at the System.Security.Cryptography namespace and the Simple3Des class, in particular for VB. There is documentation and a step-by-step guide on encrypting strings in a program here .