Take the 2-minute tour ×
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 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.

share|improve this question

migrated from serverfault.com Jun 20 '13 at 15:48

This question came from our site for system and network administrators.

4 Answers 4

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

share|improve this answer
    
But that's the problem -- a Windows principal from Domain B cannot be created as a login on the server in Domain A. –  Jon Seigel Jun 20 '13 at 16:54
    
@JonSeigel Agreed and hence SQL Authentication is the way then. Can't think of any other possiblity –  Kin Jun 20 '13 at 16:58

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.

share|improve this answer
    
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! :-) –  Dan Jun 20 '13 at 19:26
up vote 1 down vote accepted

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.

share|improve this answer
    
YES! Very good idea. I had assumed this was already in production because the security was so tight. –  Jon Seigel Jun 20 '13 at 23:06
    
It is in a production environment but the DB itself is not quite production yet. This was one piece of the puzzle before it could go production :-) –  Dan Jun 21 '13 at 12:07

This is the correct ORDER:

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.