Not able to connect to on premise sql server using self integration run time in azure data factory

Question

I am able connect to on premise sql server using SSMS tool

but while connecting through azure data factory using self hosted integration runtime and linked service getting below error. No mistakes in user name and password .Please help me on this.

{
    "name": "SqlServer1",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "annotations": [],
        "type": "SqlServer",
        "typeProperties": {
            "server": "DESKTOP-M1KNF59\\SQLSERVER2022",
            "database": "bankingdatabase",
            "encrypt": "mandatory",
            "trustServerCertificate": true,
            "authenticationType": "Windows",
            "userName": "DESKTOP-M1KNF59\\RAJESH",
            "password": {
                "type": "SecureString",
                "value": "**********"
            }
        },
        "connectVia": {
            "referenceName": "integrationRuntime1",
            "type": "IntegrationRuntimeReference"
        }
    }
}

TestConnection failed, error message: 'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support. Server: '', Database: 'bankingdatabase', User: 'DESKTOP-M1KNF59\RAJESH'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.,Source=Microsoft.DataTransfer.ClientLibrary,StackTrace= at Microsoft.DataTransfer.ClientLibrary.SqlProviderHandler.OpenConnection(RetryPolicy retry, Guid transferId, Guid activityId)

at Microsoft.DataTransfer.Runtime.SqlConnection.TestConnection()

at Microsoft.DataTransfer.TransferTask.InteractiveTaskFactoryV2.ConnectorTestConnection(ConnectionSetting connectionSetting, TestConnectionV2Response response)

at Microsoft.DataTransfer.TransferTask.InteractiveTaskFactoryV2.TestConnection(TestConnectionV2Request request, TestConnectionV2Response response, CancellationToken token)

at Microsoft.DataTransfer.TransferTask.InteractiveTask`2.Execute(),''Type=System.Data.SqlClient.SqlException,Message=Login failed for user 'DESKTOP-M1KNF59\RAJESH'.,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 'DESKTOP-M1KNF59\RAJESH'.,},],StackTrace= at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

at System.Data.SqlClient.SqlConnection.Open()

at System.Threading.Tasks.Task.Execute()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

at Microsoft.DataTransfer.Common.Shared.HelperMethod.ExecuteWithTimeout(Action action, TimeSpan timeout, String timeoutErrorMessage)

at Microsoft.Practices.TransientFaultHandling.RetryPolicy.<>c__DisplayClass1.<ExecuteAction>b__0()

at Microsoft.Practices.TransientFaultHandling.RetryPolicy.ExecuteActionTResult

at Microsoft.DataTransfer.ClientLibrary.RetryPolicyExtensionMethods.ExecuteActionWithTimeout(RetryPolicy retryPolicy, Action action, TimeSpan timeout, String timeoutErrorMessage)

at Microsoft.DataTransfer.ClientLibrary.RetryPolicyExtensionMethods.ExecuteActionWithTimeout(RetryPolicy retryPolicy, Action action, TimeSpan timeout, HybridDeliveryExceptionCode errorCode, String timeoutErrorMessageFormat)

at Microsoft.DataTransfer.ClientLibrary.SqlProviderHandler.OpenConnection(RetryPolicy retry, Guid transferId, Guid activityId),'.

Log ID: Error

Answer 1

Hello Rajesh,

Welcome to the Microsoft Q&A forum.

Couple of things I noticed from the error message and screenshot:

It seems like you are using windows auth to connect to SQL using SSMS

Please check if SQL auth is enabled

also per the error message: make sure the SQL Database firewall allows the integration runtime

This seems to be firewall issue.

Make sure to whitelist the VMs IP addresses and open ports(443 ad 1433) to allow communication from ADF to SQL.

You can follow this document: https://learn.microsoft.com/azure/data-factory/create-self-hosted-integration-runtime

I hope this helps.

Answer 2

To debug the issue, can you please make sure you try logging in to the database via SSMS from the server on which the Integration runtime is installed?

If you are unable to access from within the server, that might be either due to port or network blockage thereby causing this issue