This error seems quite self explanatory but I have no idea how to rectify it. I am trying to copy a group of databases from a SQL Server 2012 instance on one virtual server to a SQL Server 2019 instance on a different virtual server.
When I start up the "Database Copy Utility", it asks me to authenticate on both the source and destination server - I use my Windows login authentication for this (I am designated as an administrator on both source and destination servers) and it seems to have no problem at this stage of the process.
I go through the rest of the wizard and submit the job but upon submitting the job it fails at the very last step - "Execute SQL Server Agent Job":
When I check the event log on the destination server I see that the returned error is:
Failed to connect to [Source SQL Server]. Login failed for user [Company AD Domain][Destination SQL Server name]:
Event Name: OnError Message: Failed to connect to server cas-db\casdbserver.
StackTrace:
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp)InnerException:
Login failed for user '[AD Domain]\CAS-DB2$'.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()Operator: NT Service\SQLSERVERAGENT
Source Name: cas-db_casdbserver_cas-db2_Transfer Objects Task
Source ID: {AF36C077-A7C9-441C-BD2D-F4C41CCA5449}
Execution ID: {78B5D0E0-C15B-4225-92FF-4A68FAA86766}
Start Time: 11/2/2024 3:20:31 PM
End Time: 11/2/2024 3:20:31 PM
Data Code: 0
I am not sure where it is getting that "User" from as that User doesn't exist - nor do I know why it is using it since I am providing my Active Directory credentials for both the source and destination server when running the Database Copy Utility in SSMS. Any advice on how I could resolve this issue?
Thanks so much in advance.
Tl;dr: Tried copying database from SQL Server 2012 instance to SQL Server 2019 instance on different virtual server using SQL Server Database Copy Utility. I was expecting it to use the credentials I provided in the utility to authenticate and perform the copy but instead it keeps attempting to connect to the source database with credentials that don't seem to exist causing the copy to fail.
[AD Domain]\CAS-DB2$ is the "machine account" for the server CAS-DB2. When a program running on CAS-DB2 with the "Network Service" account, or any service virtual account (Agent uses this), makes a network request, that request uses the servers machine account.
So this job appears to be running as the SQL Agent, and the SSIS connection is configured to use Windows Integrated Security. So it will try to connect as the machine account. This can be changed in the SSIS package, the Agent configuration for the SSIS package, or you can just grant this account the necessary rights on the other SQL Server, eg
in the target database:
create login [AD Domain\CAS-DB2$] from windows
create user [AD Domain\CAS-DB2$] for login [AD Domain\CAS-DB2$]
grant select to [AD Domain\CAS-DB2$] --or whatever