Linked server doesn't recognize login via a local group

From: Pete F (phf175SPAMBLOCK@hotmail.com)
Date: 06/18/02 

From: "Pete F" <phf175SPAMBLOCK@hotmail.com>
Date: Tue, 18 Jun 2002 06:16:23 -0700

SQL2K, SP2 on Win2K AS, SP2
Repro script at the bottom for clarity

Hi all,

I have an issue with linked servers. I'm using SQL2K in an
NT4 style domain ie no AD, so no account delegation. I'm
trying to set up a SQL2K linked server (REMOTESRV) from my
local server (LOCALSRV). So, I add the linked server
definition, and then try to specify the login mapping. My
Windows domain login is in a domain global group
(DOMAIN_DBA), which is in a LOCALSRV local group
(LOCALSRV_DBA), which is in turn granted server access and
is a member of Sysadmins. I am therefore a Sysadmin by
being in DOMAIN_DBA.

However, I cannot map LOCALSRV_DBA to a SQL login (sa, for
example) on REMOTESRV, because SQL Server will not accept
that LOCALSRV_DBA is a local login - I keep getting the
error "Error 7416: Access to the remote server is denied
because no login-mapping exists".

In EM, LOCALSRV_DBA is not displayed in the drop-down box
on the Security tab of the linked server properties
dialogue - only individual Windows account appear, not
groups. sp_addlinkedsrvlogin will add the login, but I get
the error as above.

So, it appears that despite having implemented a security
architecture as per BOL recommendations (global groups
into local), I can't then use linked servers without
creating a personal login on LOCALSRV, which totally
defeats the objective of Windows group management of SQL
logins.

Has anyone else been in this situation and (hopefully)
found a workaround of some sort that doesn't involve
adding every DBA's personal domain account to LOCALSRV?

Thanks,

Pete

Repro script:

/* Grant server access to a local server group and a
domain account */
exec sp_grantlogin 'LOCALSRV\LOCAL_DBA'
exec sp_grantlogin 'DOMAIN\RANDOM_ACCOUNT'

/* Add the linked server */
exec sp_addlinkedserver 'REMOTESRV', 'SQL Server'

/* Map local group login to sa on REMOTESRV */

exec
sp_addlinkedsrvlogin 'REMOTESRV', 'false', 'LOCALSRV\LOCAL_
DBA', 'sa', 'sa_password'

/* Map domain user login to sa on REMOTESRV */

exec
sp_addlinkedsrvlogin 'REMOTESRV', 'false', 'DOMAIN\RANDOM_A
CCOUNT', 'sa', 'sa_password'

/* Both the above commands execute without error, but
while DOMAIN\RANDOM_ACCOUNT can now access REMOTESRV as
sa, LOCALSRV\LOCAL_DBA members cannot (error 7416) */

Relevant Pages

  • Re: Linked Server/NT Group
    ... You would like to allow Domain\SpecialUsers through the linked server without specifying Fred and Sally separately. ... mappings of rights always need to be for singleton users. ... On the server being linked to, grant Domain\SpecialUsers the appropriate rights in the remote server's database or databases. ... I have to add each NT login to the server ...
    (microsoft.public.sqlserver.security)
  • Linked Server Security
    ... I'm having problems querying a linked server. ... "SELECT permission was denied on the object all_columns, database ... I get the same "Login failed" error message. ...
    (microsoft.public.sqlserver.security)
  • Re: Setting up Linked server to MsAccess
    ... This will set the security so anyone using the linked server ... At the database level I have added my Server login ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Job Running Remote Query
    ... In the linked server security properties, ... login and password for the udb database. ...
    (microsoft.public.sqlserver.security)
  • Re: MAS90
    ... To set up a linked server from SQL Server 2000 to MAS90 using Enterprise ...
    (comp.databases.ms-access)
Quantcast