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 Servers with Delegation

I am trying to implement a linked server that uses integrated authentication on a 64 bit Wndows 2003 SP1 server. I have both Sql Server 2005 and Sql Server installed, and have successfully created database link that is able to use double hop authentiction on the Sql Server 2005 instance. I am unable to do the same usign the Sql Server 2000 instance. Does anyone know if double hop uathentication using Kerberos is supported on Sql Server 2000. The linked server on Sql Server 2005 is created using this syntax

"EXEC sp_addlinkedserver @.server=’LinkedServer’,

@.srvproduct=''",

@.provider='SQLNCLI',

@.datasrc=’SQLB’,--the data source

@.provstr="Integrated Security=SSPI; "

"exec sp_addlinkedsrvlogin ‘LinkedServer’, 'true'"

SPN's and domain accounts have been created as documented and those same accounts are used in both the Sql Server 2005 and Sql Server 2000 instances.

The error message going from a Sql Server 2000 or 2005 client, to the Sql Server 2000 instance that has the linked server using the SQLNCLI provider is

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' reported an error. Authentication failed.
[OLE/DB provider returned message: Communication link failure]
[OLE/DB provider returned message: Named Pipes Provider: No process is on the other end of the pipe.
]
[OLE/DB provider returned message: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]
OLE DB error trace [OLE/DB Provider 'SQLNCLI' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

If I use the Sql Server 2000 OLEDB provider when creating the link I get this error

Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

The same link using the SQLNCLI provider in Sql Server 2005

works and I am able to use double hop authentication.

My question is , does anyone know if double hop authentication is supported using a Sql Server 2000 linked server?

Using Kerberos as Authentication protocol and the appropiate configuration this should work even for SQL Server 2000.

http://msdn2.microsoft.com/en-us/library/aa905162(sql.80).aspx
http://msdn2.microsoft.com/en-us/library/ms189580.aspx

Jens K. Suessmeyer.

http://www.sqlserver2005.de

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 users to user sql server

Hello there
When i started using sql server i choose Windows authentication.
Now i need to move to sql server authentication so that not anyone can have
full access to Enterprice manager or the query anlyser
In fact i don't want that the administrator on the Windows 2000 server would
be the administrator on the sql server
What i need to do for this?
any help would be usefulYou can remove the administrators group from being able to log into SQL
Server. There is no changing to SQL authentication, you would change to
mixed mode which allows SQL and Windows authentication. But it sounds like
you are just wanting to restrict people in the admin group from accessing
and that is done by removing the admin groups from SQL.
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:Og$rJBWxDHA.3224@.tk2msftngp13.phx.gbl...

quote:

---

> Hello there
> When i started using sql server i choose Windows authentication.
> Now i need to move to sql server authentication so that not anyone can

---

have

quote:

---

> full access to Enterprice manager or the query anlyser
> In fact i don't want that the administrator on the Windows 2000 server

---

would

quote:

---

> be the administrator on the sql server
> What i need to do for this?
> any help would be useful
>

---

|||Just remove the BUILTIN\Administrators login from SQL Server. But before
you do this make sure you know the sa password or have another NT account
that has sysadmin priviledge.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Limiting Access with Forms Authentication

All,
I have successfully implemented a forms authentication model for
reporting services in our dev environment. I took the code directly
from the MSDN whitepaper "Using Forms Authentication in Reporting
Services", although I did modify the authentication portion to use an
in house Oracle solution. How do I go about ensuring that only certain
groups (departments) have access to certain folders in the report
manager? Is this possible with a forms authentication solution?
ThanksSearch the below URL for 'forms authentication groups'. I found quite a few
results:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs
Adrian M.
MCP
"Will" <wlansing@.rlcarriers.com> wrote in message
news:1109799138.404265.258540@.z14g2000cwz.googlegroups.com...
> All,
> I have successfully implemented a forms authentication model for
> reporting services in our dev environment. I took the code directly
> from the MSDN whitepaper "Using Forms Authentication in Reporting
> Services", although I did modify the authentication portion to use an
> in house Oracle solution. How do I go about ensuring that only certain
> groups (departments) have access to certain folders in the report
> manager? Is this possible with a forms authentication solution?
> Thanks
>