Denis

My feedback

  1. 30 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  12 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Denis commented  · 

    following is an example of encrypting simple string '123' and decrypting it back with the same symmetric key on different SQL server versions:

    1. Create a Master Key:
    CREATE MASTER KEY ENCRYPTION BY
    PASSWORD = 'My p@55w0Rd'
    CREATE CERTIFICATE TEST_Certificate
    WITH SUBJECT = 'Key Protection'
    CREATE SYMMETRIC KEY TEST_KEY WITH
    KEY_SOURCE = 'My key generation bits. This is a shared secret!',
    ALGORITHM = AES_256,
    IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'
    ENCRYPTION BY CERTIFICATE TEST_Certificate

    2. Ecnrypting a string '123' on SQL Server 2016 returns the output: '0x00D1CCE9771AE7554D479F7B93A456110100000013F8665AAE4A1AD1922171D19ED08B4422DB2C7B626045EAABF885378B4BDCC4'

    declare @script varchar(8000)
    declare @enc varbinary(8000)
    declare @enc_varchar varchar(8000)

    -- Open the symmetric key with which to encrypt the data.
    OPEN SYMMETRIC KEY TEST_KEY
    DECRYPTION BY CERTIFICATE TEST_Certificate

    declare @key_guid uniqueidentifier = KEY_GUID('TEST_KEY')
    -- Encrypt
    SET @script='123'
    set @enc = ENCRYPTBYKEY(@key_guid,@script)
    set @enc_varchar = CONVERT(varchar(8000),@enc,1)
    SELECT @enc
    SELECT @enc_varchar

    3. Decrypting the encrypted string back '0x00D1CCE9771AE7554D479F7B93A456110100000013F8665AAE4A1AD1922171D19ED08B4422DB2C7B626045EAABF885378B4BDCC4'
    • SQL Server 2016 / SQL Server 2014 / SQL Server 2012 returns original string '123' back
    • SQL Server 2017 returns NULL

    declare @dec varbinary(8000)
    declare @dec_varchar varchar(8000)
    declare @enc_varchar varchar(8000)

    -- Open the symmetric key with which to decrypt the data.
    OPEN SYMMETRIC KEY TEST_KEY
    DECRYPTION BY CERTIFICATE TEST_Certificate
    declare @key_guid uniqueidentifier = KEY_GUID('TEST_KEY')
    -- Decrypt
    set @dec_varchar='0x00D1CCE9771AE7554D479F7B93A456110100000013F8665AAE4A1AD1922171D19ED08B4422DB2C7B626045EAABF885378B4BDCC4'
    set @dec = CONVERT(varbinary(8000),@dec_varchar,1)
    set @enc_varchar = CONVERT(varchar(8000),DECRYPTBYKEY(@dec))
    SELECT @enc_varchar

    NB: encrypting '123' on SQL Server 2017 returns a similar output '0x00D1CCE9771AE7554D479F7B93A4561102000000917D28D28AC21BD875D117DBA83F0448C25751947845B19033F1AD29C219B076' BUT this is not decryptable on any other server except SQL Server 2017.

    Denis shared this idea  · 

Feedback and Knowledge Base