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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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 & Architect, EE Solution GuideCommented:
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 & Architect, EE Solution GuideCommented:
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 & Architect, EE Solution GuideCommented:
>> 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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.