Forum Discussion

swapnilkamle's avatar
swapnilkamle
Copper Contributor
Mar 25, 2022

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:

 

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 with 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..