1

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?

Improve this question

1 Answer 1

Reset to default
1

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:

use LS_PRIMARY
go
create user LS_User without LOGIN;
go
--- 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 
go
create login LS_User with PASSWORD = 'yourSecurePassword', 
SID = 0x125896541236980KIN -- This is the SID from above query
Improve this answer
3
  • Thanks Paul. To clarify, I was aware of the "SID" option when creating a login, I was actually curious to know if the vendor could create a Sql Server login with a SID that matched a Windows login on my side. I'm going to test it, but figured I'd give someone like you the opportunity to weigh in. I'm guessing the mode of authentication will stop us from doing that, but you never know.
    – paulbarbin
    Commented Apr 22, 2016 at 20:49
  • @paulbarbin if the vendor could create a Sql Server login with a SID that matched a Windows login on my side Its not possible. Think about it .. if it was possible whats the point in having sql vs windows authentication !
    – Kin Shah
    Commented Apr 22, 2016 at 21:27
  • As I mentioned in my comments I figured that the mode of authentication would stop us, but I wanted to give it a try. In regards to your answer above, we'd prefer not to use sql authentication for multiple reasons, most important is the password is sent across the network in clear text and our database contains Protected Health Information. Our current workaround allows us to setup our own logins and manage security on our side, but it's definitely more complex and therefore prone to process failure.
    – paulbarbin
    Commented Apr 25, 2016 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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