locked
SQL Always Encrypted - New-SqlColumnEncryptionKeyEncryptedValue ERROR RRS feed

  • Question

  • SQL Always Encrypted for CNG Key Provisioning With Role Separation must be executed from Powershell.

    Security Administrators do not have access to DB.

    https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-keys-using-powershell?view=sql-server-2017

    Windows 2016 STD x64 with .Net Framework 4.6.2

    MS SQL 2017 Enterprise Edition RTM CU-11

    Latest module “SqlServer” version 21.1.18179 installed.

    HSM Model : Gemalto Safenet - LunaSA 6.2.1, Firmware 6.10.9, FIPS 140-2 in ON

    Master Key was created on target machine hosting DB with the HSM-specific tool:

    C:\Program Files\SafeNet\LunaClient\win32\KSP\KspConfig.exe

    I granted permission to myself &  SYSTEM\NT_AUTHORITY     

    Partition contents confirm a key label “Always-Encrypted-Auto1” was created.

    I tested the Always Encrypted keys using SSMS 17.9 with SUCCESS, but my objective is via Powershell.

    I launched Powershell ISE as Administrator

    ----------------------------------------------

    PS C:\> Import-Module "SqlServer"

    $CMKSettings = New-SqlCngColumnMasterKeySettings -CngProviderName "SafeNet Key Storage Provider" -KeyName "Always-Encrypted-Auto1"

    #Generate a column encryption key, encrypt it with the column master key to produce an encrypted value of the column encryption key.

    $encryptedValue = New-SqlColumnEncryptionKeyEncryptedValue -TargetColumnMasterKeySettings $CMKSettings

    # Share the location of the column master key and an encrypted value of the column encryption key with a DBA, via a CSV file on a share drive

    $keyDataFile = "E:\SharedFolder\AlwaysEncryptedKeyData.txt"

    "KeyStoreProviderName, KeyPath, EncryptedValue" > $keyDataFile

    $cmkSettings.KeyStoreProviderName + ", " + $cmkSettings.KeyPath + ", " + $encryptedValue >> $keyDataFile

    ----------------------------------------------

    ERROR MESSAGE:

    New-SqlColumnEncryptionKeyEncryptedValue : The type initializer for 'Microsoft.SqlServer.Management.AlwaysEncrypted.Types.AlwaysEncryptedManager' threw

    an exception.

    At line:5 char:19

    + ... ptedValue = New-SqlColumnEncryptionKeyEncryptedValue -TargetColumnMas ...

    +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        + CategoryInfo          : NotSpecified: (:) [New-SqlColumnEncryptionKeyEncryptedValue], TypeInitializationException

        + FullyQualifiedErrorId : System.TypeInitializationException,Microsoft.SqlServer.Management.PowerShell.AlwaysEncrypted.NewSqlColumnEncryptionKeyEncry

       ptedValue

    --------------------------------------------------------------

    PS C:\> Import-Module "PSPKI"

    Get-CryptographicServiceProvider

    Name                                                    Type                  IsLegacy Algorithms                                                        

    ----                                                    ----                  -------- ----------                                                         

    Microsoft Software Key Storage Provider                 None                  False    {RSA, DH, DSA, ECDH...}                                           

    Microsoft Passport Key Storage Provider                 None                  False    {RSA, AES, 3DES, DES...}                                          

    Microsoft Smart Card Key Storage Provider               None                  False    {RSA, ECDH_P256, ECDH_P384, ECDH_P521...}                         

    Luna Cryptographic Services for Microsoft Windows       RsaFull               True     {}                                                                

    Luna enhanced RSA and AES provider for Microsoft Win... RsaAes                True     {}                                                                 

    Luna SChannel Cryptographic Services for Microsoft W... RsaSChannel           True     {}                                                                

    Microsoft Base Cryptographic Provider v1.0              RsaFull               True     {rc2, rc4, sha1, md2...}                                          

    Microsoft Base DSS and Diffie-Hellman Cryptographic ... DSSDiffieHellman      True     {rc2, rc4, sha1, md5...}                                          

    Microsoft Base DSS Cryptographic Provider               DSS                   True     {sha1, md5, DSA}                                                  

    Microsoft Base Smart Card Crypto Provider               RsaFull               True     {rc2, rc4, sha1, sha256...}                                       

    Microsoft DH SChannel Cryptographic Provider            DiffieHellmanSChannel True     {rc2, rc4, sha1, md5...}                                          

    Microsoft Enhanced Cryptographic Provider v1.0          RsaFull               True     {rc2, rc4, sha1, md2...}                                          

    Microsoft Enhanced DSS and Diffie-Hellman Cryptograp... DSSDiffieHellman      True     {rc2, rc4, sha1, md5...}                                           

    Microsoft Enhanced RSA and AES Cryptographic Provider   RsaAes                True     {rc2, rc4, sha1, sha256...}                                       

    Microsoft RSA SChannel Cryptographic Provider           RsaSChannel           True     {rc2, rc4, sha1, md5...}                                          

    Microsoft Strong Cryptographic Provider                 RsaFull               True     {rc2, rc4, sha1, md2...}                                           

    Wednesday, September 25, 2019 8:28 PM

Answers

  • SOLUTION : As per vendor, Always Encrypted with Safenet HSM is configured in Win 32 architecture as initially indicated in my post. But the catch was that Powershell ISE must be launched in Win 32 (x86) as well.

    • Marked as answer by Eric_C_Canada Monday, October 7, 2019 1:21 PM
    Monday, October 7, 2019 1:21 PM

All replies

  • Hi Eric_C_Canada,

     

    >>New-SqlColumnEncryptionKeyEncryptedValue : The type initializer for 'Microsoft.SqlServer.Management.AlwaysEncrypted.Types.AlwaysEncryptedManager' threw

    an exception.

     

    This error indicates that some dependencies or file cannot be loaded when using 'Microsoft.SqlServer.Management.AlwaysEncrypted.Types.AlwaysEncryptedManager'. Would you please check for it?

     

    Best regards,

    Dedmon Dai


    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

    Thursday, September 26, 2019 2:31 AM
  • SOLUTION : As per vendor, Always Encrypted with Safenet HSM is configured in Win 32 architecture as initially indicated in my post. But the catch was that Powershell ISE must be launched in Win 32 (x86) as well.

    • Marked as answer by Eric_C_Canada Monday, October 7, 2019 1:21 PM
    Monday, October 7, 2019 1:21 PM