SQL 2005的DES加密算法
--参考:
下面给出了一个存储过程，它的作用是自动将当前数据库的用户存储过程加密。
DECLARE@sp_namenvarchar(400)
DECLARE@sp_contentnvarchar(2000)
DECLARE@asbeginint
declare@nowdatetime
select@now=getdate()
DECLARE sp_cursor CURSORFOR
SELECTobject_name(id)
FROM sysobjects
WHERE xtype ='P'
AND type ='P'
AND crdate <@now
ANDOBJECTPROPERTY(id, 'IsMSShipped')=0
OPEN sp_cursor
FETCHNEXTFROM sp_cursor
INTO@sp_name
WHILE@@FETCH_STATUS=0
BEGIN
SELECT@sp_content=textFROM syscomments WHERE id =OBJECT_ID(@sp_name)
SELECT@asbegin=PATINDEX ( '%AS'+char(13) +'%', @sp_content)
SELECT@sp_content=SUBSTRING(@sp_content, 1, @asbegin-1)
+' WITH ENCRYPTION AS'
+SUBSTRING (@sp_content, @asbegin+2, LEN(@sp_content))
SELECT@sp_name='DROP PROCEDURE ['+@sp_name+']'
EXEC sp_executesql @sp_name
EXEC sp_executesql @sp_content
FETCHNEXTFROM sp_cursor
INTO@sp_name
END
CLOSE sp_cursor
DEALLOCATE sp_cursor
该存储过程利用了 sysobjects 和 syscomments 表，并巧妙地修改了原存储过程的 SQL 定义语句，将 AS 修改为了 WITH ENCRYPTION AS，从而达到了加密存储过程的目的。本存储过程在 SQL Server 2000 上通过。
____________________________________________________________________
下面给出了一个存储过程，它的作用是自动将当前数据库的用户存储过程加密。
DECLARE@sp_namenvarchar(400)
DECLARE@sp_contentnvarchar(2000)
DECLARE@asbeginint
declare@nowdatetime
select@now=getdate()
DECLARE sp_cursor CURSORFOR
SELECTobject_name(id)
FROM sysobjects
WHERE xtype ='P'
AND type ='P'
AND crdate <@now
ANDOBJECTPROPERTY(id, 'IsMSShipped')=0
OPEN sp_cursor
FETCHNEXTFROM sp_cursor
INTO@sp_name
WHILE@@FETCH_STATUS=0
BEGIN
SELECT@sp_content=textFROM syscomments WHERE id =OBJECT_ID(@sp_name)
SELECT@asbegin=PATINDEX ( '%AS'+char(13) +'%', @sp_content)
SELECT@sp_content=SUBSTRING(@sp_content, 1, @asbegin-1)
+' WITH ENCRYPTION AS'
+SUBSTRING (@sp_content, @asbegin+2, LEN(@sp_content))
SELECT@sp_name='DROP PROCEDURE ['+@sp_name+']'
EXEC sp_executesql @sp_name
EXEC sp_executesql @sp_content
FETCHNEXTFROM sp_cursor
INTO@sp_name
END
CLOSE sp_cursor
DEALLOCATE sp_cursor
该存储过程利用了 sysobjects 和 syscomments 表，并巧妙地修改了原存储过程的 SQL 定义语句，将 AS 修改为了 WITH ENCRYPTION AS，从而达到了加密存储过程的目的。本存储过程在 SQL Server 2000 上通过。
____________________________________________________________________
加密存贮过程的源代码
createPROCEDURE sp_decrypt_sp (@objectNamevarchar(50))
AS
begin
declare@objectname1varchar(100)
declare@sql1nvarchar(4000),@sql2nvarchar(4000),@sql3nvarchar(4000),@sql4nvarchar(4000),@sql5nvarchar(4000),@sql6nvarchar(4000),@sql7nvarchar(4000),@sql8nvarchar(4000),@sql9nvarchar(4000),@sql10nvarchar(4000)
DECLARE@OrigSpText1nvarchar(4000), @OrigSpText2nvarchar(4000) , @OrigSpText3nvarchar(4000), @resultspnvarchar(4000)
declare@iint , @tbigint
declare@mint,@nint,@qint
set@m=(SELECTmax(colid) FROM syscomments WHERE id =object_id(@objectName))
set@n=1
--get encrypted data
createtable #temp(colid int,ctext varbinary(8000))
insert #tempSELECT colid,ctext FROM syscomments WHERE id =object_id(@objectName)
--select * from #temp
set@sql1='ALTER PROCEDURE '+@objectName+' WITH ENCRYPTION AS '
--set @sql1='ALTER PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '
set@q=len(@sql1)
set@sql1=@sql1+REPLICATE('-',4000-@q)
select@sql2=REPLICATE('-',4000),@sql3=REPLICATE('-',4000),@sql4=REPLICATE('-',4000),@sql5=REPLICATE('-',4000),@sql6=REPLICATE('-',4000),@sql7=REPLICATE('-',4000),@sql8=REPLICATE('-',4000),@sql9=REPLICATE('-',4000),@sql10=REPLICATE('-',4000)
exec(@sql1+@sql2+@sql3+@sql4+@sql5+@sql6+@sql7+@sql8+@sql9+@sql10)
while@n<=@m
begin
SET@OrigSpText1=(SELECT ctext FROM #tempWHERE colid=@n)
set@objectname1=@objectname+'_t'
SET@OrigSpText3=(SELECT ctext FROM syscomments WHERE id=object_id(@objectName) and colid=@n)
if@n=1
begin
SET@OrigSpText2='CREATE PROCEDURE '+@objectName+' WITH ENCRYPTION AS '--
set@q=4000-len(@OrigSpText2)
set@OrigSpText2=@OrigSpText2+REPLICATE('-',@q)
end
else
begin
SET@OrigSpText2=REPLICATE('-', 4000)
end
--start counter
SET@i=1
--fill temporary variable
SET@resultsp=replicate(N'A', (datalength(@OrigSpText1) /2))
--loop
WHILE@i<=datalength(@OrigSpText1)/2
BEGIN
--reverse encryption (XOR original+bogus+bogus encrypted)
SET@resultsp=stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^
(UNICODE(substring(@OrigSpText2, @i, 1)) ^
UNICODE(substring(@OrigSpText3, @i, 1)))))
SET@i=@i+1
END
--drop original SP
--EXECUTE ('drop PROCEDURE '+ @objectName)
--remove encryption
--preserve case
SET@resultsp=REPLACE((@resultsp),'WITH ENCRYPTION', '')
SET@resultsp=REPLACE((@resultsp),'With Encryption', '')
SET@resultsp=REPLACE((@resultsp),'with encryption', '')
IFCHARINDEX('WITH ENCRYPTION',UPPER(@resultsp) )>0
SET@resultsp=REPLACE(UPPER(@resultsp),'WITH ENCRYPTION', '')
--replace Stored procedure without enryption
print@resultsp
--execute( @resultsp)
set@n=@n+1
end
droptable #temp
end
__________________________________
createPROCEDURE sp_decrypt_sp (@objectNamevarchar(50))
AS
begin
declare@objectname1varchar(100)
declare@sql1nvarchar(4000),@sql2nvarchar(4000),@sql3nvarchar(4000),@sql4nvarchar(4000),@sql5nvarchar(4000),@sql6nvarchar(4000),@sql7nvarchar(4000),@sql8nvarchar(4000),@sql9nvarchar(4000),@sql10nvarchar(4000)
DECLARE@OrigSpText1nvarchar(4000), @OrigSpText2nvarchar(4000) , @OrigSpText3nvarchar(4000), @resultspnvarchar(4000)
declare@iint , @tbigint
declare@mint,@nint,@qint
set@m=(SELECTmax(colid) FROM syscomments WHERE id =object_id(@objectName))
set@n=1
--get encrypted data
createtable #temp(colid int,ctext varbinary(8000))
insert #tempSELECT colid,ctext FROM syscomments WHERE id =object_id(@objectName)
--select * from #temp
set@sql1='ALTER PROCEDURE '+@objectName+' WITH ENCRYPTION AS '
--set @sql1='ALTER PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '
set@q=len(@sql1)
set@sql1=@sql1+REPLICATE('-',4000-@q)
select@sql2=REPLICATE('-',4000),@sql3=REPLICATE('-',4000),@sql4=REPLICATE('-',4000),@sql5=REPLICATE('-',4000),@sql6=REPLICATE('-',4000),@sql7=REPLICATE('-',4000),@sql8=REPLICATE('-',4000),@sql9=REPLICATE('-',4000),@sql10=REPLICATE('-',4000)
exec(@sql1+@sql2+@sql3+@sql4+@sql5+@sql6+@sql7+@sql8+@sql9+@sql10)
while@n<=@m
begin
SET@OrigSpText1=(SELECT ctext FROM #tempWHERE colid=@n)
set@objectname1=@objectname+'_t'
SET@OrigSpText3=(SELECT ctext FROM syscomments WHERE id=object_id(@objectName) and colid=@n)
if@n=1
begin
SET@OrigSpText2='CREATE PROCEDURE '+@objectName+' WITH ENCRYPTION AS '--
set@q=4000-len(@OrigSpText2)
set@OrigSpText2=@OrigSpText2+REPLICATE('-',@q)
end
else
begin
SET@OrigSpText2=REPLICATE('-', 4000)
end
--start counter
SET@i=1
--fill temporary variable
SET@resultsp=replicate(N'A', (datalength(@OrigSpText1) /2))
--loop
WHILE@i<=datalength(@OrigSpText1)/2
BEGIN
--reverse encryption (XOR original+bogus+bogus encrypted)
SET@resultsp=stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^
(UNICODE(substring(@OrigSpText2, @i, 1)) ^
UNICODE(substring(@OrigSpText3, @i, 1)))))
SET@i=@i+1
END
--drop original SP
--EXECUTE ('drop PROCEDURE '+ @objectName)
--remove encryption
--preserve case
SET@resultsp=REPLACE((@resultsp),'WITH ENCRYPTION', '')
SET@resultsp=REPLACE((@resultsp),'With Encryption', '')
SET@resultsp=REPLACE((@resultsp),'with encryption', '')
IFCHARINDEX('WITH ENCRYPTION',UPPER(@resultsp) )>0
SET@resultsp=REPLACE(UPPER(@resultsp),'WITH ENCRYPTION', '')
--replace Stored procedure without enryption
print@resultsp
--execute( @resultsp)
set@n=@n+1
end
droptable #temp
end
__________________________________
存储过程解密(破解函数,过程,触发器,视图.仅限于SQLSERVER2000)_____________________________________________________________
--*/
/*--调用示例:
--解密指定存储过程
exec sp_decrypt '存储过程名'
--*/
create PROCEDURE sp_decrypt(@objectName varchar(50))
AS
begin
begin tran
declare @objectname1 varchar(100),@orgvarbin varbinary(8000)
declare @sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000),@sql5 nvarchar(4000),@sql6 nvarchar(4000),@sql7 nvarchar(4000),@sql8 nvarchar(4000),@sql9 nvarchar(4000),@sql10 nvarchar(4000)
DECLARE @OrigSpText1 nvarchar(4000), @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)
declare @i int,@status int,@type varchar(10),@parentid int
declare @colid int,@n int,@q int,@j int,@k int,@encrypted int,@number int
select @type=xtype,@parentid=parent_obj from sysobjects where id=object_id(@ObjectName)
create table #temp(number int,colid int,ctext varbinary(8000),encrypted int,status int)
insert #temp SELECT number,colid,ctext,encrypted,status FROM syscomments WHERE id = object_id(@objectName)
select @number=max(number) from #temp
set @k=0
while @k <=@number
begin
if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)
begin
if @type='P'
set @sql1=(case when @number>1 then 'ALTER PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '
else 'ALTER PROCEDURE '+ @objectName+' WITH ENCRYPTION AS '
end)
if @type='TR'
set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '
if @type='FN' or @type='TF' or @type='IF'
set @sql1=(case @type when 'TF' then
'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '
when 'FN' then
'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'
when 'IF' then
'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'
end)
if @type='V'
set @sql1='ALTER VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 '
set @q=len(@sql1)
set @sql1=@sql1+REPLICATE('-',4000-@q)
select @sql2=REPLICATE('-',4000),@sql3=REPLICATE('-',4000),@sql4=REPLICATE('-',4000),@sql5=REPLICATE('-',4000),@sql6=REPLICATE('-',4000),@sql7=REPLICATE('-',4000),@sql8=REPLICATE('-',4000),@sql9=REPLICATE('-',4000),@sql10=REPLICATE('-',4000)
exec(@sql1+@sql2+@sql3+@sql4+@sql5+@sql6+@sql7+@sql8+@sql9+@sql10)
end
set @k=@k+1
end
set @k=0
while @k <=@number
begin
if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)
begin
select @colid=max(colid) from #temp where number=@k
set @n=1
while @n <=@colid
begin
select @OrigSpText1=ctext,@encrypted=encrypted,@status=status FROM #temp WHERE colid=@n and number=@k
SET @OrigSpText3=(SELECT ctext FROM syscomments WHERE id=object_id(@objectName) and colid=@n and number=@k)
if @n=1
begin
if @type='P'
SET @OrigSpText2=(case when @number>1 then 'CREATE PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '
else 'CREATE PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '
end)
if @type='FN' or @type='TF' or @type='IF'--刚才有错改一下
SET @OrigSpText2=(case @type when 'TF' then
'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '
when 'FN' then
'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'
when 'IF' then
'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'
end)
if @type='TR'
set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '
if @type='V'
set @OrigSpText2='CREATE VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 '
set @q=4000-len(@OrigSpText2)
set @OrigSpText2=@OrigSpText2+REPLICATE('-',@q)
end
else
begin
SET @OrigSpText2=REPLICATE('-', 4000)
end
--start counter
SET @i=1
--fill temporary variable
SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))
--loop
WHILE @i <=datalength(@OrigSpText1)/2
BEGIN
SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^
(UNICODE(substring(@OrigSpText2, @i, 1)) ^
UNICODE(substring(@OrigSpText3, @i, 1)))))
SET @i=@i+1
END
set @orgvarbin=cast(@OrigSpText1 as varbinary(8000))
set @resultsp=(case when @encrypted=1
then @resultsp
else convert(nvarchar(4000),case when @status&2=2 then uncompress(@orgvarbin) else @orgvarbin end)
end)
print @resultsp
--execute( @resultsp)
set @n=@n+1
end
end
set @k=@k+1
end
drop table #temp
rollback tran
end
GO
简单的字符串的加密函数
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTERfunction[dbo].[f_jmstr](@strvarchar(8000),@pwdstrvarchar(8000))
returnsvarchar(8000)
As
begin
declare@revarchar(8000)
declare@iint
select@i=len(@str),@re=''
whilelen(@pwdstr)<@i
set@pwdstr=@pwdstr+'123'+@pwdstr
while@i>0
select@re=nchar(unicode(substring(@str,@i,1))^unicode(substring(@pwdstr,@i,1)))+@re ,@i=@i-1
return(@re)
end
set QUOTED_IDENTIFIER ON
go
ALTERfunction[dbo].[f_jmstr](@strvarchar(8000),@pwdstrvarchar(8000))
returnsvarchar(8000)
As
begin
declare@revarchar(8000)
declare@iint
select@i=len(@str),@re=''
whilelen(@pwdstr)<@i
set@pwdstr=@pwdstr+'123'+@pwdstr
while@i>0
select@re=nchar(unicode(substring(@str,@i,1))^unicode(substring(@pwdstr,@i,1)))+@re ,@i=@i-1
return(@re)
end
******************************************************************************
ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[fn_PwdCrypt]') and xtype in (N'FN', N'IF', N'TF'))
dropfunction[dbo].[fn_PwdCrypt]
GO
CREATEFUNCTION dbo.fn_PwdCrypt(@ptSourcevarchar(255), @ptPasswordvarchar(255))
RETURNSvarbinary(64)
BEGIN
DECLARE@tdest varchar(255),
@lteller int,
@lPasswTeller int,
@cnt int,
@len int,
@tFinal varbinary(64)
SELECT@tFinal=Convert(varbinary(64),'')
SELECT@len=Len(@ptSource)
SELECT@lteller=1
SELECT@tdest=@ptSource
SELECT@lPasswTeller=0
WHILE@lteller<=@len
BEGIN
SELECT@lPasswTeller=@lPasswTeller-1
If@lPasswTeller<1
SELECT@lPasswTeller=Len(@ptPassword)
SELECT@tfinal=@tfinal+Convert(varbinary(64),Stuff(Substring(@tdest, @lteller, 1),1,Len( Char(Ascii(Substring(@ptSource, @lteller, 1)) ^Ascii(Substring(@ptPassword, @lPasswTeller, 1)))), Char(Ascii(Substring(@ptSource, @lteller, 1)) ^Ascii(Substring(@ptPassword, @lPasswTeller, 1))) ) )
SELECT@lteller=@lteller+1
END
RETURN@tfinal
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[fn_PwdDeCrypt]') and xtype in (N'FN', N'IF', N'TF'))
dropfunction[dbo].[fn_PwdDeCrypt]
GO
CREATEFUNCTION dbo.fn_PwdDeCrypt(@ptSourcevarchar(255), @ptPasswordvarchar(255))
RETURNSvarchar(255)
BEGIN
DECLARE@tdest varchar(255),
@lteller int,
@lPasswTeller int,
@cnt int,
@len int,
@tFinal varchar(255)
SELECT@tFinal=''
SELECT@len=Len(@ptSource)
SELECT@lteller=1
SELECT@tdest=@ptSource
SELECT@lPasswTeller=0
WHILE@lteller<=@len
BEGIN
SELECT@lPasswTeller=@lPasswTeller-1
If@lPasswTeller<1
SELECT@lPasswTeller=Len(@ptPassword)
SELECT@tfinal=@tfinal+Left(Stuff(substring(@tdest, @lteller, 1),1,Len( Char(Ascii(substring(@ptSource, @lteller, 1)) ^Ascii(Substring(@ptPassword, @lPasswTeller, 1)))), Char(Ascii(Substring(@ptSource, @lteller, 1)) ^Ascii(Substring(@ptPassword, @lPasswTeller, 1))) ), 1)
SELECT@lteller=@lteller+1
END
RETURN@tfinal
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--Test it here
createtable #temp
( pwd varbinary(64))
insertinto #temp (pwd)
select dbo.fn_PwdCrypt ('MyPassword','secret')
select pwd from #temp
select dbo.fn_PwdDeCrypt (pwd,'secret') from #temp
droptable #temp
dropfunction[dbo].[fn_PwdCrypt]
GO
CREATEFUNCTION dbo.fn_PwdCrypt(@ptSourcevarchar(255), @ptPasswordvarchar(255))
RETURNSvarbinary(64)
BEGIN
DECLARE@tdest varchar(255),
@lteller int,
@lPasswTeller int,
@cnt int,
@len int,
@tFinal varbinary(64)
SELECT@tFinal=Convert(varbinary(64),'')
SELECT@len=Len(@ptSource)
SELECT@lteller=1
SELECT@tdest=@ptSource
SELECT@lPasswTeller=0
WHILE@lteller<=@len
BEGIN
SELECT@lPasswTeller=@lPasswTeller-1
If@lPasswTeller<1
SELECT@lPasswTeller=Len(@ptPassword)
SELECT@tfinal=@tfinal+Convert(varbinary(64),Stuff(Substring(@tdest, @lteller, 1),1,Len( Char(Ascii(Substring(@ptSource, @lteller, 1)) ^Ascii(Substring(@ptPassword, @lPasswTeller, 1)))), Char(Ascii(Substring(@ptSource, @lteller, 1)) ^Ascii(Substring(@ptPassword, @lPasswTeller, 1))) ) )
SELECT@lteller=@lteller+1
END
RETURN@tfinal
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[fn_PwdDeCrypt]') and xtype in (N'FN', N'IF', N'TF'))
dropfunction[dbo].[fn_PwdDeCrypt]
GO
CREATEFUNCTION dbo.fn_PwdDeCrypt(@ptSourcevarchar(255), @ptPasswordvarchar(255))
RETURNSvarchar(255)
BEGIN
DECLARE@tdest varchar(255),
@lteller int,
@lPasswTeller int,
@cnt int,
@len int,
@tFinal varchar(255)
SELECT@tFinal=''
SELECT@len=Len(@ptSource)
SELECT@lteller=1
SELECT@tdest=@ptSource
SELECT@lPasswTeller=0
WHILE@lteller<=@len
BEGIN
SELECT@lPasswTeller=@lPasswTeller-1
If@lPasswTeller<1
SELECT@lPasswTeller=Len(@ptPassword)
SELECT@tfinal=@tfinal+Left(Stuff(substring(@tdest, @lteller, 1),1,Len( Char(Ascii(substring(@ptSource, @lteller, 1)) ^Ascii(Substring(@ptPassword, @lPasswTeller, 1)))), Char(Ascii(Substring(@ptSource, @lteller, 1)) ^Ascii(Substring(@ptPassword, @lPasswTeller, 1))) ), 1)
SELECT@lteller=@lteller+1
END
RETURN@tfinal
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--Test it here
createtable #temp
( pwd varbinary(64))
insertinto #temp (pwd)
select dbo.fn_PwdCrypt ('MyPassword','secret')
select pwd from #temp
select dbo.fn_PwdDeCrypt (pwd,'secret') from #temp
droptable #temp
******************************************************************************************
2005中SQL本身就带了DES加密算法。
CREATE SYMMETRIC KEY key1 WITH ALGORITHM=DES
ENCRYPTION BY PASSWORD='abc$123'
GO
OPEN SYMMETRIC KEY key1
DECRYPTION BY PASSWORD='abc$123'
DECLARE@strNVARCHAR(100),@EncryptedVARBINARY(MAX)
SET@str=N'要加密的字串'
SELECT@Encrypted=ENCRYPTBYKEY(KEY_GUID('key1'),@str)
SELEcT'加密'=@Encrypted ,'解密'=CAST(DECRYPTBYKEY(@Encrypted) ASNVARCHAR(100))
CLOSE SYMMETRIC KEY key1
GO
DROP SYMMETRIC KEY key1
GO
**********************************************************************************
CREATE SYMMETRIC KEY key1 WITH ALGORITHM=DES
ENCRYPTION BY PASSWORD='abc$123'
GO
OPEN SYMMETRIC KEY key1
DECRYPTION BY PASSWORD='abc$123'
DECLARE@strNVARCHAR(100),@EncryptedVARBINARY(MAX)
SET@str=N'要加密的字串'
SELECT@Encrypted=ENCRYPTBYKEY(KEY_GUID('key1'),@str)
SELEcT'加密'=@Encrypted ,'解密'=CAST(DECRYPTBYKEY(@Encrypted) ASNVARCHAR(100))
CLOSE SYMMETRIC KEY key1
GO
DROP SYMMETRIC KEY key1
GO
**********************************************************************************
写个扩展存储过程
RETCODE __declspec(dllexport) xp_EncryptData(SRV_PROC *srvproc)
RETCODE __declspec(dllexport) xp_DecryptData(SRV_PROC *srvproc)
use master
GO
exec sp_addextendedproc N'xp_EncryptData', N'DESProc.dll'
GO
exec sp_addextendedproc N'xp_DecryptData', N'DESProc.dll'
GO
RETCODE __declspec(dllexport) xp_EncryptData(SRV_PROC *srvproc)
RETCODE __declspec(dllexport) xp_DecryptData(SRV_PROC *srvproc)
use master
GO
exec sp_addextendedproc N'xp_EncryptData', N'DESProc.dll'
GO
exec sp_addextendedproc N'xp_DecryptData', N'DESProc.dll'
GO
