Posted on

How to use Microsoft SQL Always On Column Encryption?

Issue:

How to use Microsoft SQL 2017 Always On Column Encryption?

Environment:

Windows, MS SQL 2017, SSMS 2017, Visual Studio 2019

Resolution:

Follow these steps to get unencrypted value with different tools.

Step-by-step instructions:-

Setting Up Encrypted Column on a Table

  1. Right click on a table and select “Encrypt Columns…”
  2. Depend on what type of column, choose “Randomized” if possible for better security
  3. Select default for other values
  4. Follow the screen instructions and finish with successful message
  5. You keys are created and column is ready to store encrypted data.
  6. See next section for inserting and retrieving data.

Insert Value into Encrypted Column with SSMS 2017

  1. Enable Query Parameterization on SSMS
  2. Insert Query with parameter for encrypted data 
    DECLARE @myEncryptedColumnValue char(36) = '09DF3BDF-078E-EA11-A2FA-005056A14182';

INSERT INTO [dbo].[myEncryptedColumnTable]
           ([myEncryptedColumn])
     VALUES
           (@keyEncrypted)
  3. Select Query to retrieve encrypted data (no special syntax needed, just connection)
SELECT * FROM [dbo].[myEncryptedColumnTable]

Notes:
Make sure your connect to database server is with “Column Encryption Setting = Enabled”

Import/Export Encrypted Column Data with SSMS 2017

  1. Right click on DB Name and select Task, Export
  2. On Choose a Source, select “.NET Framework Data Provider for SqlServer”
  3. On the same screen, select Enabled next to “Column Encryption Seeting”, Select True next to “Intergrated Security” if you’re using that, enter [myServerName] next to “Data Source”, and [myDBName] next to “Initial Catalog”.
  4. On Choose a Destination, select “.NET Framework Data Provider for SqlServer”
  5. On the same screen, select Enabled next to “Column Encryption Seeting”, Select True next to “Intergrated Security” if you’re using that, enter [myOtherServerName] next to “Data Source”, and [myDBName] next to “Initial Catalog”.
  6. Check you destination encrypted column.
    Notes:

    • Make sure your connect to destination server is with “Column Encryption Setting = Enabled“
    • Make sure you created destination keys using the same keys you created on source database.

Permission Needed for User Other Then db_owner

  1. Create a database role and assign role to user 
    CREATE ROLE [ReadEncryptedColumn] AUTHORIZATION [dbo]
GO
GRANT SELECT ON [dbo].[myColumnEncryptedTable] TO [ReadEncryptedColumn]
GO
GRANT VIEW ANY COLUMN MASTER KEY DEFINITION TO [ReadEncryptedColumn]
GO
GRANT VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO [ReadEncryptedColumn]
GO
  2. Notes:
    • Make sure user connect to destination server with “Column Encryption Setting = Enabled“

Troubleshoot:

Testing Encrypted column with PowerShell

#seeing encrypted value
$serverName = "myServerName"
$databaseName = "myDBName"
$strConn = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
Invoke-Sqlcmd -Query "SELECT TOP(10) * FROM myColumnEncryptedTable" -ConnectionString $strConn| 
format-table -AutoSize

#seeing unencrypted value
$strConn = $strConn + "; Column Encryption Setting = Enabled"
Invoke-Sqlcmd -Query "SELECT TOP(10) * FROM myColumnEncryptedTable" -ConnectionString $strConn| 
format-table -AutoSize

Reference:

Always Encrypted

Posted on

TFS Build and Release Management Definition Automation Tutorial (Part 2)

Issue:

I would like to setup a build definition that will run when I check in my application and database projects. Then I want my artifact to automatically release to production environment. Where do I start?

Environment:

Team Foundation Server (TFS) 2017, Visual Studio (VS) 2017, Build Server (BS)  2017, ASP.Net Project, SQL Server 2016

Resolution:

For Build definition instruction, please refer to TFS Build and Release Management Definition Automation Tutorial (Part 1). Here are step-by-step instructions on how to setup release definition that will publish code and database automatically. This release definition uses artifact created by build definition previously with different build configurations to publish to different environments.

Step-by-step instructions:-

  1. Create a new Release definition by clicking on “Releases” tab and “+ New definition” link.
    new release definition
    Or by clicking on “Releases” tab, then click on “+” dropdown and select “Create release definition” link.
  2. Choose “Empty” template and click “Next”.
  3. Select the correct build definition from “Source (Build definition)” dropdown that created previously on part 1. Click on checkbox for “Continuous deployment …”. Then click on “Create” button to create a new release definition.
  4. Rename your Release definition and Environment name
  5. Click on “Artifacts” tab and verify that the source is correct from the build you created on part 1.
  6. Click on “Triggers” tab and verify that “Set trigger on artifact source” dropdown is correctly selected.
  7. Click on “Environments” tab and “+ Add tasks” link to add a task. Do not click on dropdown and select “Add an agent phase” unless you are doing something else.
  8. Click on “Utility” tab, scroll down to “PowerShell” task and click on “Add” button twice to add two PowerShell tasks.

  9. Rename to “App Deployment Script” and enter script path and arguments as shown below. Make sure AppPackageV1.0.0.0.ps1 Custom PowerShell script is accessible by build server.

    Script Path: \\put-this-ps-on-your-build-server-share\AppPackageV1.0.0.0.ps1
    Arguments: $(my_Target_Path1) $(my_Encrypt_Cfg) $(my_Cleanup_Target) $(my_Source_Path)
  10. Rename to “Database Deployment Script” and enter script path and arguments as shown below. Make sure SqlPackageV1.0.0.0.ps1 Custom PowerShell script is accessible by build server.

    Script Path: \\put-this-ps-on-your-build-server-share\SqlPackageV1.0.0.0.ps1
    Arguments: $(my_Source_DACPAC_File) $(my_Publish_Profile)
  11. Next is to convert PowerShell task to Task Group so that it can be called easily with variables. To convert to Task Group, right click on “App Deployment Script” and select “Create task group”.
  12. Enter “App Deployment Task” on Name field and click on “Create”.
  13. As you can see below, it’s using “App Deployment Task” task group with variables on the right hand side. By doing this, you can deploy your code to multiple location with new “App Deployment Task” task found under “Add tasks” link.
    Custom PowerShell Script (PS) Variable Description:

    my_Target_Path1 = Target folder (i.e., web server UNC path: \\webserver1\demo)

my_Source_Path = Source folder (i.e., build artifact wwwroot folder: $(System.DefaultWorkingDirectory)\Project_Demo_Build\drop\[Debug]\wwwroot)

my_Encrypt_Cfg = Encrypt web.config sections (0 = disable encrypt; 1 = enable encrypt (default))

my_Cleanup_Target = Delete target folder (0 = disable delete; 1 = enable delete (default))
  14. Do the same for “Database Deployment Task” from step 11 to 13 and you will end up with “Database Deployment Task” task group as shown below.

    Custom PS Variable Description:

    my_Source_DACPAC_File = Source DACPAC database file (i.e., build artifact dbroot folder: $(System.DefaultWorkingDirectory)\Project_Demo_Build\drop\[Debug]\dbroot\Proj.Demo.Database.dacpac)

my_Publish_Profile = Predefined publish profile for database (i.e.,  $(System.DefaultWorkingDirectory)\Project_Demo_Build\drop\[Debug]\dbroot\Proj.Demo.Database.[Debug].publish.xml)
  15. Click on “Run on agent” link to select a deployment queue/pool.
  16. In case you wonder, Task Groups can be found by clicking on “Task Groups” link as shown below.
  17. Click on “Save” link to save your release definition.

 

Troubleshoot:

  1. If you see “The model version ‘2.9. is not support.”, this could be the wrong version of SQLPackage.exe is being used. Make sure to use 2017 SQLPackage.exe.

Reference: