Data factory Authentication With Azure SQL DB + Managed Identities + Federated Credential

Venkatesh Bandaru 61 Microsoft Employee

I am facing ADF Auth issues with Azure SQL DB. ADF is in torus tenant and Azure SQL DB is in Microsoft tenant. Below are the steps i have followed to up FCI.

Created User Assigned managed identity in Torus tenant.

Created App registration in Torus tenant

Configured FCI on App registration for managed identity.

Created App Service principal in Microsoft tenant

On Microsoft tenant Azure SQL Server , added App service principal in step 4 as Admin.

Created linked service in Torus tenant ADF with sql server Databased and managed identity.

Connection is failing here, receiving below error. Can you help here ? It would be great if we could connect for few mins.

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'edtestdblastmile-msft.database.windows.net', Database: 'ED_TESTDB_LastMile', 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>'. The server is not currently configured to accept this token., SqlErrorNumber=18456,Class=14,State=235,

Harishga 4,325 Reputation points Microsoft Vendor

2024-05-14T08:02:19.17+00:00
Hi @venkatesh bandaru
Welcome to Microsoft Q&A platform and thanks for posting your question here.

It appears that there may be an issue with the authentication between Azure Data Factory and Azure SQL Database due to the cross-tenant setup.

To troubleshoot and resolve this issue, you can try the following steps:

Verify that the User Assigned Managed Identity created in the Torus tenant has been properly configured and has the necessary permissions to access the Azure SQL Database in the Microsoft tenant.

Confirm that the App Registration in the Torus tenant is correctly set up with the required permissions and that the Federation Configuration Information (FCI) is correctly associated with the Managed Identity.

Ensure that the App Service Principal created in the Microsoft tenant has been granted the necessary roles and permissions on the Azure SQL Server to act as an admin.

On the Azure SQL Server in the Microsoft tenant, verify that the App Service Principal has been added as an admin correctly and that there are no typos or errors in the configuration.

Check the firewall settings to ensure that the necessary rules are in place to allow connections from the ADF.

Review the linked service configuration in the ADF to ensure that all the details are correct, including the server name, database name, and the managed identity.

Review the token configuration and ensure that it’s being generated and passed correctly.

Reference:
https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-ver16
I hope these steps help you resolve the issue. Let me know if you have any further questions
Venkatesh Bandaru 61 Reputation points Microsoft Employee

2024-05-14T08:52:27.9666667+00:00
@Harishga

Verify that the User Assigned Managed Identity created in the Torus tenant has been properly configured and has the necessary permissions to access the Azure SQL Database in the Microsoft tenant. [Venkatesh] - As it is cross tenant how can i configure managed identity in torus to have access Azure SQL DB. Because of cross tenant reason only we are setting up FCI

Review the token configuration and ensure that it’s being generated and passed correctly. [venkatesh] : As the connection is between ADF to SQL DB, how can verify that the token is generated and passed.

Confirm that the App Registration in the Torus tenant is correctly set up with the required permissions and that the Federation Configuration Information (FCI) is correctly associated with the Managed Identity. [venkatesh] : what permissions we need to configure here ?
Harishga 4,325 Reputation points Microsoft Vendor

2024-05-14T13:24:58.8433333+00:00
@Venkatesh Bandaru
To address your cross-tenant access concerns between Azure Data Factory in the Torus tenant and Azure SQL Database in the Microsoft tenant, you need to perform the following steps:

Create a multi-tenant application in the Torus tenant and configure it with a federated identity credential using the User Assigned Managed Identity. In the Microsoft tenant, grant the necessary permissions to this federated identity, typically by assigning roles such as the SQL DB Contributor role, which allows for management of SQL databases.

To verify the token generation and passing, use Azure Monitor and diagnostic logs to trace the authentication process. You can also test the connection using tools like PowerShell or Azure CLI to simulate the authentication flow and check for successful token retrieval.

Configure the App Registration in the Torus tenant to allow it to create and manage service principals in the Microsoft tenant. This involves setting up the correct API permissions in the app registration’s settings, which include permissions like Directory.Read.All or Directory.ReadWrite.All.

Regarding the error message you’re receiving, it suggests that the server is not configured to accept the token provided. This could be due to incorrect permissions, improper setup of the service principal as an admin on the Azure SQL Server, or the token not being correctly passed by ADF.

I hope these steps help you resolve the issue. Let me know if you have any further questions
Venkatesh Bandaru 61 Reputation points Microsoft Employee

2024-05-14T13:53:43.1633333+00:00

Let me check and comeback on this.
Harishga 4,325 Reputation points Microsoft Vendor

2024-05-15T11:51:17.1966667+00:00

Hi @Venkatesh Bandaru
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Venkatesh Bandaru 61 Reputation points Microsoft Employee

2024-05-16T17:24:35.3633333+00:00

Hi @Harishga i tried to do this, But some how this is not working with ADF. Could you please double check with ADF team, whether ADF supports FCI ?

1 answer

Harishga 4,325 Reputation points Microsoft Vendor

2024-05-17T10:33:50.2366667+00:00

Hi @Venkatesh Bandaru

Azure SQL Database does support federated authentication. This allows you to use Microsoft Entra (formerly Azure Active Directory) credentials from a federated domain for authentication. It’s part of the hybrid identity solutions provided by Microsoft Entra, which also includes cloud authentication, pass-through authentication, and password hash authentication.

Federated authentication is supported and can be used with SQL Server Management Studio (SSMS) and SQL Server Data Tools (SSDT) for strong authentication, including multifactor authentication options. To support Windows single sign-on credentials, you can use Microsoft Entra credentials from a federated or managed domain that is configured for seamless single sign-on.

As you received an error message, it's important to ensure that the federated authentication is correctly set up and that the Azure SQL Server is configured to accept tokens from the federated domain. You need to review the trust relationships between the domains and the Azure SQL Database and ensure that the necessary permissions are granted to the managed identity and service principal involved in the configuration.

Reference
https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview?view=azuresql#overview
https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-sql-database?tabs=data-factory#linked-service-properties

I hope these steps help you resolve the issue. Let me know if you have any further questions
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

Data factory Authentication With Azure SQL DB + Managed Identities + Federated Credential

1 answer