ActiveInfoSys
asked on
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.AP P.tblCarCo untSet
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=Servic e Accounts,OU=Resources,DC=i x,DC=avisr ac,DC=net
MSSQLSvc/EFXIXRMDB02:1433
Updated object
C:\Program Files\Support Tools>setspn -A MSSQLSvc/EFXIXRMDB02.IX.AV ISRAC.NET: 1433 ix\svc-sql-fx-08
Registering ServicePrincipalNames for CN=SVC-SQL-FX-08,OU=Servic e Accounts,OU=Resources,DC=i x,DC=avisr ac,DC=net
MSSQLSvc/EFXIXRMDB02.IX.AV ISRAC.NET: 1433
Updated object
So I'm not so sure double-hop authentication/impersonati on is at play here, but the LINKED SERVER issues are preventing the query from running and returning the Login failed error message.
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.AP
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=Servic
MSSQLSvc/EFXIXRMDB02:1433
Updated object
C:\Program Files\Support Tools>setspn -A MSSQLSvc/EFXIXRMDB02.IX.AV
Registering ServicePrincipalNames for CN=SVC-SQL-FX-08,OU=Servic
MSSQLSvc/EFXIXRMDB02.IX.AV
Updated object
So I'm not so sure double-hop authentication/impersonati
ASKER
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=i x,DC=
avisrac,DC=net:
MSSQLSvc/EIXIXRDDB01.IX.AV ISRAC.NET: 1433
C:\Program Files\Support Tools>setspn -L SVC-SQL-FX-03
Registered ServicePrincipalNames for CN=SVC-SQL-FX-03,OU=Servic e Accounts,OU=Resources,DC=
ix,DC=avisrac,DC=net:
MSSQLSvc/EFXIXSQL03.IX.AVI SRAC.NET:1 433
C:\Program Files\Support Tools>setspn -L SVC-SQL-FX-08
Registered ServicePrincipalNames for CN=SVC-SQL-FX-08,OU=Servic e Accounts,OU=Resources,DC=
ix,DC=avisrac,DC=net:
MSSQLSvc/EFXIXRMDB02.IX.AV ISRAC.NET: 1433
C:\Program Files\Support Tools>setspn -L SVC-SQL-FX-09
Registered ServicePrincipalNames for CN=SVC-SQL-FX-09,OU=Servic e Accounts,OU=Resources,DC=
ix,DC=avisrac,DC=net:
MSSQLSvc/EFXIXRMDB02.IX.AV ISRAC.NET: 1435
C:\Program Files\Support Tools>setspn -L SVC-SQL
Registered ServicePrincipalNames for CN=SVC-SQL,OU=Service Accounts,OU=Resources,DC=i
avisrac,DC=net:
MSSQLSvc/EIXIXRDDB01.IX.AV
C:\Program Files\Support Tools>setspn -L SVC-SQL-FX-03
Registered ServicePrincipalNames for CN=SVC-SQL-FX-03,OU=Servic
ix,DC=avisrac,DC=net:
MSSQLSvc/EFXIXSQL03.IX.AVI
C:\Program Files\Support Tools>setspn -L SVC-SQL-FX-08
Registered ServicePrincipalNames for CN=SVC-SQL-FX-08,OU=Servic
ix,DC=avisrac,DC=net:
MSSQLSvc/EFXIXRMDB02.IX.AV
C:\Program Files\Support Tools>setspn -L SVC-SQL-FX-09
Registered ServicePrincipalNames for CN=SVC-SQL-FX-09,OU=Servic
ix,DC=avisrac,DC=net:
MSSQLSvc/EFXIXRMDB02.IX.AV
ASKER
And the results for all SERVERS involved:
C:\Program Files\Support Tools>setspn -L EIXIXRDDB01
Registered ServicePrincipalNames for CN=EIXIXRDDB01,OU=Database ,OU=Heathr ow,OU=Serv ers,DC=
ix,DC=avisrac,DC=net:
HOST/EIXIXRDDB01
HOST/eixixrddb01.ix.avisra c.net
C:\Program Files\Support Tools>setspn -L EFXIXRMDB02
Registered ServicePrincipalNames for CN=EFXIXRMDB02,OU=Database ,OU=Frankf urt,OU=Ser vers,DC
=ix,DC=avisrac,DC=net:
HOST/EFXIXRMDB02
HOST/EFXIXRMDB02.ix.avisra c.net
C:\Program Files\Support Tools>setspn -L EFXIXSQL03
Registered ServicePrincipalNames for CN=EFXIXSQL03,OU=Database, OU=Frankfu rt,OU=Serv ers,DC=
ix,DC=avisrac,DC=net:
HOST/EFXIXSQL03
HOST/EFXIXSQL03.ix.avisrac .net
C:\Program Files\Support Tools>setspn -L EIXIXRDDB01
Registered ServicePrincipalNames for CN=EIXIXRDDB01,OU=Database
ix,DC=avisrac,DC=net:
HOST/EIXIXRDDB01
HOST/eixixrddb01.ix.avisra
C:\Program Files\Support Tools>setspn -L EFXIXRMDB02
Registered ServicePrincipalNames for CN=EFXIXRMDB02,OU=Database
=ix,DC=avisrac,DC=net:
HOST/EFXIXRMDB02
HOST/EFXIXRMDB02.ix.avisra
C:\Program Files\Support Tools>setspn -L EFXIXSQL03
Registered ServicePrincipalNames for CN=EFXIXSQL03,OU=Database,
ix,DC=avisrac,DC=net:
HOST/EFXIXSQL03
HOST/EFXIXSQL03.ix.avisrac
ASKER
BIG QUESTION:
Does the secondary file server need any SPN assigned:
EFXIXRMOR01
Does the secondary file server need any SPN assigned:
EFXIXRMOR01
ASKER
Such that:
C:\Program Files\Support Tools>setspn -L EFXIXRMOR01
Registered ServicePrincipalNames for CN=EFXIXRMOR01,OU=Applicat ion,OU=Fra nkfurt,OU=
,DC=ix,DC=avisrac,DC=net:
HOST/EFXIXRMOR01
HOST/EFXIXRMOR01.ix.avisra c.net
C:\Program Files\Support Tools>setspn -L EFXIXRMOR01
Registered ServicePrincipalNames for CN=EFXIXRMOR01,OU=Applicat
,DC=ix,DC=avisrac,DC=net:
HOST/EFXIXRMOR01
HOST/EFXIXRMOR01.ix.avisra
No it does not, unless there is a SQL Server installed on it that you are accessing in this process.
ASKER
No SQL Server installed on secondary FILE server.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.