I have a 2008 web server with a sql server 2008 web database and purchased a new windows 2012 web server with a sql server 2014 web database at Rackspace.
I am on the 2014 dbf copying from the 2008 as source to the 2014 as destination using the Copy task.
It lets me use Authenticated login and I can see the list of databases on the 2008 server, I can select the one I want. Everything looks great like my connection is fine, but then when I click Finish, on the last thing, "Execute SQL Server Agent Job" it fails and says to look at the events.
The event says:
Message: Failed to connect to server XXX.XXX.XXX.XXX.
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 'NT AUTHORITY\ANONYMOUS LOGON'.
I read on some forum posts to add the anonymous login on the server and database as db owner. I added this to my list of users on the destination database, but it isn't even an option to have it on my sql server 2008 database, so I didn't add it there. It didn't help. I don't like having this user as dbowner anyway, but thought I'd try it temporarily. So I have removed it from my sql server 2012 for now since it didn't help.
Another person said they logged in as "sa" rather than "windows user" and it worked. But I don't even know how I would go about doing that and I'd prefer logging in as myself which seems like that should be allowed.
So any other suggestions. I am just so close, yet so far. :( Sigh.