ENCRYPT function not saving data as encrypted

Question

ENCRYPT function not saving data as encrypted

Steve DiDomenico

Ten Centuries

Points: 1078
More actions
April 15, 2004 at 1:36 pm

#108126

Hello,
We have two stored procedures that save password information using the encrypt function. When the pasword is saved it is stored as the original data with each character seperated by a space.
Example:
password: "12345"
Saved as: "1 2 3 4 5"
Here is the table and the two SP's. Can anyone provide a solution to this problem?
CREATE TABLE [dbo].[sys_user] (
[id] [int] IDENTITY (10000, 1) NOT NULL ,
[username] [varchar] (20) NOT NULL ,
[password] [varchar] (50) NOT NULL ,
[first_name] [varchar] (50) NOT NULL ,
[last_name] [varchar] (50) NOT NULL ,
[last_login_date] [datetime] NOT NULL ,
[is_active] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE sp_sys_user_insert
@username VARCHAR(20),
@password VARCHAR(10),
@first_name VARCHAR(50),
@last_name VARCHAR(50)
AS
INSERT INTO sys_user (
username,
password,
first_name,
last_name,
last_login_date,
is_active) VALUES (
@username,
ENCRYPT(@password), /* encrypt password */
@first_name,
@last_name,
GETDATE(), /* default last_login_date to today */
1);
GO
CREATE PROCEDURE sp_sys_user_update
@sys_user_id INT,
@username VARCHAR(20),
@password VARCHAR(10),
@first_name VARCHAR(50),
@last_name VARCHAR(50)
AS
UPDATE sys_user
SET username = @username,
password = ENCRYPT(@password),
first_name = @first_name,
last_name = @last_name
WHERE id = @sys_user_id
GO
Thanks,
Steve DiDomenico, Nashua, NH

Viewing 10 posts - 1 through 9 (of 9 total)

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

SamiU SSCrazy Points: 2277 More actions · Answer 1

Steve,

It appears that the encrypt function takes the characters that are typed in and then returns the Hex numbers of each character separated by 00.

I am not sure if that will provide the results that you want. You might want to try using pwdencrypt and pwdcompare. These are both undocumented features, so the functionality might change between versions of SQL Server.

Examples:

Select Encrypt('test') - returns 0x7400650073007400
Select ascii('t') - returns 116. (Hex 74 = Decimal 116.)
Select ascii('e') - returns 101. (Hex 65 = Decimal 101.)
Select ascii('s') - returns 115. (Hex 73 = Decimal 115.)
Select ascii('t') - returns 116. (Hex 74 = Decimal 116.)

Try using pwdencrypt(@password) instead of encrypt(@password). You can then use the following test condition:

INSERT INTO tableName (userID, password) VALUES (1, pwdencrypt

('mypassword')) ;

SELECT @test-2 = password FROM TableName where userID = 1

PRINT pwdcompare('wrongpassword',@test ); (returns 0)

PRINT pwdcompare('mypassword',@test ); (returns 1)

A value = 1 means a match, a 0 means no match.

Many Links, but try this for the full example: http://www.devx.com/tips/Tip/14407

Thanks,

Sami

(Note: There are quite a few warnings against using these functions, so use them at your own risk.)

Steve DiDomenico Ten Centuries Points: 1078 More actions · Answer 2

Thanks Sami, I forgot to mention that this worked on SQL Server 7.0 and stopped working after migration to SQL Server 2000. I'll give your suggestion a try. The only problem is that the passwords can be read if someone runs a Select from QA or opens a table returning rows in EM.

SamiU SSCrazy Points: 2277 More actions · Answer 3

I think the pwdencrypt() will store it in an encrypted fashion. That may solve your problem.

Glenda Baker SSC Enthusiast Points: 151 More actions · Answer 4

pwdencrypt() does encrypt, but the article at http://www.theregister.co.uk/2002/07/08/cracking_ms_sql_server_passwords/

explains how they can also be cracked.

Alamir SSCertifiable Points: 6085 More actions · Answer 5

what about encryption the whole table ?

Alamir Mohamed
Alamir_mohamed@yahoo.com

Steve DiDomenico Ten Centuries Points: 1078 More actions · Answer 6

Hi Alamir,

Thanks for the post. I don't know what you mean.

Steve

Alamir SSCertifiable Points: 6085 More actions · Answer 7

I am sorry there is no Encrypt for Tables in SQL Server 2000 !!

you can use stored procedures, views, and functions and apply "With Encryption" to them.

Also there is a product called "XP_Crypt", I think it Encrypts Tables

see this link

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=6&messageid=99573

I hope this help u

Alamir Mohamed
Alamir_mohamed@yahoo.com

Steve DiDomenico Ten Centuries Points: 1078 More actions · Answer 8

Is "With Encryption" different than what I am using in my Stored Procedures which I included in my first post to start this thread?

SamiU SSCrazy Points: 2277 More actions · Answer 9

Yes. I think this thread is referring to the encryption of the data in a table, not the encryption of the stored procedure.

You can encrypt a stored procedure so that it can't be viewed, only executed. (You can also find freeware utilities to decrypt any encrypted stored procedure). It does not encrypt the data in a table.

Real-life example: We had a DBA who encrypted his stored procedures prior to leaving the company. The applications worked, but were impossible to change. We found a freeware utility and decrypted the SP's.

From BOL:

Encrypting Procedure Definitions

If you are creating a stored procedure and you want to make sure that the procedure definition cannot be viewed by other users, you can use the WITH ENCRYPTION clause. The procedure definition is then stored in an unreadable form.

After a stored procedure is encrypted, its definition cannot be decrypted and cannot be viewed by anyone, including the owner of the stored procedure or the system administrator.