SQL Server wont connect to data factory

Question

I recently set up a SQL server and database. However, when I try to connect it to data factory using SQL Authentication, i get the following error

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

Login failed for user 'CloudSA51cedbb9'. Reason: Azure Active Directory only authentication is enabled. Please contact your system administrator.

I was following a tutorial posted 4 years ago, and the main difference was that you were allowed to set your own admin name and password when creating the database. Now, it wont give you the option. I tried resetting the password from the server and attempting to login using my Server Admin Name and Microsoft Entra Admin Name with the new password, but nothing still seems to work.

Answer 1

Can you please provide the screenshot of your linked server configuration?
Based on the error message:

You are trying to access the SQL server via SQL authentication but at the SQL server SQL auth has been disabled and AD/windows auth only is supported

Answer 2

Hi Sage Desiderio,

Thanks for reaching out to Microsoft Q&A.

The error indicates that "Azure Active Directory only authentication is enabled." This means that SQL Authentication is not set up for your database. The test connection in the linked service is throwing up the same error?

Can you try the below options, I bet one of them should make it work for you!

• Enabling SQL Auth: If you need to use SQL Authentication, ensure that it is enabled for your Azure SQL Database. This can be done in the Azure portal under the SQL server settings.
• Firewall Rules: In the Azure portal, navigate to your SQL server, and under the "Networking" section, add your Azure Data Factory's IP address or allow Azure services to access the server. Look for the option "Allow Azure services and resources to access this server" and enable it.
• Configure SHIR: If your SQL Database is in a Vnet, you may need to install a Self-Hosted Integration Runtime on a machine that can access the SQL Database and configure it to allow communication through the necessary ports (443 and 1433)
• Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.