Use Azure Stream Analytics Managed Identity to access SQL DB using terraform

Question

There is an option to create Managed Identity from terraform for Stream analytics job (azurerm_stream_analytics_job, using identity block).

And it is possible to use Managed Identity to connect to databases (as explained here)

But I could not find how to use managed identity to create input using azurerm_stream_analytics_reference_input_mssql

UPDATE:

To be clear, thats what I am after: And then

[Support for Azure Stream Analytics Reference Input MSSQL](https://github.com/hashicorp/terraform-provider-azurerm/issues/9231) and [New resource azurerm_stream_analytics_reference_input_mssql](https://github.com/hashicorp/terraform-provider-azurerm/pull/13822) — Ecstasy, Jul 28 '22 at 07:14
[azurerm_stream_analytics_reference_input_mssql - Example Usage](https://github.com/hashicorp/terraform-provider-azurerm/blob/034da45ed8634aaff1a485ad3f0e3a5ec30532fa/website/docs/r/stream_analytics_reference_input_mssql.html.markdown#example-usage) — Ecstasy, Jul 28 '22 at 07:21
@DeepDave-MT thanks for the links, but I am interested in managed identity specifically. As I understand, those examples have username and password as required fields — JoeBloggs, Jul 28 '22 at 11:21

Thomas · Accepted Answer · 2022-08-02T10:10:43.720

3

As Per July 2022

It does not look like terraform is supporting it (see documentation).

With this arm template, I was able to deploy ("authenticationMode": "Msi"):

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "streamAnalyticsJobName": {
      "type": "string"
    },
    "streamAnalyticsJobNameInputName": {
      "type": "string"
    },
    "sqlServerName": {
      "type": "string"
    },
    "databaseName": {
      "type": "string"
    }
  },
  "resources": [
    {
      "type": "Microsoft.StreamAnalytics/streamingjobs/inputs",
      "apiVersion": "2017-04-01-preview",
      "name": "[format('{0}/{1}', parameters('streamAnalyticsJobName'), parameters('streamAnalyticsJobNameInputName'))]",
      "properties": {
        "type": "Reference",
        "datasource": {
          "type": "Microsoft.Sql/Server/Database",
          "properties": {
            "authenticationMode": "Msi",
            "server": "[parameters('sqlServerName')]",
            "database": "[parameters('databaseName')]",
            "refreshType": "Static",
            "fullSnapshotQuery": "SELECT Id, Name, FullName\nFrom dbo.Device\nFOR SYSTEM_TIME AS OF @snapshotTime --Optional, available if table Device is temporal"
          }
        }
      }
    }
  ]
}

So you could always use azurerm_template_deployment resource to deploy using terraform.

edited Aug 02 '22 at 10:10

answered Jul 30 '22 at 05:14

Thomas

24,234
6
81
125

Yes, sorry if my question is not very clear. Is it possible to use terraform (azurerm_stream_analytics_reference_input_mssql) to connect to database using Managed Identity? – JoeBloggs Aug 02 '22 at 06:36
It is not supported by Azure for the moment so it can't be done using terraform. – Thomas Aug 02 '22 at 06:38
It is supported by Azure - Reference Input - SQL Database – JoeBloggs Aug 02 '22 at 08:51
have you check my link ? only reference data and outputs support managed identity not inputs. – Thomas Aug 02 '22 at 08:53
Yes, I might be unclear with wording. I am after Reference Input. I updated my question with screenshots from the portal – JoeBloggs Aug 02 '22 at 08:56
oh ok, just saw your edit, will have a look then. sounds like it is possible but the documentation is not up to date – Thomas Aug 02 '22 at 09:00
@JoeBloggs let me know if that make more sense for you. – Thomas Aug 02 '22 at 10:11
1

Yes, thank you very much I'll keep the question open for a bit, but it looks like your solution is the only option for now – JoeBloggs Aug 02 '22 at 11:19

Use Azure Stream Analytics Managed Identity to access SQL DB using terraform

1 Answers1