Terraform, AzApi, Sql Managed Instance, DTC and it's consequences

Something from my daily work (It is possible, that I will more about it as I see daily issues).
I got an assignment to enhance out Azure SQL Managed Instances Terraform deployment module with DTC (Yes, it is a good question, why on earth this outdated technology is still needed).
For the DTC you will need two resources added to your managed instance. One is the DTC configuration, the other is the serverTrustGroup what determine which of your managed instances trust each other for the distributed transaction.
As I not even surprised, none of the above exists in the Terrafrom AzureRM provider, so you need to use AzAPI provider for it.
The DTC setting itself is a piece of cake - if you have experience using the AzAPI resources you can easily handle it.
The trustGroup is a different animal.
Here you can find the Microsoft documentation for it: https://learn.microsoft.com/en-us/azure/templates/microsoft.sql/2023-05-01-preview/locations/servertrustgroups?pivots=deployment-language-terraform
You just add the resource and done...


resource "azapi_resource" "symbolicname" {
type = "Microsoft.Sql/locations/serverTrustGroups@2023-05-01-preview"
name = "string"
parent_id = "string"
body = jsonencode({
properties = {
groupMembers = [
{
serverId = "string"
}
]
trustScopes = [
"string"
]
}
})
}


Not exactly. The name, groupMembers, trustScopes are explained in the documentation. The parent_id is not exactly. The documentation said:
"The ID of the resource that is the parent for this resource.
ID for resource of type: locations"
What this means to you?
Usually in the Azure "location" is used as the synonym of "region" - here: wrong
Even, if it would be the region, where should it be located in the management group/subscription/resource group hierarchy?
Anyway, as a normal Azure resource, it should be located in a resource group. Ok, add the resource group ID there. The Terraform give back this error:
"Error: `parent_id is invalid`: expect ID of `Microsoft.Sql/locations`"
What a hack is this Microsoft.Sql/locations ?
Looked through the internet. Yes it is part of the object hierarchy of the serverTrustGroups but, as resource it doesn't exists.
Ok. Deploy the Trust Group manually. Done it. On the portal you can't see the JSON of it.
Try other way.
Try to get it back via AZ CLI:


az sql stg list -g <resource group name> --instance-name <name>


The result:


[
{
"groupMembers": [
{
"serverId": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/<resource group name>/providers/Microsoft.Sql/managedInstances/sql-test-01"
},
{
"serverId": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/<resource group name>/providers/Microsoft.Sql/managedInstances/sql-test-02"
}
],
"id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/<resource group name>/providers/Microsoft.Sql/locations/West Europe/serverTrustGroups/trust-group",
"name": "trust-group",
"resourceGroup": " <resource group name>",
"trustScopes": [
"GlobalTransactions",
"ServiceBroker"
],
"type": "Microsoft.Sql/locations/serverTrustGroups"
}
]

Now, can you see?
After this, our code will look like something this:


resource "azapi_resource" "trust-group" {
  type = "Microsoft.Sql/locations/serverTrustGroups@2023-05-01-preview"
  name = var.trust_group_name
  parent_id = "${var.resource_group_id}/providers/Microsoft.Sql/locations/${var.region}"
  body = jsonencode({
    properties = {
    groupMembers = [for ServerID in var.groupMembers : { "serverId" = ServerID }]
    trustScopes = var.trustScopes
    }
  })
}


And I try not to judge the quality of the Microsoft documentation ...

Terraform, Cloudformation and Cognito

Some things what more of my professional side than the hobby, electronics, maker things.

I'm working with IaC tools since... don't even remember. I started with AWS CloudFormation around 2014 (not sure, it is cloudy. 😆).

When I learned about Terraform, I had a fear, that I'll not get the full functionality of AWS. The CloudFormation is a native tool. Something developed by a 3rd party should fall behind the native tool of the provider.

I started using Terraform as the customer required it. So switching from Cloudformation to Terraform wasn't my decision. The fear I described above just become reality when the customer didn't wanted to upgrade the AWS provider in their deploy agents, so it wasn't the fault of HashiCorp or the Terraform community. Also, writing Terraform code is much more convenient, give you more capabilities on processing, converting the required information.

A few weeks ago, after three years exclusive Terraform work, I got a project what use CloudFormation again.

There was a - I guessed - super easy task. Publish the secret of a Cognito User Pool Client into an SSM Parameter Store value as Secret String.

In Terraform this would look like this:

// Create Cognito User Pool
resource "aws_cognito_user_pool" "pool" {
  name = "pool"
}

// Create Cognito User Pool Client
resource "aws_cognito_user_pool_client" "client" {
  name = "client"
  user_pool_id = aws_cognito_user_pool.pool.id
  generate_secret     = true
}

// Publish to SSM Parameter Store
resource "aws_ssm_parameter" "secret" {
  name        = "/production/cognito/clientSecret"
  type        = "SecureString"
  value       = ${aws_cognito_user_pool_client.client.client_secret}
}

And we are done.

Try the same in CloudFormation.

Can the AWS::Cognito::UserPoolClient object export the secret? Nope. Ref: https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-cognito-userpoolclient.html

Can the AWS::SSM::Parameter object create SecureString? Nope. Ref: https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-ssm-parameter.html

Here my previous theory on the "CloudFormation should be ahead of Terraform" fall.

I tried several things finally this was working (One small comment: the import cfnresponse is in a separate line. It is not because of my lazyness. It is because otherwise the infrastructure doesn't realize, the cfnresponse.py should be added to the zip as it is not a part of the pip repo):

Resources:
  # Create Cognito User Pool
  UserPool:
    Type: "AWS::Cognito::UserPool"
    Properties:
      UserPoolName: pool

  # Create Cognito User Pool Client
  UserPoolClient:
    Type: "AWS::Cognito::UserPoolClient"
    Properties:
      AccessTokenValidity: 15
      AllowedOAuthFlowsUserPoolClient: false
      ClientName: client
      GenerateSecret: true

  # You need a lambda to
  #     - read from the cognito user pool client
  #     - write to the parameter store
  #     - report back to the CloudFormation when finished
  CognitoSecretExporterLambda:
    Type: AWS::Lambda::Function
    Properties:
      FunctionName: CognitoSecretExporter
      Runtime: python3.9
      Role: !GetAtt CognitoSecretExporterExecutionRole.Arn
      Handler: index.handler
      Timeout: 30
      Environment:
        Variables:
          CognitoUserPoolId: !Ref UserPool
          CognitoUserPoolClientId: !Ref UserPoolClient
          ssmParameterName: /production/cognito/ClientSecret
      Code:
        ZipFile: |
          import boto3, os
          import cfnresponse

          def handler(event, context):
              CognitoUserPoolId = os.environ['CognitoUserPoolId']
              CognitoUserPoolClientId = os.environ['CognitoUserPoolClientId']
              ssmParameterName = os.environ['ssmParameterName']

              # Read Cognito
              cognito = boto3.client('cognito-idp')
              response = cognito.describe_user_pool_client(
                  UserPoolId = CognitoUserPoolId,
                  ClientId = CognitoUserPoolClientId
              )
              cognitoClientSecret = response['UserPoolClient']['ClientSecret']
              print(cognitoClientSecret)
              ssm = boto3.client('ssm')

              # Write to parameter store
              response = ssm.put_parameter(
                  Name = ssmParameterName,
                  Description = '[CF] Cognito Client Secret used by the WebApp',
                  Value = cognitoClientSecret,
                  Type = 'SecureString',
                  KeyId = 'alias/aws/ssm',
                  Overwrite = True,
                  Tier='Standard'
              )

              # Report success to CloudFormation
              responseData = {}
              responseData['Data'] = 120
              cfnresponse.send(event, context, cfnresponse.SUCCESS, responseData) 

  # Trigger the lambda from the CloudFormation stack
  CognitoSecretExporterInvoke:
    Type: AWS::CloudFormation::CustomResource
    DependsOn: CognitoSecretExporterLambda
    Version: "1.0"
    Properties:
      ServiceToken: !GetAtt CognitoSecretExporterLambda.Arn 

  # IAM Role for the lambda above
  CognitoSecretExporterExecutionRole:
    Type: AWS::IAM::Role
    Properties:
      RoleName: CognitoSecretExporterExecutionRole
      AssumeRolePolicyDocument:
        Version: '2012-10-17'
        Statement:
        - Effect: Allow
          Principal:
            Service:
              - lambda.amazonaws.com
          Action:
            - sts:AssumeRole
      Path: "/"
      Policies:
      - PolicyName: root
      PolicyDocument:
          Version: '2012-10-17'
          Statement:
            - Sid: EnablePutLogEvents
              Effect: Allow
              Action:
                - logs:CreateLogGroup
                - logs:CreateLogStream
                - logs:PutLogEvents
              Resource:
                - '*'
            - Sid: ReadCognito
              Effect: Allow
              Action:
                - cognito-idp:DescribeUserPoolClient
              Resource:
                - !GetAtt UserPool.Arn
            - Sid: ParamStore
              Effect: Allow
              Action:
                - ssm:DeleteParameter
                - ssm:PutParameter
                - ssm:GetParameter
              Resource: 
                - !Sub arn:aws:ssm:${AWS::Region}:${AWS::AccountId}:parameter/production/cognito/ClientSecret