Solved

SQL Management Studio Access [Linked Server and Kerberos Delegation]

Posted on 2007-03-19
9
679 Views
Last Modified: 2010-03-19
SQL Management Studio Access [Linked Server and Kerberos Delegation]

Please help.
When accessing SQL Server via RDP all is OK and runs just fine.

However, when accessing SVRSQL01 inside SQL Management Studio on client PC the following error occurs.
"Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"

Linked Server code I am running.
   SELECT CarCountSetId FROM EIXIXRMDB01.Forecasting.APP.tblCarCountSet

Windows authentication is used to connect to server?
The userid (The SQL Service Account) has access to all shares?
Security Principle Names (SPNs) have already been trusted for delegation using the following commands:

C:\Program Files\Support Tools>setspn -A MSSQLSvc/EFXIXRMDB02:1433 ix\svc-sql-fx-08
Registering ServicePrincipalNames for CN=SVC-SQL-FX-08,OU=Service Accounts,OU=Resources,DC=ix,DC=avisrac,DC=net
        MSSQLSvc/EFXIXRMDB02:1433
Updated object
C:\Program Files\Support Tools>setspn -A MSSQLSvc/EFXIXRMDB02.IX.AVISRAC.NET:1433 ix\svc-sql-fx-08
Registering ServicePrincipalNames for CN=SVC-SQL-FX-08,OU=Service Accounts,OU=Resources,DC=ix,DC=avisrac,DC=net
        MSSQLSvc/EFXIXRMDB02.IX.AVISRAC.NET:1433
Updated object

So I'm not so sure double-hop authentication/impersonation is at play here, but the LINKED SERVER issues are preventing the query from running and returning the Login failed error message.
0
Comment
Question by:ActiveInfoSys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 16

Expert Comment

by:rboyd56
ID: 18748727
When you are using delegation the SQL Server machines themself have to be trusted for delegation as well as the service account, at least the SQL Server that has the linked server defined.

Client ---> SQL Server 1 ---> SQL Server 2

SQL Server 1's machine has to be trusted for delegation as well as it's service account. It also needs an SPN.
SQL Server 2's service account must have an SPN assigned and the account must be trusted for delegation

The SPN has to be assigned to the fully qualified domain name. Not hte netbios name. So this one is incorrect and should be deleted:

setspn -A MSSQLSvc/EFXIXRMDB02:1433 ix\svc-sql-fx-08

If the client finds this one and tries to use it the connection will fail. So that may be the problem you are having.
0
 

Author Comment

by:ActiveInfoSys
ID: 18755094
OK.  Thanks RBOYD56....No NetBIOS Names...see results for all Service Accounts involved.

C:\Program Files\Support Tools>setspn -L SVC-SQL
Registered ServicePrincipalNames for CN=SVC-SQL,OU=Service Accounts,OU=Resources,DC=ix,DC=
avisrac,DC=net:
    MSSQLSvc/EIXIXRDDB01.IX.AVISRAC.NET:1433

C:\Program Files\Support Tools>setspn -L SVC-SQL-FX-03
Registered ServicePrincipalNames for CN=SVC-SQL-FX-03,OU=Service Accounts,OU=Resources,DC=
ix,DC=avisrac,DC=net:
    MSSQLSvc/EFXIXSQL03.IX.AVISRAC.NET:1433

C:\Program Files\Support Tools>setspn -L SVC-SQL-FX-08
Registered ServicePrincipalNames for CN=SVC-SQL-FX-08,OU=Service Accounts,OU=Resources,DC=
ix,DC=avisrac,DC=net:
    MSSQLSvc/EFXIXRMDB02.IX.AVISRAC.NET:1433

C:\Program Files\Support Tools>setspn -L SVC-SQL-FX-09
Registered ServicePrincipalNames for CN=SVC-SQL-FX-09,OU=Service Accounts,OU=Resources,DC=
ix,DC=avisrac,DC=net:
    MSSQLSvc/EFXIXRMDB02.IX.AVISRAC.NET:1435
0
 

Author Comment

by:ActiveInfoSys
ID: 18755098
And the results for all SERVERS involved:

C:\Program Files\Support Tools>setspn -L EIXIXRDDB01
Registered ServicePrincipalNames for CN=EIXIXRDDB01,OU=Database,OU=Heathrow,OU=Servers,DC=
ix,DC=avisrac,DC=net:
    HOST/EIXIXRDDB01
    HOST/eixixrddb01.ix.avisrac.net


C:\Program Files\Support Tools>setspn -L EFXIXRMDB02
Registered ServicePrincipalNames for CN=EFXIXRMDB02,OU=Database,OU=Frankfurt,OU=Servers,DC
=ix,DC=avisrac,DC=net:
    HOST/EFXIXRMDB02
    HOST/EFXIXRMDB02.ix.avisrac.net

C:\Program Files\Support Tools>setspn -L EFXIXSQL03
Registered ServicePrincipalNames for CN=EFXIXSQL03,OU=Database,OU=Frankfurt,OU=Servers,DC=
ix,DC=avisrac,DC=net:
    HOST/EFXIXSQL03
    HOST/EFXIXSQL03.ix.avisrac.net

0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:ActiveInfoSys
ID: 18755103
BIG QUESTION:

Does the secondary file server need any SPN assigned:


EFXIXRMOR01
0
 

Author Comment

by:ActiveInfoSys
ID: 18755107
Such that:
C:\Program Files\Support Tools>setspn -L EFXIXRMOR01
Registered ServicePrincipalNames for CN=EFXIXRMOR01,OU=Application,OU=Frankfurt,OU=
,DC=ix,DC=avisrac,DC=net:
    HOST/EFXIXRMOR01
    HOST/EFXIXRMOR01.ix.avisrac.net
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18755454
No it does not, unless there is a SQL Server installed on it that you are accessing in this process.
0
 

Author Comment

by:ActiveInfoSys
ID: 18755584
No SQL Server installed on secondary FILE server.
0
 
LVL 16

Accepted Solution

by:
rboyd56 earned 500 total points
ID: 18755861
Ar the machine accounts trusted for delegation in Active Directory. How about the SQL SErver service accounts?
0
 

Author Comment

by:ActiveInfoSys
ID: 18762378
I can confirm for all computer accounts the following is set:

Computer Properties

Delegation ¦ "Trust this computer for delegation to specified services only ((*Use Kerberos Only))

[ Services to which this account can present delegated credentials    ]
MSSQLSvc -->  EFXIXSQL03.IX.AVISRAC.Net --> 1433                    

Yes. Machine Accounts are delegated in AD.

As above, SETSPN - L show all accounts delgated properly.

Any final ideas?  I am really stuck.

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL- GROUP BY 4 25
UPDATE JOIN multiple tables 5 23
Need quicker response from an Execption table 11 24
SQL Query Across Multiple Tables - Help 5 23
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
When you upgrade from Windows 8 to 8.1 or to Windows 10 or if you are like me you are on the Insider Program you may find yourself with many 450MB recovery partitions.  With a traditional disk that may not be a problem but with relatively smaller SS…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question