I keep running into errors adding Azure SQL as a linked service to Data Factory. I'm new to Azure so please bear with me. Following all the online guides, I've done all of the following: On the SQL Server side: Under Microsoft Entra ID, enabled Microsoft Entra admin Under Networking, allowed "Azure services and resources to access this server" Under IAM, added Data Factory with an SQL DB Contributor role On the Data Factory side: Under Settings > Managed identities, clicked "On" for the system assigned managed identity Despite it all, I keep getting this error: Cannot connect to SQL Database. Please contact SQL server team for further support. Server: <>, Database: <>, 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>'., SqlErrorNumber=18456,Class=14,State=1, Help would be much appreciated.

Hi @shellkyle Welcome to Microsoft Q&A platform and thanks for posting your question here. Based on the error message you provided, it seems like there is an authentication failure when trying to connect to the Azure SQL Database. This can be due to various reasons such as incorrect credentials or the user not having the correct permissions. To troubleshoot this issue, you can take the following steps: Make sure that the SQL Database firewall is configured to allow the integration runtime to access it. This may involve adding the IP address of the integration runtime to the firewall rules. Verify the linked service configuration to ensure that the server name, database name, authentication method, and credentials are correct. Use a SQL client tool like Azure Data Studio or SQL Server Management Studio to manually connect to the SQL Database using the same credentials as specified in the linked service to test connectivity. Confirm that the Data Factory’s managed identity has been granted the necessary permissions on the Azure SQL server. It should have at least the db_datareader and db_datawriter roles, or higher, depending on the operations you need to perform. If you’re using Azure Active Directory for authentication, ensure that you can generate an access token for the account and that the token is valid. Reference: https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-sql-database?tabs=data-factory#troubleshooting https://learn.microsoft.com/en-us/answers/questions/950724/adf-linked-service-system-managed-service-identity Hope this helps. Do let us know if you any further queries. 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.

How to add Azure SQL Database as linked service to Azure Data Factory

shellkyle 0

I keep running into errors adding Azure SQL as a linked service to Data Factory. I'm new to Azure so please bear with me. Following all the online guides, I've done all of the following:

On the SQL Server side:
- Under Microsoft Entra ID, enabled Microsoft Entra admin
- Under Networking, allowed "Azure services and resources to access this server"
- Under IAM, added Data Factory with an SQL DB Contributor role
On the Data Factory side:
- Under Settings > Managed identities, clicked "On" for the system assigned managed identity

Despite it all, I keep getting this error:

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: <>, Database: <>, 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>'., SqlErrorNumber=18456,Class=14,State=1,

Help would be much appreciated.

Harishga 5,990 Reputation points Microsoft Vendor

2024-05-08T04:11:31.7433333+00:00

Hi @shellkyle
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.

1 answer

Harishga 5,990 Reputation points Microsoft Vendor

2024-05-07T07:40:50.7833333+00:00
Hi @shellkyle

Welcome to Microsoft Q&A platform and thanks for posting your question here.

Based on the error message you provided, it seems like there is an authentication failure when trying to connect to the Azure SQL Database. This can be due to various reasons such as incorrect credentials or the user not having the correct permissions.

To troubleshoot this issue, you can take the following steps:

Make sure that the SQL Database firewall is configured to allow the integration runtime to access it. This may involve adding the IP address of the integration runtime to the firewall rules.

Verify the linked service configuration to ensure that the server name, database name, authentication method, and credentials are correct.

Use a SQL client tool like Azure Data Studio or SQL Server Management Studio to manually connect to the SQL Database using the same credentials as specified in the linked service to test connectivity.

Confirm that the Data Factory’s managed identity has been granted the necessary permissions on the Azure SQL server. It should have at least the db_datareader and db_datawriter roles, or higher, depending on the operations you need to perform.

If you’re using Azure Active Directory for authentication, ensure that you can generate an access token for the account and that the token is valid.

Reference:
https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-sql-database?tabs=data-factory#troubleshooting

https://learn.microsoft.com/en-us/answers/questions/950724/adf-linked-service-system-managed-service-identity

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

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.
Please sign in to rate this answer.
shellkyle 0 Reputation points

2024-05-07T09:13:52.7166667+00:00

How do I do this?

Confirm that the Data Factory’s managed identity has been granted the necessary permissions on the Azure SQL server. It should have at least the db_datareader and db_datawriter roles, or higher, depending on the operations you need to perform.

shellkyle 0 Reputation points

2024-05-07T09:23:15.55+00:00

How do I obtain the IP Address of the integration runtime? I am just using the default AutoResolveIntegrationRuntime

Harishga 5,990 Reputation points Microsoft Vendor

2024-05-07T16:49:20.7133333+00:00

Hi shellkyle
To confirm that the Data Factory's managed identity can access the Azure SQL server, you should check the SQL server's IAM settings and verify that the managed identity has been granted the db_datareader and db_datawriter roles.

The IP addresses used by the default AutoResolveIntegrationRuntime in Azure Data Factory depend on the region where the integration runtime is located. To obtain the IP ranges for filtering in data stores, Network Security Group (NSG), and firewalls for inbound access from Azure Integration runtime, you can download the service tags IP range list from the Azure portal for the specific region where your resources are located.

Reference:
https://learn.microsoft.com/en-us/azure/data-factory/azure-integration-runtime-ip-addresses

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

shellkyle 0 Reputation points

2024-05-09T10:03:54.8433333+00:00

I have been unable to find the db_datareader and db_datawriter roles through the SQL server IAM (on the server UI). Instead, I've been able to grant the data factory the "SQL Server Contributor" role - how do I check if this will suffice?

I've been able to download the file with the IP addresses but I am lost as to how to use it. Is there a way I can add the entire set of IP addresses under "Sql.EastUS" among my firewall rules?

Harishga 5,990 Reputation points Microsoft Vendor

2024-05-09T10:30:21.24+00:00

Hi shellkyle
To answer your first question, the "SQL Server Contributor" role should suffice for granting the necessary permissions to the Data Factory. This role provides the ability to create and manage all resources related to SQL Server, including databases, elastic pools, and servers. You can check the specific permissions granted by this role in the Azure documentation [here]

Regarding your second question, you can add the entire set of IP addresses under "Sql.EastUS" to your firewall rules by following these steps:

Navigate to your Azure SQL Database server in the Azure portal.

Under the "Security" section, click on "Firewalls and virtual networks".

Click on "Add client IP" to add your current IP address to the firewall rules.

Click on "Add IP range" to add the entire set of IP addresses under "Sql.EastUS".

In the "Start IP" field, enter "0.0.0.0".

In the "End IP" field, enter "255.255.255.255".

Click on "Save" to add the IP range to your firewall rules.

Note that adding the entire set of IP addresses under "Sql.EastUS" to your firewall rules may increase the risk of unauthorized access to your SQL Database. It is recommended to only add the necessary IP addresses or IP ranges to your firewall rules.

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

shellkyle 0 Reputation points

2024-05-09T12:44:42.93+00:00

Hi Harishga,

Thank you for all the help. I've followed all the steps, but I unfortunately still receive this error when trying to test the connection (I'm using the AutoResolveIntegrationRuntime, and the SQL server I'm trying to connect to is in US East, and I'm trying to use the Authentication type "System Assigned Managed Identity" for the data factory).

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'drg-sql.database.windows.net', Database: 'xxxx', 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>'., SqlErrorNumber=18456,Class=14,State=1,

Please let me know if there's anything else I can try.

Harishga 5,990 Reputation points Microsoft Vendor

2024-05-09T17:54:02.4733333+00:00

Hi shellkyle
Can you please follow the below steps

Ensure that the SQL Server Authentication mode is enabled if you're using that method to connect. To check this, follow these steps:

Open SQL Server Management Studio and connect to the SQL Server instance.

Right-click on the server name and select "Properties".

In the "Server Properties" dialog box, select the "Security" page.

Under "Server authentication", select "SQL Server and Windows Authentication mode".

Click "OK" to save the changes.

Verify that the login credentials you're using to connect to the SQL Server are correct. Double-check the username and password you've entered in the connection string.

If you're still unable to connect, try resetting the password for the SQL Server login. To do this, follow these steps:

Open SQL Server Management Studio and connect to the SQL Server instance.

Expand the "Security" folder and right-click on the login you're using.

Select "Properties" and then select the "General" page.

Enter a new password in the "Password" and "Confirm password" fields.

Click "OK" to save the changes.

Reference
https://learn.microsoft.com/en-us/answers/questions/674299/error-18456-severity-14-state-1-windows-authentica?orderBy=Helpful

https://stackoverflow.com/questions/2474839/unable-to-login-to-sql-server-sql-server-authentication-error-18456

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

shellkyle 0 Reputation points

2024-05-14T04:55:12.85+00:00

Hi @Harishga ,

I've downloaded SQL Server Management Studio, but I'm unable to connect to my own SQL Server using Microsoft Entra Default, this is the error:

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53) For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-53-database-engine-error

I have indicated the "server type" as "database engine" and am using my own username and password, as the SQL server's admin. Another question: Both the SQL server and data factory are under the same resource group, and I am the Owner for both. Is there a way to just automatically get all resources within the same resource group to connect to each other?

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Harishga 5,990 Reputation points Microsoft Vendor

2024-05-14T11:54:11.7933333+00:00

Hi shellkyle
To troubleshoot the connection issue, you're facing with SQL Server Management Studio (SSMS), you can follow these steps:

Check that the SQL Server service is running on the server. You can do this by searching for "services" in the Control Panel and looking for the SQL Server service.

Confirm that the instance name you're using to connect is correct. If you're using a named instance, ensure you're specifying the instance name correctly in SSMS.

Open SQL Server Configuration Manager and check that the protocols for SQL Server, especially TCP/IP and Named Pipes, are enabled.

Make sure that the firewall on the server is configured to allow traffic on the port that SQL Server is using (default is 1433 for TCP/IP). You need to add an exception for sqlservr.exe and the SQL Server port in the firewall settings.

Verify that the SQL Server Browser service is running. This service provides information about SQL Server instances installed on the machine.

Double-check that you're using the correct username and password, and that the login has the necessary permissions on the server.

Regarding your second question, resources within the same Azure resource group do not automatically connect to each other. You can set up networking and access policies to allow resources to communicate.

Reference
https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/network-related-or-instance-specific-error-occurred-while-establishing-connection#a-network-related-or-instance-specific-error-occurred-while-establishing-a-connection-to-sql-server-verify-that-the-instance-name-is-correct-and-that-sql-server-is-configured-to-allow-remote-connections

https://stackoverflow.com/questions/9945409/how-do-i-fix-the-error-named-pipes-provider-error-40-could-not-open-a-connec

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

PRADEEPCHEEKATLA 90,351 Reputation points

2024-05-20T08:12:12.92+00:00

shellkyle - 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.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to add Azure SQL Database as linked service to Azure Data Factory

1 answer

Your answer