Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

So, first: the setup. We have SQL Server 2012 (ServerA) running in domain A. We have SQL Server 2012 (ServerB) running in domain B, separate subnet, no trusts. These are completely separate domains for security reasons and they cannot be joined/trusted/etc. We need to be able to query the data directly from domain B via Windows Authenticated logins. I was able to use this guide to set up transaction log shipping to get the databases from ServerA to ServerB (summary: create the transaction log shipping config, use WinSCP to copy the logs to the remote server, manually create the secondary using SQL script). So now we have the two databases running in STANDBY/read-only on ServerB.

Now, the problem: we cannot grant access to these databases because they are in read-only so we cannot modify the permissions. How can I grant read-only access to these databases (either at the server level or DB level) to a domain group from DomainB on ServerB? I've found severalreferences to creating a SQL login on the sending side, but I can't find any way to replicate it with a Windows Auth Login.

Doing this directly isn't possible. (And we're not going down the path of granting sysadmin to the principal on Domain B.)

Without a trust established, it's by-design not possible to reference a principal across the domains.

However, because to authenticate a principal SQL Server only cares about the SID it sees, what you actually need to end up with is the SID of the principal on Domain B created in the read/write database on Domain A. Note I didn't say you need the principal to match; just the SID.

Here are some options, in my descending order of preference:

Temporarily create a one-way trust so the principal on Domain B can be created on Server A. Once you've created the login, the trust can be dumped; the login will remain.

Figure out a way to create a principal on Domain A with the same SID as the principal on Domain B. I did some searching, and this doesn't look either trivial or documented.

Modify the system tables on Server A to change an existing Windows login to the SID of the principal on Domain B. (Note: this is unsupported.)

If none of these are satisfactory (or work, as admittedly I haven't tested any of these solutions), you'll have to switch mechanisms to something like transactional replication where the database isn't read-only. It's unclear for what purpose you're doing this, so that may not be appropriate.

First option wasn't workable in our environment, second option didn't sound fun, and the third option was not possible because SQL would not let me modify the system tables. But the third option sparked the answer that I posted, so thank you for that inspiration! :-)
–
DanJun 20 '13 at 19:26