My scenario is like this:
Task to accomplish: Migrate all Source server databases approx 100 to Destination server along with sql login credentials.
I tried with copy database wizard. It only worked at my local instances. When I did same task into my network server then following error raised: Date 11/26/2018 11:47:16 AM Log Job History (CDW_DB-TESTSRV_SAMEER-DBA_SQL2017_2)
Step ID 1
Server SAMEER-DBA\SQL2017
Job Name CDW_DB-TESTSRV_SAMEER-DBA_SQL2017_2
Step Name CDW_DB-TESTSRV_SAMEER-DBA_SQL2017_2_Step
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: SAMEER-DBA\Administrator. Microsoft (R) SQL Server Execute Package Utility Version 14.0.3045.24 for 64-bit Copyright (C) 2017 Microsoft. All rights reserved. Started: 11:47:17 AM Error: 2018-11-26 11:47:17.51 Code: 0x00000000 Source: DB-TESTSRV_SAMEER-DBA_SQL2017_Transfer Objects Task Description: Failed to connect to server DB-TESTSRV. StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp) InnerException-->Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. 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.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) 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, TaskCompletionSource1 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 Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity) at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:47:17 AM Finished: 11:47:17 AM Elapsed: 0.484 seconds. The package execution failed. The step failed.
Thanks in advance.