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, 2017 at 21:03
  • @BrentOzar I am using SSMS and it's running on the SQL Server itself.
    – RoastBeast
    Feb 22, 2017 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, 2017 at 21:40
  • @BrentOzar I am using 64-bit SSMS.
    – RoastBeast
    Feb 23, 2017 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. Feb 23, 2017 at 16:02

2 Answers 2


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? Feb 23, 2017 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, 2017 at 19:50

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)   
        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, 2017 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. Feb 23, 2017 at 16:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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