| Groups | Blog | Home |
sql server (microsoft) : Linked Servers fail with Integrated Authentication
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
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 * [quoted text, click to view] <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 >
[quoted text, click to view]
> I would check the SPNs for SQL server service account. Good idea. Will do. [quoted text, click to view] > 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 [quoted text, click to view] > <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 -
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 * [quoted text, click to view] <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 - > >
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 [quoted text, click to view] 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 -
Don't see what you're looking for? Try a search.
|
March 2003
April 2003
May 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |