Link to Server via Windows Authentication

Well, then everyone who used the linked server will be
using the new account created. I want people to only use
what thier domain account has permissions to do.
Any way to do this?
>--Original Message--
>You'll need to create a domain account that has login
>permissions to SQL Server "A" and the appropriate
database
>permissions as well. Use this account for the user login
>credentials when you create the link.
>Steve
>.
>You can use delegation to accomplish this. You can find
information on this in books online under the topic:
Security Account Delegation
-Sue
On Mon, 1 Mar 2004 06:57:34 -0800, "Linda"
<anonymous@.discussions.microsoft.com> wrote:
>Well, then everyone who used the linked server will be
>using the new account created. I want people to only use
>what thier domain account has permissions to do.
>Any way to do this?
>database

Link server not working thru Windows authentication - "Login failed

I've two SQL server 2005 servers running under the same domain and the
SQL server service is also running under domain adminstrator for both
the servers. The OS on both the servers is Win2K3.
All the developers were working on these servers as 'sa' but recently
we have changed it to run on windows authentications. In doing so I
added all the developer's domain logins to a local group "Power Users"
on both the servers and now every body can log on to these servers
using their domain logins(windows authentication). But the link server
from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
run a distributed querry from QA.
I've also tried adding individual logins on the Security tab on link
server gui and checked impersonate check box also but with no results.
On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
sysadmin as server role.
One strange thing is if I directly logon (using my login) to the serv1
and run a distributed querry using the same link server it works.
Any help will be appriciated
Thanks,
SGsaboo,
I believe that you have a delegation problem here. Is Kerberos running in
your domain and are both servers correctly set up to user Kerboros? Read
this for an explanation:
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
When you directly login to serv1 you are now only one hop away from serv2
and delegation is not an issue. But, from a desktop to serv2 is two hops.
RLF
"saboo" <subodh97@.gmail.com> wrote in message
news:c210f136-752a-445a-94cf-b05599286972@.j20g2000hsi.googlegroups.com...
> I've two SQL server 2005 servers running under the same domain and the
> SQL server service is also running under domain adminstrator for both
> the servers. The OS on both the servers is Win2K3.
> All the developers were working on these servers as 'sa' but recently
> we have changed it to run on windows authentications. In doing so I
> added all the developer's domain logins to a local group "Power Users"
> on both the servers and now every body can log on to these servers
> using their domain logins(windows authentication). But the link server
> from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
> Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
> run a distributed querry from QA.
> I've also tried adding individual logins on the Security tab on link
> server gui and checked impersonate check box also but with no results.
> On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
> sysadmin as server role.
> One strange thing is if I directly logon (using my login) to the serv1
> and run a distributed querry using the same link server it works.
> Any help will be appriciated
> Thanks,
> SG|||On Feb 13, 2:40=A0pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> saboo,
> I believe that you have a delegation problem here. =A0Is Kerberos running =in
> your domain and are both servers correctly set up to user Kerboros? =A0Rea=d
> this for an explanation:http://blogs.msdn.com/sql_protocols/archive/2006/0=
8/10/694657.aspx
> When you directly login to serv1 you are now only one hop away from serv2
> and delegation is not an issue. =A0But, from a desktop to serv2 is two hop=s.
> RLF
> "saboo" <subod...@.gmail.com> wrote in message
> news:c210f136-752a-445a-94cf-b05599286972@.j20g2000hsi.googlegroups.com...
>
> > I've two SQL server 2005 servers running under the same domain and the
> > SQL server service is also running under domain adminstrator for both
> > the servers. The OS on both the servers is Win2K3.
> > All the developers were working on these servers as 'sa' but recently
> > we have changed it to run on windows authentications. In doing so I
> > added all the developer's domain logins to a local group "Power Users"
> > on both the servers and now every body can log on to these servers
> > using their domain logins(windows authentication). But the link server
> > from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
> > Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
> > run a distributed querry from QA.
> > I've also tried adding individual logins on the Security tab on link
> > server gui and checked impersonate check box also but with no results.
> > On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
> > sysadmin as server role.
> > One strange thing is if I directly logon (using my login) to the serv1
> > and run a distributed querry using the same link server it works.
> > Any help will be appriciated
> > Thanks,
> > SG- Hide quoted text -
> - Show quoted text -
Delegation isn't too bad to deal with. Most likely your SQL servers
are not using a Local Service account to run (good for you, this is
not good security) and the accounts it's using are not domain admins
(again, good job). Here's the quick down-low on what you need to do:
You need domain admin rights to create an SPN, if you don't have them
you need to get your DA to do this for you. Download the Windows
Server tools for your flavor of server(http://go.microsoft.com/fwlink/?
LinkId=3D100114). You want the SetSPN tool. You need to create an SPN
for each of the SQL servers, this can be done from your workstation as
it's set Domain wide, not at the server level.
setspn -a service/name hostname
i.e.
setspn -a MSSQLsvc/my.server.name:1433 mydomain\MySqlServiceAccount
Again, do this for BOTH sql servers...sql

Limiting Domain Admin access to SQL Server

How do I limit Domain Admins from administering SQL Server?
In my orginization we have multiple Domain Admins and we
need to limit the number of user ID's that can administer
SQL Server to as few as possible.
There are many legal issues that brought this forward.
Mainly the need to limit the number of people that have
the ability to "change" data that resides on this server.
Thanks.
MattAdd only the trusted individuals to the sysadmin role, then remove
BUILTIN\Administrators from the sysadmin role.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Matt Troska" <matt.troska@.co.mcleod.mn.us> wrote in message
news:01a701c3dae0$59f035f0$a601280a@.phx.gbl...
How do I limit Domain Admins from administering SQL Server?
In my orginization we have multiple Domain Admins and we
need to limit the number of user ID's that can administer
SQL Server to as few as possible.
There are many legal issues that brought this forward.
Mainly the need to limit the number of people that have
the ability to "change" data that resides on this server.
Thanks.
Matt