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

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:

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

-
set up Kerberos Authentication on their server

-
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?

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, y

StackExchange Database Administrators Q#245236, answer score: 6