locked
Is there a way to use a variable to set the ENCRYPTED_VALUE on the COLUMN ENCRYPTION KEY? RRS feed

  • Question

  • Is there a way to use a variable to set the ENCRYPTED_VALUE on the COLUMN ENCRYPTION KEY?

    I'd much rather use a variable that have it hardcoded like this.

    CREATE COLUMN ENCRYPTION KEY [ColumnEncryptionKey1]
    WITH VALUES
    (
    COLUMN_MASTER_KEY = [ColumnMasterKey1],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F003700640066003400610063003700330031003700380064003300360035003200390031006400660039003400330065003000660030003900360035003300640064006500320039003600660063003700A4E3998FA143C159CB3F54B819D1E95827F40122E563B9DCBB855F366B1192979EE73374992A2D074DF7F4B19B0D6BE84D674FDFD574A52C1FD09F375D954B8EA01D63F002D36BF7BBE3AE8D3279EAA189F589F4FE520CF76A0064929E094FC1BDEE91B7C53DAB47F09FA3888C5DF07C210A604329F9F5CEB4495E00F7CD7055993ACA7E1D7D6885553D06312C60396A0DF2449144FFC32008B7636901C2B9EDEC8588BAE8DA079496DDA9BCF8367AE618C3D1F1504559A523379C29C8BA8658972BA3A25F46DDE3FC50CB198A3D3EE24CE92E7739CE8443E01A12BD7ABB58790812EF49AB592D0E537D808E1205C92B00D188EB221740CA8891B43EF58CC1968735F7AE779F5A98DF2BC1B983E16D767BD0926AF4EB6847B7D928D0548D77EC76C935DF37C4FB2E410B4925E26BD52648FABF2ED08DDFFD49B13EE5CEC923A1CA3F222AB7E104E0C0649D0D158DBA33687DC1C6FBBF5573F057CF0ADCEF4033ECB832B57C658127109DB871D83CB7FAD6ADADC05089424EECAA57A27BA41AD71AA17109F710004BFC71FF0E30E826F131FB8A63D0284B5EC33C1FA34AD2982E5613F243F8F916C5A4DF9C46210255580E29DE4FC0DE189A013078D4C6E1BA672CE549F4B52672D2FC06B94B9E7F95BFB2238EF73B0694706AF068FD07A874E4ADAF86DD405F2A68D9ABEA50292824555AD4E319A3B024D189D08E8E43CAD235
    )
    GO


    Tuesday, January 8, 2019 7:27 PM

All replies

  • Dynamic SQL is the only option:

    DECLARE @b varbinary(MAX) = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F003700640066003400610063003700330031003700380064003300360035003200390031006400660039003400330065003000660030003900360035003300640064006500320039003600660063003700A4E3998FA143C159CB3F54B819D1E95827F40122E563B9DCBB855F366B1192979EE73374992A2D074DF7F4B19B0D6BE84D674FDFD574A52C1FD09F375D954B8EA01D63F002D36BF7BBE3AE8D3279EAA189F589F4FE520CF76A0064929E094FC1BDEE91B7C53DAB47F09FA3888C5DF07C210A604329F9F5CEB4495E00F7CD7055993ACA7E1D7D6885553D06312C60396A0DF2449144FFC32008B7636901C2B9EDEC8588BAE8DA079496DDA9BCF8367AE618C3D1F1504559A523379C29C8BA8658972BA3A25F46DDE3FC50CB198A3D3EE24CE92E7739CE8443E01A12BD7ABB58790812EF49AB592D0E537D808E1205C92B00D188EB221740CA8891B43EF58CC1968735F7AE779F5A98DF2BC1B983E16D767BD0926AF4EB6847B7D928D0548D77EC76C935DF37C4FB2E410B4925E26BD52648FABF2ED08DDFFD49B13EE5CEC923A1CA3F222AB7E104E0C0649D0D158DBA33687DC1C6FBBF5573F057CF0ADCEF4033ECB832B57C658127109DB871D83CB7FAD6ADADC05089424EECAA57A27BA41AD71AA17109F710004BFC71FF0E30E826F131FB8A63D0284B5EC33C1FA34AD2982E5613F243F8F916C5A4DF9C46210255580E29DE4FC0DE189A013078D4C6E1BA672CE549F4B52672D2FC06B94B9E7F95BFB2238EF73B0694706AF068FD07A874E4ADAF86DD405F2A68D9ABEA50292824555AD4E319A3B024D189D08E8E43CAD235, 
            @sql nvarchar(MAX)
    SELECT @sql = '
       CREATE COLUMN ENCRYPTION KEY [ColumnEncryptionKey1]
       WITH VALUES
       (
       COLUMN_MASTER_KEY = [ColumnMasterKey1],
       ALGORITHM = ''RSA_OAEP'',
       ENCRYPTED_VALUE = ' + convert(nvarchar(MAX), @b, 1) + ')'
    PRINT @sql
    EXEC (@sql)
    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, January 8, 2019 10:35 PM
  • Dynamic SQL is the only option:

    DECLARE @b varbinary(MAX) = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F003700640066003400610063003700330031003700380064003300360035003200390031006400660039003400330065003000660030003900360035003300640064006500320039003600660063003700A4E3998FA143C159CB3F54B819D1E95827F40122E563B9DCBB855F366B1192979EE73374992A2D074DF7F4B19B0D6BE84D674FDFD574A52C1FD09F375D954B8EA01D63F002D36BF7BBE3AE8D3279EAA189F589F4FE520CF76A0064929E094FC1BDEE91B7C53DAB47F09FA3888C5DF07C210A604329F9F5CEB4495E00F7CD7055993ACA7E1D7D6885553D06312C60396A0DF2449144FFC32008B7636901C2B9EDEC8588BAE8DA079496DDA9BCF8367AE618C3D1F1504559A523379C29C8BA8658972BA3A25F46DDE3FC50CB198A3D3EE24CE92E7739CE8443E01A12BD7ABB58790812EF49AB592D0E537D808E1205C92B00D188EB221740CA8891B43EF58CC1968735F7AE779F5A98DF2BC1B983E16D767BD0926AF4EB6847B7D928D0548D77EC76C935DF37C4FB2E410B4925E26BD52648FABF2ED08DDFFD49B13EE5CEC923A1CA3F222AB7E104E0C0649D0D158DBA33687DC1C6FBBF5573F057CF0ADCEF4033ECB832B57C658127109DB871D83CB7FAD6ADADC05089424EECAA57A27BA41AD71AA17109F710004BFC71FF0E30E826F131FB8A63D0284B5EC33C1FA34AD2982E5613F243F8F916C5A4DF9C46210255580E29DE4FC0DE189A013078D4C6E1BA672CE549F4B52672D2FC06B94B9E7F95BFB2238EF73B0694706AF068FD07A874E4ADAF86DD405F2A68D9ABEA50292824555AD4E319A3B024D189D08E8E43CAD235, 
            @sql nvarchar(MAX)
    SELECT @sql = '
       CREATE COLUMN ENCRYPTION KEY [ColumnEncryptionKey1]
       WITH VALUES
       (
       COLUMN_MASTER_KEY = [ColumnMasterKey1],
       ALGORITHM = ''RSA_OAEP'',
       ENCRYPTED_VALUE = ' + convert(nvarchar(MAX), @b, 1) + ')'
    PRINT @sql
    EXEC (@sql)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    I'm trying to do this in a Visual Studio 2017 deployment script. It doesn't seem to like Dynamic SQL. When I try this type of script it says there's a syntax error near RSA_OAEP. The best alternative we've come up with is not to do it this way. Instead we can use PowerShell to add the column encryption keys then set the encryption on the table columns as needed after deployment. Which is less desirable than just doing it in the deployment step but a workable work around for now.


    Tuesday, January 8, 2019 10:54 PM
  • Hi Matthew LaCrosse,

    >>I'm trying to do this in a Visual Studio 2017 deployment script. It doesn't seem to like Dynamic SQL.

    How did you deploy this script? Based on my test, it can work normally on my machine, what's the result of the "PRINT @sql" in above query?

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 9, 2019 7:44 AM
  • Hi Matthew LaCrosse,

    >>I'm trying to do this in a Visual Studio 2017 deployment script. It doesn't seem to like Dynamic SQL.

    How did you deploy this script? Based on my test, it can work normally on my machine, what's the result of the "PRINT @sql" in above query?

    Best Regards,

    Teige


    Deploying with the PS script? It's part of a bigger deployment process that I can't go into detail on here.

    The problem with VS2017 seemed to happen with trying to pass in the encrypted value using a SqlCmdVariable. That's when I got the error. If I print the dynamic SQL and copy/past it to SSMS it works.



    Wednesday, January 9, 2019 2:09 PM
  • It's not easy to reproduce and troubleshoot this problem if we don't have the sample. Can you share some part of the code to help us reproduce this problem?

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 11, 2019 2:26 AM