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

How To Solve “GatherAllFilesToPublish” Error?

Issue:

I’m getting an error “GatherAllFilesToPublish”  when tried to publish my code. What’s happening to my csproj file?

---------------------------
Publish failed
---------------------------
Publish has encountered an error.

Build failed. Check the Output window for more details.


A diagnostic log has been written to the following location:

"C:\Users\username\AppData\Local\Temp\1\tmpC98.tmp"
---------------------------
OK   
---------------------------

Error MSB4057: The target "GatherAllFilesToPublish" does not exist in the project

 

Environment:

Windows 10, Visual Studio 2019, C# Project with Framework 4.0

Resolution:

This error is due to incorrect setting on project file (*.csproj). Please follow step-by-step instructions below.

Step-by-step instructions:-

  1. Open you project under Visual Studio
  2. Right click on project name and select “Unload Project”
  3. Right click on project name again and select “Edit *.csproj”
  4. Scroll down to the bottom of the file and you should see following line:
    <Import Project="$(MSBuildExtensionsPath32)\Microsoft\VisualStudio\v10.0\WebApplications\Microsoft.WebApplication.targets" />
  5. Replace above line with following two lines:
    <Import Project="$(VSToolsPath)\WebApplications\Microsoft.WebApplication.targets" Condition="'$(VSToolsPath)' != ''" />
    
    <Import Project="$(MSBuildExtensionsPath32)\Microsoft\VisualStudio\v10.0\WebApplications\Microsoft.WebApplication.targets" Condition="false" />

  6. Save the file and right click on project name to Reload the project
  7. Try publish again to see if it solves the issue

Troubleshoot:

If fail again, please verify by going through step 2 to 4 to see if two lines still there.