none
length of encrypted data RRS feed

  • Question

  • i have executed below queries and found length difference which looks surprise for me

    select ENCRYPTBYCERT(cert_ID('CertiID'),'Hello')

    output --0xC69F7CC65282E9284D405B7069F3E1706E3351CFDDE990E6B4996EEF20024AB626AA67A80FC7E66EE046653EDB35C1A1658AA282F8EDD65AB1F6EC5B44D3A5D6BEBEE655CAEAD1A69830E314B31D5178CFFFE5B04963FD6ACA2B2EBF2999FED67BF5C1561CD24F5F4EA8BE3AD80A93846160C5782312388FBEF188DC723B8292


    select len(ENCRYPTBYCERT(cert_ID('CertiID'),'Hello'))

    output --128

    select len('0xC69F7CC65282E9284D405B7069F3E1706E3351CFDDE990E6B4996EEF20024AB626AA67A80FC7E66EE046653EDB35C1A1658AA282F8EDD65AB1F6EC5B44D3A5D6BEBEE655CAEAD1A69830E314B31D5178CFFFE5B04963FD6ACA2B2EBF2999FED67BF5C1561CD24F5F4EA8BE3AD80A93846160C5782312388FBEF188DC723B8292')


    output --258

    what is the reason len function returns different value for both same encrypted values

    Apart from above query, my original question is, when i check length of encrypted value of 'hello' on server A it returns 514 and on server B it returns 518, on what basis this length gets returned while i am using same script of certificate creation and symmetric key creation on both server A and B.


    SQL Server DBA


    • Edited by Zeal DBA Thursday, April 6, 2017 5:37 AM
    Wednesday, April 5, 2017 9:22 AM

All replies

  • Apart from above query, my original question is, when i check length of encrypted value of 'hello' on server A it returns 514 and on server B it returns 518, on what basis this length gets returned while i am using same script of certificate creation and symmetric key creation on both server A and B.


    SQL Server DBA



    • Edited by Zeal DBA Wednesday, April 5, 2017 9:30 AM
    Wednesday, April 5, 2017 9:29 AM
  • On original question, this query should help you understand what you are seeing:

    select len(EncryptByAsymKey(AsymKey_ID('PacificSales09'),'Hello')),SQL_VARIANT_PROPERTY(EncryptByAsymKey(AsymKey_ID('PacificSales09'),'Hello'),'BaseType')
    
    select len('0x98BFBA488F036A6415DB219AF01129FF7980F62C874A00776B9F0D4C81EC75EF4BD8105EDA41644DD396C63425713F789E743C0A78F6A3C95FCA3638C9E538C87D01CAF6E23BC6B913CEB8E7AE5EA4DEE3B274B1537ABB6FE09A6184A4861227CE7E972BB4334BBE70BD165A77D8758471DBC4B7A2E43D4A1C01601A623C1E11'), SQL_VARIANT_PROPERTY('0x98BFBA488F036A6415DB219AF01129FF7980F62C874A00776B9F0D4C81EC75EF4BD8105EDA41644DD396C63425713F789E743C0A78F6A3C95FCA3638C9E538C87D01CAF6E23BC6B913CEB8E7AE5EA4DEE3B274B1537ABB6FE09A6184A4861227CE7E972BB4334BBE70BD165A77D8758471DBC4B7A2E43D4A1C01601A623C1E11','BaseType')

    On second question, i can't say what's going on there, but i'm pretty sure the certificate is encrypted using RSA_4096.


    • Edited by DeLoreanMC Wednesday, April 5, 2017 4:42 PM
    Wednesday, April 5, 2017 4:33 PM
  • The return type from ENCRYPTBYCERT is varbinary, but it is displayed with hexadecimal characters in the output. Each binary character is converted into two hexadecimal characters. That is why SELECT LEN('0x......') is 258 (2 x 128 + 2 (0x)) since '0x......' is treated as a string.

    A Fan of SSIS, SSRS and SSAS

    Wednesday, April 5, 2017 6:18 PM