question

SamGarth avatar image
0 Votes"
SamGarth asked kvinpar-2579 answered

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON', SPN working

Hello, I have set up two fresh SQL Servers and have set up SPN, both servers report the following...

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SERVERNAME ] for the SQL Server service.

I have set up a linked server between the pair of them and then from my desktop SSMS tried to test the connection and get the error.

Has anyone had this before?

Thanks

Sam

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
1 Vote"
AmeliaGu-msft answered Shashank-Singh commented

Hi @SamGarth,
Is there any update on this case?
Please make sure SQL Server service account was trusted for delegation in AD.
You can go to domain controller -> open active directory users and computers -> users -> right-click the SQL Server Service account in users folder -> Properties.
Then go to delegation tab in the Properties dialog box, ensure that "Trust this user for delegation to any service (Kerberos only)" or "Trust this user for delegation to specified services (Kerberos only) – Use Kerberos only "is selected. If you choose the " Trust this user for delegation to specified services (Kerberos only)", please add the SQL Server service. ( please do the same for the delegation tab in the Properties of server's computer object in active directory users and computers.)

57453-01.jpg
Then go to the account tab in properties and ensure that the "account is sensitive and cannot be delegated" option is not selected.

57487-02.jpg


Please refer to this article which might help.

Best Regards,
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



01.jpg (57.7 KiB)
02.jpg (49.6 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Excellent, thank you so much!

I have never created the accounts before so didn't know this part and I have been googling for days!

0 Votes 0 ·

@samGarth this was also highlighted in my reply although Amelia gave more detailed screenshots

0 Votes 0 ·
kvinpar-2579 avatar image
0 Votes"
kvinpar-2579 answered

these many kinphe out thew realm osed the nursing as continuing for the greath apocalypsme but thew realm as continuing by thew delphe and the disturb as conti9nuing by fhalth and norespounw sounce of the thrance of the sound of thew sining and authority and NAuthority as continuing by deeb web

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KvinPar-8926 avatar image
0 Votes"
KvinPar-8926 answered

hi i wana big name in imposible am Anonymous logon prevwies my accoupt and release the rest

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DirkHondong avatar image
0 Votes"
DirkHondong answered SamGarth commented

Hi there,

what you describe is a classic double hop scenario.
local SSMS->Server A -lkd srv conn -> Server B

Just having an SPN for both servers is not enough.
The account running the sql server engine needs the information where to delegate to.

So, if you have a SQL Server A which runs under yourdomain\mysvcAccforServerA, than this account needs the right to delegate credentials to another SPN, eg MSSQLSvc/MyServerb.yourdomain

Regards
Dirk

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the response, it was delegation through AD. I hadn't seen this part before.

1 Vote 1 ·
YonggangHuang-1791 avatar image
0 Votes"
YonggangHuang-1791 answered SamGarth commented

You need to make sure Kerberos works on both servers. If you can RDP into the server that set up the linked server and connect to the linked server fine, verify it is using Kerberos, you can check the auth_scheme from the sys.dm_exec_connections on the target server to verify it. For example,

select c.auth_scheme,* from sys.dm_exec_sessions s join sys.dm_exec_connections c on s.session_id=c.session_id where s.host_name ='yourhostname'.

Make sure it is Kerberos used by the connection.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes all connections are kerberos

0 Votes 0 ·
Shashank-Singh avatar image
1 Vote"
Shashank-Singh answered SamGarth commented
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks I have gone through that but still no joy.

0 Votes 0 ·

Have you followed all the things that is mentioned in the Link, if so, would you show me your Linked server configuration ?

0 Votes 0 ·

It was delegation through AD. Thank you for your help it did help rule things out!

0 Votes 0 ·