unable to connect synapse db

Vineet S 385 Reputation points
2024-07-02T14:07:35.56+00:00

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

Azure Storage Accounts
Azure Storage Accounts
Globally unique resources that provide access to data management services and serve as the parent namespace for the services.
2,906 questions
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,665 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,110 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Deepanshukatara-6769 7,750 Reputation points
    2024-07-02T14:22:04.29+00:00

    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.


    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.


  2. Amira Bedhiafi 19,616 Reputation points
    2024-07-02T14:46:21.6533333+00:00

    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.

    240756-image.png