ssis transfer sql server objects task copy all logins made the destination server unaccessible

Question

Post reply

ssis transfer sql server objects task copy all logins made the destination server unaccessible

invaliddba

Default port

Points: 1450
More actions
April 12, 2012 at 3:46 am

#272130

I did the ssis transfer sql server objects task and used copy all logins and copy all users from source db to destination db .

The question is what the heck happened that the destination server made accesible?

I was transferring sql server objects from one db to other db .

When I executed the SSIS task I got this error :

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1073548784 description=Executing the query "CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WI..." failed with the following error: "User does not have permission to perform this action.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}".

Now I cannot connect to the destination sql server with windows auth ( I only have windows auth enables for this server . This made the destination server unaccessible)

I see these errors under the destination server error log:

2012-04-12 05:18:31.37 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

2012-04-12 05:18:31.82 spid20s The activated proc '[dbo].[sp_syspolicy_events_reader]' running on queue 'msdb.dbo.syspolicy_event_queue' output the following: 'Cannot execute as the database principal because the principal "##MS_PolicyEventProcessingLogin##" does not exist, this type of principal cannot be impersonated, or you do not have permission.'

2012-04-12 05:18:45.56 Logon Error: 18456, Severity: 14, State: 58.

2012-04-12 05:25:50.95 Logon Login failed for user 'HOME\Administrator'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]