Copy Database Fails with Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
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.Manage
at Microsoft.SqlServer.Dts.Ta
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.
thanks!
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.Manage
at Microsoft.SqlServer.Dts.Ta
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.
thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Vitor,
I tried copying the backup file to the new server, but it had an unspecified error when 15 minutes remained. So I just don't see doing that. The COPY function says to use it when wanting to move a sql server 2000 or greater instance to sql server 2005 or greater, and since this is 2008 to 2014 I thought that would be the way to go. Later, when this is working, I also have 2000 to 2014 that I have to do.
Any ideas?
thanks.
I tried copying the backup file to the new server, but it had an unspecified error when 15 minutes remained. So I just don't see doing that. The COPY function says to use it when wanting to move a sql server 2000 or greater instance to sql server 2005 or greater, and since this is 2008 to 2014 I thought that would be the way to go. Later, when this is working, I also have 2000 to 2014 that I have to do.
Any ideas?
thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes, there's enough space and that didn't work. I forget what error I had. I'd really like to get the COPY to work, rather than using a restore file, because it can't "see" that file, but I can see the database to copy it. It just seems so close except for that anonymous login error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Is this information for getting the COPY to work?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried to do a restore, but it doesn't allow access to the remote sit4e's directories where the backups are located. How does on restore a backup from a file on another server? they do not have mapped drives to one another.
dung,
I made sure both databases have the Admin user as the db owner, I was logged in as Admin when I did it, and tried it to no avail.
the second half of what you said, is not applicable.
any other ideas, or please be more specific. thanks!