Sql-server – Managing Security for a Log Shipped db across two (unconnected) domains

active-directoryauthenticationlog-shippingsql serversql-server-2008

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?

Best Answer

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

Related Solutions

Sql-server – Managing Access for DBA and IT Support

Regarding providing DBA access, you might be better-off creating an Active Directory group and adding the DBAs into that group. That way, you can provide group-level permissions and give sysadmin to the group rather than individual users, meaning that when a DBA leaves (or a new one gets hired), you just need to adjust Active Directory rights rather than going into each SQL Server and altering logins. The same applies (though without granting sysadmin!) for other groups of internal users. Windows authentication is more secure than SQL authentication, and if you can use it, you probably should.

Regarding auditing, you can still have individual-level auditing when users are in a group. You can use SUSER_NAME() to get the username in whatever process you're going to use to log activity. Note that somebody could do an EXECUTE AS to switch their user context, but you should be able to log that as well as the IP address from which the query came, so you'd still be able to correlate activity to a person.

The idea of using a jumpstation (in your case, you mentioned a server) is possible. It would make administration a bit easier, especially if the laptop domain does not have a full trust relationship with the domain upon which the SQL Server instances are located. It would probably be a bit annoying for the support personnel, honestly, but if you go that route, I'd recommend a virtualized desktop for each IT support team member. That way, they don't have to deal with server contention issues and you can minimize the pain. If you do go down that route, then the IT support team members could have domain accounts and you'd create relevant Windows groups the same way as the DBA group.

Sql-server – SQL Server grant permissions to STANDBY database

Below steps should work :

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

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>'

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

Best Answer

Related Solutions

Sql-server – Managing Access for DBA and IT Support

Sql-server – SQL Server grant permissions to STANDBY database

Related Question