When I created linked server can I define Windows NT group in local login or
I need to explicit define users from that group one by one?
No problem with group in SQL 2000.
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Quote:> When I created linked server can I define Windows NT group in local login
> I need to explicit define users from that group one by one?
This posting is provided "AS IS" with no warranties, and confers no rights.
| Newsgroups: microsoft.public.sqlserver.server
| Subject: Windows NT group as local login in linked servers
| Date: Mon, 13 May 2002 18:00:19 +0200
| Organization: Bear foot
| Lines: 9
| NNTP-Posting-Host: zw154157.win.vipnet.hr
| X-Trace: fegnews.vip.hr 1021305561 10958 10.243.154.157 (13 May 2002
| NNTP-Posting-Date: Mon, 13 May 2002 15:59:21 +0000 (UTC)
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
| Xref: cpmsftngxa08 microsoft.public.sqlserver.server:209572
| X-Tomcat-NG: microsoft.public.sqlserver.server
| When I created linked server can I define Windows NT group in local login
| I need to explicit define users from that group one by one?
SQL2K, SP2 on Win2K AS, SP2
Repro script at the bottom for clarity
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
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?
/* 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 */
sp_addlinkedsrvlogin 'REMOTESRV', 'false', 'LOCALSRV\LOCAL_
DBA', 'sa', 'sa_password'
/* Map domain user login to sa on REMOTESRV */
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) */