Not able to create a new SQL server linked service using azure data factory

Question

Not able to create a new SQL server linked service using azure data factory

Pratisti Satardekar 130

Hi,

Im able to connect to the DB through microsoft sql server management studio, powerApps, powerBI but not able to create an linked service using the same.
User's image

User's image

im getting the following error:

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'XXXX001.database.windows.net', Database: 'XXXX-001', User: 'XXXXApp'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.), SqlErrorNumber=11001,Class=20,State=0,
No such host is known

Can you please guide me through.

Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2023-10-11T08:27:25.04+00:00

can you please confirm whether the server settings is Allow Azure Service to connect property enabled?
Smaran Thoomu 32,520 Reputation points Microsoft External Staff Moderator

2023-10-15T04:37:30.6866667+00:00

@Pratisti Satardekar Following up to see if the below answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer accepted by question author

0 additional answers

Your answer

Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2023-10-11T08:27:25.04+00:00

can you please confirm whether the server settings is Allow Azure Service to connect property enabled?
Smaran Thoomu 32,520 Reputation points Microsoft External Staff Moderator

2023-10-15T04:37:30.6866667+00:00

@Pratisti Satardekar Following up to see if the below answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

@Pratisti Satardekar

Thank you for reaching out with your query. In additional to @Nandan Hegde
As per error message suggests that the linked service configuration may not be correct, and the SQL Database firewall may not allow the integration runtime to access. It could be several reasons for this error, including incorrect linked service configuration, firewall settings, network connectivity issues or authentication issues. When I tried to repro its working fine.
If you encounter any error code, could you please share it with us?
User's image

Meanwhile you can follow the below steps:

Check SQL Database firewall is configured to allow the integration runtime to access the database.
Check whether the allow azure service property is enabled in Azure SQL server. If not, then you would have to whitelist the IP range of the region in which the ADF is created.
In case if your source or sink is leveraging an integration runtime, then the azure database must also be accessible from the IR.

Additional Information:

To explore more about ADF connectivity issues with Azure Synapse Analytics, Azure SQL Database, and SQL Server please refer to this doc: ADF connectivity issues with Azure Synapse Analytics, Azure SQL Database, and SQL Server
Also please refer to this helpful MS doc : Troubleshooting connectivity issues and other errors with Azure SQL Database and Azure SQL Managed Instance

I hope this helps you resolve the issue. If you have any further questions or concerns, please don't hesitate to ask.

Pratisti Satardekar 130 Reputation points

2023-10-12T05:15:17.3633333+00:00

Hi @Smaran Thoomu ,

I wasn't trying using Azure SQL DB. its just a new Service link for an SQL server not Azure SQL DB. For that also it will be the same?
Smaran Thoomu 32,520 Reputation points Microsoft External Staff Moderator

2023-10-12T06:26:11.9533333+00:00

Hi @Pratisti Satardekar ,I apologize for the incorrect screenshot earlier. I've now corrected it, and the steps I provided to resolve the issue remain the same.
Smaran Thoomu 32,520 Reputation points Microsoft External Staff Moderator

2023-10-13T14:17:21.75+00:00

@Pratisti Satardekar Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

Not able to create a new SQL server linked service using azure data factory

0 additional answers

Your answer