Currently, on our project, we have a situation: there are a SQL Server 2012 instance IA in domain A and MS SQL Server 2012 instance IB in domain B. Domains A and B are trusted. IB configured as the linked server for IA.
Could you help with an idea how can be configured "forwarding" of Windows authentication security context from instance IA to IB and what is the set of requirements to doing this? Is it only way to use Kerberos delegation for linked server or is enough that they are located in trusted domains?
UPDATE:
There is an issue… it is clear that Kerberos delegation can be used for that but what about other ways? This not work for our case in the reason that for Kerberos delegation between different Active Directory Domain controllers will work only in the case when both od Domain Controllers are located on machines with OS Windows 12+ (not our case). Possibly it can be enough that SQL Server instances located in trusted domains. If client application located on the same server as SQL Server instance IA everything is working well without SPN configuration and etc. But what about the case when client application located on other server (different from IA and IB)?
Best Answer
The only way you can pass Windows Credentials between servers to my knowledge is via delegation as you've already taken a look at. If there's truly no way to get delegation working in your environment, an alternative to delegation is to fall back to mapping your Domain accounts to SQL authenticated accounts on the remote server that are setup with permissions that are equivalent to your needs. The downside here is any query executed remotely will be logged to the SQL Authentication account and not your AD Account, so audits won't be as transparent unless you take pains to map a one-to-one SQL Authenticated account to AD Account.
To map AD accounts to remote SQL Authenticated accounts, you first need to create the SQL Authenticated accounts on the remote server and then configure local logins to map to them on your linked server. If you're happy to use the GUI, you can find this window under Server Objects → Linked Servers → Linked Server Name → Properties → Security Page:
If you prefer TSQL instead, you can perform this same functionality via the sp_addlinkedsrvlogin stored procedure.
Final Note Here: Because you don't have delegation setup, you'll want to leave the Impersonate checkbox unchecked or if you're using TSQL, the
@useselfparameter should be passed in asFALSEotherwise it will try to pass through authentication which won't work in your scenario.