none
TestQuestion RRS feed

Answers

  • test reply
    No Signaturesss!
    • Marked as answer by VamcAlerts Friday, February 11, 2011 12:45 AM
    Friday, February 11, 2011 12:45 AM

All replies

  • test edit 4
    • Edited by Brent Serbus Thursday, February 3, 2011 1:25 AM adsadf
    Friday, November 26, 2010 6:38 AM
  • trtrtrt  asdfa fads fadsfad
    No Signaturesss!
    Monday, January 31, 2011 8:01 PM
  • test tst tst
    Monday, January 31, 2011 10:28 PM
  • t2k2 2k2k2
    Tuesday, February 1, 2011 7:01 PM
  • test reply  4
    hello
    Wednesday, February 2, 2011 12:22 AM
  • test reply
    No Signaturesss!
    • Marked as answer by VamcAlerts Friday, February 11, 2011 12:45 AM
    Friday, February 11, 2011 12:45 AM
  • Test Question

     testttete

     

     


    No Signaturess!


    No Signaturesss!
    Friday, February 11, 2011 1:08 AM
  • Test Question
    Thursday, February 17, 2011 7:13 PM
  • testttt
    No Signaturesss!
    Friday, February 18, 2011 12:11 AM
  • test e


    No Signaturesss!
    Friday, February 18, 2011 12:11 AM
  • any reply
    Friday, February 18, 2011 12:22 AM
  • any reply
    Friday, February 18, 2011 12:23 AM
  • Test the reply
    Friday, February 18, 2011 1:38 AM
  • test console.write("hi");
    shanthi
    Friday, February 18, 2011 2:35 AM
    Moderator
  • Quick Check
    Test
    Friday, February 18, 2011 6:37 PM
  • Re check.
    Friday, February 18, 2011 8:27 PM
  • We created a DB on a specific server with the following script for creating and managing encypted fields

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>'
    CREATE CERTIFICATE MyCert AUTHORIZATION MyUser WITH SUBJECT = 'Encryption'
    CREATE SYMMETRIC KEY My_DES_Key WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE MyCert
    GRANT VIEW definition ON symmetric key::My_DES_Key to MyRole

    CREATE PROCEDURE [dbo].[proc_MyTable_Update]
     @ID int,
     @Pwd nvarchar(255),
    AS
     OPEN SYMMETRIC KEY My_DES_Key DECRYPTION BY CERTIFICATE MyCert;
        Update MyTable
        SET Pwd=EncryptByKey(Key_GUID(''My_DES_Key''), @Pwd) 
     WHERE ID = @ID 

    Now, I've backed up the DB from the original server and restored it onto a new server. Having read through various forums I carried out the following on the new server

    OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>'
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

    However, the following

    OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>'
    OPEN SYMMETRIC KEY My_DES_Key DECRYPTION BY CERTIFICATE MyCert;
    SELECT CONVERT(nvarchar, DecryptByKey(Pwd)) as Pwd
    FROM MyTable

    returns all 'null' values.

    What am I missing?


    Community Forums Program Manager
    Friday, February 18, 2011 11:14 PM
  • Hi,

    This is what I did.

    1) Created a database named as TEST.

    2) Created a SQL LOGIN named as TEST and make it as DB_OWNER for database TEST.

    3) Created a ROLE and grant SELECT permissions to user TEST on database TEST.

    4) Ran below code:

    CREATE TABLE MyTable (ID Int, PWD nvarchar(255))
    Insert into MyTable (ID) values (1)
    Insert into MyTable (ID) values (2)
    Insert into MyTable (ID) values (3)
    
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TEST123456'
    CREATE CERTIFICATE MyCert AUTHORIZATION test WITH SUBJECT = 'Encryption'
    CREATE SYMMETRIC KEY My_DES_Key WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE MyCert
    GRANT VIEW definition ON symmetric key::My_DES_Key to testrole
    
    alter PROCEDURE [dbo].[proc_MyTable_Update]
     @ID int,
     @Pwd nvarchar(255)
    AS
     OPEN SYMMETRIC KEY My_DES_Key DECRYPTION BY CERTIFICATE MyCert;
     Update MyTable
     SET Pwd=EncryptByKey(Key_GUID('My_DES_Key'), @Pwd) 
     WHERE ID = @ID 
    
    exec proc_MyTable_Upgrade 1,'Hi buddy'
    exec proc_MyTable_Upgrade 2,'this is good'
    exec proc_MyTable_Upgrade 3,'this is bad'
    
    select * from MyTable
    
    --Output
    
    id   pwd
    ----------- ---------------------------------
    1   촀広‎䱥⊲舊详 瞥�愄ﻎ泪䅒ㆮ錾ᑳ蕷瓖᫭礰㿌䓉八宽
    2   촀広‎䱥⊲舊详 檪�㹧脉菻釥辋᭛Ɵ墬慼冘邒蛏�斷猐움ṧᩡ௶蠪Ր
    3   촀広‎䱥⊲舊详 巈Ꙥ淎�ꎖ૚⁓胮�蟏맱췳ᝇ깶훐脫옑釢▘츶
    
    
    

    5) Then I took backup of this database and moved to another instance of sql.

    6) I restored this backup and then run below commands (as you posted)

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'TEST123456'
    OPEN SYMMETRIC KEY My_DES_Key DECRYPTION BY CERTIFICATE MyCert;
    SELECT CONVERT(nvarchar, DecryptByKey(Pwd)) as Pwd
    FROM MyTable
    
    --Output
    
    Pwd
    ------------------------------
    Hi buddy
    this is good
    this is bad
    
    (3 row(s) affected)
    
    
    

    So basically I am not able to repro this thing. Only thing that is coming to me after going thru your problem description is that it is missing execution of procedure to encrypt the column (this is what I am able to make from the statement but I am sure you must have executed this procedure).

    HTH


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    Friday, February 18, 2011 11:15 PM
  • replied ....
    Regards,
    Saturday, February 19, 2011 7:16 AM
  • test march
    No Signaturesss!
    Thursday, March 17, 2011 10:38 PM
  • Quick test on 17th March 2011
    My Signature
    public class test
    {
    }
    Thursday, March 17, 2011 11:02 PM
  • st velocity experiences: two architectural and a performance ...
    Local Cache Sync
    Access Velocity cache from unmanaged code -- using CLR stored ...
    Comparing Performance of Local Cache with Dictionary<T, K>
    SqlCacheDependency Support
    My Signature
    Thursday, March 17, 2011 11:04 PM
  • wowww
    • Proposed as answer by JamesbRaja Monday, October 24, 2011 11:11 PM
    Monday, October 24, 2011 11:11 PM