2

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)

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.