SQL Server - encryption and decryption - Asked By abhijit sen on 23-Nov-11 07:16 AM

i have encrypted and decrypted arround 1400000 row of a table with algorithm aes_256 i want it to change it in blowfish 
the query i used to create symmetric key is
 CREATE SYMMETRIC KEY key_Encryption WITH ALGORITHM = AES_256
 


      ENCRYPTION BY CERTIFICATE cert_ProtectEncryptionKeys
please help how to change the algorithm 
dipa ahuja replied to abhijit sen on 23-Nov-11 07:21 AM
You can use the ENCRYPT function to encrypt data with the same method used by the WITH ENCRYPTION keyword. There's a rather large problem that I will discuss after the example. To use the ENCRYPT function, use it before the string value as shown below:
SELECT ENCRYPT('TestPW1')
CREATE TABLE Users (
UserID Varchar(10),
UserPW Varchar (20))

INSERT INTO USERS values('TestUser1',ENCRYPT('TestPW1'))
INSERT INTO USERS values('TestUser2',ENCRYPT('TestPW2'))
INSERT INTO USERS values('TestUser3',ENCRYPT('TestPW3'))
INSERT INTO USERS values('TestUser4',ENCRYPT('TestPW4'))


http://msdn.microsoft.com/en-us/library/cc278098(v=sql.100).aspx
http://www.sqlservercentral.com/articles/Security/encryptfunction/372/
abhijit sen replied to dipa ahuja on 23-Nov-11 07:27 AM
thx for replying 
but i want to just change the algorithm i.e AES_256 to blowfish 
as i came to know that blowfish is faster then AES 
i hav completed the process of encryption and decryption but i m stuck  with the performance issue as the queries are running too slow.
i think if i change the algorithm it will help can u just give me the query to use blowfish
i m using sql2005 in windows xp 
Devil Scorpio replied to abhijit sen on 23-Nov-11 07:41 AM
Hi,

SQL SERVER can use the following algorithms in encryption:

DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192 AND AES_256.

Now create a table …

-- Create a Sample Table
CREATE TABLE USERS (UID INT IDENTITY(100,1),UFNAME VARCHAR(100),ULNAME VARCHAR(100),ULOGINDI VARCHAR(50),UPASSWORD VARBINARY(256))

So now its turn to insert data in USERS table and we encrypt the UPASSWORD field using our symmetric key this can be done by a Store Procedure so we create a Insert Procedure for this.. so here is the procedure….

-- Create a Procedure to Insert Data in Table
CREATE PROC [InsertUSER]
  @UFNAME VARCHAR(100),
  @ULNAME VARCHAR(100),
  @ULOGINDI VARCHAR(12),
  @UPASSWORD VARCHAR(20)
AS
BEGIN
-- you must open the key as it is not already
    OPEN SYMMETRIC KEY TESTKEY 
        DECRYPTION BY CERTIFICATE ENCDECTEST;
    
-- Insert statement
    INSERT INTO [USERS] 
    (UFNAME, ULNAME, ULOGINDI, UPASSWORD) 
    VALUES 
    (@UFNAME, @ULNAME, @ULOGINDI, 
     EncryptByKey(Key_GUID('TESTKEY'), @UPASSWORD));
     
END;

Refer the following website for more info
http://beyondrelational.com/blogs/ashish/archive/2009/05/30/encryption-amp-decryption-in-sql-server-2005.aspx 
Anil Kumar replied to abhijit sen on 23-Nov-11 07:54 AM
Hi Abhijit,

It should suffice to say whether bcrypt or SHA-512 is good enough. And the answer is yes, either algorithm is secure enough that a breach will occur through an implementation flaw, not cryptanalysis.

If you insist on knowing which is "better", SHA-512 has had in-depth by NIST and others. It's good, but flaws have been recognized that, while not exploitable now, have led to the the SHA-3 competition for new hash algorithms. Also, keep in mind that the study of hash algorithms is "newer" than that of ciphers, and cryptographers are still learning about them.

Even though bcrypt as a whole hasn't had as much scrutiny as Blowfish itself, I believe that being based on a cipher with a well-understood structure gives it some inherent security that hash-based authentication lacks.

bcrypt is an algorithm that uses Blowfish internally. It is not an encryption algorithm itself. It is used to irreversibly obscure passwords, just as hash functions are used to do a "one-way hash".

Hope it serves you better.
Thank you
Anil

abhijit sen replied to Devil Scorpio on 23-Nov-11 08:03 AM
thank you 

Devil Scorpio

as your reply suggested i will not be able to use blowfish 
i m using this processas given bellow   
use master
 
CREATE DATABASE db_Demo
 
go
 
USE db_Demo
 
go
 
CREATE TABLE t_Customer(
 
    SSN nvarchar(20) PRIMARY KEY,
 
    name nvarchar(200)
 
    NOT NULL,
 
    Status int NOT NULL,
 
    ExtraData nvarchar(100) )
 
go
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Yukon900'
 
go
 
  
 
CREATE CERTIFICATE cert_ProtectEncryptionKeys WITH SUBJECT = 'Data encryption key protection'
 
go
 
  
 
CREATE SYMMETRIC KEY key_Encryption WITH ALGORITHM =
    ENCRYPTION BY CERTIFICATE cert_ProtectEncryptionKeys
 
go
 
  
 
CREATE CERTIFICATE cert_ProtectIndexingKeys WITH SUBJECT = 'Data indexing key protection'
 
go
 
  
 
 
CREATE SYMMETRIC KEY key_Indexing WITH ALGORITHM =AES_256
 
    ENCRYPTION BY CERTIFICATE cert_ProtectIndexingKeys
 
go
 
 
CREATE TABLE t_MacIndexKeys( table_id int PRIMARY KEY, Mac_key varbinary(100) not null )
 
go
 
  
 
  
 
CREATE FUNCTION MAC( @Message nvarchar(4000), @Table_id int )
 
RETURNS varbinary(24)
 
WITH EXECUTE AS 'dbo'
 
AS
 
BEGIN
 
    declare @RetVal varbinary(24)
 
    declare @Key    varbinary(100)
 
    SET @RetVal = null
 
    SET @key    = null
 
    SELECT @Key = DecryptByKeyAutoCert( cert_id('cert_ProtectIndexingKeys'), null, Mac_key) FROM t_MacIndexKeys WHERE table_id = @Table_id
 
    if( @Key is not null )
 
      SELECT @RetVal = HashBytes( N'SHA1', convert(varbinary(8000), @Message) + @Key )
 
    RETURN @RetVal
 
END
 
go
 
  
 
CREATE PROC AddMacForTable @Table_id int
 
WITH EXECUTE AS 'dbo'
 
AS
 
    declare @Key     varbinary(100)
 
    declare @KeyGuid uniqueidentifier
 
    SET @KeyGuid = key_guid('key_Indexing')
 
    OPEN SYMMETRIC KEY key_Indexing DECRYPTION BY CERTIFICATE cert_ProtectIndexingKeys
 
    SELECT @Key = HashBytes( N'SHA1', ENCRYPTBYKEY( @KeyGuid, convert(varbinary(100), newid())) )
 
    SET @KEY = ENCRYPTBYKEY( @KeyGuid, @Key )
 
    CLOSE SYMMETRIC KEY key_Indexing
 
    if @Key is null
 
    BEGIN
 
      RAISERROR( 'Failed to create new key.', 16, 1)
 
    END
 
    INSERT INTO t_MacIndexKeys VALUES( @Table_id, @Key )
 
go
 
 CREATE TABLE dbo.t_CustomerRaw(
 
  SSN_index    varbinary(20) PRIMARY KEY,  
 
    SSN_cipher   nvarchar(60) NOT NULL,
 
    Name_cipher nvarchar(300),
 
    Status int,
 
    ExtraData nvarchar(100) )
 
go
 
  
 
declare @objid int
 
SET @objid = object_id('t_CustomerRaw')
 
EXEC AddMacForTable @objid
 
go
 
  
 
CREATE TRIGGER trig_ProtectSSN on t_CustomerRaw
 
INSTEAD OF INSERT
 
AS
 
    declare @Index varbinary(24)
 
    declare @KeyGuid uniqueidentifier
 
    declare @Cipher nvarchar(60)
 
  
 
    if( select count(*) from inserted where SSN_cipher is null ) > 0
 
      RAISERROR( 'Cannot store null as protected data. ', 16, 1)
 
    ELSE
 
      BEGIN
 
      SET @KeyGuid = key_guid('key_Encryption')
 
      SELECT @Index = dbo.MAC( SSN_cipher,
 
object_id('t_CustomerRaw') ) from inserted
 
  
 
      if( @Index is null
 
 OR @KeyGuid is null
 
 OR encryptbykey( key_guid('key_Encryption'), 0x00)
 
   is null )
 
          BEGIN
 
          RAISERROR( 'Cannot Insert protected data. Either the encryption or indexing keys are not available or the indexing key is not valid for MAC generation.', 16, 1)
 
          END
 
      ELSE
 
          INSERT INTO dbo.t_CustomerRaw select
 
          @Index,
 
          encryptbykey( key_guid('key_Encryption'), SSN_cipher, 1, @Index ),
 
          encryptbykey( key_guid('key_Encryption'), Name_cipher, 1, @Index ),
 
          Status, Extradata
 
          from inserted
 
    END
 
go
 
  
CREATE TRIGGER trig_ProtectSSNUpdate on t_CustomerRaw
 
INSTEAD OF UPDATE
 
AS
 
    if( COLUMNS_UPDATED() & 3 ) > 0
 
      raiserror( 'Cannot update protected columns. Drop the row and create a new one with the updated information.', 16, 1 )
 
    ELSE
 
      BEGIN
 
      SET NOCOUNT ON
 
      if( COLUMNS_UPDATED() & 4 ) > 0
 
      BEGIN
 
          if( encryptbykey( key_guid('key_Encryption'), 0x00) is null )
 
          BEGIN
 
            RAISERROR( 'Cannot Insert protected data. The encryption or indexing keys are not available.', 16, 1)
 
          END
 
          ELSE
 
            UPDATE t_CustomerRaw SET Name_cipher = encryptbykey( key_guid('key_Encryption'), ins.Name_cipher, 1, ins.SSN_index )
 
                FROM inserted ins, t_CustomerRaw orig
 
                WHERE ins.SSN_index = orig.SSN_index
 
      END
 
      UPDATE t_CustomerRaw
 
SET Status = ins.Status, ExtraData = ins.ExtraData
 
FROM inserted ins, t_CustomerRaw orig
 
WHERE ins.SSN_index = orig.SSN_index
 
      END
 
go
 
  
 
 
OPEN SYMMETRIC KEY key_Encryption DECRYPTION BY CERTIFICATE cert_ProtectEncryptionKeys
 
go
 
SET NOCOUNT ON
 
 
DECLARE @SSN nvarchar(15)
 
DECLARE @Name nvarchar(200)
 
DECLARE @Status int
 
DECLARE @ExtraData nvarchar(100)
 
DECLARE curs_Customer CURSOR FOR SELECT SSN, Name, Status, ExtraData FROM t_Customer
 
OPEN curs_Customer
 
FETCH NEXT FROM curs_Customer INTO @SSN, @Name, @Status, @ExtraData
 
  
 
WHILE @@FETCH_STATUS = 0
 
BEGIN
 
    INSERT INTO t_CustomerRaw VALUES(  null, @SSN, @Name, @Status, @ExtraData )
 
    FETCH NEXT FROM curs_Customer INTO @SSN, @Name, @Status, @ExtraData
 
END
 
CLOSE curs_Customer
 
DEALLOCATE curs_Customer
 
go
 
 select * from t_CustomerRaw
SELECT
 
  convert( nvarchar(15), DecryptbyKeyAutoCert(
 
      cert_id('cert_ProtectEncryptionKeys'), null,
 
      SSN_cipher, 1, SSN_index)) as SSN,
 
  convert( nvarchar(200), DecryptbyKeyAutoCert(
 
      cert_id('cert_ProtectEncryptionKeys'), null,
 
      Name_cipher, 1, SSN_index)) as Name,
 
 
    Status, ExtraData FROM dbo.t_CustomerRaw ORDER BY SSN
 
go
  
 
CLOSE SYMMETRIC KEY key_Encryption
 
go
 
select * from t_CustomerRaw
DROP TABLE t_Customer
 
go
 
CREATE VIEW t_Customer
 
WITH SCHEMABINDING
 
AS
 
SELECT 
 
  SSN_index,
 
    convert( nvarchar(15), DecryptbyKeyAutoCert(
 
      cert_id('cert_ProtectEncryptionKeys'), null,
 
      SSN_cipher, 1, SSN_index)) as SSN,
 
  convert( nvarchar(200), DecryptbyKeyAutoCert(
 
      cert_id('cert_ProtectEncryptionKeys'), null,
 
      Name_cipher, 1, SSN_index)) as Name,
 
    Status, ExtraData FROM dbo.t_CustomerRaw
 
go
 
CREATE TRIGGER trig_CustomerDataEx_ins on t_Customer
 
INSTEAD OF INSERT
 
AS
 
SET NOCOUNT ON
 
INSERT INTO dbo.t_CustomerRaw select null, SSN, Name, Status, Extradata from inserted
 
go
 
  
CREATE TRIGGER trig_CustomerDataEx_upg on t_Customer
 
INSTEAD OF UPDATE
 
AS
 
SET NOCOUNT ON
 
UPDATE dbo.t_CustomerRaw SET
 
    Name_cipher = ins.Name,
 
    Status = ins.Status,
 
    Extradata = ins.Extradata
 
    FROM inserted
 
    left outer join t_CustomerRaw raw ON raw.SSN_index = dbo.MAC( ins.SSN, object_id('t_CustomerRaw') )
 
    WHERE raw.SSN_index = dbo.MAC( ins.SSN, object_id('t_CustomerRaw') )
 
go
 
OPEN SYMMETRIC KEY key_Encryption DECRYPTION BY CERTIFICATE cert_ProtectEncryptionKeys
 
go
 
  
 
INSERT INTO v_CustomerDataEx VALUES ( null, N'555-55-5555', N'User 5555',1, N'Extra Data ' )
 
go
 
UPDATE v_CustomerDataEx SET
 
    SSN_index = null,
 
    SSN = N'555-55-5555',
 
    Name = N'New User 5555',
 
    Status = 0,
 
    ExtraData = N'Extra Data2'
 
    WHERE SSN_index = dbo.MAC( N'555-55-5555', object_id('t_CustomerRaw') )
 
go
 
SELECT * FROM v_CustomerDataEx WHERE SSN = N'555-55-5555'
 
go
 
DELETE v_CustomerDataEx WHERE SSN = N'555-55-5555'
 
go
 
SELECT * FROM v_CustomerDataEx WHERE SSN = N'555-55-5555'
 
go
 
CLOSE SYMMETRIC KEY key_Encryption
 
go
 
  
use db_Demo
SELECT * FROM v_CustomerDataEx
 
go
 
-------------------------------------------------------
 
-----------------------------------------------------
use db_demo
select * from v_CustomerDataEx
but when i alter the code in my database having data more then 1400000 rows each and every query slow down so i need to do performance turning. for what i m looking into some alternative for big database 
i know that encrypting entire database will slow down but it is the requirement and delay can't be accepted above 10 sec but now i m having above 2 min 
suggest me what to do 
abhijit sen replied to Anil Kumar on 23-Nov-11 08:21 AM
thank you Anil 
for giving such a well defined understanding of all this.
As a dba  i was asked to encrypt the entire database at database label so i had only 2 option either going for TDE or Cell level encryption. 
As TDE is not accepted so i went for Cell level encryption where i found this is the best technique and the fastest one but when i came down to performance of the business logic queries i was shocked with the execution time it takes or if we execute the
select * from tablename 
for table having more then 1400000 rows and 27 columns

Please help me in performance tuning  of my database or give me some better option .

thanking you
Reena Jain replied to abhijit sen on 23-Nov-11 09:25 AM
hi,

SQL server  allows you to encrypt data using different algorithms using symmetric and asymmetric keys. Alternatively, you can also use password-based encryption (that password must be supplied by the client to encrypt/decrypt data).
refer this for more details
http://www.sqlsolutions.com/articles/articles/Encrypted_Columns_and_SQL_Server_Performance.htm
http://msdn.microsoft.com/en-us/library/ms179331.aspx
abhijit sen replied to Reena Jain on 23-Nov-11 09:35 AM

thank you
 Reena Jain
as ur link suggest i tried by using asymmetric key too but while working with such a large database it gives same performance now as i have went through all the methods and process i came to a conclusion that by any means the process will be same but the time the server it taking to encrypt and decrypt the data must be reduced  so to hav much better performance can you suggest me anything for that 

Reena Jain replied to abhijit sen on 23-Nov-11 09:41 AM
Hi,

You can encrypt the data from front end application and then save this data in database so it will give better performance. You have not provide the application detail so i guess you are using c#.net and here is the code for encryption
public string base64Encode(string sData)
  {
  try
  {
    byte[] encData_byte = new byte[sData.Length];
 
    encData_byte = System.Text.Encoding.UTF8.GetBytes(sData);
 
    string encodedData = Convert.ToBase64String(encData_byte);
 
    return encodedData;
 
  }
  catch (Exception ex)
  {
    //ClsCommon.Error = 4;
    //ClsCommon.ErrorMsg = ex.Message;
    //FrmError obj = new FrmError();
    //obj.ShowDialog();
    throw new Exception("Error in base64Encode" + ex.Message);
  }
  }
for decryption
public string base64Decode(string sData)
  {
 
    System.Text.UTF8Encoding encoder = new System.Text.UTF8Encoding();
 
    System.Text.Decoder utf8Decode = encoder.GetDecoder();
 
    byte[] todecode_byte = Convert.FromBase64String(sData);
 
    int charCount = utf8Decode.GetCharCount(todecode_byte, 0, todecode_byte.Length);
 
    char[] decoded_char = new char[charCount];
 
    utf8Decode.GetChars(todecode_byte, 0, todecode_byte.Length, decoded_char, 0);
 
    string result = new String(decoded_char);
 
    return result;
 
  }
abhijit sen replied to Reena Jain on 23-Nov-11 09:50 AM
thank you 

 Reena Jain

we are using C# .net but the problem as mentioned earlier is that the application end encryption is not possible in our case they want database label only i.e entire dataset must be in encrypted form as i have kept in the form of RAW Table and when the application want they must get the decrypted data for that i have used the view to optimize the time i used 2 triggers one for update and another for insertion but they 2 doesn't help when we look into such a big table 

I think we must use some kind of store procedure  or trigger so that the time consuming to decrypt the records can be reduced  and served to the application.

Anil Kumar replied to abhijit sen on 24-Nov-11 12:02 AM
Hi Abhijit,

The SQL Encryption Toolkit includes the xp_base64encode and xp_base64decode extended stored procedures to convert messages to and from Base64. The format for using these xp's is:
EXEC master..xp_base64encode @plaintext [, @encodedtext OUTPUT]
EXEC master..xp_base64decode @encodedtext [, @plaintext OUTPUT]
 For further details about the procedure read the article:
http://www.sqlservercentral.com/articles/Security/freeencryption/1980/

Do update us again.
Thank you
Anil
abhijit sen replied to Anil Kumar on 24-Nov-11 07:42 AM
thanks 

 Anil Kumar


I tryed with this too but thing is that it only works on  CHAR and VARCHAR variables/columns so it is of now use can u just look into the last procedure and the 2 trigger that i have used and say can i modify or optimize it more so that it works faster. To describe the process in detail let me say the entire process step by step what i have done here

1st i created 1 master key 2 certificate 2 symmetric key (1 certificate and 1symmetric key is used to encrypt and decrypt the index key and 1 certificate and symmetric key to encrypt the database)


Then we have to cerate MAC index table.Then a function named MAC , it is used for decryption of mac key.After that a  procedure is created, this will create a mac-key in mac-key index. It will take all the table with table id and generate the encrypted MAC KEY

The Raw table with the master task should be created now this stores the encrypted data.Then we generating the master table key.then creating the insert trigger in row table,creating update trigger on row table,Fetching the data from master table to raw table. Now we drop the master table

Atlast we creating a view of the same name of the master table for accessing data from row table and we will have index on Mac column, on that we creating the insert trigger which give a index on the main data so that it can be executed faster.then we creating the update trigger on this view

Please check the code that i have posted above. and suggested me about any performance tuning that i can work on it 
 i thought of changing in sql query changed in where clause as we will convert the sql where condition statement into encrypted form n check into the row table but thing is dat in certain cases the application want entire tables in hand that will again cause error