Unable to connect to azure database sql with adf linked service

Question

Unable to connect to azure database sql with adf linked service

Tyron Lee 20

I am trying to setup a simple connection from azure data factory to azure sql database with link service but have faced the following error message:

ERROR USING MICROSOFT ENTRA AUTHENTICATION

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'somerandomdbserver.database.windows.net', Database: 'somerandomdb', 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>'.

ERROR USING

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'somerandomdbserver.database.windows.net', Database: 'somerandomdb', User: 'somerandomadmin'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.
Login failed for user 'somerandomadmin'.

Please advice what I am missing as I've previously managed to connect to azure sql db some months back with the same method metioned below.

My setup as follows:

Create an instance of adf (done)
Create an instance of azure server
1. Settings:
  1. Authentication Method: Use Microsoft Entra Authentication and SQL
  2. Set Microsoft Entra admin: ******@somewhere.com
  3. Serveradminlogin: configured
Create an instance of azure sql db
1. Settings
  1. SQL elastic pool: no
  2. workload environment: development
  3. compute and storage: basic
  4. backup storage redundancy: local
  5. connectivity method: public endpoint
  6. Allow Azure services and resources to access this server: Yes
  7. Add current client IP address: Yes
  8. Connection policy: Default
  9. TLS version: 1.2
  10. security: default, nothing touched.
Configured Azure SQL Server network settings
1. Public network access: Selected Networks
2. Firewall rules: Added my local ip
3. Exceptions: Allow azure resources and server to access this server

Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2024-12-04T03:26:05.9+00:00

Can you please share the image of your linked service configurations (masking the sensitive servername and databasename) for which you are getting the error?

Also can you check the server logs for when you are trying to access to get the accurate error on the server/database side?
Tyron Lee 20 Reputation points

2024-12-04T04:01:14.05+00:00
Tyron Lee 20 Reputation points

2024-12-04T04:06:22.55+00:00
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2024-12-04T04:11:57.06+00:00

Can you confirm whether you have granted the ADF Managed identity access within the Azure SQL database which it is trying to access?
If yes, can you try switching to Legacy version setting rather than recommended within linked service config and confirm back
Tyron Lee 20 Reputation points

2024-12-04T05:39:34.3+00:00
Still unable to access using the Managed identity access. Have tried to allow with the following:

https://learn.microsoft.com/en-us/azure/data-factory/data-factory-service-identity#generate-system-assigned-managed-identity-using-powershell

Set-AzDataFactoryV2 -ResourceGroupName "somerandom_resouces" -Name "mydatafactory" -Location "SoutheastAsia"
Erland Sommarskog 129.6K Reputation points MVP Volunteer Moderator

2024-12-04T22:05:45.09+00:00

We don't know what Tyler's task from ADF is in the database. db_owner may be a little hefty. If you are only going to read data, only grant SELECT permission on the tables in question.
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2024-12-05T03:34:48.23+00:00

Yes, definitely agree. Thanks for highlighting!! The above screenshot was just for reference that the ADF needs to be granted access within the database.
Tyron Lee 20 Reputation points

2024-12-06T07:36:40.5466667+00:00

Hi Nandan.

Thanks for the solution provided. In the past, was able to create user-managed identity without configuring at the azure sql db. Now we have to set it up using the query editor before proceeding with creation of the linked-service.

Many thanks once again.
NIKHILA NETHIKUNTA 4,610 Reputation points Microsoft External Staff

2024-12-06T08:17:06.92+00:00

@Tyron Lee
Glad that your query got resolved. Can you please accept the above solution provided by Nandan. This would be helpful for the community.

Thanks
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2024-12-06T08:17:10.65+00:00

Let me clear so aspect here,

by enabling an Azure object with user managed identity, you are providing that object a unique recognition within the Azure AD.

Now you want to access Azure SQL database via ADF,

so without granting the ADF access within the database, how would it authroize to it?

So for authorization purpose, you need to add that managed identity within Az SQL database

Answer accepted by question author

0 additional answers

Your answer

Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2024-12-04T03:26:05.9+00:00

Can you please share the image of your linked service configurations (masking the sensitive servername and databasename) for which you are getting the error?

Also can you check the server logs for when you are trying to access to get the accurate error on the server/database side?
Tyron Lee 20 Reputation points

2024-12-04T04:01:14.05+00:00
Tyron Lee 20 Reputation points

2024-12-04T04:06:22.55+00:00
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2024-12-04T04:11:57.06+00:00

Can you confirm whether you have granted the ADF Managed identity access within the Azure SQL database which it is trying to access?
If yes, can you try switching to Legacy version setting rather than recommended within linked service config and confirm back
Tyron Lee 20 Reputation points

2024-12-04T05:39:34.3+00:00

Still unable to access using the Managed identity access. Have tried to allow with the following:

https://learn.microsoft.com/en-us/azure/data-factory/data-factory-service-identity#generate-system-assigned-managed-identity-using-powershell

Set-AzDataFactoryV2 -ResourceGroupName "somerandom_resouces" -Name "mydatafactory" -Location "SoutheastAsia"
Erland Sommarskog 129.6K Reputation points MVP Volunteer Moderator

2024-12-04T22:05:45.09+00:00

We don't know what Tyler's task from ADF is in the database. db_owner may be a little hefty. If you are only going to read data, only grant SELECT permission on the tables in question.
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2024-12-05T03:34:48.23+00:00

Yes, definitely agree. Thanks for highlighting!! The above screenshot was just for reference that the ADF needs to be granted access within the database.
Tyron Lee 20 Reputation points

2024-12-06T07:36:40.5466667+00:00

Hi Nandan.

Thanks for the solution provided. In the past, was able to create user-managed identity without configuring at the azure sql db. Now we have to set it up using the query editor before proceeding with creation of the linked-service.

Many thanks once again.
NIKHILA NETHIKUNTA 4,610 Reputation points Microsoft External Staff

2024-12-06T08:17:06.92+00:00

@Tyron Lee
Glad that your query got resolved. Can you please accept the above solution provided by Nandan. This would be helpful for the community.

Thanks
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2024-12-06T08:17:10.65+00:00

Let me clear so aspect here,

by enabling an Azure object with user managed identity, you are providing that object a unique recognition within the Azure AD.

Now you want to access Azure SQL database via ADF,

so without granting the ADF access within the database, how would it authroize to it?

So for authorization purpose, you need to add that managed identity within Az SQL database

Answer 1

Nandan Hegde 36,716 MVP Volunteer Moderator

What I meant is you need to grant the ADF access within the database:

User's image

plz follow the below link :

https://azureops.org/articles/connect-azure-sql-from-data-factory-using-managed-identity/#:~:text=Navigate%20to%20Azure%20SQL%20database,will%20select%20the%20admin%20account.

Share via

Unable to connect to azure database sql with adf linked service

0 additional answers

Your answer