Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Gama and Naughter


TEA encryption with XP_CRYPTOAPI

 

The code is identical to the encryption with TSQL, with the exception of the two UDF's, which are wrappers of XP_CRYPTOAPI. Besides the simplicity of the code of the UDF's it is also obvious that the UDF's could use any encryption algorithm with minimal effort. There is even the possibility of having a parameter in the UDF to allow choosing the encryption algorithm.

 

The code to create the test table:

 

CREATE TABLE tblCrypt2(secret varchar(8000))

 

The insert trigger:

 

CREATE TRIGGER "tblCrypt_ITrig2" ON tblCrypt2

INSTEAD OF INSERT

AS

SET NOCOUNT ON

INSERT tblCrypt2(secret)

SELECT dbo.UDFencTEAXP(secret, 'abc') FROM INSERTED

 

The update trigger:

 

CREATE TRIGGER "tblCrypt_UTrig2" ON tblCrypt2

INSTEAD OF UPDATE

AS

SET NOCOUNT ON

UPDATE tblCrypt2 SET secret= dbo.UDFencTEAXP(INSERTED.secret, 'abc') FROM INSERTED

 

The view for examining the records unencrypted:

 

CREATE VIEW dbo.VIEW_tblCrypt2

AS

SELECT dbo.UDFdecTEAXP(secret, 'abc') as secret

FROM  dbo.tblCrypt2

 

These UDF's are simple wrappers for XP_TEA_ENCRYPT:

 

CREATE function UDFencTEAXP(@EncryptedMessage VARCHAR(8000), @key VARCHAR(16))

returns  VARCHAR(8000)

as

  BEGIN

  DECLARE @encrypted VARCHAR(8000)

  EXEC master..XP_TEA_ENCRYPT @EncryptedMessage, @key, @encrypted OUT

  RETURN(@encrypted)

  END

 

UDFdecTEAXP only differs in its name and the XP called, XP_TEA_DECRYPT.

 

RC4 only needs one UDF because the algorithm is the same for both encryption and decryption. The UDF, named UDFRC4, uses strings as one-dimensional arrays. There will be two UDF's for the RC4 XP, just like for the TEA example.

 

If this error occurs:

 

XP_RC4_ENCRYPT: Failed to acquire CSP, Error:-2146893801

 

This error can occur if you are running on Windows 2000 and do not have the High Encryption Pack installed on your machine. It can be downloaded from http://www.microsoft.com/windows2000/downloads/recommended/encryption/default.asp
 

The encryption UDF can be replaced by any other function such as UDFencTEA, UDFencTEAXP, UDFRC4 or UDFencRC4XP. That is a good way to compare the performance. There is another interesting test, which is to not use the UDF within the SELECT statement and use a cursor instead. This is an excellent solution for cases when there are only a few records inserted at a time.

 

CREATE TRIGGER "tblCrypt_ITrig" ON tblCrypt

INSTEAD OF INSERT

AS

SET NOCOUNT ON

DECLARE @secret varchar(8000)

DECLARE cur_Itrig CURSOR

FAST_FORWARD -- firehose cursor

FOR SELECT secret FROM INSERTED

OPEN cur_Itrig

FETCH NEXT FROM cur_Itrig INTO @secret

WHILE @@FETCH_STATUS = 0

      BEGIN

      EXEC SPencTEA @secret, 'abc', @secret OUT

      INSERT INTO tblCrypt VALUES (@secret)

      FETCH NEXT FROM cur_Itrig INTO @secret

      END

CLOSE cur_Itrig

DEALLOCATE cur_Itrig

 

Tests encrypting 8000 bytes of data (10,000 records):

 

Function called

Using SELECT (hh:mm:ss)

Using cursor (hh:mm:ss)

UDFencTEA

1:09:48

1:11:57

UDFencTEAXP

8

10

UDFRC4

22:22

23:00

UDFencRC4XP

49

15


Table 25.1 time comparison

 

An interesting fact is that the implementation with UDFencTEAXP is much faster than UDFencTEA, specifically 4188613 / 8243 = 508 times faster!
 

There are many situations like database driven websites or web applications where records are inserted or modified one at a time but there might be a high number of simultaneous connections to the database. The UDF might be much slower than a SP and that could increase locking issues, resulting in severe performance degradation. One solution is to use a SP before modifying the data, if it is known for sure that only one record will be modified. Or a simple IF statement could determine whether to make one call to an SP or to use UDF's or SP's and cursors. This is clear in the following example:

 

CREATE TRIGGER "tblCrypt_ITrig" ON tblCrypt

INSTEAD OF INSERT

AS

SET NOCOUNT ON

IF (SELECT COUNT(secret) FROM INSERTED)=1

 BEGIN

 DECLARE @tmp VARCHAR(8000), @secret VARCHAR(8000)

 SELECT @secret=secret FROM INSERTED

 EXEC SPencTEA @secret, 'abc', @tmp OUT

 UPDATE tblCrypt2 SET secret=@tmp  FROM INSERTED

 END

ELSE

 INSERT tblCrypt(secret)

 SELECT dbo.UDFencTEA(secret, 'abc') FROM INSERTED

 


The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm
 

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter