How to troubleshoot creating service principal?

Question

I'm trying to use the Ingest feature in Data Factory and I'm on the "Source data store" step so I'm trying to establish the connection with the DB. The first code block is the error details after testing the connection for the error code: SqlFailedToConnect.
https://youtu.be/sge9qTf8GdY?si=QOFiYiCVDBY9JudO&t=225
I was following the above tutorial (timestamp included) and he says to create the service principal for the application which I tried in Data Studios. The second code block is the error I get after running the SQL code he showed. This is the issue I'm having and I'm assuming that it's something to do with permissions but I already have "Azure Active Directory (Azure AD) is now Microsoft Entra ID." on my server. Do I have to play with the SQL Server's IAM for the Service Principal or the ADF? I'm not sure.

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

Msg 33159, Level 16, State 1, Line 1 Principal '___-adf' could not be created. Only connections established with Active Directory accounts can create other Active Directory users. Msg 15151, Level 16, State 1, Line 2 Cannot add the principal '____-adf', because it does not exist or you do not have permission.

Answer 1

Hi @Ryan Tay
Thanks for the question and using MS Q&A platform.

To resolve the first error message, please refer to a similar issue discussed on the Microsoft Q&A platform: https://learn.microsoft.com/en-us/answers/questions/1665352/how-to-create-service-principal-link-to-the-azure

The error "Principal '___-adf' could not be created" suggests that you might not have the necessary permissions to create a principal. Only connections established with Active Directory accounts can create other Active Directory users. Ensure that you are logged in with an account that has the appropriate permissions to create users.

If you are trying to create a role assignment for a service principal, ensure that you manually add the service principal as a Synapse Administrator or Synapse SQL Administrator in the Azure portal.

For more details refer to these documentations: Microsoft Entra service principals with Azure SQL

Troubleshooting

Troubleshoot the Azure Synapse Analytics, Azure SQL Database, SQL Server, Azure SQL Managed Instance, and Amazon RDS for SQL Server connectors in Azure Data Factory and Azure Synapse

Hope this helps. Do let us know if you have any further queries.