SQL Server grant permissions to STANDBY database

Question

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 several references to creating a SQL login on the sending side, but I can't find any way to replicate it with a Windows Auth Login.

Answer 1

I found a way to do this that requires some downtime (DB wasn't in production yet so this was not an issue) and does not allow for easy edits, but it works!

1. Detach DB from source server and copy to destination server
2. Attach DB on destination server
3. Create the Windows Auth Login and grant db_datareader to any domain users/groups required on destination server as you would for any regular DB
4. Detach DB from destination server and copy back to source server
5. Re-attach to source server and configure transaction log shipping

The DB can now be accessed by the users/groups configured in step 3. This may break if moved to a different SQL instance but at least there is a way to fix it again in the future.

Answer 2

Below steps should work :

1. Create windows login on ServerA
2. Create user in database mapped to login
3. Drop login on ServerA - (OPTIONAL -if you want to have that login intact then leave it, else drop it).
4. Grant any required permissions to user in database

5. Create login on log shipping ServerB

   • a. If using SQL authentication then create login while specifying SID from ServerA

   • b. If using Windows authentication, create login without specifying SID

      ---To query for the SID on the ServerA:
     
       Select SID
       From sys.database_principals
       Where name = 'Test'
     
      ---To create the login on the ServerB :
     
       Create Login [Test]
       With SID = '<put SID from query here>',
       Password = '<add password here>'
     

6. Make sure log shipping jobs have completed one full run since completion of step 4

Answer 3

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.

Answer 4

This is the correct ORDER:

Create Login Login_Name WITH Password = 'PassWord', sid = 0x498SID038302373,

Answer 5

This won't work in all scenarios, but it did for ours: which involves a separate live database accessing the first (STANDBY) one.

We used module signing: we created a certificate in the original database we want to read from, granting it the appropriate permissions in that database (db_datareader).

Now, one way or another, you need that same certificate in the other database. We happened to have a temporary opportunity with admin privileges, so we could grab the certificate directly (CERTENCODED, CERTPRIVATEKEY, etc.) from the STANDBY source and create it on the target (including the private key).

A simple alternative is to simply back up and restore the certificate.

Now, the second database signs the appropriate modules with that certificate, and since it's the same on both database, voila, those modules can access the STANDBY database.