?
Solved

Copy Database Fails with Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Posted on 2014-12-01
9
Medium Priority
?
561 Views
Last Modified: 2014-12-24
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.

thanks!
0
Comment
Question by:Starr Duskk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1600 total points
ID: 40473238
Why are you using the Copy Database Wizard? You could restore a backup from the SQL Server 2008 database and then change the compatibility level to SQL Server 2014.
0
 
LVL 8

Assisted Solution

by:Dung Dinh
Dung Dinh earned 400 total points
ID: 40473656
Hi,

If OS can not validate who you are, you are NT AUTHORITY\ANONYMOUS LOGON so you should check your account and make sure that you have at least READ permission on source database and WRITE permission on destination database.

Anyway, if there are two systems in parallel , you should create  the mechanisms to synchronize data such as REPLICATION,  use SSIS packages or using T-SQL (OPENROWSET, BULK INSERT,...)
0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 40474809
Vitor,
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!
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 2

Author Comment

by:Starr Duskk
ID: 40474888
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.
0
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1600 total points
ID: 40475354
You can restore a backup from a share:
RESTORE DATABASE YourDBNameHere FROM DISK = '\\Servername\Sharename'

tried copying the backup file to the new server, but it had an unspecified error when 15 minutes remained.
Check if the new server has enough space for receive the file.
0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 40483492
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.
0
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 1600 total points
ID: 40486359
Are you a Domain Admin or do you have access to the Domain Controller?
If so,  check if the SQL Server service account is configured to be trusted for delegation in AD (5.Ensure that "Trust this user for delegation to any service (Kerberos only)" is selected for this service).
0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 40489428
Is this information for getting the COPY to work?
0
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1600 total points
ID: 40490668
Yes it is.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question