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