The connection to the sink database is failed. Detailed error message is: Login failed for user '<token-identified principal>'AzureSynapse Link forSQL

Question

I'm trying to create an Azure Synapse Link for Azure SQL Database, using the steps from here: https://learn.microsoft.com/en-us/azure/synapse-analytics/synapse-link/connect-synapse-link-sql-database

After I create the link connection and I want to start it I receive the following error: The connection to the sink database is failed. Detailed error message is: Login failed for user ''.

ConnectionToAzureDB

LinkConnection

Also I have configurated the Azure SQL database to use ADD Auth. The connection to the Azure Database seems to be working. My user ( used to create the Synapse workspace is Subscription Owner) The user is also owner of the storage account. I added the SQL Managed Identity as Storage Blob Data Contributor

Did anyone else got this error and manage to fix it?

Answer 1

There are certain limitations while connecting SQL Database to Synapse Link as per document:

• When setting up your workspace, users must select "Disable Managed Virtual Network" and "Allow connections from any IP addresses."
• A link connection cannot be enabled by Azure Synapse link for SQL if the database owner does not have a mapped log in. it will cause to get error.The (ALTER AUTHORIZATION command can be used to workaround this problem by changing the database owner to an user.)
• With fewer than 100 DTUs, the Free, Basic, or Standard tiers do not allow Azure Synapse Link for SQL.

With is limitation I tried to Connect SQL Database to Synapse Link and able to connect without error:

Answer 2

I was trying to create a Synapse Link service with On Premises SQL Server and getting following error

Failed to enable Synapse Link on the source due to 'Failed to enable the source database: Some internal error happened due to 'Calling internal service failed: Failed to execute non query on change publisher with status code 400 and error Fail to non-query change publisher with error: 'sqlErrorCode - 22301; exceptionCode - TransferServiceUnknowError; error - A database operation failed with the following error: 'Could not update the metadata. The failure occurred when executing the command '(null)'. The error/state returned was 15517/1: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request.'; detailedError - A database operation failed with the following error: 'Could not update the metadata. The failure occurred when executing the command '(null)'. The error/state returned was 15517/1: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request.' 

I resolved by by changing the corresponding database user to 'sa' and it works.

use [YourCorrespondingDatabase] EXEC sp_changedbowner 'sa'