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