July 19, 2011 at 10:07 am
Hi All,
I am implementing a Log-shippping configuration for a customer and I've run into a problem with managing access to the read-only (reporting) DB on the target side.
Here's the situation:
1. Company A is contracted to engage in an extensive research project for Company B.
2. Both companies have stringent network security policies, and cannot see or access each other's networks.
3. Company A is collecting data to a SQL Server 2008 R2 database that Company B wants constant access to, with no more that 24-hour latency, in order to use its own analysis tools on the data.
4. Because of #2, above, company B's users cannot get into Company A's network or database.
So here is what I have done:
5. Setup Company A's database to Log-Ship its database (ResearchDB) to a local share.
6. Setup Company B with a database defined as a reciever of the ResearchDB, pulling its log backup copies from it's own local share, and leaving itself in Standby-ReadOnly mode, so that it can act as a Reporting database.
7. Implement some hand-waving trick to get company A's local share to magically replicate to Company B's local share. (It's no big deal, I just cannot discuss it for security reasons).
8. Turn it all on, an GO. It works great.
Now here's where the problem arises. Company B want to be able to control who has access to this data within their company. However:
A. since it's in read-only mode, I cannot add users to the database.
B. I cannot take the reporting DB out of Read-Only mode to fix this, as that would break the restore-chain and make log-shipping impossible.
C. I could add a SID-synchonized SQL Login to ResearchDB at Company A, but the analysis software is designed to use Trusted-connections and not SQL Server Logins/Passwords.
D. In theory, I could change the owner of the Reporting DB by changing the principla that does the restores, but that would only get me a single Windows User access, when what I really need is to give access to a single Windows Group (after that, I can just manage the group to control who has access).
E. I cannot just add a CompanyB\WindowsGroup to the ResearchDB at company A, because being different companies, they have completely different AD Forests, with no trust between them. So the CREATE LOGIN at company A rejects any Company B Group/User because it cannot find it in AD.
So that's where I stand. But I have to bite the bullet and implement an entire redirection database (a lot more work than I was planning on), can anyone suggest anything else, or see anything that I have overlooked or misunderstood?
Thanks.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 19, 2011 at 10:15 am
Are you using Enterprise Edition or Standard? If Enterprise, you might be able to set up a snapshot you can use. I've heard of that being used with mirroring, and it might work with log shipping. Not sure about the whole thing, just popping out an idea.
Otherwise, it's probably a matter of a pass-through DB that just has a bunch of synonyms in it that point to the read-only DB. Pretty easy to set up, but might be harder to maintain if objects are routinely created/dropped in the source database.
Any reason you're using log shipping instead of replication? I've done replication between sites and ADs without difficulty. It can even be done through FTP for another level of security, instead of file-shares.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 19, 2011 at 10:32 am
I like the hand waving step 😉
What about using a reporting database at company B with views to the log shipped db, and then enabling some level of cross database ownership? If the views had the same owner (thinking dbo), then perhaps they can control the security in reporting_db, which reads across the log_ship_db?
July 19, 2011 at 11:00 am
Thanks, Gus & Steve. The Redirection DB that I mentioned is the same thing as the reporting DB with views/synonyms into the ReadOnly DB. I've done it many times before, (I even have it automated). I just wanted to avoid the extra setup/maintenance, hoping for a one-time fix/silver bullet.
GSquared (7/19/2011)
Are you using Enterprise Edition or Standard? If Enterprise, you might be able to set up a snapshot you can use. I've heard of that being used with mirroring, and it might work with log shipping. Not sure about the whole thing, just popping out an idea.
OK, I'll check it out.
Otherwise, it's probably a matter of a pass-through DB that just has a bunch of synonyms in it that point to the read-only DB. Pretty easy to set up, but might be harder to maintain if objects are routinely created/dropped in the source database.
Yep. The metadata is not in Company B's control, and could change somewhat without warning. Log-shipping by itself can handles that seamlessly.
Any reason you're using log shipping instead of replication? I've done replication between sites and ADs without difficulty. It can even be done through FTP for another level of security, instead of file-shares.
In my experience log-shipping is easier & less work, so long as the requirements fit (long latency, ect.). Plus having a slightly non-managed schema (see above) is a headache for replication, but a no-op for log-shipping.
Also, I wasn't aware that Replication nowadays was as amenable these kind of network/security tricks (FTP, etc.). Good to know for the future.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 19, 2011 at 11:09 am
Really interesting question. I had to create something like this recently (used SFTP file transfer between untrusted domains) but, lucky for me, I could use SQL auth 🙂 .
Snapshots won't work AFAIK, because you cannot snap a log shipping secondary, only a primary database, and the source DB for a snap must be online.
One hacky idea - just brainstorming - you could create a loopback linked server and set the security up to map windows users to a sql account that was added at the source system. Maintenance and perf could be dodgy, so I'm not sure I like this idea, but it seems like it could work.
I like the synonyms idea better. It might even be possible to automate refreshing the synonyms by periodically walking all the tables & views in the log-shipped database.
July 19, 2011 at 11:37 am
I've tried the loopback trick before Merrill, and redirection with synonyms/views definitely seemed easier.
I guess what I was hoping for was something like:
A) some way to force a WindowsGroup logon as the owner of the restored DB at the receiving Company, or ..
B) some way to force a 'foreign' Windows logon ID as a user of the DB at the sending company..
??
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 19, 2011 at 11:48 am
I'm pretty sure you can't have a database server exist in two AD domains. Could be wrong about that, since I'm not an AD expert, but I don't think it's feasible. Hence, a passthrough database is probably your best bet.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 20, 2011 at 1:28 pm
OK, well that more or less matches what I thought. I was just checking to see if I might have missed a quick fix...
Thanks, All!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply