SQL Management Studio Access [Linked Server and Kerberos Delegation]

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.
ActiveInfoSysAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rboyd56Commented:
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
ActiveInfoSysAuthor Commented:
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
ActiveInfoSysAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ActiveInfoSysAuthor Commented:
BIG QUESTION:

Does the secondary file server need any SPN assigned:


EFXIXRMOR01
0
ActiveInfoSysAuthor Commented:
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
rboyd56Commented:
No it does not, unless there is a SQL Server installed on it that you are accessing in this process.
0
ActiveInfoSysAuthor Commented:
No SQL Server installed on secondary FILE server.
0
rboyd56Commented:
Ar the machine accounts trusted for delegation in Active Directory. How about the SQL SErver service accounts?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ActiveInfoSysAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.