Tag Archives: PowerShell

ByHariharan Rajendran

Always Encrypted–PowerShell Scripts Part-2

Applying the encryption to the table column is easy with the PowerShell. Please make sure to follow the below steps before run the below scripts.

1. Connect to SQL Server from PowerShell

2. Create column master key & column encryption key   – You can create by using wizard or PowerShell Scripts

Wizard method

PowerShell Script

3. Choose the column which you want to apply the encryption.

4. Defined the encryption type

 

Run the below script just continue with this script

$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName “dbo.Patients.SSN” -EncryptionType “Deterministic” -EncryptionKey “CEK1”

Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces

You can append multiple encryption by adding + symbol, like below

$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName “dbo.Patients.SSN” -EncryptionType “Deterministic” -EncryptionKey “CEK1”
$ces += New-SqlColumnEncryptionSettings -ColumnName “dbo.Patients.BirthDate” -EncryptionType “Randomized” -EncryptionKey “CEK1”
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces

ByHariharan Rajendran

Always Encrypted–PowerShell Scripts

We have discussed that how to create a Always encrypted columns and steps to create custom column master key & column encryption key.

This article explains about the creation of keys with the help of PowerShell scripts.

Latest PowerShell Cmdelt’s update from Microsoft, https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/

Pre-Requisites:

Since Always Encrypted feature introduced in SQL Server 2016, we need to upgrade our SQLPS.exe. If you use the older version of SQLPS then that will not recognize the some of the below cmdlets. So make sure to upgrade SQLPS.exe first.

You can upgrade by installing the latest version of SSMS (SQL Server management studio) 2016.

Check for the below modules installed in PowerShell,

#List directory wise installed modules

Get-Module –ListAvailable

image

Check for SQLASCMDLETS & SQLPS. If there is no modules then make sure you have SQLPS.exe file available in below location

C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules

# Import the SQL Server Module.
Import-Module Sqlps -DisableNameChecking;

 

Steps to Create a Column Master Key & Column Encryption Key.

In the Below script, we need to specify following information,

  • Server Name
  • Database Name
  • Column Master Key Name
  • Column Encryption Key Name

First Connect to the Server and Database using PowerShell Script

# Connect to your database.
$serverName = “<server name>”
$databaseName = “<database name>”

$connStr = “Server = ” + $serverName + “; Database = ” + $databaseName + “; Integrated Security = True”
$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ConnectionString = $connStr
$connection.Connect()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName]

As we know, we have different key stores available. This below script using “Windows Certification Store – Current User”.

# Create a column master key in Windows Certificate Store.
$cert1 = New-SelfSignedCertificate -Subject “AlwaysEncryptedCert” -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange

In the above script, you can check the “CertStoreLocation”. To identify the location, you can check in T-SQL Statement to create master key. I just highlighted in below script for understanding purpose.

USE [Sample_Encrypt]
CREATE COLUMN MASTER KEY [test1]
WITH
(
    KEY_STORE_PROVIDER_NAME = N’MSSQL_CERTIFICATE_STORE’,
    KEY_PATH = N’CurrentUser/My/55A4DDAF58D274547B9C3BA5ABA154662CDF62D0′
)

GO

# Create a SqlColumnMasterKeySettings object for your column master key.
$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation “CurrentUser” -Thumbprint $cert1.Thumbprint

# Create column master key metadata in the database.
$cmkName = “<cmk name>”
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

# Generate a column encryption key, encrypt it with the column master key and create column encryption key metadata in the database.
$cekName = “<cek name>”
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName

Verify the keys in SQL Server Database Security folder.

ByHariharan Rajendran

Azure PowerShell Commandlets Issues

As usual, I was trying to use PowerShell Command line tool to manage my azure services. I used to prefer PowerShell as we have more control on it. This time, I got an error message, I was not able to run any Azure commandlets but I was able to add my azure account.

The error message is bit tricky and to solve the issue, I just follow closed and re-opened but still I got the same error message. I found that I got something issue with my saved credentials (token).

Error message,

Get-AzureSubscription : Failed to refresh token. AADSTS90002 : No Service namespace named ‘ ‘ was not found in the data store.
Trace ID:XXXXXXXXXX
Correlation ID: XXXXXXXXXXXXXXXXXX
TimeStamp: 2015-09-12 13:24:477 : The remote server returned an error : (400) Bad Request.

image

I followed the below steps to solve the issue,

Step 1: Explored the following folder in your system,  C:\Users\{User Name}\AppData\Roaming\Windows Azure Powershell.

Note: AppData folder will be in hidden by default and replace {User Name} into your system name.

Step 2: I saw the below files inside the folder and Just deleted all the files.

image

Step 3: Closed the PowerShell tool and reopen again.

Step 4: Added my azure account, this time, I was able to run all commands successfully.

1