April 15, 2004 at 1:36 pm
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
April 15, 2004 at 2:27 pm
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:
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.)
April 15, 2004 at 2:38 pm
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.
April 15, 2004 at 4:50 pm
I think the pwdencrypt() will store it in an encrypted fashion. That may solve your problem.
April 16, 2004 at 7:29 am
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.
April 19, 2004 at 3:43 am
what about encryption the whole table ?
Alamir Mohamed
Alamir_mohamed@yahoo.com
April 19, 2004 at 6:14 am
Hi Alamir,
Thanks for the post. I don't know what you mean.
Steve
April 19, 2004 at 6:50 am
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
April 19, 2004 at 7:50 am
Is "With Encryption" different than what I am using in my Stored Procedures which I included in my first post to start this thread?
April 19, 2004 at 1:46 pm
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:
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply