orchestration-iac/.github/workflows/sqlcmdexe.yml at main · datanatus/orchestration-iac · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
name: SQL command execution workflow

on:
  workflow_dispatch:
    inputs:
      resource_group:
        description: 'Azure resource group'
        type: string
        default: ''
        required: true
      sql_server:
        description: 'SQL server'
        type: string
        default: ''
        required: true
      sql_database:
        description: 'SQL database'
        type: string
        default: ''
        required: true

permissions:
  contents: read
  id-token: write

env:
  RESOURCEGROUP: ${{github.event.inputs.resource_group}}
  SQLSERVER: ${{github.event.inputs.sql_server}}
  SQLDATABASE: ${{github.event.inputs.sql_database}}
  CONNECTIONSTRING: "Server=tcp:${{github.event.inputs.sql_server}}.database.windows.net,1433;Initial Catalog=${{github.event.inputs.sql_database}};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Default;"

jobs:
  SQLCommandExecute:
    name: 'SQL Command Execution job'
    runs-on: ubuntu-latest
    steps:
      - name: 'Checkout repo'
        uses: actions/checkout@v4

      # - name: 'Login Azure - SP'
      #   uses: azure/login@v2
      #   with:
      #     creds: ${{secrets.AZURE_CREDENTIALS}}
      #     enable-AzPSSession: true

      - name: 'Login Azure - MI'
        uses: azure/login@v2
        with:
          client-id: ${{secrets.AZURE_CLIENT_ID}}
          tenant-id: ${{secrets.AZURE_TENANT_ID}}
          subscription-id: ${{secrets.AZURE_SUBSCRIPTION_ID}}
          enable-AzPSSession: true

      - name: 'Modify the SQL server firewall rule'
        uses: azure/cli@v2
        with:
          azcliversion: 'latest'
          inlineScript: |
            publicIp=$(curl api.ipify.org)
            az sql server firewall-rule create \
              --resource-group ${{env.RESOURCEGROUP}} \
              --server ${{env.SQLSERVER}} \
              --name "Allow Github actions" \
              --start-ip-address $publicIp \
              --end-ip-address $publicIp

      # - name: 'SQL Cmd exec - SP'
      #   shell: pwsh
      #   run: |
      #     Install-Module -Name Az -AllowClobber -Scope CurrentUser -Force -Confirm:$false
      #     Install-Module -Name SqlServer -Scope CurrentUser -Force -AllowClobber
      #     $clientid = "${{secrets.APP_REG_CLIENT_ID}}"
      #     $secret = "${{secrets.APP_REG_SECRET}}"
      #     $tenantid = "${{secrets.AZURE_TENANT_ID}}"
      #     $request = Invoke-RestMethod -Method POST `
      #      -Uri "https://login.microsoftonline.com/$tenantid/oauth2/token"`
      #      -Body @{ resource="https://database.windows.net/"; grant_type="client_credentials"; client_id=$clientid; client_secret=$secret }`
      #      -ContentType "application/x-www-form-urlencoded"
      #     $access_token = $request.access_token
      #     Write-Host "$access_token"
      #     Invoke-Sqlcmd -ServerInstance sql-orch-gartnercat.database.windows.net -Database AA-ConfigDB -AccessToken $access_token`
      #      -query "CREATE USER [mi-boc-gartnercat] FROM EXTERNAL PROVIDER; ALTER ROLE [db_owner] ADD MEMBER [mi-boc-gartnercat];"

      - name: 'SQL Cmd exec - MI'
        shell: pwsh
        run: |
          Install-Module -Name Az -AllowClobber -Scope CurrentUser -Force -Confirm:$false
          Install-Module -Name SqlServer -Scope CurrentUser -Force -AllowClobber
          Get-AzContext
          # Invoke-Sqlcmd -Query "CREATE USER [mi-boc-gartnercat] FROM EXTERNAL PROVIDER; ALTER ROLE [db_owner] ADD MEMBER [mi-boc-gartnercat];" -ConnectionString "Server=tcp:sql-orch-gartnercat.database.windows.net,1433;Initial Catalog=AA-ConfigDB;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Default;"
          $access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

          # Now that we have the token, we use it to connect to the database 'mydb' on server 'myserver'
          Invoke-Sqlcmd -ServerInstance sql-orch-gartnercat.database.windows.net -Database AA-ConfigDB -AccessToken $access_token `
                        -query 'CREATE USER [mi-boc-gartnercat] FROM EXTERNAL PROVIDER; ALTER ROLE [db_owner] ADD MEMBER [mi-boc-gartnercat];'