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

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:

I opened “Purview Studio”
There I have created a “Collection”
Then I go to “Register Sources”
Then I search for “Azure Synapse Dedicated Pool”
Then I select the subscription where my Azure Synapse Dedicated Pool is present
Then I Registered my Data Source
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 publically 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..

Solution

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

According to Microsoft official documentation, to execute the above command one must be Azure Synapse Administrator in the workspace. It is alsi required that your purview account name must have reader role set which can be done from Access Control (IAM) under the Azure Synapse Workspace resource.

To create SQL Pools, Apache Spark Pools and Integration Runtimes, users must have at least Azure Contributor role in the workspace. The contributor role also allows these users to manage the resources, including pausing and scaling. If you're- using Azure Portal or Synapse Studio to create SQL Pools, Apache Spark Pools and INtegration Runtimes, then you need Azure Contributor role at the resource group level.

To GRANT access to a Dedicated SQL Pool database, the scripts can be run by the workspace creator or any member of the workspace1_SynapseAdministrators group.

Follow the below steps in the Azure Synapse SQL script editor:

Create the USER in the database by running the following command on the target database, selected using the Connect to dropdown:

CREATE USER [<alias@domain.com>] FROM EXTERNAL PROVIDER;

Grant a user a role to access the database

EXEC sp_addrolemember 'db_owner', '<alias@domain.com>'