add_xp.sql

关于SQL SERVER 的加密和解密包

-- ADD_XP.SQL sample script to install extended stored procedures
--
-- Michael Coles, MCDBA
-- 7/2005
--
-- Updated 1/19/2006 - AES UDFs modified to use VARBINARY instead of VARCHAR
--
USE master
GO
EXEC sp_addextendedproc xp_rot13, 'xp_rot13.dll'
GO
EXEC sp_addextendedproc xp_base64encode, 'xp_base64encode.dll'
GO
EXEC sp_addextendedproc xp_base64decode, 'xp_base64decode.dll'
GO
EXEC sp_addextendedproc xp_blowfishencrypt, 'xp_blowfishencrypt.dll'
GO
EXEC sp_addextendedproc xp_blowfishdecrypt, 'xp_blowfishdecrypt.dll'
GO
EXEC sp_addextendedproc xp_generatekey, 'xp_generatekey.dll'
GO
EXEC sp_addextendedproc xp_aesencrypt, 'xp_aesencrypt.dll'
GO
EXEC sp_addextendedproc xp_aesdecrypt, 'xp_aesdecrypt.dll'
GO
CREATE FUNCTION dbo.udf_blowfishdecrypt
	(@string VARCHAR(2000),
	 @key VARCHAR(500))
RETURNS VARCHAR(2000)
AS
BEGIN
	DECLARE @decstring VARCHAR(2000)
	DECLARE @err AS INT
	EXEC master..xp_blowfishdecrypt @string, @key, @decstring OUTPUT
	SET @err = @@error
	IF @err <> 0
		SET @decstring = NULL
	RETURN @decstring
END
GO
CREATE FUNCTION dbo.udf_blowfishencrypt
	(@string VARCHAR(2000),
	 @key VARCHAR(500))
RETURNS VARCHAR(2000)
AS
BEGIN
	DECLARE @encstring VARCHAR(2000)
	DECLARE @err AS INT
	EXEC master..xp_blowfishencrypt @string, @key, @encstring OUTPUT
	SET @err = @@error
	IF @err <> 0
		SET @encstring = NULL
	RETURN @encstring
END
GO
CREATE FUNCTION dbo.udf_aesdecrypt (@cipher_text VARBINARY(4000), @key VARBINARY(256))
RETURNS VARBINARY(4000)
AS
BEGIN
	DECLARE @plain_text VARBINARY(4000)
	DECLARE @i INT
	EXEC @i = master..xp_aesdecrypt @cipher_text, @key, @plain_text OUTPUT
	IF @i <> 0
		SET @plain_text = NULL
	RETURN @plain_text
END
GO
CREATE FUNCTION dbo.udf_aesencrypt (@plain_text VARBINARY(4000), @key VARBINARY(256))
RETURNS VARBINARY(4000)
AS
BEGIN
	DECLARE @cipher_text VARBINARY(4000)
	DECLARE @i INT
	EXEC @i = master..xp_aesencrypt @plain_text, @key, @cipher_text OUTPUT
	IF @i <> 0
		SET @cipher_text = NULL
	RETURN @cipher_text
END
GO