Using a public key on Azure server, and the private key on-premise

Question

This question is about how to use a public key/private key pair to encrypt sensitive customer data.

The data will be input by the customer in a web portal in azure and then stored to a database in Azure.

Then the data will be synced down to an on-premise database. The private key will then be used to decrypt the data when it is needed. For example, the Portal will input bank account information, it is then encrypted, synced, and then used on premise to send a payment.

As far as I understand it, SQL Server can generate a Assemetric key pair for this purpose.

I have found this to create the key pair:

CREATE ASYMMETRIC KEY asymDemoKey — Creating Asymmetric Key Names
WITH ALGORITHM = RSA_512 — Encryption Security Type
ENCRYPTION BY PASSWORD =’TestASYM123!’– Password

However, I don't understand how to then export the public key from the on-premise server, and then inport it in the Azure Database.

So is there a way to export the public key for use on Azure?

Or is there a better way to do this?

Answer 1

Asymmetric keys are used for securing symmetric keys. An asymmetric key consists of a private key and a corresponding public key. Asymmetric keys can be imported from strong name key files, but they cannot be exported.

Always Encrypted is a feature designed to protect sensitive data, such as Bank details, stored in databases. Clients can encrypt sensitive data within client apps and never give the encryption keys to the Database Engine with Always Encrypted. As a result, Always Encrypted creates a barrier between those who own the data and should have access to it and those who administer it but should not. Always Encrypted allows clients to reliably keep critical data outside of their direct control by assuring that on-premises database administrators, cloud database operators, or other high-privileged unauthorized individuals cannot access the encrypted data.

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15