在Azure中始终加密的SQL [英] SQL Always Encrypted in Azure

问题描述

我需要构建一个访问数据库中某些加密列的Web应用程序.所有必须托管在客户的天蓝色帐户中.我搜索了几天，并阅读了很多教程，但找不到解决我问题的答案.

我主要遵循以下原则:

 创建列主键[TESTMASTERKEY]和(KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT'，KEY_PATH = N''-粘贴您的密钥标识符)去 

我试图在数据库上创建一个新表并使用保管库对其进行加密键，但是我得到了:

• 始终尝试下载

  因此，请尝试下载最新的SSMS版本.

  我在某处阅读了我需要在广告中授予我许可的地方应用程序，但是我没有我的客户(所有者的所有者)的权限Azure订阅).

  这主要是针对客户端的.您需要注册您的应用程序，以获取客户端应用程序与数据库中的加密数据进行对话的 client id 和 client secret .在此处阅读有关如何注册客户端的信息应用.除非注册您的应用程序，否则您将无法从任何客户端(SSMS除外)进行连接.您需要联系订阅所有者进行注册.

  我还读到必须使用存储过程来读写数据库.这是真的吗?

  取决于您的加密类型.有两种类型的加密https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted http://www.bradleyschacht.com/always-encrypted-with-azure-key-vault/

  I was able to run a web app on my machine with the certificate generated by SSMS encryption wizard and a SQL DB hosted on azure. I couldn't do it with an azure vault key.

  Now I need to publish my web app on azure but I'm unable to access/modify the DB data. I need to either use the certificate from my machine or use the azure vault. Can anyone explain to me how it's done?

  • I tried to export the certificate to the azure vault, but I don't know how to "reference" it

  • I tried to create a new table on the db and encrypting it with a vault key, but I get:

  Failed to decrypt a column encryption key. Invalid key store provider name: 'AZURE_KEY_VAULT'. A key store provider name must denote either a system key store provider or a registered custom key store provider. Valid system key store provider names are: 'MSSQL_CERTIFICATE_STORE', 'MSSQL_CNG_STORE', 'MSSQL_CSP_PROVIDER'. Valid (currently registered) custom key store provider names are: . Please verify key store provider information in column master key definitions in the database, and verify all custom key store providers used in your application are registered properly."

  • I read somewhere that I need to give permission in the AD to my application, but I don't have permissions from my client (the owner of the Azure subscription) to do that.
  • I read also that a stored procedure must be used to read and write to the DB. Is this true?

  Thanks in advance for any help.

  解决方案

  I need to either use the certificate from my machine or use the azure vault. Can anyone explain to me how it's done?

  It depends on your use-case. Actually Selecting Keystore Provider for your Column Master key is depends on which driver and version you are using. There are two high-level categories of key stores : Read here

  • Local
  • Centralized Key Store

  Local
  

  If you planning to deploy your App in On-Prem/VM, then you can generate our own Certificate and keep the certificate within your Local VM.

  Centralized Key Store
  

  If you planning to deploy your App in azure web APP/Cloud then you should keep your Key Store in a centralized Secure Vault which may be here as Azure Key Vault

  As a best practice, you should not store the provider in the Local machine, Which would be a problem if you VM is compromised then your DB certificate also be compromised.

  I tried to export the certificate to the azure vault, but I don't know how to "reference" it

  CREATE COLUMN MASTER KEY [TESTMASTERKEY]
  WITH
  (
      KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT',
      KEY_PATH = N'' --Paste your Key Identifier 
  )
  GO
  

  I tried to create a new table on the DB and encrypting it with a vault key, but I get:

  • Always try to download the latest SSMS version.
    
  • Assume you are using Azure SQLDB. Always encryption will work only on SQL Server 2016 and above in on-prem and all versions of Azure SQLDB
    
  • Set the connection string to Column Encryption Setting=enabled

  The behavior you describe is a bug in CTP 3.0 and SSMS October update. The issue, as you surmised, is that the Azure Key Vault provider is not registered if you open the Query Editor window opening the Always Encrypted wizard first. We’ve already fixed this for the next update of SSMS! In the meantime, the workaround is to open the Always Encrypted wizard (you can close it/cancel immediately after opening) which will cause the Azure Key Vault provider to get registered. This bug manifests itself only through this specific case (using the Query Editor before the wizard), and won’t at all impact your ability to use the Always Encrypted wizard or use the Azure Key Vault provider with any of your client applications.

  So try to download the latest SSMS version.

  I read somewhere that I need to give permission in the AD to my application, but I don't have permissions from my client (the owner of the Azure subscription) to do that.

  This is mainly for the Client side. You need to register your app in order to get the client id and client secret for your client-side application to talk with encrypted data in DB. Read here for how to register your client app. Unless you register your app, you couldn't able to connect from any client-side(Except SSMS). You need to contact the subscription owner to register the app.

  I read also that a stored procedure must be used to read and write to the DB. Is this true?

  Depends on your Encryption Type. There are two types of Encryption Read here about it

  • Deterministic
  • Randomized

  Each having its own pro and cons.

  Deterministic encryption always generates the same encrypted value for any given plaintext value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. However, but may also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column, especially if there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character columns.

  Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.

  这篇关于在Azure中始终加密的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！