How to encrypt password in MsSQL

It is believed that many inexperienced people have no idea about how to encrypt passwords in MsSQL. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

In fact, as long as you take a closer look at master.dbo.sp_addlogin, you can see the code in MSSQL's sp, which is really good.

Let's see how it works. Notice the line select @ passwd = pwdencrypt (@ passwd). Then @ passwd is encrypted. Let's give it a try.

DECLARE @ ClearPWD varchar

DECLARE @ EncryptedPWD varbinary

SELECT @ ClearPWD = 'test'

SELECT @ EncryptedPWD = CONVERT (varbinary (255), pwdencrypt (@ ClearPWD))

SELECT @ EncryptedPWD

It looks good, it is encrypted, but how can I restore it?

Ha ha, this is hopeless, password encryption is one-way, with encrypted ciphertext to compare it.

If you continue to look at other user-related sp, you can find that there is a password comparison in master.dbo.sp_password.

Pwdcompare (@ old, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0 END))

Don't worry about xstatus, this is a status mask, usually we can just use 0 when we use it.

DECLARE @ ClearPWD varchar

DECLARE @ EncryptedPWD varbinary

SELECT @ ClearPWD = 'test'

SELECT @ EncryptedPWD = CONVERT (varbinary (255), pwdencrypt (@ ClearPWD))

SELECT pwdcompare (@ ClearPWD, @ EncryptedPWD, 0)

SELECT pwdcompare ('ErrorPassword', @ EncryptedPWD, 0)

After reading the above, have you mastered how to encrypt passwords in MsSQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!