Windows NT group as local login in linked servers

Windows NT group as local login in linked servers

Post by Tomislav Herce » Wed, 15 May 2002 01:00:19



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?

Tnx.

Sincerely,
Tomislav

 
 
 

Windows NT group as local login in linked servers

Post by Dejan Sark » Wed, 15 May 2002 15:23:07


Tomislav,

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
or
> I need to explicit define users from that group one by one?

> Tnx.

> Sincerely,
> Tomislav


 
 
 

Windows NT group as local login in linked servers

Post by Kun Cheng [M » Wed, 22 May 2002 05:15:26


You can do it on both sql 7.0 and sql 2k.

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
15:59:21 GMT)

| 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
| Path:
cpmsftngxa08!tkmsftngp01!newsfeed00.sul.t-online.de!t-online.de!newsfeeds.be
lnet.be!news.belnet.be!news-hub.siol.net!news1.hinet.hr!newsVIP.hr!not-for-m
ail
| 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
or
| I need to explicit define users from that group one by one?
|
| Tnx.
|
| Sincerely,
| Tomislav
|
|
|

 
 
 

1. Linked server doesn't recognize login via a local group

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) */

2. Will pay for your advice

3. SQL2K - Linked server - Mapping Windows group to a remote login

4. QuickRep subdetail shows only once

5. using windows NT Server Local groups

6. ***NY/NJ-DATA MODELERS/LOGICAL/PHYSICAL-WANTED*******

7. NT local group as a login in SQL 7.0

8. pgsql/src backend/access/transam/xact.c backen ...

9. Remapping/Copying SQL Logins from NT Local Groups to Domain Global Groups

10. Renaming Server name - Impact on local NT group Sql IDs

11. SQL Server 7 and Windows NT Terminal Server and Windows NT 5.0 beta

12. Windows NT Server vs Windows NT Server Enterprise version

13. Linked Server and NT groups