March 15, 2017 at 7:40 am
Hello everyone. I am starting to work on a project that requires the encryption of a number of columns in a number of tables. The database server is 2016 SP1 and we have decided to take advantage of the “Always Encrypted” feature (which works perfectly well for our purposes).
The very same database schema is deployed on many databases and servers (we are a software manufacturer / vendor, and these are the database of our customers). The databases, as you can imagine, already contain data.
Given the number of columns / tables / databases that need to be encrypted, we are using SQL Server Management Studio to generate Poweshell scripts. The idea is that we would replace server and database name within the script(s) and execute that script against the target database(s).
The problem that we are encountering is related to the execution of such Powershell script(s)! Here is a simple sequence of operations….
1) On database server & database, create a Column Master Key (generating a Local Machine certificate) named (for example) THE_MASTER_KEY
2) On database server & database, create a Column Encryption Key named (for example) THE_COLUMN_KEY (using the THE_MASTER_KEY as Column Master Key)
3) On database server & database, within SSMS, right click on column to encrypt, choose Encrypt Column from the menu, and follow the prompts.
4) Generate Powershell script to run later (we do NOT encrypt the column at this time)
5) Review the Powershell script and everything seems to be in order
6) Create simple batch file to execute the Powershell script. The batch file contains this simple line
C:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe -noexit -executionpolicy bypass -File C:\TEMP\name_of_powershell_script.ps1
7) Execute the batch file against the very same server / database that generated the Powershell script… everything seems to go well… BUT the Powershell script ends up returning this error...
C:\WINDOWS\system32>powershell.exe -noexit -executionpolicy bypass -File "C:\TEMP\name_of_powershell_script "
Windows PowerShell
Copyright (C) 2016 Microsoft Corporation. All rights reserved.
Set-SqlColumnEncryption : Unable to determine the identity of domain.
At C:\TEMP\name_of_powershell_script.ps1:62 char:1
+ Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Set-SqlColumnEncryption], SqlPackageException
+ FullyQualifiedErrorId : EncryptionError,Microsoft.SqlServer.Management.PowerShell.AlwaysEncrypted.SetColumnEncryption
The batch file is being invoked by me (domain administrator). I even logged onto the database server as local machine administrator. Same result.
More: I execute the batch file using “Run as administrator”. Same error.
Note that the columns can be encrypted without any problem within SSMS...
I am obviously missing something essential. Any suggestions?
Thanks, in advance, for any help…
Giorgio
March 17, 2017 at 2:37 pm
Sue
March 17, 2017 at 3:23 pm
Sue
Thanks for your suggestion. Just minutes before your post, I came across the solution. I really do not fully understand it (I think that even the person that passed it along does not understand it)...but it definitely works.The point is that the Powershell script will always report that error UNLESS the following section is placed BEFORE the statement Import-Module SqlServer...
# This section is required, to avoid the inevitable error
# Set-SqlColumnEncryption : Unable to determine the identity of domain.
# when completing script execution (would result in encryption not being completed.)
$replacementEvidence = New-Object System.Security.Policy.Evidence
$replacementEvidence.AddHost((NewObject System.Security.Policy.Zone ([Security.SecurityZone]::MyComputer)))
$currentAppDomain = [System.Threading.Thread]::GetDomain()
$securityIdentityField = $currentAppDomain.GetType().GetField("_SecurityIdentity", ([System.Reflection.BindingFlags]::Instance -bOr [System.Reflection.BindingFlags]::NonPublic))
$securityIdentityField.SetValue($currentAppDomain,$replacementEvidence)
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
# etc. etc. etc.
Now that I have that section in place, the Powershell script can be executed without errors... the column(s) are being encrypted.... and as a bonus I get a listing of all database objects that could be affected by the encryption of the specific columns. Which SSMS would not provide.
Now... if only someone could actually explain those lines... !!!
Thanks
Giorgio
March 19, 2017 at 1:31 pm
Giorgio Vidali - Friday, March 17, 2017 3:23 PMSueThanks for your suggestion. Just minutes before your post, I came across the solution. I really do not fully understand it (I think that even the person that passed it along does not understand it)...but it definitely works.The point is that the Powershell script will always report that error UNLESS the following section is placed BEFORE the statement Import-Module SqlServer...
# This section is required, to avoid the inevitable error
# Set-SqlColumnEncryption : Unable to determine the identity of domain.
# when completing script execution (would result in encryption not being completed.)
$replacementEvidence = New-Object System.Security.Policy.Evidence
$replacementEvidence.AddHost((NewObject System.Security.Policy.Zone ([Security.SecurityZone]::MyComputer)))
$currentAppDomain = [System.Threading.Thread]::GetDomain()
$securityIdentityField = $currentAppDomain.GetType().GetField("_SecurityIdentity", ([System.Reflection.BindingFlags]::Instance -bOr [System.Reflection.BindingFlags]::NonPublic))
$securityIdentityField.SetValue($currentAppDomain,$replacementEvidence)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
# etc. etc. etc.Now that I have that section in place, the Powershell script can be executed without errors... the column(s) are being encrypted.... and as a bonus I get a listing of all database objects that could be affected by the encryption of the specific columns. Which SSMS would not provide.
Now... if only someone could actually explain those lines... !!!
Thanks
Giorgio
I forgot about that one. I've seen (and had to use) something similar. Vaguely understand the gist of it from that experience. From what I remember at a high level (which is all I know) would be that the issue is around how .Net security works. Evidence is basically the term used for the properties on which the security is built.
When command line Powershell starts, the default App Domain's evidence is empty. So you'd either have to create another App Domain with the necessary evidence or modify the existing App Domain. This just modifies the default domain that loaded when Powershell starts - creates new evidence for the MyComputer security zone.
Then captures the current evidence, identity using reflection and then replaces that with the new evidence just created.
So then the error makes some sense - it's just saying the current app domain has no identity as there was no evidence.
And I still have problems remembering it.
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply