【数据加密】使用托管身份和ODBC访问Power BI报表或Azure数据工厂中的Azure SQL始终加密数据

始终加密是Azure SQL数据库中的一项功能，它允许加密客户端应用程序中的敏感数据，这样加密密钥就永远不会泄露给数据库。此功能的主要好处是，拥有数据的企业主可以保持对数据的完全控制，而不会将实际解密的数据透露给管理数据的团队，如云运营、DBA等。此功能非常有趣，尤其是对于受监管的行业。有关此功能的详细信息，请参阅Microsoft官方文档页 — https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?redirectedfrom=MSDN&view=sql-server-ver15

解决方案解决的主要用例

在报告场景中（使用Power BI等工具），有时需要对此类“始终加密”表进行解密以供业务使用，才能以纯文本形式查看数据，Power BI本机驱动程序此时（2020年3月）无法解密“始终加密的数据”，此处描述的解决方案满足了在Power BI中以纯文本方式查看“始终加密数据”的要求。

解决方案启用的其他相关用例

此处共享的解决方案不仅限于Power BI用例，还适用于许多不同的场景，如：

• Azure数据工厂本机驱动程序无法读取始终加密的数据，因此可能需要从Azure SQL数据库读取加密数据，并将其解密格式保存在数据湖敏感区域中。
• Tableau需要用于对始终加密的Azure SQL数据库进行报告。
• 需要通过ODBC使用Azure虚拟机的Azure Active Directory托管标识访问Azure SQL数据库--这是一个独特的客户用例，其中有用户在Azure中分配了VM，但他们不是Azure Active Directory的一部分，目标是不使用SQL身份验证（需要维护用户名/密码），但同时也有一些原因不将这些用户添加到Azure Active Directory，尽管数据库不是总是加密的（事实上，数据库是Azure Synapse，以前是Azure SQL数据仓库），但这种技术很有用。

解决方案架构

该解决方案需要使用ODBC来连接和解密来自Azure SQL数据库的数据，下图显示了该解决方案的总体架构。

解决方案详细信息

假定具备Azure平台的基本知识和对Azure订阅的访问权限。另一个先决条件是Azure SQL数据库，它有一个表，其中至少有几列使用“始终加密”功能加密，加密密钥保存在Azure密钥库中。此处记录了使用密钥保管库设置此类Azure SQL数据库的详细说明

— https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault?tabs=azure-powershell

请注意，“始终加密”功能可以使用Azure密钥库或Windows证书存储中的密钥，但在这篇文章中，我将使用Azure密钥存储（它尚未经过验证，但预计类似的原则也适用于Windows证书存储）。

分步说明

1. 创建Azure Windows虚拟机（任何Windows Server 2016或更高版本都应该工作）--此虚拟机用于Power BI内部部署数据网关，创建虚拟机的说明 https://docs.microsoft.com/en-us/azure/virtual-machines/windows/quick-create-portal
2.  一旦虚拟机的创建完成，请在虚拟机上启用托管身份，如下面的屏幕截图所示。托管身份是一项非常有用的功能，可作为Azure平台的一部分使用，其中像Azure虚拟机这样的单个服务具有与其关联的Azure AD身份，并且在该虚拟机中运行的代码可以在没有任何密码的情况下使用该身份。您可以在此处阅读有关托管身份的更多信息 — https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/overview

3. 授予Azure SQL数据库上托管标识的权限，因为将使用此标识检索数据库中的数据：

• 如果Azure虚拟机名称为MyDataGatewayVM，则在启用托管身份时会自动创建具有相同名称的Azure AD应用程序。
• 以下是在SQL数据库上授予托管标识权限的示例命令

CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER Mary;//Command for Azure SQL Data Warehouse to add user to a role is slightly differentEXEC sp_addrolemember ‘db_owner’, ‘Mary’;

Please see the documentation for more detail around granting accessing to Azure SQL and Synapse — https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

4.创建Azure AD应用程序--ODBC驱动程序将使用此应用程序从Azure密钥库中检索解密密钥。使用以下链接上的说明创建另一个Azure AD应用程序和密钥，注意ClientID和密钥以备以后使用。

• Only execute the “Create an Azure Active Directory application” section — https://docs.microsoft.com/en-us/azure/active-directory/develop/howto-create-service-principal-portal#create-an-azure-active-directory-application
• Only execute “Create a new application secret” sub-section- https://docs.microsoft.com/en-us/azure/active-directory/develop/howto-create-service-principal-portal#certificates-and-secrets

5. Grant Azure AD application permissions to read keys on Azure Key Vault using the instructions documented here — https://docs.microsoft.com/en-us/azure/key-vault/managed-identity

Make sure to grant “Key Permissions” Get, List, Decrypt, Unwrap and Verify

6. Setup On-Premises Power BI Data Gateway VM in Azure, this is software that will need to be installed on Azure VM which serves as an intermediary between SQL Database and Power BI Service — https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem

Usually this is used to access data from on-premises data stores but in this solution this is required for Azure SQL Database to perform decryption of the Always Encrypted Data

If your use case is for Azure Data Factory, the solution here is exactly the same except Self-Hosted Integration Runtime software needs to be used instead of On-Premises Data Gateway — https://docs.microsoft.com/en-us/azure/data-factory/create-self-hosted-integration-runtime Self-Hosted Integration Runtime agent works in the same manner as On-Premises Power BI Data Gateway.

7. Download and Install ODBC Driver 17 or newer for SQL Server on the VM — https://www.microsoft.com/en-us/download/details.aspx?id=56567

8.设置ODBC系统DSN--安装ODBC驱动程序后，您需要设置ODBC系统的DSN，如下面的屏幕截图所示

• 通过在搜索栏中键入ODBC启动ODBC应用程序，选择ODBC数据源（64位）
• 选择“系统DSN”选项卡，单击“添加”，选择“ODBC Driver 17 for SQL Server”，然后单击“完成”

Go through the wizard with default options for most part except couple screens.

Make sure to put Azure SQL Server DNS Name for Server

Make use to change the default database to the Azure SQL Database Name and also check Column Encryption Checkbox

8.ODBC系统DSN的注册表设置更新--使此ODBC连接与Power BI一起工作的关键因素是该连接需要在非交互式模式下工作，为此，我们需要修改Power BI数据网关VM上ODBC连接的一些注册表项。使用RegEdit命令打开注册表编辑器并设置以下注册表项（也显示在下面的屏幕截图中）

• 身份验证--将值设置为ActiveDirectoryMsi
• KeystoreAuthentication--将值设置为KeyVaultClientSecret
• KeystorePrincipalID——这是在上面的步骤2中创建的Azure AD应用程序的ClientID，用于从密钥库中检索解密密钥
• KeystoreSecret--这是在上面的步骤2中创建的Azure AD应用程序的密钥，它与KeyStorePrincipalID一起用于从KeyVault检索解密密钥

注意：出于保密原因，屏幕截图中划掉了KeystorePrincipalID、KeyStoreSecret和Server的值

在要求仅使用具有托管身份KeyStoreAuthentication的ODBC访问Azure SQL数据库的用例中，KeyStorePrincipalID和KeystoreSecret值可以保留为空。

9.[可选]使用Powershell测试ODBC连接--现在，您可以在Powershell提示符下使用以下Powershell片段来验证ODBC连接是否正常工作（请不要忘记替换最后一行中的表名和DSN名）。如果一切都设置正确，您应该看到数据库表中的数据以纯文本显示。

function Get-ODBC-Data{
param([string]$query=$(throw 'query is required.'),[string]$dsn)write-output("Dsn: " + $dsn)
$conn = New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString = "DSN=$dsn;"
$conn.open()$cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)$ds = New-Object system.Data.DataSet
(New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null
$conn.close()$ds.Tables[0]
}#replace the System DSN Name and the Table Name
Get-ODBC-Data -query "select * from XXXX" -dsn "XXXX"

10.实现报告并将其发布到Power BI服务--此时，您可以在VM上使用Power BI Desktop，并使用ODBC连接到Azure SQL数据库，在“数据加载预览”窗口中，您将看到未加密的数据。将Power BI服务配置为使用数据管理网关后，您可以将报告发布到Power BI服务，报告用户将能够看到满足其业务需求的所需数据。访问控制可在Power BI服务中保留。

注意：这里需要注意的一点是，Power BI Reports只支持导入模式，而不能使用直接查询模式。