Linked Servers fail with Integrated Authentication

[jwgoerlich@gmail.com]

Hello group,

I have two SQL database servers. The first is SQL 2000, default
instance, hosted on Windows 2000 Server. The second is SQL 2005, named
instance, hosted on Windows 2003 Enterprise Server.

The DBAs manage the SQL servers from their client computers. Clients
and servers are in one AD Domain. The DBAs use Windows integrated
authentication to connect.

On the SQL 2000 server, I have created a linked server entry to point
to the SQL 2005 server. I did this with the following commands:

EXEC sp_addlinkedserver @server='2005server\instance',
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='2005server\instance',
@catalog = 'master',
@provstr="Integrated Security=SSPI"
EXEC sp_addlinkedsrvlogin '2005server\instance', 'true'

This link works if a query is executed from the server console. When
Query Analyzer is run from the DBA's computers, however,
authentication fails. The same user account is used both on the server
console and on the client.

The error is:

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

What steps should I take to successfully link the two SQL servers and
use integrated authentication?

J Wolfgang Goerlich

 

[S. Pidgorny]

I would check the SPNs for SQL server service account.

Also: are you using the same account on the workstations that works on the
server console?

--
Svyatoslav Pidgorny, MS MVP - Security, MCSE
-= F1 is the key =-

* http://sl.mvps.org * http://msmvps.com/blogs/sp *


<jwgoerlich@gmail.com> wrote in message
news:1192221575.496011.24870@e34g2000pro.googlegroups.com...
> Hello group,
>
> I have two SQL database servers. The first is SQL 2000, default
> instance, hosted on Windows 2000 Server. The second is SQL 2005, named
> instance, hosted on Windows 2003 Enterprise Server.
>
> The DBAs manage the SQL servers from their client computers. Clients
> and servers are in one AD Domain. The DBAs use Windows integrated
> authentication to connect.
>
> On the SQL 2000 server, I have created a linked server entry to point
> to the SQL 2005 server. I did this with the following commands:
>
> EXEC sp_addlinkedserver @server='2005server\instance',
> @srvproduct='',
> @provider='SQLOLEDB',
> @datasrc='2005server\instance',
> @catalog = 'master',
> @provstr="Integrated Security=SSPI"
> EXEC sp_addlinkedsrvlogin '2005server\instance', 'true'
>
> This link works if a query is executed from the server console. When
> Query Analyzer is run from the DBA's computers, however,
> authentication fails. The same user account is used both on the server
> console and on the client.
>
> The error is:
>
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
>
> What steps should I take to successfully link the two SQL servers and
> use integrated authentication?
>
> J Wolfgang Goerlich
>

 

[jwgoerlich@gmail.com]

> I would check the SPNs for SQL server service account.

Good idea. Will do.


> Also: are you using the same account on the workstations that works on the
> server console?

Yes. Oddly enough, it sporadically works from the workstations. This
is maybe once in twenty times, however. I am leaning heavily towards
this being an authentication issue.

J Wolfgang Goerlich


> <jwgoerl...@gmail.com> wrote in message
>
> news:1192221575.496011.24870@e34g2000pro.googlegroups.com...
>
>
>
> > Hello group,
>
> > I have two SQL database servers. The first is SQL 2000, default
> > instance, hosted on Windows 2000 Server. The second is SQL 2005, named
> > instance, hosted on Windows 2003 Enterprise Server.
>
> > The DBAs manage the SQL servers from their client computers. Clients
> > and servers are in one AD Domain. The DBAs use Windows integrated
> > authentication to connect.
>
> > On the SQL 2000 server, I have created a linked server entry to point
> > to the SQL 2005 server. I did this with the following commands:
>
> > EXEC sp_addlinkedserver @server='2005server\instance',
> > @srvproduct='',
> > @provider='SQLOLEDB',
> > @datasrc='2005server\instance',
> > @catalog = 'master',
> > @provstr="Integrated Security=SSPI"
> > EXEC sp_addlinkedsrvlogin '2005server\instance', 'true'
>
> > This link works if a query is executed from the server console. When
> > Query Analyzer is run from the DBA's computers, however,
> > authentication fails. The same user account is used both on the server
> > console and on the client.
>
> > The error is:
>
> > Server: Msg 18456, Level 14, State 1, Line 1
> > Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
>
> > What steps should I take to successfully link the two SQL servers and
> > use integrated authentication?
>
> > J Wolfgang Goerlich- Hide quoted text -
>
> - Show quoted text -

 

[S. Pidgorny]

As for the authentication setup - use http://support.microsoft.com/kb/319723
as a checklist. There are many other articles - just search for "Kerberos
SQL".

Use Kerberos tray/klist to list Kerberos tickets on the client mahine make
sure the TGT is there. Look for anomalies.

Keep us posted about your investigation please.

--
Svyatoslav Pidgorny, MS MVP - Security, MCSE
-= F1 is the key =-

* http://sl.mvps.org * http://msmvps.com/blogs/sp *


<jwgoerlich@gmail.com> wrote in message
news:1192268871.101710.237270@v23g2000prn.googlegroups.com...
>> I would check the SPNs for SQL server service account.
>
> Good idea. Will do.
>
>
>> Also: are you using the same account on the workstations that works on
>> the
>> server console?
>
> Yes. Oddly enough, it sporadically works from the workstations. This
> is maybe once in twenty times, however. I am leaning heavily towards
> this being an authentication issue.
>
> J Wolfgang Goerlich
>
>
>> <jwgoerl...@gmail.com> wrote in message
>>
>> news:1192221575.496011.24870@e34g2000pro.googlegroups.com...
>>
>>
>>
>> > Hello group,
>>
>> > I have two SQL database servers. The first is SQL 2000, default
>> > instance, hosted on Windows 2000 Server. The second is SQL 2005, named
>> > instance, hosted on Windows 2003 Enterprise Server.
>>
>> > The DBAs manage the SQL servers from their client computers. Clients
>> > and servers are in one AD Domain. The DBAs use Windows integrated
>> > authentication to connect.
>>
>> > On the SQL 2000 server, I have created a linked server entry to point
>> > to the SQL 2005 server. I did this with the following commands:
>>
>> > EXEC sp_addlinkedserver @server='2005server\instance',
>> > @srvproduct='',
>> > @provider='SQLOLEDB',
>> > @datasrc='2005server\instance',
>> > @catalog = 'master',
>> > @provstr="Integrated Security=SSPI"
>> > EXEC sp_addlinkedsrvlogin '2005server\instance', 'true'
>>
>> > This link works if a query is executed from the server console. When
>> > Query Analyzer is run from the DBA's computers, however,
>> > authentication fails. The same user account is used both on the server
>> > console and on the client.
>>
>> > The error is:
>>
>> > Server: Msg 18456, Level 14, State 1, Line 1
>> > Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
>>
>> > What steps should I take to successfully link the two SQL servers and
>> > use integrated authentication?
>>
>> > J Wolfgang Goerlich- Hide quoted text -
>>
>> - Show quoted text -
>
>

 

[jwgoerlich@gmail.com]

Definitely a Kerberos ticket issue. I can see it fail and then switch
to anonymous login. I have the SPNs setup for the service accounts.
Still, there is no delegation tab under the service account properties
in Users and Computers. This is strange because the SPN is working (I
checked in ADSIEdit) and we are in native Windows 2003 AD mode. By
chance, do you know how I can enable this tab?

J Wolfgang Goerlich

On Oct 13, 7:18 pm, "S. Pidgorny <MVP>" <slavi...@yahoo.com> wrote:
> As for the authentication setup - usehttp://support.microsoft.com/kb/319723
> as a checklist. There are many other articles - just search for "Kerberos
> SQL".
>
> Use Kerberos tray/klist to list Kerberos tickets on the client mahine make
> sure the TGT is there. Look for anomalies.
>
> Keep us posted about your investigation please.
>
> --
> Svyatoslav Pidgorny, MS MVP - Security, MCSE
> -= F1 is the key =-
>
> *http://sl.mvps.org*http://msmvps.com/blogs/sp*
>
> <jwgoerl...@gmail.com> wrote in message
>
> news:1192268871.101710.237270@v23g2000prn.googlegroups.com...
>
>
>
> >> I would check the SPNs for SQL server service account.
>
> > Good idea. Will do.
>
> >> Also: are you using the same account on the workstations that works on
> >> the
> >> server console?
>
> > Yes. Oddly enough, it sporadically works from the workstations. This
> > is maybe once in twenty times, however. I am leaning heavily towards
> > this being an authentication issue.
>
> > J Wolfgang Goerlich
>
> >> <jwgoerl...@gmail.com> wrote in message
>
> >>news:1192221575.496011.24870@e34g2000pro.googlegroups.com...
>
> >> > Hello group,
>
> >> > I have two SQL database servers. The first is SQL 2000, default
> >> > instance, hosted on Windows 2000 Server. The second is SQL 2005, named
> >> > instance, hosted on Windows 2003 Enterprise Server.
>
> >> > The DBAs manage the SQL servers from their client computers. Clients
> >> > and servers are in one AD Domain. The DBAs use Windows integrated
> >> > authentication to connect.
>
> >> > On the SQL 2000 server, I have created a linked server entry to point
> >> > to the SQL 2005 server. I did this with the following commands:
>
> >> > EXEC sp_addlinkedserver @server='2005server\instance',
> >> > @srvproduct='',
> >> > @provider='SQLOLEDB',
> >> > @datasrc='2005server\instance',
> >> > @catalog = 'master',
> >> > @provstr="Integrated Security=SSPI"
> >> > EXEC sp_addlinkedsrvlogin '2005server\instance', 'true'
>
> >> > This link works if a query is executed from the server console. When
> >> > Query Analyzer is run from the DBA's computers, however,
> >> > authentication fails. The same user account is used both on the server
> >> > console and on the client.
>
> >> > The error is:
>
> >> > Server: Msg 18456, Level 14, State 1, Line 1
> >> > Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
>
> >> > What steps should I take to successfully link the two SQL servers and
> >> > use integrated authentication?
>
> >> > J Wolfgang Goerlich- Hide quoted text -
>
> >> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -