3

Using Always Encrypted we are attempting to encrypt one column of type varchar(50) in a table that contains 5.5 million rows with the deterministic encryption. After a while the wizard returns failure due to the following error:

Exception of type 'System.OutOfMemoryException' was thrown..

Any ideas on how to prevent this error?

  • What are you using to do the encryption? (SSIS, C# app, what?) Where are you running it? (On the SQL Server itself, or on a separate app server?) – Brent Ozar Feb 22 '17 at 21:03
  • @BrentOzar I am using SSMS and it's running on the SQL Server itself. – RoastBeast Feb 22 '17 at 21:22
  • 1
    yeah, 32-bit SSMS isn't exactly known for its high-performance memory management. Time to step up to something bigger. – Brent Ozar Feb 22 '17 at 21:40
  • @BrentOzar I am using 64-bit SSMS. – RoastBeast Feb 23 '17 at 15:58
  • 2
    There is no such thing, on 32-bit or 64-bit machines, SSMS is based on a 32-bit visual studio shell and is subject to all the limitations that imposes. – Aaron Bertrand Feb 23 '17 at 16:02
4

Does your table have a primary key or a clustered index? There is a known issue in the wizard/PowerShell that may cause it to run out of memory, if the table does not have a primary or a clustered index, so one workaround could be to create a primary key/clustered index, if you don't have it already.

You can also try to use 64-bit PowerShell. Using 32-bit PowerShell instead of SSMS will likely not help, as SSMS and PowerShell use the same library (DacFx) to support most Always Encrypted scenarios.

  • 1
    Wait, people still use 32-bit Powershell? On purpose? – Aaron Bertrand Feb 23 '17 at 13:42
  • @user118250 - your answer worked as well - I added a primary key (not the field I was trying to encrypt) to the table, ran the wizard, and the encryption completed successfully. – RoastBeast Feb 23 '17 at 19:50
4

For large tables I would definitely work with T-SQL or Powershell rather than the wizard - clunky UIs that run all kinds of background things and take over-protective locks do not have a great track record. For example, this wizard actually offers an option to generate Powershell:

enter image description here

I would do that. Here is what it produces:

# Generated by SQL Server Management Studio at 9:10 PM on 2/22/17

Import-Module SqlServer
# Load reflected assemblies

[reflection.assembly]::LoadwithPartialName('System.Data.SqlClient') | Out-Null
[reflection.assembly]::LoadwithPartialName('Microsoft.SQLServer.SMO') | Out-Null
[reflection.assembly]::LoadwithPartialName('Microsoft.SQLServer.ConnectionInfo') | Out-Null

# Set up connection and database SMO objects

$sqlConnectionString = 'Data Source=SERVER\INSTANCE;Integrated Security=True;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=True;Packet Size=4096;Application Name="Microsoft SQL Server Management Studio"'
$sqlConnection = New-Object 'System.Data.SqlClient.SqlConnection' $sqlConnectionString
$serverConnection = New-Object 'Microsoft.SqlServer.Management.Common.ServerConnection' $sqlConnection
$smoServer = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $serverConnection
$smoDatabase = $smoServer.Databases['DatabaseName']

# If your encryption changes involve keys in Azure Key Vault, uncomment one of the lines below in order to authenticate:
#   * Prompt for a username and password:
#Add-SqlAzureAuthenticationContext -Interactive

#   * Enter a Client ID, Secret, and Tenant ID:
#Add-SqlAzureAuthenticationContext -ClientID '<Client ID>' -Secret '<Secret>' -Tenant '<Tenant ID>'

# Change encryption schema

$encryptionChanges = @()

# Add changes for table [dbo].[TableName]
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.TableName.ColumnName -EncryptionType Deterministic -EncryptionKey ColumnKey

Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $smoDatabase

Now, this still might take a long time; I haven't done thorough benchmarks on encrypting existing data. But you won't run out of memory (this error was because of SSMS limitations, not SQL Server's).

I looked quickly and, while it is easy to generate T-SQL scripts for creating master and column keys, I don't know of a way to run ALTER TABLE. This passes syntax checks:

ALTER TABLE dbo.TableName
ALTER COLUMN ColumnName varchar(11)   
        COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = ColumnKey,  
        ENCRYPTION_TYPE = DETERMINISTIC ,  
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256');

However it fails at runtime with:

Msg 206, Level 16, State 2
Operand type clash: varchar is incompatible with varchar(11) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ColumnKey', column_encryption_key_database_name = 'DatabaseName') collation_name = 'Latin1_General_BIN2'

  • I tried your PowerShell suggestion and it worked, thank you! It took around 2 hours and seemed to be stuck on "100% Completed" for most of the time, but in the end the job was done. Seeing more and more that SSMS falls short because of its limitations. – RoastBeast Feb 23 '17 at 15:55
  • 1
    @RoastBeast Generally, any time I open up a dialog or wizard in SSMS, I get suspicious. Most have a little script drop down to generate the script it would have produced if you let the UI do it - I grab those and inspect them before running them myself in a query window. – Aaron Bertrand Feb 23 '17 at 16:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.