在Azure数据工厂中参数化连接(ARM模板) [英] Parameterize connections in Azure data factory (ARM templates)

问题描述

我正在尝试在CI/CD设置中设置Azure数据工厂.我遵循了Microsoft的最佳做法( https://docs. microsoft.com/en-us/azure/data-factory/continuous-integration-deployment ).

I am trying to setup an Azure Data Factory in a CI/CD setup. I followed Microsoft's best practices (https://docs.microsoft.com/en-us/azure/data-factory/continuous-integration-deployment).

我有4个环境(开发，测试，UAT，珠三角)

I have 4 environments (dev, test, UAT, PRD)

简而言之，我做了什么:

In short what I have done:

• 创建一个Azure数据工厂并将其链接到DEV环境上的我的Azure DevOps存储库

• Create an Azure data factory and link it to my Azure DevOps repo on DEV environment

在其他环境(测试，UAT和PRD)上创建Azure数据工厂，但不要将其链接到DevOps.取而代之的是，使用ARM模板在这些数据工厂上发布管道，并在Azure DevOps中发布管道.

Create an Azure data factory on the other environments (test, UAT and PRD), but do NOT link it to DevOps. Instead, pipelines are released on these data factories using ARM templates and release pipelines in Azure DevOps.

我已经参数化了所有必需的部分，以便能够覆盖每个环境中的设置.

I have parameterized all necessary parts to be able to overwrite the settings in each of my environments.

此刻，我能够成功部署到其他环境，但是，与Azure上的数据库的链接服务无法正常工作.我已经像微软建议的那样对所有参数进行了参数化，但是当我将我的链接服务导出到ARM模板时，它使用连接字符串而不是参数化设置.

At this moment, I am able to succesfully deploy to my other environments, however, the linkedservice to my database on azure is not working. I have parameterized everything, like Microsoft suggests, but when I export my linkedservice to an ARM template, it uses a connection string instead of my parameterized settings.

下面是Azure数据工厂门户中我的设置​​的图片:

Below is a picture of my settings in the Azure Data Factory portal:

当我尝试将其导出到ARM模板时，得到以下信息:

When I try to export this to ARM templates, I get the following:

{
            "name": "[concat(parameters('factoryName'), '/database')]",
            "type": "Microsoft.DataFactory/factories/linkedServices",
            "apiVersion": "2018-06-01",
            "properties": {
                "parameters": {
                    "sqlServerUrl": {
                        "type": "string"
                    },
                    "databaseName": {
                        "type": "string"
                    },
                    "sqlPwd": {
                        "type": "string"
                    },
                    "sqlAdminUsername": {
                        "type": "string"
                    }
                },
                "annotations": [],
                "type": "AzureSqlDatabase",
                "typeProperties": {
                    "connectionString": {
                        "type": "SecureString",
                        "value": "[parameters('database_connectionString')]"
                    }
                }
            },
            "dependsOn": []
        },

此ARM模板的问题在于它不使用参数来创建连接字符串，而是使用连接字符串参数database_connectionString(默认情况下，连接字符串始终由Azure参数化，因此我无法删除此参数) .

The problem with this ARM templates is that it does not use the parameters to create the connection string, but it uses the connection string parameter database_connectionString (the connection string is by default always parameterized by Azure, so I cannot remove this parameter).

当发布管道使用此模板时，未填充连接字符串(仅填充了参数)，因此，与数据库的连接失败.您应该如何设置连接，以便仅更改参数即可自动(无需人工干预)部署到所有环境?

When the release pipeline uses this template, the connectionstring is not filled in (only the parameters are filled in) and hence, the connection to the database fails. How should you setup the connection so that you can automatically (without human interaction) deploy to all environments by only changing the parameters?

我不想更改来自Azure数据工厂的ARM模板，因为这需要人工交互

I do not want to change the ARM templates coming from Azure Data Factory, because this requires human interaction

推荐答案

提供的其他答案之一是使用覆盖参数的有效方法.此答案将提供不同的答案，以及有关如何定义SQL连接以及如何利用和实施通过Key Vault和Data Factory Integration进行的某些更新的更多上下文.

One of the other answers provided is a valid way using override parameters. This answer will provide a different answer as well as some more context on how to define the SQL connections and how to utilize and implement some of the updates made with Key Vault and Data Factory Integration.

如果对SQL使用on prem连接，则连接字符串将如下所示:

If using an on prem connection to SQL the conenction string will look like:

"Server={serverName};Database={databaseName};User ID={domain}\{userName};Password={password};Integrated Security=True;" 

引号是必需的，并且可以作为覆盖参数传递.

The quotes are required and can be passed in as an override parameter.

如果使用Azure数据库或什至使用密钥保管库，请添加托管身份，其中数据工厂支持，将其包含在ARM模板中

If using an Azure Database or even using Key Vault look to add Managed Identity which Data Factory Supports by including this in your ARM template

 "identity": {
        "type": "SystemAssigned"
    }

添加后，Azure SQL数据库将需要具有

Once this is added then the Azure SQL Database will need to have the managed identity added. This can be done via a reusable SQL Script like:

    DECLARE @rolename AS NVARCHAR(100) = 'DataFactory_User'
    DECLARE @username AS NVARCHAR(100) -- This will be the DataFactory name
    SET @username = 'DataFacotryName'

if exists(select * from sys.database_principals where name = @username and Type = 'X' or Type='E')
    BEGIN
        DECLARE @dropUserSql varchar(1000)
        SET @dropUserSql='DROP USER [' + @username + ']'
        PRINT 'Executing ' + @dropUserSql
        EXEC (@dropUserSql)
        PRINT 'Completed ' + @dropUserSql
    END

DECLARE @createUserSql varchar(1000)
SET @createUserSql='CREATE USER [' + @username + '] FROM EXTERNAL PROVIDER'
PRINT 'Executing ' + @createUserSql
EXEC (@createUserSql)
PRINT 'Completed ' + @createUserSql

我建议删除并重新创建该用户. SQL会识别托管身份的指纹，并且每次删除DataFactory并重新创建时，都会创建一个新的指纹.

I recommend dropping and recreating this user. SQL recognizes the thumbprint of the Managed Identity and everytime the DataFactory is dropped and recreated a new thumbprint is created.

在利用Key Vault方面，有一个

In terms of leveraging Key Vault there is a LinkedService type of Key Vault that relies on the Managed Identity described above to retrieve secrets.

如果Key Vault是通过ARM部署的，则需要将访问策略更新为与此类似的内容:

The Key Vault if deployed via ARM will need to have the access policy updated to something similar to this:

"accessPolicies": [
          {
            "tenantID": "[subscription().tenantId]",
            "objectId": "[reference(resourceId('Microsoft.DataFactory/factories/', parameters('DataFactoryName')), '2018-02-01', 'Full').identity.principalId]",
            "permissions": {
              "secrets": [
                "get"
              ],
              "keys": [
                "get"
              ],
              "certificates": [
                "import"
              ]
            },
            "dependsOn": [
              "[resourceId('Microsoft.DataFactory/factories/', parameters('DataFactoryName'))]"
            ]
          }
        ]

此代码段假定Key Vault和数据工厂位于同一ARM模板中.如果不是，则仍然可以通过

This snippet assumes the Key Vault and Data Factory are in the same ARM template. If they are not the access policy can still be accomplished via ARM by obtaining the ObjectId of the Data Factory defined Managed Identity and passing it in as the ObjectId and removing the dependsOn statement.

这篇关于在Azure数据工厂中参数化连接(ARM模板)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！