Failed to access the Azure Dedicated SQL pool with the given credentials Azure Purview

Question

Our organization having an Azure Synapse Dedicated Pool instance. I am trying to register the Azure Synapse Dedicated Pool with Azure Purview and want to scan the Synapse DB. However, I am getting the following Error every time:

“Failed to access the Azure Dedicated SQL pool with the given credentials”

Following are the process I followed to Register the Data Source:

1. I opened “Purview Studio”
2. There I have created a “Collection”
3. Then I go to “Register Sources”
4. Then I search for “Azure Synapse Dedicated Pool”
5. Then I select the subscription where my Azure Synapse Dedicated Pool is present
6. Then I Registered my Data Source
7. Now I am trying to create a New Scan for my Synapse Dedicated Pool

The problem starts from here, First of all I selected the subscription, then I selected the resource group and then I selected the Synapse DB name. I tried two authentication methods to authenticate my Synapse Instance. First one is Purview MSI account and second one is SQL Authentication. I have added my Purview MSI account as a user in Synapse Dedicated pool using following command.

CREATE USER [PurviewAccountName] FROM EXTERNAL PROVIDER
GO
EXEC sp_addrolemember 'db_datareader', [PurviewAccountName]
GO

Now I tried to test the connection but it is not working and giving me following Error:

“Failed to access the Azure Dedicated SQL pool with Purview MSI account”

My Azure Synapse Dedicated Pool instance in not publicly accessible, we have put it behind the private link. I can connect my Azure Synapse Instance using VPN connectivity on my machine and login through SSMS and Azure Data Studio.

I also tried with SQL authentication by using SQL username and Password which is kept under the KeyVault. I have checked it multiple times and I am confident I have configured it correctly. But still when I try to test the connection. It is showing following error:

“Failed to access the Azure Dedicated SQL pool with the given credentials”

Some where I have read I need self-hosted-integration runtime if the Azure Synapse instance is behind private link.

So I installed integration runtime on my machine, configure it and tested for the Synapse connection with SQL Authentication by connecting to VPN.
Self-Hosted IR configured successfully. I tested with both the IR. Azure IR and Self-hosted-IR. But no luck, I am getting the same error.

I have also added Purview MSI account to Access Policy in keyvault and provided GET, List permission on keys and Secrets.

However, I am not getting what I am missing here and why it is giving me the same error.

Any help on this is really means a lot me..

Answer 1

@swapnil kamle , I am reposting the solution that worked for you. Please mark it as an answer for everyone’s benefit. Thanks!

Please follow this link https://learn.microsoft.com/en-us/azure/purview/register-scan-synapse-workspace . As per the link, authentication for enumerating Synapse SQL resources requires Reader role to be assigned to Purview MSI on Synapse Workspace.
Please also setup SHIR which has line of sight to both the private endpoints of Synapse & Purview