login failed attempting cross-server DB copy

Question

I'm attempting a cross-server DB copy, and I _think_ I'm following all the rules from http://msdn.microsoft.com/en-us/library/ff951624.aspx, however I am seeing a "login failed" message when attempting the copy.  

I have two servers (in order to protect the innocent, I'll call them "source" and "destination" here)

In order to answer the permission questions that I will inevitably be asked, here is a sequence of queries running against my servers to show that I meet the criteria outlined on the above page:

1)Both servers must have the same Login (with the same password)

SELECT SERVERPROPERTY('ServerName') 'ServerName', db.Name 'DbName', logins.Name 'Owner Login'
FROM sys.databases db
INNER JOIN sys.sql_logins logins
	ON logins.sid = db.owner_sid

Execute this in "master" on "source":

ServerName  DbName  Login
source    MyDb   sourceadmin

execute this in "master" on "destination":

SELECT SERVERPROPERTY('ServerName') 'ServerName', logins.Name 'Login'
FROM sys.sql_logins logins

with result:

ServerName  Login
destination  destinationadmin
destination  sourceadmin

In order to ensure both servers have the same password, I re-executed in master on both servers:

ALTER LOGIN sourceadmin WITH PASSWORD = 'xxxxxxxx' 

2) On destination server, login must be have dbmanager role:

select SERVERPROPERTY('ServerName') 'ServerName', dp2.Name 'RoleName', dp.name 'Member'
from sys.database_role_members drm
inner join sys.database_principals dp
	on dp.principal_id = drm.member_principal_id
inner join sys.database_principals dp2
	on dp2.principal_id = drm.role_principal_id

with result:

ServerName  RoleName  Member
destination  dbmanager  sourceadmin
destination  db_owner  dbo

3) On the source server, the login must be the DBO of the db (same query from 1) above shows that sourceadmin is the DBO of the DB MyDb)

With that established, I execute the following in the destination.master, while logged in as sourceadmin:

CREATE DATABASE MyDb AS COPY OF source.MyDb

Only to get the error message:

Msg 40532, Level 11, State 1, Line 1
Cannot open server "source" requested by the login. The login failed.

I can't figure out what I'm doing wrong?

 

Pete

 

Answer 1

Damnit.  The two servers are in different regions (North Europe vs West Europe).  Why did I do that!?

Answer 2

 

Do you know of the Affinity Group? I think for what you're doing, you may be able to benefit from it.

 

 

---

Thanks, Scott Ma Schlumberger IT & Software Community Leader. SME for Entity Framework and Windows Azure Platform.