Creating new linked services returns SqlFailedToConnect

Question

I'd like to start by first stating that this is a continuation of my efforts to connect an Azure SQL DB to Data Factory via the Linked services.

Creating new linked services returns InvalidCredentialReference

My new error is below in the azure portal in Data Factory when creating a new linked service which is my existing Azure SQL DB:

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'log1.database.windows.net', Database: 'DB2', User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.
Database 'DB2' on server 'log1.database.windows.net' is not currently available.  Please retry the connection later. 

OR SOMETIMES THE LAST LINE SAYS:
Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'log1.database.windows.net', Database: 'DB2', 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>'.

I've tried: Azure SQL Server -> Access control (IAM) -> Role assignments -> added role assignment -> LOGDF (which is the data factory I created) I've given the following access.. roles: Owner, Contributor, SQL DB ContributorI've also ticked the check box "Allow Azure Services and Resources to access this server" in Security within the Server resource.

Answer 1

@Kristina Payan Thank you for reaching out.

Sorry about the issue you are facing.

Please check if you have enabled allow others azure services to access this server.

https://learn.microsoft.com/en-us/azure/azure-sql/database/network-access-controls-overview?view=azuresql#allow-azure-services

Are you connecting through VNET?

Regards,

Oury

Answer 2

It seems Azure Data Factory' (ADF) managed identity doesn’t have access to the Azure SQL server.

When you create the ADF, by default system managed identity will be created with the same name. You will need to provide access to Azure SQL Database to that system managed identity.

Go to ADF and on the search, under settings, click on managed identities. That is your ADF system managed identity.

Connect to your Azure SQL Database as an Azure Active Directory Administrator using SQL Server Management Studio and run the following commands.

CREATE USER [ADF_system_managed_identity] FROM EXTERNAL PROVIDER;  
GO
 
EXEC sys.sp_addrolemember   
    @rolename = N'db_datareader',  
    @membername = [ADF_system_managed_identity]  
EXEC sys.sp_addrolemember   
    @rolename = N'db_datawriter',  
    @membername = [ADF_system_managed_identity]  

Please note that your Azure SQL Database needs to be configured to allow Azure Active Directory (Entra) Authentication.

For more detailed information follow steps provided here.