Azure Data Factory: Server connection error

Carlo Lodola 0 Reputation points
2023-05-12T13:34:31.2833333+00:00

Hi

Data Source: Storage container: File uploaded Authentication method: Access key

Destination : SQL Server managed instance

ADF:

Linked Service connected to the SQL Server managed instance , SQL Authentication , test connection successful

I have created a simple pipeline to copy data from the source to a table in the server (destination)

Server side I have user Admin set as DB sys admin permission to connect to SQL

Based on the error below , it appears to be indicating it is a firewall issue? Linked Service is successful when i do a test connection

If everything is under the same resource group , I presume a firewall is not the issue?

Any idea's? Managed instance I cannot see any settings for a firewall in the portal

Error

ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'SQL managed instance', Database: 'zzzz', User: 'Admin'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Login failed for user 'Admin'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=18456,Class=14,ErrorCode=-2146232060,State=1,Errors=[{Class=14,Number=18456,State=1,Message=Login failed for user 'Admin'.,},],'

Azure Data Lake Storage
Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,342 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,544 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Boris Von Dahle 3,116 Reputation points
    2023-05-13T14:04:26.7633333+00:00

    Hello,

    SQL Server Error 18456 indicate a login authentication error:
    https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-ver16

    Testing the linked service checks the ability to reach the SQL endpoint but does not validate the ability to login and perform operations

    Please check user credentials are correct by connecting to your SQL Server Managed Instance directly using the same username and password..

    Managed Instance supports both SQL Server Authentication and Azure Active Directory Authentication. Make sure the SQL Server Managed Instance is set to the correct authentication mode that aligns with the credentials you're using.

    If this answer was helpful please mark it as accepted so others users with same question can find this topic.

    0 comments