I was able to grant access in MSSQL to an Active Directory group and add a service account to that group. It's working fine, I'm able to use trusted connection to login on that MSSQL using both my personal account and the service account, from MSSQL and from .Net exe.
Now I need to configure a Linked Server on another instance to this one, using this AD service account.
For creating a Linked Server with local MSSQL account, I just open New Linked Server dialog, provide MSSQL host, set server type to MSSQL, in Security set Be made using this security context
, provide username and password, and in Server Options set true to RPC and RPC Out.
But when I try to do that with an AD account, I get an error saying that login failed.
Any idea how to configure a Linked Server using AD account?
Best Answer
The two most common scenarios for security via a linked-server are:
Create a SQL login on the target SQL Server, and use that login in the definition of the linked server. You then
GRANT
andDENY
access to that login on the target server objects as necessary, using the principle of least privilege.Define the SQL Server to allow "pass through" authentication by configuring security to "be made using the login's current security context". This allows security to vary amongst users and is far more flexible. From MSDN:
In order to use option #2 above, in a multi-hop authentication scenario, you'll need to enable several things to support Kerberos Authentication. MSDN has a great blog on the requirements, available here
You may also want to look at this question.