How do you create an Event Grid Subscription for successful SQL DacPac imports?

Question

I am not able to create an Event Grid Subscription for the action "Create new database from DacPac".

I have tried creating an Event Grid Subscription for the Resource Group that contains my SQL Server. I have enabled all events to send for the Resource Group and registered a webhook to send for the events on the resource group. I have tested the Event Grid Subscription, and it is sending webhooks for ResourceWriteSuccess, ResourceDeleteSuccess, etc. So I know the implementation between my application and Azure is functioning properly.

Here is the payload of the event I am trying to create an Event Grid Subscription for.

{
    "authorization": {
        "action": "Microsoft.Sql/servers/import/action",
        "scope": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/my-resource-group/providers/Microsoft.Sql/servers/my-sql-server"
    },
    "caller": "00000000-0000-0000-0000-000000000000",
    "channels": "Operation",
    "claims": {
        "aud": "https://management.azure.com/",
        "iss": "https://sts.windows.net/00000000-0000-0000-0000-000000000000/",
        "iat": "1560193452",
        "nbf": "1560193452",
        "exp": "1560197352",
        "aio": "82WgYJi2DHbwKEodlmt4/lNKdx2cDQA=",
        "appid": "00000000-0000-0000-0000-000000000000",
        "appidacr": "1",
        "http://schemas.microsoft.com/identity/claims/identityprovider": "https://sts.windows.net/00000000-0000-0000-0000-000000000000/",
        "http://schemas.microsoft.com/identity/claims/objectidentifier": "00000000-0000-0000-0000-000000000000",
        "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/nameidentifier": "00000000-0000-0000-0000-000000000000",
        "http://schemas.microsoft.com/identity/claims/tenantid": "00000000-0000-0000-0000-000000000000",
        "uti": "OzdZ2AA_HkCWRjLQ0E9hAQ",
        "ver": "1.0"
    },
    "correlationId": "00000000-0000-0000-0000-000000000000",
    "description": "",
    "eventDataId": "00000000-0000-0000-0000-000000000000",
    "eventName": {
        "value": "EndRequest",
        "localizedValue": "End request"
    },
    "category": {
        "value": "Administrative",
        "localizedValue": "Administrative"
    },
    "eventTimestamp": "2019-06-10T19:09:14.4117972Z",
    "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/my-resource-group/providers/Microsoft.Sql/servers/my-sql-server/events/00000000-0000-0000-0000-000000000000/ticks/636957905544117972",
    "level": "Informational",
    "operationId": "5b1a45c6-297c-4c1e-81c5-31a10375a9e0",
    "operationName": {
        "value": "Microsoft.Sql/servers/import/action",
        "localizedValue": "Create new database from DacPac"
    },
    "resourceGroupName": "my-resource-group",
    "resourceProviderName": {
        "value": "Microsoft.Sql",
        "localizedValue": "Microsoft SQL"
    },
    "resourceType": {
        "value": "Microsoft.Sql/servers",
        "localizedValue": "Microsoft.Sql/servers"
    },
    "resourceId": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/my-resource-group/providers/Microsoft.Sql/servers/my-sql-server",
    "status": {
        "value": "Accepted",
        "localizedValue": "Accepted"
    },
    "subStatus": {
        "value": "Accepted",
        "localizedValue": "Accepted (HTTP Status Code: 202)"
    },
    "submissionTimestamp": "2019-06-10T19:10:57.1665135Z",
    "subscriptionId": "00000000-0000-0000-0000-000000000000",
    "properties": {
        "statusCode": "Accepted",
        "serviceRequestId": "00000000-0000-0000-0000-000000000000"
    },
    "relatedEvents": []
}

I expected that creating an Event Grid Subscription for a resource group with all events enabled would send a webhook for the event shown above; however, I am not getting any webhooks indicating that this operation was performed successfully.

For test purpose. make an export/import your test database to/from storage container using a portal UI. You should see multiple eventTypes such as: Microsoft.Resources.ResourceActionSuccess and Microsoft.Resources.ResourceWriteSuccess in your subscriber. — Roman Kiss
Yes, I do receive these events when importing through the portal; however, I do not receive these events when I make import through the Azure API. In my application, we automate the on-boarding of existing customers by allowing them to upload a database backup, and I need an event to know when the import completes. — Colin
How do you making a database import from your application, are you using the REST API calls? — Roman Kiss
Yes, I make the API call and it works great. The import works, and my database is created. Azure simply isn't firing an event for the resource action when called this way. — Colin
That's correct, I have tested for Export, Create Database (empty), Delete Database and my AEG subscriber receiving all events. But for Import, there is no event. Based on the docs docs.microsoft.com/en-us/rest/api/sql/…, the Import Operation must be done into an existing empty database, so I have created an empty database in prior of the Import (problably the same way is used by azure portal) and then I have received all events. — Roman Kiss

Roman Kiss Roman Kiss · Accepted Answer · 2019-06-13T11:57:49

Based on our discussion and testing:

Import database using a feature on the Azure portal is working very well, the portal Activity Log shows all steps of the importing background async process and also the AEG events are published.

Import database using the REST POST Databases - Import Export - Import has a different behavior in the Activity Log and there is no any AEG event published. The following example shows this issue:

POST: https://management.azure.com/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/Default-SQL-NorthCentralUS/providers/Microsoft.Sql/servers/h000000000/import?api-version=2014-04-01
{
  "databaseName": "TestDbImport",
  "edition": "Basic",
  "serviceObjectiveName": "Basic",
  "maxSizeBytes": "2147483648",
  "storageKeyType": "StorageAccessKey",
  "storageKey": "....",
  "storageUri": "https://xxxxxxx.blob.core.windows.net/database/testbacpac.bacpac",
  "administratorLogin": "myadmin",
  "administratorLoginPassword": "********",
  "authenticationType": "SQL"
}

The POST is Accepted and the portal Active Log is shoving the following:

the above log message is stayed forever, it indicated, that the Import operation has been accepted, but there are no more activity logs from this Import background process. There is no log message the process has been completed, etc. Also, there is no single AEG event published from this Import process. On the other side, the database has been created, updated, etc. but we don't have any indication about the completion. Note, that the Activity Log status should be finished in either the Failed or Succeeded state.

I can see here, there is an issue. We can not expect an AEG events from this resource group, if there is no Activity Log messages. It looks like, there is a possible an instrumentation bug in the Import operation process for Activity log messages.

Note, that other REST APIs such as Export, Create Database, Delete, etc. is working well included all Activity Logs and AEG events.

So, the following is a workaround for this protentional bug. It is based on the creating an empty database before using the Import operation.

The following is example:

Creating empty database:

    PUT: https://management.azure.com/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/Default-SQL-NorthCentralUS/providers/Microsoft.Sql/servers/h000000000/databases/TestDbImport?api-version=2017-10-01-preview
    {
      "location": "NorthCentralUS"
    }

After delivery an AEG event about the successful writing a database,

 "eventType": "Microsoft.Resources.ResourceWriteSuccess",
 "resourceUri": "/subscriptions/00000000-0000-0000-0000-000000000000/resourcegroups/Default-SQL-NorthCentralUS/providers/Microsoft.Sql/servers/h000000000/databases/TestDbImport",
 "operationName": "Microsoft.Sql/servers/databases/write",
 "status": "Succeeded",

the Import operation can be POSTed (see the above url and payload).

The following Activity Log from my portal shows all activities where the Import operation is done to the empty database:

I do recommend to make a feedback to the Azure SQL team, for this issue with an Import operation.

How do you create an Event Grid Subscription for successful SQL DacPac imports?

1 Answers