how to set up a secured linked server?

background sql2k on nt5.
sorry to post this again. didn't get a good response for my last post and
really need some advice on this.
I have read that subject in SQL BOL.
and it doesn't make much sense to me.
If I were to have a NT group called sqladmins, and i assign Bob, Chris, and
Doug to that group.
I have 2 servers, ServerA and Server B. I want sqladmis to be able to query
ServerB (any database, such as sysobjects in master database) from ServerA,
but not for other users not belong to that group.
What should I do?
the bottom line is, I'd like to set up linked server securely and only allow
certain users in certain NT group to use it. (since sa password will be
supplied when set up a linked server, i don't want any user to use an
established linked server as they were sa to other servers). is that
possible?
Pls advise! Thank youSteve,
Using sp_addlinkedserverlogin you could have four logins.
1-3 = Bob, Chris, and Doug as such:
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
'OtherServerAdmin, 'OtherServerAdminPassword'
4 = for everybody else
EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login as
themselves.
Or, you could only use line 4 and then make the sysadmins group (Bob, et al)
also sysadmins on the other server. If you are letting them in as 'sa' then
they are being sysadmins. (Insert Here: Standard advice to not use the 'sa'
account.)
Russell Fields
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> background sql2k on nt5.
> sorry to post this again. didn't get a good response for my last post and
> really need some advice on this.
> I have read that subject in SQL BOL.
> and it doesn't make much sense to me.
> If I were to have a NT group called sqladmins, and i assign Bob, Chris,
and
> Doug to that group.
> I have 2 servers, ServerA and Server B. I want sqladmis to be able to
query
> ServerB (any database, such as sysobjects in master database) from
ServerA,
> but not for other users not belong to that group.
> What should I do?
> the bottom line is, I'd like to set up linked server securely and only
allow
> certain users in certain NT group to use it. (since sa password will be
> supplied when set up a linked server, i don't want any user to use an
> established linked server as they were sa to other servers). is that
> possible?
> Pls advise! Thank you
>|||thanks for the reply.
what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
can I have more detailed scripts for demonstrating the usage of this subject
based on the following info?
ServerA Name: sql2kt
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
===================ServerB Name: sql2k
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
My questions:
1. how to establish linked servers (from sql2kt to sql2k, to able to read
data either in finance or HR)?
2. how to allow this connection only used by Domain\SQLAdmins, but not by
Domain\JohnDoe?
Thank you.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
> Steve,
> Using sp_addlinkedserverlogin you could have four logins.
> 1-3 = Bob, Chris, and Doug as such:
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> 4 = for everybody else
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login
as
> themselves.
> Or, you could only use line 4 and then make the sysadmins group (Bob, et
al)
> also sysadmins on the other server. If you are letting them in as 'sa'
then
> they are being sysadmins. (Insert Here: Standard advice to not use the
'sa'
> account.)
> Russell Fields
>
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> > background sql2k on nt5.
> >
> > sorry to post this again. didn't get a good response for my last post
and
> > really need some advice on this.
> >
> > I have read that subject in SQL BOL.
> > and it doesn't make much sense to me.
> >
> > If I were to have a NT group called sqladmins, and i assign Bob, Chris,
> and
> > Doug to that group.
> > I have 2 servers, ServerA and Server B. I want sqladmis to be able to
> query
> > ServerB (any database, such as sysobjects in master database) from
> ServerA,
> > but not for other users not belong to that group.
> > What should I do?
> >
> > the bottom line is, I'd like to set up linked server securely and only
> allow
> > certain users in certain NT group to use it. (since sa password will be
> > supplied when set up a linked server, i don't want any user to use an
> > established linked server as they were sa to other servers). is that
> > possible?
> >
> > Pls advise! Thank you
> >
> >
>|||Steve,
Sorry about the lack of scripts, but..
sp_addlinkedserver - defines the link to another server
sp_addlinkedsrvlogin - defines a login (or logins) that will be used in the
link to the other server
The logins that pass through the link need to be authorized on the link
server as well. So, in the case of Domain\Bob you would need to GRANT him
rights to the finance and HR databases (or to the needed views, etc. in
those databases.)
Russell
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OkKa2K4PEHA.3216@.TK2MSFTNGP12.phx.gbl...
> thanks for the reply.
> what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
> can I have more detailed scripts for demonstrating the usage of this
subject
> based on the following info?
> ServerA Name: sql2kt
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> ===================> ServerB Name: sql2k
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> My questions:
> 1. how to establish linked servers (from sql2kt to sql2k, to able to read
> data either in finance or HR)?
> 2. how to allow this connection only used by Domain\SQLAdmins, but not by
> Domain\JohnDoe?
> Thank you.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
> > Steve,
> >
> > Using sp_addlinkedserverlogin you could have four logins.
> >
> > 1-3 = Bob, Chris, and Doug as such:
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
> > 'OtherServerAdmin, 'OtherServerAdminPassword'
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
> > 'OtherServerAdmin, 'OtherServerAdminPassword'
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
> > 'OtherServerAdmin, 'OtherServerAdminPassword'
> >
> > 4 = for everybody else
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to
login
> as
> > themselves.
> >
> > Or, you could only use line 4 and then make the sysadmins group (Bob, et
> al)
> > also sysadmins on the other server. If you are letting them in as 'sa'
> then
> > they are being sysadmins. (Insert Here: Standard advice to not use the
> 'sa'
> > account.)
> >
> > Russell Fields
> >
> >
> > "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> > news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> > > background sql2k on nt5.
> > >
> > > sorry to post this again. didn't get a good response for my last post
> and
> > > really need some advice on this.
> > >
> > > I have read that subject in SQL BOL.
> > > and it doesn't make much sense to me.
> > >
> > > If I were to have a NT group called sqladmins, and i assign Bob,
Chris,
> > and
> > > Doug to that group.
> > > I have 2 servers, ServerA and Server B. I want sqladmis to be able to
> > query
> > > ServerB (any database, such as sysobjects in master database) from
> > ServerA,
> > > but not for other users not belong to that group.
> > > What should I do?
> > >
> > > the bottom line is, I'd like to set up linked server securely and only
> > allow
> > > certain users in certain NT group to use it. (since sa password will
be
> > > supplied when set up a linked server, i don't want any user to use an
> > > established linked server as they were sa to other servers). is that
> > > possible?
> > >
> > > Pls advise! Thank you
> > >
> > >
> >
> >
>