hsiung.博格 ERP軟體

HashBytes提供的加密法有幾種 (MD2、MD4、MD5、SHA、SHA1 )，但最常使用的還是 MD5 與 SHA1。
select hashbytes('MD5','12345') as MD5,hashbytes('SHA1','12345') as SHA1;

01 CREATE TABLE [dbo].[HashTest](
02 [INDEX_NO] [int] IDENTITY(1,1) NOT NULL,
03 [PassWD] [varchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
04 [HashedPW] [varchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
05 CONSTRAINT [PK_HashTest] PRIMARY KEY CLUSTERED  
06 (
07 [INDEX_NO] ASC
08 )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
09 ) ON [PRIMARY]
10 GO

欄位直接加密
use AdventureWorksDW

INSERT INTO TbEncrypt (cencrypt)
    VALUES (CONVERT(varbinary(255), pwdencrypt('test'))
)

SELECT     pwdcompare('test', cencrypt, 0) AS Expr1
FROM         TbEncrypt

金鑰加密
USE AdventureWorks;
GO
-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard
    ADD CardNumber_EncryptedbyPassphrase varbinary(256);
GO
-- First get the passphrase from the user.
DECLARE @PassphraseEnteredByUser nvarchar(128);
SET @PassphraseEnteredByUser
    = 'A little learning is a dangerous thing!';

-- Update the record for the user's credit card.
-- In this case, the record is number 3681.
UPDATE Sales.CreditCard
SET CardNumber_EncryptedbyPassphrase = EncryptByPassPhrase(@PassphraseEnteredByUser
    , CardNumber, 1, CONVERT( varbinary, CreditCardID))
WHERE CreditCardID = '3681';
GO

金鑰解密
USE AdventureWorks;
-- Get the pass phrase from the user.
DECLARE @PassphraseEnteredByUser nvarchar(128);
SET @PassphraseEnteredByUser
= 'A little learning is a dangerous thing!';

-- Decrypt the encrypted record.
SELECT CardNumber, CardNumber_EncryptedbyPassphrase
    AS 'Encrypted card number', CONVERT(nvarchar,
    DecryptByPassphrase(@PassphraseEnteredByUser, CardNumber_EncryptedbyPassphrase, 1
    , CONVERT(varbinary, CreditCardID)))
    AS 'Decrypted card number' FROM Sales.CreditCard
    WHERE CreditCardID = '3681';
GO