Post reply

Cell Level Encryption/Decryption

  • August 1, 2011 at 2:48 pm

    #255142

    Hi Folks,

    I wanted to check if someone has any functions or stored procedure that I can use to encrypt and decrypt column data in SQL Server 2000 and SQL Server 2005.

    I want to use stored procedures or functions or even triggers to automate the encryption and decryption process. I am planning to use symmetric encryption With Certificate.

    Thanks a lot for your time and help.

    Thanks,

    Laura

  • August 1, 2011 at 7:07 pm

    #1361923

    bumping it up...

  • August 1, 2011 at 8:43 pm

    #1361939

    Laura in SQL 2000, there was no built in encryption, you had to use extended procedures.

    When i was using SQL2000, i used the dba toolkit from SQL Server Central:

    http://www.sqlservercentral.com/articles/Advanced+Querying/sql2000dbatoolkitpart2/2362/

    you could install the same extended procs in SQL2005, but i don't think they are allowed anymore in 2008 and above...you'd have to check on that yourself.

    any chance you can upgrade your 2000 servers so you can use the built in stuff they added in 2005 and above?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 1, 2011 at 8:55 pm

    #1361941

    try this laura:

    http://sqlserverpedia.com/blog/sql-server-bloggers/sql-server-encryption-for-express-or-standard/

  • August 1, 2011 at 9:02 pm

    #1361942

    Thanks lowell for your response. Actually I will be doing this on SQL 2005 and SQL 2008. I do not have to implement that on SQL 2000 as we will be moving that particular DB to SQL 2005 or SQL 2008 instance.

  • August 1, 2011 at 9:18 pm

    #1361946

    these two links are my favorite introductions to 2005 and up encryption; i found the article very easy to follow:

    http://www.databasejournal.com/features/mssql/article.php/3714031/SQL-Server-2005-Encryption-types.htm

    http://www.databasejournal.com/features/mssql/article.php/3483931/SQL-Server-2005-Security---Part-3-Encryption.htm

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 2, 2011 at 5:48 am

    #1362063

    Thanks lowell. Have you used symmetric encryption with certificate? What are the db objects I need to create to facilitate that? Thanks!

    Laura

    Lowell (8/1/2011)

    these two links are my favorite introductions to 2005 and up encryption; i found the article very easy to follow:

    http://www.databasejournal.com/features/mssql/article.php/3714031/SQL-Server-2005-Encryption-types.htm

    http://www.databasejournal.com/features/mssql/article.php/3483931/SQL-Server-2005-Security---Part-3-Encryption.htm

  • August 2, 2011 at 5:59 am

    #1362066

    Laura those articles go into some really good detail on how to use encryption, and do a better job than i can at explaining .

    As for database changes, it depends on the encryption technique you decide to use...some require NVARCHAR columns, others VARBINARY for the storage of the encrypted data...it depends on your decision.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 2, 2011 at 6:14 am

    #1362081

    In sql Sever 2005 onword U can use passpharse method to encrypt/decrypt the string if you do'nt want to maintian the master key

    DECLARE @passPhrase NVARCHAR(50),

    @encryptedPass VARBINARY(128),

    @decryptedPass VARCHAR(128);

    --Column datatype should be VARBINARY for this method

    SET @passPhrase='Srikant';

    -- Encrypt the String

    SET @encryptedPass=Encryptbypassphrase(@passPhrase, 'XYZ')

    SELECT @encryptedPass

    --Decrypt the string

    SET @decryptedPass=Decryptbypassphrase(@passPhrase, @encryptedPass);

    SELECT @decryptedPass

  • August 2, 2011 at 7:35 am

    #1362147

    Thanks Srikant have you used this method? What are the different db objects that I need to create?

    Thanks,

    Laura

  • August 2, 2011 at 7:50 am

    #1362155

    You have create two function One for

    Encrypt(F_EncryptString) the String and another decrypt (F_decryptString) the string.In both function you have to use same passphrase string.

  • August 2, 2011 at 7:55 am

    #1362162

    --supposed we have a table

    CREATE TABLE WebUsers (

    UserName varchar(100),

    UserEmail varchar(100),

    UserPass varchar(100)

    )

    --and some data

    Insert into WebUsers

    SELECT 'Lowell','lowell@somedomain.com','NotMyRealPassword' UNION ALL

    SELECT 'Calvin','Calvin@somedomain.com','G.R.O.S.S.' UNION ALL

    SELECT 'Hobbes','Hobbes@somedomain.com','Calvins Friend'

    --now we want to encrypt the password so nobody can really see it.

    --with Encrypt by passphrase, we need a varbinary field twice the size of the encrypted string.

    ALTER TABLE WebUsers add EncryptedPass varbinary(200)

    --http://www.databasejournal.com/features/mssql/article.php/3714031/SQL-Server-2005-Encryption-types.htm

    -- EncryptByPassPhrase(@password,@DataToEncrypt )

    UPDATE WebUsers

    SET EncryptedPass = EncryptByPassPhrase('MyPassPhrase', UserPass )

    --peek:

    SELECT * FROM WebUsers

    -- DecryptByPassPhrase(@password,@DataToDecrypt )

    --can we get teh password back correctly?

    SELECT

    UserName,

    UserEmail,

    convert(varchar(100),DecryptByPassPhrase('MyPassPhrase',EncryptedPass))

    FROM WebUsers

    --we are good, lets drop that unencrypted data:

    ALTER TABLE WebUsers DROP COLUMN UserPass

    --now the passwordd is *relatively* secure, but someone might find out the PassPhrase...which is why a certificate is more secure.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 2, 2011 at 8:07 am

    #1362183

    thanks guys. I found this encrypting using ASCII characters, but I don't think this adds any security...

    http://www.sql-server-helper.com/functions/string-encryption.aspx

  • August 2, 2011 at 10:02 am

    #1362301

    when creating Symmetric key with Certification do I have to create certificate for every users who will access the data column?

  • August 2, 2011 at 12:52 pm

    #1362508

    Laura_SqlNovice (8/2/2011)

    when creating Symmetric key with Certification do I have to create certificate for every users who will access the data column?

    nope...typically you create one cert and use it everywhere....if you have something that needed a seperate cert, like some super secret project, you might have multiple certs.

    typically it's lord of the rings style:

    one cert to rule them all

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply