Using the Copy Database Wizard fails

We just set up a new server box and installed SQL Server 2005 on it. I have a number of databases on our old server that I would like to transfer to the new box. When I try to use the Copy Database Wizard, it fails on the last step with an error (from the log): "Login failed for user 'dhausner'. The user is not associated with a trusted SQL Server connection. [CLIENT: <local machine>]".

Both boxes are set up to use Windows Authentication only and I am a member of the sysadmin group on both.

Am I missing sometthing? Is there a better way to put these databases on the new server?

As always, any help is greatly appreciated!
dhausnerAsked:
Who is Participating?
 
dhausnerAuthor Commented:
I've tried all the things that have been discussed and still can't get the wizard to complete without failing.
I'm just going to use the backup/restore to copy all the databases over to the new server.

Thanks for all your help.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> The user is not associated with a trusted SQL Server connection

Try setting it to Mixed Mode Authentication once and revert once the operation is successful.
Error above specifies / will occur in case if you use SQL Authentication..
0
 
dhausnerAuthor Commented:
I did as you said and set both servers to mixed mode but the wizard still fails. Do I login as my usual windows authentication account or use SQL Server Authentication? (if SQL Server Authentication, what account?)

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Ok.. That should not be the problem and thought of confirming it.
Are you sure you have sysadmin privilege at both servers and also check whether any users are connected to the source database since detach and attach process should not have any users connected to the source database at the time you are trying to use Copy Database Wizard..
0
 
dhausnerAuthor Commented:
Yes, I have sysadmin privileges on both servers. There are no users connecting to either as well.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Ok, To nail it down do the steps given below:

1. Take the backup Manually using this login from Source server.
2. Copy the backup file to Destination server.
3. Restore the backup in Destination server.

If there is permission issues, it should error out somewhere..
0
 
dhausnerAuthor Commented:
Yes, I did this earlier and had no problems whatsoever.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
If you haven't faced any problems doing the above set of steps manually, then try applying SP3 for SQL Server 2005 and latest Service Packs for your Windows OS which might help sometimes.
Also try applying latest Service Packs for .Net Framework.
0
 
dhausnerAuthor Commented:
SQL and Server 2003 are both fully updated. Somehow, I think the wizard is trying to use NT/AUTHORITY as the login when running the wizard. I don't know if it's supposed to be using that login, but everything I've tried so far has yet to work.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> I think the wizard is trying to use NT/AUTHORITY as the login when running the wizard.

It would use the logged in account privileges only.
And what is the startup account used for SQL Server service.

Try providing some other login in the wizard for both database and try once and does your startup account has Domain or network access since copying files by the wizard requires that..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.