Secure your password with HashBytes TIP #89

Dear Friends,

In my last article (TIP 87) I wrote about PWDENCRYPT.  I forgot to write few details but thanks to all my talented friends & blog readers who guided me by providing there feedback on post.

I respect their inputs and always interested to get more inputs. Thanks to all of you.

Now I would like to share information about “HASHBYTES”  function which is also available for secure your password using various Hash algorithms like MD2 , MD4 , MD5 , SHA ,SHA1 , SHA2_256 , SHA2_512.

The PWDENCRYPT  can be use but  HASHBYTES function provides you various options to make your content robust secure.

The Syntax is very easy as shown below

HASHBYTES (ALGORITHM, ‘INPUT WHICH YOU WANT TO SECURE’)

Now let see an example to understand it more

Just wanted to add here that SHA2_256, SHA2_512 available with 2012 version and above.

Someone said this line right “More option More confusion”

Now we have different algorithm then which one we have to use so the answer is (according to my knowledge) use the latest most secure one Like SHA1, SHA2 etc.

I hope this article may be useful to you.

Thanks !!!

RJ!!!

PWDCOMPARE–a hidden function of SQL SERVER TIP #88

 

I the last tip TIP#88 we saw how to encrypt a password. Now in this tip I would like to share how to check encrypted password ?

Means once you stored your encrypted password in database now next step is to compare that particular password with your input password and return results accordingly.

The Syntax of the PWDCOMPARE   is very simple

PWDCOMPARE(‘Password plain text’, ‘Password encrypted form’)

This function return 1 if plain text and hash value  are matched else return o.

For example

Lets suppose we have created a table with 3 columns like userId, username and password

as shown below

DECLARE @tblLogin AS TABLE (UserId INT IDENTITY,
                            Username  VARCHAR(100),
                            EncryptedPassword NVARCHAR(MAX))

Now suppose we have inserted 2 rows in to it wit encrypted password

INSERT INTO @tblLogin VALUES ('Indiandotnet',PWDENCRYPT(N'MyPassword'))
INSERT INTO @tblLogin VALUES ('SQLRaaga', PWDENCRYPT(N'Test'))

Now, Suppose we have want to write a query which return rows from @tbllogin whose password is Test then it should return SQL Raaga for this

I have to write following query

SELECT * FROM @tblLogin WHERE PWDCOMPARE(N'Test',EncryptedPassword) = 1

For detail  take a look of below snap

 

I hope you understand with above provided  example.

 

Enjoy !!!

RJ!!

Security tips- How to encrypt Stored Procedure–TIP #24

 

Sometimes for security reason it is require to encrypt the objects which you have created (Suppose you have deployed your database on client end or shared hosting and you don’t want any other person to see your logic behind your stored procedure and function).

To achieve this you need to use a simple keyword encryption

In below example I have encrypted the proc_GetListUser

 

Now when someone try to see the content of stored procedure generally he/she will write following command

sp_helptext proc_GetListUser

When he/she  will run this command he will get encryption message not the stored procedure content.

 

 

Hope you will use this tip soon.

Cheers!