Sql-server – how to encrypt one column of a table without using always encrypted

always-encryptedencryptionSecuritysql serversql-server-2016

I have been working on a solution to synchronise logins (using T-SQL) between 2 servers, or between AlwaysOn nodes, inspired by sqlsoldier.

It requires a linked Server.

When run, this procedure outputs the following set of scripts that need to be applied in the current server for synchronising the logins (example):

 Create Login [MY_COMPANY_UK\DW_and_Extracts] From Windows; 
 Create Login [MY_COMPANY_UK\AuPairDWETL_TS] From Windows; 
 Exec  sp_addsrvrolemember @rolename = 'sysadmin', @loginame = 'NT SERVICE\MSSQLSERVER'; 
GRANT VIEW ANY DEFINITION  To  [MY_COMPANY_UK\RGDLMonitoring]; 
GRANT CONNECT SQL  To  [MY_COMPANY_UK\DW_and_Extracts]; 
GRANT CONNECT SQL  To  [MY_COMPANY_UK\AuPairDWETL_TS]; 
GRANT CONNECT On ENDPOINT::[SSBEndpoint] To [MY_COMPANY_USA\sqlservice];

I would like to log all these commands to a table before I apply them to my current server, but I understand these are sensitive information, and I would like to be able to encrypt it and be able to decrypt.

What could I achieve this?

I have already seen this:

Is It Possible to Encrypt varbinary(max) Column Using Always Encrypted Feature?

How could I achieve this, with minimal effort, and not affecting anything else on my systems?

(I currently don't use always encrypted)

Best Answer

SQL Server has had column encryption with server-managed keys since SQL 2005. See Encrypt a Column of Data.

AlwaysEncrypted uses client-side keys.

Related Question