Self-made non-public cryptographic functions using MSSQL SP

Functions | Encryption how does Microsoft SQL Server encrypt passwords? How do I make an undisclosed cryptographic function?

If the users of the MSSQL information is interested, may find master.dbo.sysxlogins inside the user's password, but, password field if not NULL is a bunch of binary can not understand, this password is how to encrypt it?

In fact, as long as a careful look at the master.dbo.sp_addlogin will know, MSSQL SP can see the code, is really good.


Let's take a look at how it is done, note that this line of select @passwd = Pwdencrypt (@passwd), this time after the @passwd is encrypted, let us also try

DECLARE @ClearPWD varchar (255)
DECLARE @EncryptedPWD varbinary (255)
SELECT @ClearPWD = ' Test '
SELECT @EncryptedPWD = CONVERT (varbinary (255), Pwdencrypt (@ClearPWD))
SELECT @EncryptedPWD

It looks good, it's really encrypted, but how can I restore it?

Oh, this is over, password encryption is one-way, with encryption after the ciphertext to compare it.

Continue to look at other users related to the SP, you can find Master.dbo.sp_password inside the contents of the password comparison.

Pwdcompare (@old, password, (case when xstatus&2048 = 2048 THEN 1 ELSE 0))

Do not have to pay attention to xstatus, this is a state mask, generally we use when the direct use of 0 can be

DECLARE @ClearPWD varchar (255)
DECLARE @EncryptedPWD varbinary (255)
SELECT @ClearPWD = ' Test '
SELECT @EncryptedPWD = CONVERT (varbinary (255), Pwdencrypt (@ClearPWD))
SELECT Pwdcompare (@ClearPWD, @EncryptedPWD, 0)
SELECT pwdcompare (' Errorpassword ', @EncryptedPWD, 0)

So we can use these two functions to encrypt our own password: