|
|
|
date: Tue, 1 Apr 2008 15:37:00 -0700,
group: microsoft.public.sqlserver.security
back
Linked Server Security Trouble
Hello,
I am having trouble configuring a linked server on a Windows domain that
does not have Active Directory. I would like to be able to use Windows
Authentication for this Linked Server. We are building an app that needs to
run from SERVER1 and acquire data from SERVER2 to solve some business
problems.
I have created a linked server on SERVER1 that is called SERVER2 that points
to SERVER2. If I test the linked server by running SSMS on SERVER1,
everything works well. Here is the code that I used to create the linked
server:
-----------------------------------------
declare @ServerName varchar(50)
Set @ServerName = 'SERVER2'
exec sp_dropserver @ServerName
exec sp_addlinkedserver @server=@ServerName, @srvproduct='',
@provider='SQLNCLI', @datasrc=@ServerName, @provstr=''
-- Set options
exec sp_serveroption @ServerName, 'data access', 'true'
exec sp_serveroption @ServerName, 'rpc', 'true'
exec sp_serveroption @ServerName, 'rpc out', 'true'
exec sp_serveroption @ServerName, 'use remote collation', 'true'
-- Create linked server login...doesn't seem to help
EXEC sp_addlinkedsrvlogin @ServerName, 'true'
-- Test connection
declare @SQL nvarchar(200)
set @sql = 'select top 1 * from ' + @ServerName + '.master.dbo.sysobjects'
exec sp_executesql @sql
-----------------------------------------
If I test this linked server from a client machine (my dev box), I get the
following error:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
My Windows account does have rights on both machines. I can open and use
SSMS from my dev machine and connect to both SERVER1 & SERVER2 using Windows
Authentication.
I have read about the Kerberos Delegation article and others that talk about
getting around the "double hop" issue be they all mention Active Directory
and I think that my organization doesn't have AD. We do have a Windows
Domain but not AD.
Thanks for any advice and/or insight,
Josh Blair (hfdev)
HydraForce, Inc.
date: Tue, 1 Apr 2008 15:37:00 -0700
author: hfdev
Re: Linked Server Security Trouble
> I have read about the Kerberos Delegation article and others that talk
> about
> getting around the "double hop" issue be they all mention Active Directory
> and I think that my organization doesn't have AD. We do have a Windows
> Domain but not AD.
Kerberos and AD is a requirement for delegation.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"hfdev" wrote in message
news:187EDDFA-CCFC-4A33-9DA1-0F48BD2AEB32@microsoft.com...
> Hello,
>
> I am having trouble configuring a linked server on a Windows domain that
> does not have Active Directory. I would like to be able to use Windows
> Authentication for this Linked Server. We are building an app that needs
> to
> run from SERVER1 and acquire data from SERVER2 to solve some business
> problems.
>
> I have created a linked server on SERVER1 that is called SERVER2 that
> points
> to SERVER2. If I test the linked server by running SSMS on SERVER1,
> everything works well. Here is the code that I used to create the linked
> server:
>
> -----------------------------------------
> declare @ServerName varchar(50)
>
> Set @ServerName = 'SERVER2'
>
> exec sp_dropserver @ServerName
>
> exec sp_addlinkedserver @server=@ServerName, @srvproduct='',
> @provider='SQLNCLI', @datasrc=@ServerName, @provstr=''
>
> -- Set options
> exec sp_serveroption @ServerName, 'data access', 'true'
> exec sp_serveroption @ServerName, 'rpc', 'true'
> exec sp_serveroption @ServerName, 'rpc out', 'true'
> exec sp_serveroption @ServerName, 'use remote collation', 'true'
>
> -- Create linked server login...doesn't seem to help
> EXEC sp_addlinkedsrvlogin @ServerName, 'true'
>
> -- Test connection
> declare @SQL nvarchar(200)
> set @sql = 'select top 1 * from ' + @ServerName + '.master.dbo.sysobjects'
> exec sp_executesql @sql
> -----------------------------------------
>
> If I test this linked server from a client machine (my dev box), I get the
> following error:
>
> Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
>
> My Windows account does have rights on both machines. I can open and use
> SSMS from my dev machine and connect to both SERVER1 & SERVER2 using
> Windows
> Authentication.
>
> I have read about the Kerberos Delegation article and others that talk
> about
> getting around the "double hop" issue be they all mention Active Directory
> and I think that my organization doesn't have AD. We do have a Windows
> Domain but not AD.
>
> Thanks for any advice and/or insight,
>
> Josh Blair (hfdev)
> HydraForce, Inc.
date: Wed, 2 Apr 2008 07:07:43 -0500
author: Dan Guzman
Re: Linked Server Security Trouble
Thanks for the reply and the info.
Can you offer any recommendations on other ways to configure a linked server
that allows a client app to pull data into SERVER1 from SERVER2?
Thanks,
Josh Blair
HydraForce, Inc.
"Dan Guzman" wrote:
> > I have read about the Kerberos Delegation article and others that talk
> > about
> > getting around the "double hop" issue be they all mention Active Directory
> > and I think that my organization doesn't have AD. We do have a Windows
> > Domain but not AD.
>
> Kerberos and AD is a requirement for delegation.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "hfdev" wrote in message
> news:187EDDFA-CCFC-4A33-9DA1-0F48BD2AEB32@microsoft.com...
> > Hello,
> >
> > I am having trouble configuring a linked server on a Windows domain that
> > does not have Active Directory. I would like to be able to use Windows
> > Authentication for this Linked Server. We are building an app that needs
> > to
> > run from SERVER1 and acquire data from SERVER2 to solve some business
> > problems.
> >
> > I have created a linked server on SERVER1 that is called SERVER2 that
> > points
> > to SERVER2. If I test the linked server by running SSMS on SERVER1,
> > everything works well. Here is the code that I used to create the linked
> > server:
> >
> > -----------------------------------------
> > declare @ServerName varchar(50)
> >
> > Set @ServerName = 'SERVER2'
> >
> > exec sp_dropserver @ServerName
> >
> > exec sp_addlinkedserver @server=@ServerName, @srvproduct='',
> > @provider='SQLNCLI', @datasrc=@ServerName, @provstr=''
> >
> > -- Set options
> > exec sp_serveroption @ServerName, 'data access', 'true'
> > exec sp_serveroption @ServerName, 'rpc', 'true'
> > exec sp_serveroption @ServerName, 'rpc out', 'true'
> > exec sp_serveroption @ServerName, 'use remote collation', 'true'
> >
> > -- Create linked server login...doesn't seem to help
> > EXEC sp_addlinkedsrvlogin @ServerName, 'true'
> >
> > -- Test connection
> > declare @SQL nvarchar(200)
> > set @sql = 'select top 1 * from ' + @ServerName + '.master.dbo.sysobjects'
> > exec sp_executesql @sql
> > -----------------------------------------
> >
> > If I test this linked server from a client machine (my dev box), I get the
> > following error:
> >
> > Msg 18456, Level 14, State 1, Line 1
> > Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> >
> > My Windows account does have rights on both machines. I can open and use
> > SSMS from my dev machine and connect to both SERVER1 & SERVER2 using
> > Windows
> > Authentication.
> >
> > I have read about the Kerberos Delegation article and others that talk
> > about
> > getting around the "double hop" issue be they all mention Active Directory
> > and I think that my organization doesn't have AD. We do have a Windows
> > Domain but not AD.
> >
> > Thanks for any advice and/or insight,
> >
> > Josh Blair (hfdev)
> > HydraForce, Inc.
>
date: Thu, 3 Apr 2008 07:55:03 -0700
author: hfdev
Re: Linked Server Security Trouble
> Can you offer any recommendations on other ways to configure a linked
> server
> that allows a client app to pull data into SERVER1 from SERVER2?
Without delegation, you need to use standard security to connect to the
remote server. Assuming you don't want all local users to have access to
the remote server, you can create a mapping between local logins and a
remote SQL logins. You can do this from SSMS under the linked server
properties. You might choose to map all the local logins to a single remote
login or map one-to-one, depending on your security requirements,
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"hfdev" wrote in message
news:5DB745E6-193C-4B13-BE3C-19058D6BFC84@microsoft.com...
> Thanks for the reply and the info.
>
> Can you offer any recommendations on other ways to configure a linked
> server
> that allows a client app to pull data into SERVER1 from SERVER2?
>
> Thanks,
>
> Josh Blair
> HydraForce, Inc.
>
> "Dan Guzman" wrote:
>
>> > I have read about the Kerberos Delegation article and others that talk
>> > about
>> > getting around the "double hop" issue be they all mention Active
>> > Directory
>> > and I think that my organization doesn't have AD. We do have a Windows
>> > Domain but not AD.
>>
>> Kerberos and AD is a requirement for delegation.
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>>
>> "hfdev" wrote in message
>> news:187EDDFA-CCFC-4A33-9DA1-0F48BD2AEB32@microsoft.com...
>> > Hello,
>> >
>> > I am having trouble configuring a linked server on a Windows domain
>> > that
>> > does not have Active Directory. I would like to be able to use Windows
>> > Authentication for this Linked Server. We are building an app that
>> > needs
>> > to
>> > run from SERVER1 and acquire data from SERVER2 to solve some business
>> > problems.
>> >
>> > I have created a linked server on SERVER1 that is called SERVER2 that
>> > points
>> > to SERVER2. If I test the linked server by running SSMS on SERVER1,
>> > everything works well. Here is the code that I used to create the
>> > linked
>> > server:
>> >
>> > -----------------------------------------
>> > declare @ServerName varchar(50)
>> >
>> > Set @ServerName = 'SERVER2'
>> >
>> > exec sp_dropserver @ServerName
>> >
>> > exec sp_addlinkedserver @server=@ServerName, @srvproduct='',
>> > @provider='SQLNCLI', @datasrc=@ServerName, @provstr=''
>> >
>> > -- Set options
>> > exec sp_serveroption @ServerName, 'data access', 'true'
>> > exec sp_serveroption @ServerName, 'rpc', 'true'
>> > exec sp_serveroption @ServerName, 'rpc out', 'true'
>> > exec sp_serveroption @ServerName, 'use remote collation', 'true'
>> >
>> > -- Create linked server login...doesn't seem to help
>> > EXEC sp_addlinkedsrvlogin @ServerName, 'true'
>> >
>> > -- Test connection
>> > declare @SQL nvarchar(200)
>> > set @sql = 'select top 1 * from ' + @ServerName +
>> > '.master.dbo.sysobjects'
>> > exec sp_executesql @sql
>> > -----------------------------------------
>> >
>> > If I test this linked server from a client machine (my dev box), I get
>> > the
>> > following error:
>> >
>> > Msg 18456, Level 14, State 1, Line 1
>> > Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
>> >
>> > My Windows account does have rights on both machines. I can open and
>> > use
>> > SSMS from my dev machine and connect to both SERVER1 & SERVER2 using
>> > Windows
>> > Authentication.
>> >
>> > I have read about the Kerberos Delegation article and others that talk
>> > about
>> > getting around the "double hop" issue be they all mention Active
>> > Directory
>> > and I think that my organization doesn't have AD. We do have a Windows
>> > Domain but not AD.
>> >
>> > Thanks for any advice and/or insight,
>> >
>> > Josh Blair (hfdev)
>> > HydraForce, Inc.
>>
date: Fri, 4 Apr 2008 06:49:20 -0500
author: Dan Guzman
|
|
