在Azure中始终加密的SQL

问题描述

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

我主要遵循以下原则:

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

• 始终尝试下载最新的SSMS版本.
  
• 假设您正在使用Azure SQLDB.始终加密仅在SQL Server上有效2016年及更高版本的本地版本和所有版本的Azure SQLDB
  
• 将连接字符串设置为 Column Encryption Setting = enabled

您描述的行为是CTP 3.0和SSMS 10月更新中的错误.您推测的问题是，如果您打开查询编辑器"窗口，然后首先打开始终加密"向导，则未注册Azure Key Vault提供程序.我们已经解决了SSMS的下一个更新！同时，解决方法是打开始终加密"向导(您可以在打开后立即将其关闭/取消)，这将导致Azure Key Vault提供程序得到注册.此错误仅通过这种特定情况(在向导之前使用查询编辑器)表现出来，并且完全不会影响您使用始终加密"向导或将Azure Key Vault提供程序与任何客户端应用程序一起使用的能力.

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

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

取决于您的加密类型.有两种类型的加密在此处阅读

• 确定性
• 随机化

每个都有自己的优点和缺点.

确定性加密始终为任何给定的明文值生成相同的加密值.使用确定性加密可以在加密列上进行查找，相等联接，分组和索引..但是，也可以允许未经授权的用户通过检查加密列中的模式来猜测有关加密值的信息.一小部分可能的加密值，例如True/False或North/South/East/West地区.确定性加密必须对字符列使用具有binary2排序顺序的列排序规则.

随机加密使用一种以不太可预测的方式加密数据的方法.随机加密更安全，但会阻止在加密列上进行搜索，分组，索引和联接.

I need to build a web app that accesses some encrypted columns on a DB. All must be hosted in the client's azure account. I have searched for a couple of days and read a lot of tutorials but I can't find an answer to my problem.

I have mainly followed these:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encryptedhttp://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:

• 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.

解决方案

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.

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

• Always try to download the latest SSMS version.
  
• Assume you are using Azure SQLDB. Always encryption will work only on SQL Server2016 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.

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.

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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！