Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'sql.propvivo.com', Database: 'MicrosoftDynamicsAX', User: 'corpwebapi'. Check the linked service configuration is correct, and make sure the SQL Database firewall

Question

i am trying to login the ax database with sql server in datafactory to store the data in datalake storage, it is showing connection failed where as my server name, username and passwords are correct
with this credentials i can easily access the database on my local i.e ssms or visual studio
i am sharing some snapshots for your reference, please anyone help me to fix this issue

Answer 1

Hi Anshuman Srivastava •,

Welcome to Microsoft Q&A forum.

As I understand, you are getting Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'sql.propvivo.com',.

I assume you are connecting to Azure SQL Database from Azure Data Factory.

As the error suggests to work on the Firewall settings, please login to your Database from Azure Portal to the SQL Server -> Network Settings and Add your IP:

Let us know if this helped or share your existing Network settings so that we can understand the issue.

Thanks

Answer 2

I think you are using Self hosted Integration Runtime to connect to the SQL server. The IP address of the system where SHIR is hosted, should be whitelisted. You should not add the ip address of your system. Instead, add the IP address of the system where SHIR is available.

Answer 3

Hi @Anshuman Srivastava ,

Just checking if you are still facing the issue ?

Please check below and share information from your end to understand the issue better:

1). If issue is due to Server Configuration, please make sure in your Linked Service, Server Name is correctly given like shown below:

Same as shown in SSMS for your on premise server:

2). Secondly, if issue is related to User, check the User through which you are connecting is Active and has required settings enabled to login in ADF:

Go to Security Folder at Server level then Open Properties.

Go to Server Roles and check "sysadmin" as that might help.

Also, got to "Status" section and make sure SQL Server auth is enabled.

3). This could also be a cause that your SQL Service needs a Restart.

Please make sure your on premise SQL Server Services is at Running in SQL Server Configuration Manager and Network Configuration for your Database has Protocols Enabled:

Try restart of the Services:

Let us know if above helped or you have some information to share so that we can further troubleshoot.

Thanks.