0

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, TaskCompletionSource1 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(TaskCompletionSource1 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.

Improve this question
4
  • Looks like you are using wrong login to get this thing work can you try sa user? Things are not clear how you are trying to connect on both the servers. If you can read the error you will get to know that it is not able to connect to destination server and error is The login is from an untrusted domain and cannot be used with Windows authentication. work around for this: Change SQL server to use both Windows and SQL Server accounts.
    – Shashank Tiwari
    Nov 26, 2018 at 6:58
  • logged in with sa credential for both source and destination server
    – Sameer
    Nov 26, 2018 at 6:59
  • Did it workout for you?
    – Shashank Tiwari
    Nov 26, 2018 at 7:02
  • >>>logged in with sa credential <<< You was logged in with your Windows account, and your servers are not in the same domain. You should use SQL Server login and not Windows login
    – sepupic
    Nov 26, 2018 at 7:03

1 Answer 1

Reset to default
0

You can probably achieve your goal with a few lines of Powershell code using the cmdlets provided by dbatools, which is a free PowerShell module with over 400 SQL Server best practice, administration, development and migration commands included.

Migrating application databases with dbatools provides useful examples for the tasks you want to do.

Step 1 – Check for connections

Get-DbaProcess -SqlInstance SourceServer -Database MigratingDatabase | Stop-DbaProcess

Step 2 – Migrate the database(s)

Copy-DbaDatabase -Source SourceServer -Destination DestinationServer -Database MigratingDatabase -BackupRestore -NetworkShare \\fileshare\

Step 3 – Migrate the user logins

Copy-DbaLogin -Source SourceServer -Destination DestinationServer -Login AppReadOnly, AppReadWrite, DOMAIN\AppUser
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.