SQL Server – Creating Logins Mapping for Linked Server Without AD User Logins

linked-serversql server

We use windows auth, but we don't create logins for particular AD users. We create logins for AD groups and the users have access by being their members.

I noticed that when I try to create a mapping for some AD users (not the groups) using a command like below, besides creating the mapping the DOMAIN\ADlogin login is created.

EXEC master.dbo.sp_addlinkedsrvlogin 
  @rmtsrvname = N'RemoteName',
  @locallogin = N'DOMAIN\ADlogin',
  @useself = N'False',
  @rmtuser = N'rmtuser',
  @rmtpassword = N'password'

If I drop the login the mapping will be dropped as well.

DROP LOGIN [DOMAIN\ADlogin]

Is there any way to avoid creating logins but get the mapping for AD users? It sounds for me like it's impossible but maybe somebody has good experience in solving this problem.

Microsoft SQL Server 2017 (RTM-CU9) (KB4341265) – 14.0.3030.27 (X64)
Jun 29 2018 18:02:47 Copyright (C) 2017 Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2016 Standard 10.0
(Build 14393: )

Best Answer

Is there any way to avoid creating logins but get the mapping for AD users?

If you run this code:

sp_helptext 'sp_addlinkedsrvlogin'

You'll be able to find a piece of code responsible for win login creation:

-- IF SPECIFIED CHECK LOCAL USER NAME (NO NT GROUP!)
select @localid = 0
if (@locallogin IS NOT NULL)
begin
    -- share-lock the local login
    EXEC %%LocalLogin ( Name = @locallogin ) . Lock ( Exclusive = 0 )
    IF @@ERROR = 0
        select @localid = principal_id from sys.server_principals
            where name = @locallogin and type in ('S', 'U')
    else
    begin
        -- ADD ROW FOR NT USER LOGIN IF NEEDED --
        if (get_sid('\U'+@locallogin) IS NOT NULL)
        begin
            EXEC @ret = sys.sp_MSaddlogin_implicit_ntlogin @locallogin
            if (@ret = 0)
                select @localid = principal_id from sys.server_principals
                    where name = @locallogin and type = 'U'
        end
    end
    if (@localid = 0)
    begin
        ROLLBACK TRAN
        raiserror(15007,-1,-1,@locallogin)
        return (1)
    end
end

Here sys.sp_MSaddlogin_implicit_ntlogin @locallogin creates the corresponding login and its principal_id will next be retrieved by this code:

select @localid = principal_id from sys.server_principals
                        where name = @locallogin and type = 'U'

(here U indicates WINDOWS_LOGIN)

And then it will be passed to create a mapping:

EXEC %%LinkedServer(Name=@rmtsrvname).NewLinkedLogin(
            LocalID=@localid, UseSelf=@useselfbit, RemoteName=@rmtuser, Password=@pwd)

So the answer to your question is NO unless you map all your logins to the same remote_user by passing NULL as @locallogin.

Related Question