unable to connect synapse db

Question

Hi,

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'tcp:ag-syn-bi-group-d.sql.azuresynapse.net,1433', Database: 'bronze', User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.

Login failed for user '<token-identified principal>'.

unable to create link service for synapse database

Answer 1

Hi Vineet

Welcome to Microsoft Q&A forum and thanks for reaching out.

By looking at the error message and the SQL error number, below could be the possible root case and resolution for the issue.

• Possible root cause:
  • This error usually occurs when you are trying to access SQL data sources located in an Azure VM / VNet using Azure IR or
  • Firewall issues
  • Possible resolution/workaround: To be able to have a successful connection, you will need to install the Self-Hosted Integration Runtime in the Azure VM and whitelist the Machine’s IP address in Azure SQL VM inbound NSG rules and open Port 443 & 1433 to allow communication from Azure Data Factory to the SQL VM. Then you should be able to connect to SQL VM from Data Factory by choosing the Self-Hosted Integration Runtime in SQL Server linked service to connect. Here is the MS doc that explains how to create a Self-Hosted Integration Runtime: https://learn.microsoft.com/azure/data-factory/create-self-hosted-integration-runtime Additional information
• To explore more about ADF connectivity issues with Azure Synapse Analytics, Azure SQL Database, and SQL Server please refer to this doc: ADF connectivity issues with Azure Synapse Analytics, Azure SQL Database, and SQL Server
• Also please refer to this helpful MS doc : Troubleshooting connectivity issues and other errors with Azure SQL Database and Azure SQL Managed Instance Hope this info helps. Do let us know if you have further query.

---

Thank you Deepanshu, Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.

Answer 2

Based on this old thread :

Regarding your ask for Error: Login failed for user '<token-identified principal>', it means the user is invalid, usually related to a AAD user that does not have user created on SQL DB that you are trying to connect (User DB or Master DB) or that the user is not the AAD Server Admin.

Just create the user in the DB following the steps mentioned here: authentication-aad-configure

To create an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity, as a user with at least the ALTER ANY USER permission. Then use the following Transact-SQL syntax:

Copy

 CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;  
 CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;  
 CREATE USER [alice@fabrikam.onmicrosoft.com] FROM EXTERNAL PROVIDER;  

If you are connecting from SSMS you may also need to change the default database option (Image below). By default it will try to connect to master DB where this user may not exists there as AAD users are contained inside each user database.