Create Linked Server using Active Directory account

Question

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?

Answer 1

The two most common scenarios for security via a linked-server are:

1. Create a SQL login on the target SQL Server, and use that login in the definition of the linked server. You then GRANT and DENY access to that login on the target server objects as necessary, using the principle of least privilege.

2. 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:

Instead of having to use sp_addlinkedsrvlogin to create a predetermined login mapping, SQL Server can automatically use the Windows security credentials (Windows login name and password) of a user issuing the query to connect to a linked server when all the following conditions exist:

• A user is connected to SQL Server by using Windows Authentication Mode.

• Security account delegation is available on the client and sending server.

• The provider supports Windows Authentication Mode; for example, SQL Server running on Windows.

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.