Find Log Analytics Key of Azure Log Analytics Instance

 Issue: Find the keys to connect to a log analytics instance in Azure.

Solution:  You can find this under Log Analytics Workspace > Agents > Log Analytics Agent Instructions

Synapse Analytics ADF Error - Failed to encrypt sub-resource payload or linked service credentials [Solved]

Issue: While trying to publish an HTTP or REST linked service connection from master to live, we get an error if it is going through a self hosted Integration run time to an on-premise network. 

Publishing error

Failed to encrypt sub-resource payload

and error is: Failed to encrypted linked service credentials on self-hosted IR 'selfHostedIR', reason is: NotFound, error message is: No online instance..

Cause: When you have a HTTP call that happens inside an on-premise network and you use a self hosted integration run time to connect to this, the first time the linked service is published it has to validate the connection and hence requires the Self Hosted IR to be up and running. 

Solution: The solution is to have the self hosted IR( Azure VM in our case ) up and running while trying to publish from master to live. This enables the validation of the linked service connection, this fixed it for us.

Please do let us know if this fix worked for you. 

Storage Event Trigger Error - Register Azure Event Grid resource provider

Issue: Trying to create a storage event trigger fails in Synapse/ Azure Data Factory with the below error 

Register Azure Event Grid resource provider to your subscription before creating an event trigger.

Cause: This is caused because Azure Event Grid resource provider is not added or enabled under your Azure Subscription. 

If you are using Azure Synapse Analytics, then you also need to have the Microsoft.DataFactory resource provider regsitered.

Resolution: You can follow the steps to register event grid and data factory resource provider to your subscriptions.

Step 1: go to portal.azure.com and select the subscription where you want to create the storage event trigger.

Step 2: under the subscription go to settings > resource providers.

Step 3: Search and Register the two resource Providers 

Registering Microsoft.DataFactory resource provider

Registering Microsoft.EventGrid resource provider

Try again and now it should be working. Let us know if this worked for you.

ADF / Synapse - Error converting Excel to Parquet

Error in Synapse / Azure Data Factory

Aim - Build a pipeline that can take an excel as input in a file contianer and write into a parquet file. 

Issue:- When processing the file, the copy activity runs into an error saying one of the fields cannot have more than one value. 

"errorCode": "2200",

    "message": "ErrorCode=ParquetJavaInvocationException,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=An error occurred when invoking java, message: java.lang.IllegalStateException:field 42 (COUNTRY_NAME) can not have more than one value: [Ägypten]\ntotal entry:6\r\norg.apache.parquet.example.data.simple.SimpleGroup.add(SimpleGroup.java:106)\r\norg.apache.parquet.example.data.simple.SimpleGroup.add(SimpleGroup.java:196)\r\norg.apache.parquet.example.data.Group.add(Group.java:59)\r\norg.apache.parquet.example.data.Group.append(Group.java:137)\r\ncom.microsoft.datatransfer.bridge.parquet.ParquetBatchWriter.readRow(ParquetBatchWriter.java:112)\r\ncom.microsoft.datatransfer.bridge.parquet.ParquetBatchWriter.addRows(ParquetBatchWriter.java:60)\r\n.,Source=Microsoft.DataTransfer.Richfile.ParquetTransferPlugin,''Type=Microsoft.DataTransfer.Richfile.JniExt.JavaBridgeException,Message=,Source=Microsoft.DataTransfer.Richfile.HiveOrcBridge,'",

    "failureType": "UserError",

    "target": "Copy Bronze - Excel to Parquet",

Cause:- This has to with the excel file having two or more columns with the same name. We had the field "COUNTRY_NAME" twice in the excel and this was causing the issue. 

Fix: Remove the duplicated field and re-process the file again.

Self Hosted Integration Runtime sharing between Synapse Workspaces

Scenario: We have to connect to an on-premise SQL Server/Oracle database and have three synapse workspaces in Azure, DEV, UAT and PROD in three different subscriptions. 

Current- Architecture

To connect to these sources on the on-premise networks we have virtual networks set up on Azure, gateways and site to site connections to local network.

The downside of this is having to maintain a lot more from an administrator perspective,

1. Three Virtual Networks 
2. Peering between three networks
3. Three seperate Virtual Machines to host the respective Azure Self Hosted IRs

Instead of this, we thought of simplifying it and having only one Virtual Network, one VM where self hosted integration runtime is hosted and the IR is shared by all three Synapse workspaces, see below architecture, 

Proposed Architecture with Shared Self Hosted IR.

Now comes the road-block here, sharing integration run-time is not yet available for Synapse Analytics ( reference - https://learn.microsoft.com/en-us/azure/synapse-analytics/data-integration/concepts-data-factory-differences#available-features-in-adf--azure-synapse-analytics  )

 

This seems to be something in the backlog for the Synapse Analytics Product team, I will be updating here if this comes out in any new releases. This will be really cool to have. 

Azure Synapse Analytics workspace outage in West Europe - 23rd January 2023

There seems to be a region wide outage for Azure Synapse Analytics workspaces in the West Europe Region. We have not been able to open up our synapse workspaces hosted in West Europe region from the morning.

Twitter users were the first to report this to Azure support early in the morning today in CET time.

It is really weird as Microsoft Health Service doesn't show any service as down in west europe region,

A couple of hours later they have put up a notification as an emerging issue on the same. 

Impact Statement: Starting at 05:30 UTC on 23 Jan 2022, a number of Azure Synapse Analytics customers with resources in West Europe may be experiencing connectivity issues when trying to access their workspaces. Current Status: We are aware of this issue and are currently investigating to understand the underlying cause and work on a mitigation strategy. Further updates will be provided in 60 minutes, or as events warrant.

Microsoft is yet to provide an ETA on the same. There have also been multiple community posts raised asking for help on the same. 

Users are calling out Azure for not reporting the same on health portal.

We will keep following up and sharing udpates here. You could also follow the below community portal question to get updates on the same. c

https://learn.microsoft.com/en-us/answers/questions/1163259/synapse-workspace-in-west-europe-not-opening-up

update 11:05 AM UTC 

Update 12:50 PMUTC

 Update 18:17 PM UTC ( Hotfix applied and rolled out)

Our instances are up at the moment.

Create Power BI user for Synapse Serverless SQL Pool to access External Delta Lake Tables

Problem Statement: You are trying to create a seperate user for reading datasets in Power BI. 

Issue: After creating the user login, you are facing this error while trying to read your external tables or view

Msg 13807, Level 16, State 1, Procedure Dim_ABC, Line 2 [Batch Start Line 0]

Content of directory on path 'https://data.dfs.core.windows.net/data/Gold/Dim_ABC/_delta_log/*.*' cannot be listed.

Msg 4413, Level 16, State 1, Line 1

Could not use view or function 'dbo.Dim_ABC' because of binding errors.

Solution: 

Step 1: Create a Master Key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*******'

Step 2: Create a Database Scoped Credential with Managed Identity

CREATE DATABASE SCOPED CREDENTIAL [PowerBICredential]

WITH IDENTITY = 'Managed Identity' 

Step 3: Create a Power BI login

CREATE LOGIN PowerBI_RO WITH PASSWORD = '*******';

GO

CREATE USER PowerBI_RO FROM LOGIN PowerBI_RO

GO

ALTER ROLE db_datareader ADD  MEMBER PowerBI_RO;

Step 4 -Grant Access to Power BI Scoped Credential to Power BI login

GRANT CONTROL TO PowerBI_RO

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[PowerBICredential] TO PowerBI_RO

Step 5 - Create an External Data Source associated to this credential

CREATE EXTERNAL DATA SOURCE [DeltaLakeStorage]

    WITH (

        LOCATION = 'abfss://containername@datalakename.dfs.core.windows.net/'

        ,        CREDENTIAL= [PowerBICredential] 

    )

Step 6: Login and run a select statement and you should be able to use this to view delta lake tables.

Update(26th January): Microsoft Synapse Analytics youtube page has recently published a video tutorial on how to do this. - https://www.youtube.com/watch?v=_OZ7XUapJZs