how to stop using sql server login credentials in a linked server?

Question

I have a linked server based in Italy server_italia that connects to a server based in Oregon USA ORDB1

we are on different but trusted domains.

The connection from server_italia to ORDB1 is made through a linked server using a sql server account that has a login and the required permissions on ORDB1.

I am a sysadmin and even domain admin in my domain, and my server_italia but I am only sysadmin inside the sql server on ORDB1.

There is a similar question:

How can I get my linked server working using Windows authentication?

but it does not provide the answer.

Here is my linked server:

when I run the following queries on server_italia I get the following results:

SELECT [the_server]=@@servername,
auth_scheme 
FROM sys.dm_exec_connections WHERE session_id = @@spid ;  


SELECT net_transport, auth_scheme   
FROM sys.dm_exec_connections   
WHERE session_id = @@SPID; 

In Oregon I get this:

If my American colleagues get:

1. the Service Principal Names registered for the services being run by your service account

2. set up Kerberos Authentication on their server

3. Configure Constrained Delegation as that will allow the credentials to be passed along, otherwise you may still run into the double-hop issue. – added on the comments by John Eisbrener

Would I be able to use my linked server without the sql server account login impersonation?

Answer 1

I don't know if you got this resolved or not, but after recently going through our own (re)discovery of the requirements to get delegation running, I figured I could at least provide an answer in the event others come across your post with similar questions.

To help make my post clear (especially to anyone coming across it and not reading through your question), delegation is the ability of an intermediary service to hand off client credentials to a secondary service on behalf of the client. Represented visually, as follows:

In this case Server A is the intermediary service handing off the Client credentials to the secondary service, Server B. In your case, server_italia would be considered the intermediary server and ORDB1 would be the secondary service.

ACTIVE DIRECTORY (AD) FUNCTIONAL LEVEL

First and foremost, your Domain Functional Level heavily dictates the options available to you. If your Functionl Level is at any version prior to Windows 2012, delegation between domains is limited to Unconstrained Delegation only. Prior to 2012, constrained delegation was limited to within a single-domain only and would not have cross-domain capabilities.

If you're running a 2012 Functional Level or later, you have additional options, so it'll depend on your preference for administration, access control, etc. A 2012 Functional Level does open up the ability to delegate credentials across domains via Unconstrained Delegation, Traditional Constrained Delegation, or Resource-Based Constrained Delegation. The rest of my answer will assume you're using a 2012+ Functional Level, but if not, the Unconstrained Delegation configuration instructions are the same, regardless the functional level.

COMMON REQUIREMENTS

Regardless the type of delegation you choose (all options will be listed below), all configurations will require the following basic setup for the account you will be using to run the "intermediary" service (e.g. Server A in the image above, but this can apply to any intermediary service such as a Database, a Web Site hosted on IIS, SSIS, a .NET app, etc.). On the Domain, the account running the service delegating credentials to other services will require the following AD properties be set:

• ServicePrincipalNames: This needs to have any SPNs for Server A listed that will be delegating credentials. In your example, those would look like the following (assuming you're on the EURO domain of the CONTOSO forest): MSSQLSvc/server_italia.euro.contoso.com, MSSQLSvc/server_italia.euro.contoso.com:1433
• KerberosEncryptionType: AES256 (and AES128 if you choose to use that encryption type) should be enabled. This is important later on, but this setting will ensure Kerberos Encrypted tokens can be delegated.

NOTE: The aforementioned properties can be viewed and set via a variety of methods. The Active Direcotory Users and Computers MMC Snapin is the most common Graphical User Interface you can use to adjust these properties, BUT this interface obfuscates a lot of these properties and is total garbage when it comes to managing Group Managed Service Accounts. As a result, I suggest you use the ActiveDirectory PowerShell module as it allows you to set these properties explicitly via either the Set-ADUser or Set-ADServiceAccount commands. Additionally, you can view properties of a user/service account via the Get-ADUser or Get-ADServiceAccount commands.

In addition, all SQL Servers should have a registered SPN. You can manually create these, but if you nest the service accounts running your SQL Servers into a proper OU, self-registered SPNs will happen automatically. Instructions on that process can be found here.

TYPES OF DELEGATION

Again, assuming you're using an AD Functional Level of 2012 or later, you have 3 options for performing cross-domain delegation. I will list them in the easiest to configure/least secure to most frustrating to configure/most secure approaches. Any of these will work, and how much administration/security exposure you wish to deal with will be completely up to you.

One other tidbit here, is that there is nothing preventing you from configuring an account to use multiple or all types of delegation listed below. These configurations are not mutually exclusive. You should do your best to avoid having any account setup under multiple delegation schemes though, as it will likely use the least secure configuration first (section on determining how your accounts are setup is below).

UNCONSTRAINED DELEGATION

Unconstrained Delegation is the easiest configuration to setup, yet least restrictive approach. This configuration allows the Service Account running Server A to delegate any credential to any secondary service. This is visually represented in the image above with the blue arrows denoting no limitation on inbound client connection or secondary service that credentials can be delegated to. To configure this type of delegation on the intermediary account, you only need to set the following AD property (in addition to those listed above):

• TrustedForDelegation – Set this to True

This used to be all that there was to it, but with July 2019's Windows Security patch, you will need to re-enable delegation across trusts as well, as outlined in that link. That security patch was really designed to address the serious downside of this setup, which is much better explained in this post: Hunting in Active Directory: Unconstrained Delegation & Forests Trusts. Again, unless you're using an AD Functional Level prior to 2012, it's not recommended you use this type of delegation.

TRADITIONAL CONSTRAINED DELEGATION

Traditional Constrained Delegation limits the services Server A can delegate permissions to, such that the Service Account running Server A is only allowed to delegate credentials to services that are explicitly listed. For instance, it can still delegate anyone’s credentials but it can only pass along said credentials to the services that are explicitly listed. So visually speaking, we’re limiting the “destinations” we can delegate to, as denoted by the yellow arrow:

The AD Properties (in addition to those listed in the Common Requirements section above) you have to configure on the intermediary account are as follows:

• msDS-AllowedToDelegateTo - This needs to be a list of all applicable SPNs that this account is allowed to delegate credentials to. In this example, those would look like the following: MSSQLSvc/ORDB1.usa.contoso.com, MSSQLSvc/ORDB1.usa.contoso.com:1433 (assuming the ORDB1 server is on a USA domain of the CONTOSO forest).

With this configuration, you are limiting delegation to AD KERBEROS tokens only. If you wish to delegate non-KERBEROS credentials, such as those passed via NTLM etc., you can configure this account to delegate ANY protocol by setting the following property as well:

• TrustedToAuthForDelegation – Set this to True

Using the ANY protocol configuration is inherently risky though. This blog article digs further into these properties and their ramifications, and unless there is a need to set this to true, it's recommended you don't.

From a personal standpoint, we often use Traditional Constrained Delegation because it's easy to setup and relatively secure (unless you decide to use delegation for ANY protocol, then you're setting yourself up for issues, in my opinion).

RESOURCE-BASED CONSTRAINED DELEGATION

Resource-Based Constrained Delegation limits the intermediary services that the secondary service allows delegated credentials from. This is slightly different from the other configurations in that instead of configuring Server A like in the prior configurations, we will be configuring Server B by listing the services that it will trust delegated credentials from. This is done without having to make any additional changes to the intermediary account running Server A. So in this scenario, when Server A attempts to pass along Bob’s credentials, Server B will identify that Server A is a “trusted” service that can delegate permissions and authentication will occur without issue. Represented visually, it would look as follows:

To be absolutely clear here, the intermediary account (e.g. Server A) still requires the properties in the Common Requirements section above be configured. In addition though, the destination service (i.e. Server A) requires the additional AD Property be set:

• PrincipalsAllowedToDelegateToAccount - This needs to have all applicable SPNs listed of those services that are trusted to delegate credentials from. In this example, those would look like the following: MSSQLSvc/server_italia.euro.contoso.com, MSSQLSvc/server_italia.euro.contoso.com:1433

That’s it… conceptually. Test this out to be certain, but from the articles I’ve found outlining this approach, this is really all that’s required. In fact, this MS article runs through the process and lists out the applicable PowerShell commands necessary to set this up so a sample PowerShell program can be run to confirm the double-hop and that Resource-based Constrained Delegation will work for you. Additionally, this delegation configuration should work across domains without issue (or requirement of a trust, from what I've read). The downside here is that we have to configure all the “end points” which may be more administration work in the long run depending upon how your setup is.

HOW DO I KNOW WHAT DELEGATION SETTINGS I HAVE CONFIGURED?

In my opinion, identifying what delegation configuration an account is running under is about as clear as mud. Thankfully Willem Kasdorp posted an article along with a PowerShell script to show you what your accounts are currently configured with. If you run this from a computer with the ActiveDirectory PowerShell module installed, you should be good to go. Hopefully the combination of the types of delegation configurations I mention above and the matrix this script generates will give you a clear look at what you've currently got configured.

Finally, if you want some help with the AD PowerShell commands, this article does a decent-enough job of showing the proper syntax when setting some of the properties I talked about with lists of SPNs, etc.

If you have any questions, please post a comment and I'll try my best to answer promptly.