Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

encrypt and decrypt undocumented

Posted on 2005-04-20
5
Medium Priority
?
2,735 Views
Last Modified: 2009-12-16
Hi,

Kindly assist me on creating a store procedure to encrypt and decrypt/auth

if user name and password is correct, return 1 else 0

what is clearpin ?

Thanks.

________________________________________________________________procedure encrypt

exec dbo.encrypt @username='my username here', @password='my password here'

create proc dbo.encrypt
@myUsername as varchar(20)
@myPassword as varchar(10)
as
DECLARE @ClearPIN varchar(255)
DECLARE @EncryptedPIN varbinary(255)
SELECT @ClearPIN = '1234'                                        
SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@ClearPIN))
SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)
INSERT INTO <TABLENAME> ( @myusername, @mypassword,)
SELECT '1234',   pwdencrypt( @mypassword )
GO


________________________________________________________________procedure decrypt and auth
exec dbo.decrypt @username='my username here', @password='my password here'

create proc dbo.decrypt
@myUsername as varchar(20)
@myPassword as varchar(10)
as
DECLARE @ClearPIN varchar(255)
DECLARE @EncryptedPIN varbinary(255)
SELECT @ClearPIN = '1234'                      
SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@ClearPIN))
SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)

SELECT @PWD = PWD
FROM <TABLENAME>
WHERE UNAME = @myusername
select pwdcompare(@mypassword, @PWD)  <---------hope this is correct?
Go

related link:
http://www.windowsitpro.com/Article/ArticleID/9809/9809.html
0
Comment
Question by:EdwardPeter
  • 2
  • 2
5 Comments
 
LVL 23

Assisted Solution

by:adathelad
adathelad earned 200 total points
ID: 13826215
>> what is clearpin ? <<

@ClearPIN is the variable that contains the password (or in this case PIN number) BEFOREit has been encrypted. This is passed to pwdencrypt for to encrypt.

It looks like you should scrap this variable and substitute it with @myPassword as this will contain the password that you want to encrypt.

0
 

Author Comment

by:EdwardPeter
ID: 13826230
adathelad,

Change done. I think there is more changes needed.. hope you could kindly assist.

Thanks.

________________________________________________________________procedure encrypt

exec dbo.encrypt @username='my username here', @password='my password here'

create proc dbo.encrypt
@myUsername as varchar(20)
@myPassword as varchar(10)
as
DECLARE @ClearPIN varchar(255)
DECLARE @EncryptedPIN varbinary(255)
SELECT @ClearPIN =  @mypassword                                        
SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@ClearPIN))
SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)
INSERT INTO <TABLENAME> ( @myusername, @mypassword,)
SELECT '1234',   pwdencrypt( @mypassword )
GO


________________________________________________________________procedure decrypt and auth
exec dbo.decrypt @username='my username here', @password='my password here'

create proc dbo.decrypt
@myUsername as varchar(20)
@myPassword as varchar(10)
as
DECLARE @ClearPIN varchar(255)
DECLARE @EncryptedPIN varbinary(255)
SELECT @ClearPIN = @mypassword                      
SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@ClearPIN))
SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)

SELECT @PWD = PWD
FROM <TABLENAME>
WHERE UNAME = @myusername
select pwdcompare(@mypassword, @PWD)  <---------hope this is correct?
Go
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 1800 total points
ID: 13826236
It is not recommended to use the undocumented pwdencrypt and pwdcompare functions because they may not be supported in future releases.  

Anyway, to answer your question:

create proc dbo.encrypt
@myUsername as varchar(20)
@myPassword as varchar(10)
as

INSERT INTO <TABLENAME> ( UserName, Password)
SELECT @myUsername,   pwdencrypt( @mypassword )
GO

create proc dbo.compare
@myUsername as varchar(20)
@myPassword as varchar(10)
as

DECLARE @Password VARBINARY(255)

SELECT @Password = PWD
FROM <TABLENAME>
WHERE UNAME = @myusername

RETURN pwdcompare(@mypassword, @Password )
Go
0
 

Author Comment

by:EdwardPeter
ID: 13826248
rafrancisco,

Dont we need the following? where do we place this?

DECLARE @ClearPIN varchar(255)
DECLARE @EncryptedPIN varbinary(255)
SELECT @ClearPIN = @mypassword                      
SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@ClearPIN))
SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)

Thanks.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13826267
You don't need those.  They are just part of the example on the link you've provided.  Since the password is already encrypted in your table, all you have to do is compare it with the input password using the pwdcompare function.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question