Insert data using powershell

问题:

We have a static sql file which consists of insert statements;basically test data. is it possible to execute this script using powershell on azure sql db where alwaysencrypted is enabled. We use keyvault to store the certs.

回答1:

Unfortunately, PowerShell (Invoke-SqlCmd) does not support insert statements against encrypted columns at this point. The only SQL tool from Microsoft that supports such statements at this point is SSMS - please see: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-using-sql-server-management-studio#param .

An alternative could be to put your test data into a CSV file and use the Import Export Wizard to import the data into the database. You could save the import job as an SSIS package, which you could execute from the command line. Here is a blog article on using the I/E Wizard for importing (and encrypting) data from a database (importing from a file would be similar). https://blogs.msdn.microsoft.com/sqlsecurity/2015/10/31/ssms-encryption-wizard-enabling-always-encrypted-in-a-few-easy-steps/

Jakub