Sql-server – Managing Security for a Log Shipped db across two (unconnected) domains

active-directoryauthenticationlog-shippingsql serversql-server-2008

SQL Server 2008 (soon to be 2012).

We have a vendor that is providing us with log shipped backups of our production database. We use this log shipped database for BI staging, reporting, etc. I've seen several blogs and articles discussing pass through authentication and trusted domains, but we have no access at all to their domain. Is is possible to create an AD user or group on their side and have it function on our side?

As a workaround – probably unsupported, we take the log shipped db offline, copy the datafiles to a shell db and bring both dbs back online. This gives us a read/write copy of the database and allows us to configure security; and make any other changes we might need.

I'm wondering if there is any way to have the vendor just create Windows or SQL Server logins (say with specific SIDs to match our AD account SIDs) and therefore keep us from having to make copies of the db in order to manage security. I'm hoping the question is clear enough, I'll try to rephase it here:

Is it possible to create a login on one server, move the db to another server, and have an AD login already there that keeps the user from being orphaned?

Best Answer

but we have no access at all to their domain.

Your Windows user won't work - since you don't have access to the domain or the domains are not trusted.

What you can do?

Ask the vendor to create a SQL Server user with enough rights. Assign appropriate roles, then take that SID and create a login on the secondary server. Remember that log shipping works on the database level!

e.g. Below is pseudocode that will help you:

create user LS_User without LOGIN;
--- give permissions e.g. data reader ,etc

-- get the SID 
SELECT sid from sys.database_principals
where type ='S' and name = 'LS_User'

--- on the secondary server (your side)

use master 
create login LS_User with PASSWORD = 'yourSecurePassword', 
SID = 0x125896541236980KIN -- This is the SID from above query