how to encrypt one column of a table without using always encrypted?

Question

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)

• SqlWorldWide: There are powershell scripts available that will sync logins between nodes but I understand you might have other needs that warrant to use tsql. These might help. mssqltips.com/sqlservertip/2431/… and c-sharpcorner.com/article/…

Answer 1

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

AlwaysEncrypted uses client-side keys.