Cross-Domain Windows Authentication for SQL Server databases

There are many acquisitions these days and the DBA and sysadmins have a great tasks of doing a cross-domain authentication to their servers and databases. System admins have various options to connect interdomain where as DBA has to look up for it. Here is a solution that one can try using to access databases in cross-domain platform with windows authentication.
Consider one of the servers is in new york domain and Second is in boston domain. newyork domain can't talk to boston domain and vice versa..The idea is to make the windows user in newyork domain access database in boston domain using the same windows credentials. This can be achieved by following steps below.
1. Create a SQL login named sqluser in the secondary server and copy the sid of the login.
2. Create a login in the primary named sqluser with sid captured.
CREATE LOGIN sqluser with PASSWORD='sqluser1', SID = 0xB0B50710A1D1394D8E1B26CFA25AA3BE
--- copied from secondary server.
Assign the datareader permissions to primary server.
3. This user will be replicated to the secondary readonly db through log backup, being sid matches between the user and login named sqluser, it works.
It’s simple, no downtime required.
Windows Authentication: (user named newyork\sqluser)
It’s the same but we may have to work thru some tricky steps. Reason behind was that Windows domain account will have a unique SID value.
1. Create a windows login named newyork\sqluser in the secondary server. Copy the sid.
2. Create a login in the primary named sqluser with sid captured
CREATE LOGIN sqluser with PASSWORD='sqluser123', SID = 0x01050000000000051500000071416F0BD9618A1FC22E626EC6190003
it results with the below error..
Msg 15419, Level 16, State 1, Line 1
Supplied parameter sid should be binary(16).
3. Basic and logic helps here. Create a login named sqluser in the Primary with the datareader permissions.

CREATE LOGIN sqluser with PASSWORD='sqluser123'
4. Now the user named sqluser is replicated to the secondary readonly db but stands as a orphaned user. It’s not in sync with the windows login(newyork\sqluser).
5. whats the next option.. how do we bring the sid in sync now.
Use SQLCMD with DAC session on primary server
c:\> SQLCMD -A -d master
-A - options opens DAC(admin session)
SQLCMD>
Enable the allow updates option using sp_configure and update the sid of the sqluser login in sys.sysusers table with sid of windows login(newyork\sqluser)
This query results with an error because updates can be happened only when db is in single user mode with sql 2005.
C:\>sqlcmd -A
1> sp_configure 'allow updates'
2> go
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
allow updates 0 1 1 1
1> update sys.sysusers set sid = 0xB0B50710A1D1394D8E1B26CFA25AA3BE where name like 'sqluser'
2> Go
Msg 259, Level 16, State 1, Server servername, Line 1
Ad hoc updates to system catalogs are not allowed.
1>exit
sid can be captured in sys.sysxlgns table also which are similar to sysxlogins in SQL 2000. It can be viewed only when you open a session using DAC. In order to update the system tables we have to bring sql server in single user mode.

sqlservr -m -- helps you to bring sql server in single user mode.
Repeat step 5 but sql server should be started with single user mode.
C:\>sqlcmd -A
1> use demo;
2> go
Changed database context to 'Demo'.
1> update sys.sysusers set sid = 0xD67EDAA0627B944F997EB9C552DEE47A where name like 'sqluser';
2> go
(1 rows affected)
Warning: System table ID 27 has been updated directly in database ID 6 and cache coherence may not have been maintained. SQL Server should be restarted.
Now it works. recycle the SQL.Log backup of primary database will update the sid of the sys.sysusers in the secondary db. Now the windows user will have the datareader permissions on the table..
Now newyork\sqluser will have readonly access to secondary db