Ошибка login failed for user nt authority anonymous logon

An application that has been working without problem (and has not had any active development done on it in about 6 months or so) recently began failing to connect to database. Operations admins cant say what might have changed that would cause the problem.

The client application uses a hardcoded connection string with Integrated Security=True, but when the applications attempts to create a connection to the database, it throws an SQLException saying «Login failed for user ‘NT AUTHORITYANONYMOUS LOGON».

I can log on to the database through Management Studio on this account without problem. All of the things that I have seen for this issue are for ASP.NET projects and it is apparently the «Double Hop Problem» which being a client application darned well better not be a problem. Any help would be greatly appreciated.

Edit

The client machine and server machine as well as user accounts are on the same domain.
This occurs when Windows Firewall is off.

Leading theory is:
Server was restarted about a week or so ago, and failed to register Service Principal Name (SPN). Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos.

asked Sep 17, 2012 at 15:41

CodeWarrior's user avatar

CodeWarriorCodeWarrior

7,3687 gold badges51 silver badges77 bronze badges

0

If your issue is with linked servers, you need to look at a few things.

First, your users need to have delegation enabled and if the only thing that’s changed, it’l likely they do. Otherwise you can uncheck the «Account is sensitive and cannot be delegated» checkbox is the user properties in AD.

Second, your service account(s) must be trusted for delegation. Since you recently changed your service account I suspect this is the culprit. (http://technet.microsoft.com/en-us/library/cc739474(v=ws.10).aspx)

You mentioned that you might have some SPN issues, so be sure to set the SPN for both endpoints, otherwise you will not be able to see the delegation tab in AD. Also make sure you’re in advanced view in «Active Directory Users and Computers.»

If you still do not see the delegation tab, even after correcting your SPN, make sure your domain not in 2000 mode. If it is, you can «raise domain function level.»

At this point, you can now mark the account as trusted for delegation:

In the details pane, right-click the user you want to be trusted for
delegation, and click Properties.

Click the Delegation tab, select the Account is trusted for delegation
check box, and then click OK.

Finally you will also need to set all the machines as trusted for delegation.

Once you’ve done this, reconnect to your sql server and test your liked servers. They should work.

Jim G.'s user avatar

Jim G.

15.1k22 gold badges103 silver badges165 bronze badges

answered Sep 18, 2012 at 3:00

Code Magician's user avatar

Code MagicianCode Magician

23.1k7 gold badges59 silver badges77 bronze badges

2

First off: My problem isn’t the exact same as yours, but this post is the first thing that comes up in google for the Login failed for user 'NT AUTHORITYANONYMOUS LOGON' error at the time I wrote this. The solution may be useful to people searching for this error as I did not find this specific solution anywhere online.

In my case, I used Xampp/Apache and PHP sqlsrv to try to connect to an MSSQL database using Windows Authentication and received the Login failed for user 'NT AUTHORITYANONYMOUS LOGON' error you described. I finally found the problem to be the Apache service itself running under the user «LOCAL SERVICE» instead of the user account I was logged in as. In other words, it literally was using an anonymous account. The solution was to go into services.msc, right click the Apache service, go to Properties, go to the Log On tab, and enter the credentials for the user. This falls in line with your problem related to SPN’s as your SPN’s are set up to run from a specific user on the domain. So if the correct SPN is not running, windows authentication will default to the wrong user (likely the «LOCAL SERVICE» user) and give you the Anonymous error.

Here’s where it’s different from your problem. None of the computers on the local network are on a Domain, they are only on a Workgroup. To use Windows Authentication with a Workgroup, both the computer with the server (in my case MSSQL Server) and the computer with the service requesting data (in my case Apache) needed to have a user with an identical name and identical password.

To summarize, The Login failed for user 'NT AUTHORITYANONYMOUS LOGON' error in both our cases seems to be caused by a service not running and/or not on the right user. Ensuring the right SPN or other Service is running and under the correct user should solve the anonymous part of the problem.

answered Jul 10, 2015 at 15:01

Caboosetp's user avatar

CaboosetpCaboosetp

1111 silver badge4 bronze badges

3

I think there must have been some change in AD group used to authenticate against the database. Add the web server name, in the format domainwebservername$, to the AD group that had access to the database. In addition, also try to set the web.config attribute to «false». Hope it helps.

EDIT: Going by what you have edited.. it most probably indicate that the authentication protocol of your SQL Server has fallen back from Kerberos(Default, if you were using Windows integrated authentication) to NTLM. For using Kerberos service principal name (SPN) must be registered in the Active Directory directory service. Service Principal Name(SPNs) are unique identifiers for services running on servers. Each service that will use Kerberos authentication needs to have an SPN set for it so that clients can identify the service on the network. It is registered in Active Directory under either a computer account or a user account. Although the Kerberos protocol is the default, if the default fails, authentication process will be tried using NTLM.

In your scenario, client must be making tcp connection, and it is most likely running under LocalSystem account, and there is no SPN registered for SQL instance, hence, NTLM is used, however, LocalSystem account inherits from System Context instead of a true user-based context, thus, failed as ‘ANONYMOUS LOGON’.

To resolve this ask your domain administrator to manually register SPN if your SQL Server running under a domain user account.
Following links might help you more:
http://blogs.msdn.com/b/sql_protocols/archive/2005/10/12/479871.aspx
http://support.microsoft.com/kb/909801

answered Sep 17, 2012 at 16:10

Saurabh R S's user avatar

Saurabh R SSaurabh R S

3,0171 gold badge33 silver badges44 bronze badges

1

You probably just need to provide a user name and password in your connectionstring and set Integrated Security=false

answered May 22, 2017 at 6:11

shabber's user avatar

shabbershabber

591 silver badge1 bronze badge

1

Try setting «Integrated Security=False» in the connection string.

<add name="YourContext" connectionString="Data Source=<IPAddressOfDBServer>;Initial Catalog=<DBName>;USER ID=<youruserid>;Password=<yourpassword>;Integrated Security=False;MultipleActiveResultSets=True" providerName="System.Data.SqlClient"/>

answered Dec 17, 2018 at 6:32

Ummer Irshad's user avatar

One of my SQL jobs had the same issue. It involved uploadaing data from one server to another. The error occurred because I was using sql Server Agent Service Account. I created a Credential using a UserId (that uses Window authentication) common to all servers. Then created a Proxy using this credential. Used the proxy in sql server job and it is running fine.

answered Aug 12, 2015 at 14:42

Vipul's user avatar

VipulVipul

211 bronze badge

FWIW, in our case a (PHP) website running on IIS was showing this message on attempting to connect to a database.

The resolution was to edit the Anonymous Authentication on that website to use the Application pool identity (and we set the application pool entry up to use a service account designed for that website).

answered Jun 7, 2019 at 2:19

youcantryreachingme's user avatar

1

A similar case solved:

In our case, we wanted to set up linked servers using cnames and with the logins current security context.

All in order we checked that the service account running SQL Server had its’ proper spns set and that the AD-object was trusted for delegation. But, while we were able to connect to the cname directly, we still had issues calling a linked server on its’ cname: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.

It took us far too long to realize that the cnames we used was for A-record, [A], that was set on a higher dns level, and not in its’ own domain AD-level. Originally, we had the cname directing to [A].example.com and not (where it should) to: [A].domain.ad.example.com

Ofcourse we had these errors about anonymous logon.

answered Feb 5, 2021 at 15:50

dba's user avatar

Just Go to app pool select Process model in Advance Setting then select Identity and in identity set your account details like username and password of your system.

answered Aug 31, 2022 at 4:44

Ajit Kumar Pandey's user avatar

Got it! Solved the issue modifying the user properties in security session of SQL Server. In SQL Server Management, go into security -> Logon -> Choose the user used for DB connection and go into his properties. Go to «Securators» tab and look for line «Connect SQL», mark «Grant» option and take a try. It works for me!

Regards

answered Sep 14, 2020 at 19:50

Luiz Gustavo David Ferreira's user avatar

Источник

RRS feed

  • Remove From My Forums

  • Вопрос

  • I have reporting services 2005,  created a  report, if i run from server through IE, it is working,   i am getting this error msg

    when i try to run from my local m/c

    • An error has occurred during report processing.
      • Cannot create a connection to data source ‘SQL1DEV’.
        • Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.

Ответы

    • Помечено в качестве ответа
      Garsy Liang — MSFT
      7 января 2009 г. 11:57

Все ответы

  • Symptom:
    “Login failed for user ‘NT AuthorityANONYMOUS LOGON’”

    Resolution:
    1. In SQL Server Management Studio go to Security. Expand Logins.
    2. Right click NT AuthorityANONYMOUS LOGON.
    3. Change the default database to the database that you are trying to access.

    4. In the left pane, click server roles. Check the sys admin server role.
    5. Click OK to save the changes.

    ——————————————————-

  • thanks for reply,  i don’t have NT AuthorityANONYMOUS LOGON  account on my database server, from where i am pulling data for report.

  • Then you need to create it and then follow the steps above. Also add the user to report manager and assign it rights.

  • if i connect database on the same server where reporting services installed, i am able to see report from my local m/c, problem is if i connect to  databse different server, i am getting this error.

    is there any security setting,  if  i want to connect to different server.

  • Yes, you have to set up the security as I’ve described above.

    • Помечено в качестве ответа
      Garsy Liang — MSFT
      7 января 2009 г. 11:57

  • hello….
    I am also facing the same problem as described above,
    as per your suggestion, i have create a role named «NT AuthorityANONYMOUS LOGON»
    but when i right clicked on that role , i can’t see any option to
    Change the default database to the database that you are trying to access.

    Colud you please give some light on this…
    thanks in advance…………..

  • Hi
    I have exactly the same issue

    this is not a role that is mentionned but a login.
    You have this default database option when creating a new login
    I tryed to create it but seems not possible due to not allowed characters

    if you found any solution please share it.
    thanks

  • This is usually a problem with SPN’s not set up in the right way in your environment. This issue can be worked around by using stored crediential for Data source. The below article should help you with the same.

    http://msdn.microsoft.com/en-us/library/ms156278(SQL.90).aspx

    Feroz

    Mark as Answer if it helps. This posting is provided «AS IS» with no warranties and confers no rights.

    • Предложено в качестве ответа
      Xiaobo Yang
      26 марта 2019 г. 6:39

  • Thanks for your link but this link is for SQL server 2005 and I have no issue with 2005 but with 2008

    On this link you will se that the Home folder has been removed from management studio:
    http://msdn.microsoft.com/en-us/library/ms143380.aspx
    In Management Studio, the Home folder is removed in this release. You cannot view, manage, distribute or secure report server content in Management Studio.

    shared data source can be managed in the Report Manager : http://<servername>/Reports and Credential can be store their, but my test didn’t succeed
    for embedded connection you can also store credential but once again it not works

    It makes 3 days that I try many many thinks without any succes

    The only thinks that is working is that added user to the Administrators Group on the server where the report server is installed
    and this is not a solution for me.

    to summurize
    I have a SQL server 2005 (srv1) where all my database are stored
    I have installed SQL server 2008 on another server (srv2) with report service
    So I try to create and publish report on the srv2 using data from my srv1 using simple connection string :
    Data Source=srv1;Initial Catalog=»databasename»

    If you have any other solution…

  • I tried again today without any success.
    Embedded or not connection failed when trying to display the report
    I really don’t knwo what to do to make this report working
    it seems very simple on database on one server and report server on another but this windows credential is a big trick.

    Any help is welcomed

  • Can you please provide me the error that you receive when you use stored credientials and render the report?

    Thanks
    Feroz

    Mark as Answer if it helps. This posting is provided «AS IS» with no warranties and confers no rights.

  • Hi,
    below the Error that I get I try to run the report from report builder:
    This report cannot be run in Report Builder because it contains one or more embedded datasources with credential options that are not supported.
    Instead of Embedded data sources use Shared data sources or save and view the report on the Server

    Today I have created a report that use a database that is on the same server where Report Service 2008 is installed
    but I also get the Above errir
    Here are more details:

    the server is called VAL-T-SQL
    Both the database and report service are installed on it
    I’m Admin the computer and sysadmin SQL User

    I report Builder 2.0
    My Data source use the following Connection string:
    Data Source=VAL-T-SQL;Initial Catalog=»<MydatabaseName>»
    I alos try the following without any success
    Data Source=VAL-T-SQL;Initial Catalog=»<MydatabaseName>»;Integraterd security = SSPI
    I checked the Use a connection embedded in my report

    the credential are:
    Use current Windows user. Kerberos delegation may be required

    I have save as my Report on the Server :
    http://val-t-sql/ReportServer

    I can see the report without any error but it’s not normal that I get this error inside Report Builder
    And if other users should have access to this report I have to add them to Admin user Group
    Othert I get this error:
    The permissions granted to user ‘DomainUserName’ are insufficient for performing this operation. (rsAccessDenied)
    The user have all the Server Role of the SQL Server included sysadmin
    and he has also permission on the view that is called in the dataset.

    Do you have any idea why it’s not working ? 

  • Hi, this solutions works .. but is it good idea to give this login access in prodcution ? Is this accessing through windows AD group access???
    I need this to be added to the sQL Server .. . but bit worried that whether its safe
    thanks

    Bis

    Bis

  • From my experience, this looks exactly like a Kerberos authentication issue.

    http://blogs.technet.com/b/askds/archive/2008/06/13/understanding-kerberos-double-hop.aspx

    This link gives a good understanding of what is going on, but essentially when you get an error like the one above, it is most frequently caused by having Windows Authenticaiton turned on for the data source, and the server that houses the data is
    not on the report server.

    This causes the report server to send a request and pass credentials through to the ReportServer database on the remote server from the client.

    I would first enable the option Trusted for delegation in AD for the machine running Report Server.

    Let me know if this works for you!

    Petri

    • Предложено в качестве ответа
      Quesi Jay
      4 марта 2011 г. 23:22

  • WTF your proposing to make any anonymous report running on the server a DBO.

    I know this is a really old thread, but google brought it to me as a top 2 result.  

    If any system that this links to, links from, contains any confidential data or is running as anything other than a local account … don’t do it.

    Symptom:
    “Login failed for user ‘NT AuthorityANONYMOUS LOGON’”

    Resolution:
    1. In SQL Server Management Studio go to Security. Expand Logins.
    2. Right click NT AuthorityANONYMOUS LOGON.
    3. Change the default database to the database that you are trying to access.

    4. In the left pane, click server roles. Check the sys admin server role.
    5. Click OK to save the changes.

    ——————————————————-

     

  • Symptom:
    “Login failed for user ‘NT AuthorityANONYMOUS LOGON’”

    Resolution:
    1. In SQL Server Management Studio go to Security. Expand Logins.
    2. Right click NT AuthorityANONYMOUS LOGON.
    3. Change the default database to the database that you are trying to access.

    4. In the left pane, click server roles. Check the sys admin server role.
    5. Click OK to save the changes.

    ——————————————————-

    I know this is an old thread but just wanted to comment on this since this terrible advice is out here and don’t want others following suit. Do NOT give ANONYMOUS LOGON sysadmin rights, this opens your server wide up and will likely piss off your DBA/security
    teams. In fact, «just give it sysadmin rights» should never be your go to solution. Figure out what’s REALLY wrong and work it from there.

    Jorge Segarra
    SQLChicken.com ||
    Follow me on Twitter! || SQL University
    Please click the Mark as Answer button if a post solves your problem!

  • Symptom:
    “Login failed for user ‘NT AuthorityANONYMOUS LOGON’”

    Resolution:
    1. In SQL Server Management Studio go to Security. Expand Logins.
    2. Right click NT AuthorityANONYMOUS LOGON.
    3. Change the default database to the database that you are trying to access.

    4. In the left pane, click server roles. Check the sys admin server role.
    5. Click OK to save the changes.

    ——————————————————-

    This is horribly insecure. I hope OP didn’t actually implement this solution.

  • 4. In the left pane, click server roles. Check the sys admin server role.

    I suggest db_datareader instead of sysadmin. Using sysadmin can create a security risk.

Источник
  • Remove From My Forums

  • Question

  • Hi,

    We are building a web part to fetch data from SQL Server. The Sharepoint application and SQL database are on two different servers. But when i am trying to access the database from the web part it gives the following error

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’

     Sharepoint site details:

    Windows authentication enabled

    IIS->Application Pool Identity,IIS->website->Directory security,crediantial used to access the database ,are same
     (administrator Crediantials)

    Any help regarding this issue will be greatly appreciated.

    Thanks

    ragava_28

Answers

  • Hi,

    It sounds like you’ve encountered what is known as the «double hop» problem.
    This is a problem that occurs if you are building your own web parts in SharePoint and you are trying to access a database that is installed on a separate database server with the current user’s credentials. Windows is not able to pass the user’s credentials from the SharePoint server to the SQL Server if you are using NTLM authentication.

    You can do a couple of things to get rid of the «double hop» issue. Which one is the best depends on your specific scenario.

    1. You can use RunWithElevatedPermissions in your code. This runs the code within that code block under the application pool account. This will get rid of the hop issue, but it will access the SQL database as the application pool account of your SharePoint web application, so that will only work in some scenarios.

    2. Another solution is that you can use Kerberos to authenticate against your web application and turn on delegation. If you use delegation the web application that you are running your web part from will delegate the user’s credentials to, for instance, the SQL Server. Because the credentials are delegated the credentials will be passed to the SQL Server without a problem and you will get rid of the double hop issue. A good resource on delegation can be found here http://www.adopenstatic.com/cs/blogs/ken/archive/2007/01/28/1282.aspx. Note that in order for your SharePoint web application to use Kerberos authentication you have to configure that on your web application http://technet.microsoft.com/en-us/library/cc263449.aspx.

    3. This one isn’t recommended in most cases, as it is less secure, but theoratically you could use SQL authentication to authenticate against the database. As you are passing SQL credentials in the connection string no double hop issue will occur here either. This would however require you to turn on SQL authentication on the SQL Server and you need to store the credentials somewhere. As I said, not a recommended approach.

    Hope this helps.

    Mirjam

    www.sharepointblogs.com/mirjam

    • Marked as answer by

      Friday, July 3, 2009 8:02 AM

Источник

I have an Operations server running Windows Server 2012R2 and SQL Server 2014 Enterprise back end. This server is used to deploy new code to other production servers via cmd file called by a SQL Agent Job. All of the servers are on the same domain.

Server A runs Windows Server 2008R2 and SQL Server 2008R2 back end. This server has a linked server connecting to a database on Server B, running the same OS and SQL Server version as A. The linked server is configured with option @useself=TRUE.

  • All three servers use a service account enabled for delegation.
  • This service account has sa priveleges on all three SQL Server instances.
  • All three servers have SPNs configured with that account and are delegated to use Kerberos for the associated MSSQLSVC services.

I can run the following on each and «Kerberos» is returned

    SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid

In addition I can telnet, ping, etc. from any of those servers to any other server without issue — everything is connected. Code deployments have never had a problem and the linked server is referenced often and without issue…except for one scenario and I don’t understand why.

Double Hop Scenario

  1. A SQL Agent job on the Operations server is run adhoc by any sysadmin other than the SQL Agent service account and executes a cmd file, also on the Operations server.
  2. The SQL Agent job is configured to «Run As» a SQL Agent service account, having sa priveleges.
  3. The code that is deployed comes from a .sql file on the Operations server.
  4. The cmd file calls SQLCMD to execute the code in the .sql file against Server A.

  5. The code in the .sql file references the linked server and fails with error

    *Msg 18456, Level 14, State 1, Server ServerB, Line 1 Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’. *.

The error is directed at the linked server — Server B. If I run this code manually, using SQLCMD, from the Operations server against Server A, it runs fine. If I logon to a box as the SQL Agent service account and run the SQL Agent job, it runs fine. It’s only when the SQL Agent job is executed by someone other than the SQL Agent service account that I get the error.

I’ve read through so many posts and blogs and MSDN articles regarding Kerberos, double hop, etc telling me to do what has already been done. What am I missing?

Additional Info
I’ve finally been able to come back to this and located some additional info. Using Bogdan’s advice, I ran Process Explorer and verified the credentials for the first hop are from the SQL Agent Service account as expected and that TCP is being used. Alas, that is all the useful info I was able to get out of the tool.

I dug into the Window’s application logs and dug around for login information for the different instances on the machines and noticed that Kerberos isn’t even being used!!! Instead NTLM is being used.

So that’s the new path I’m heading down — why is NTLM authentication being used, when Kerberos is set up and proper FQDN SDNs exist by port and instance? Do I need to somehow specify authentication type in the cmd file or SQLCMD call? Or do I have something misconfigured that I’m not thinking about?

The Mystery Deepens
The SQLCMD call referenced the «first hop» server via Alias. I modified the SQLCMD server reference to the actual named instance and reran the job. It worked! We also use cNames for our machines and SQL instances and so I tried using that. It also worked! For grins and giggles I re-tried the alias…it worked??? I go check the Windows applications logs for each of these and it is still reporting authentication as NTLM!

I am completely baffled at this point and at a loss as to how to explain this behavior to fix the rest of our environment.

Источник

Sometimes when trying to access a linked server, you’d get an error saying “Login failed for user NT AUTHORITYANONYMOUS LOGON”. This happens because you’re connected using Windows authentication, and SQL Server fails to “forward” your credentials to the linked server.

Hopefully, with this blog post, I’ll provide you with an easy-to-understand step-by-step guide, which would help you resolve the issue the right way, without any workarounds.

Problem

This issue is often called “double-hop pass-through authentication”, also known as “Kerberos delegation“, which I’ll try to illustrate with the following diagram:

Double-hop with Linked Server illustrated

In some cases, you would notice that the same error does NOT happen when you’re connected directly to the SQL Server (i.e. via RDP):

Single-hop with Linked Server illustrated

This is an issue that I see coming up quite often, but very difficult to find a solution for it. Solving it involves a bunch of stuff outside our comfort zone as DBAs, which is also why most people would instead go with a workaround, either using SQL authentication instead of Windows authentication or dangerously misconfiguring their linked servers.

Solution

Solving this issue requires several steps:

1. Both SQL Servers and the Client must be in the same Domain

If all parties involved are already within the same domain, that makes everything much easier.

If you have a multiple-domain environment and the servers are in different domains, then at the very least, both of the domains must be within the same Active Directory (aka Domain “Forest”).

Taking care of this is totally not your job as a DBA, though. You’re gonna have to bother the folks at IT/DevOps/Whichever relevant department for this.

If this is not possible for whatever reason, then you’ll have no choice but to go with a workaround: Using SQL Authentication, and/or Linked Server login mapping.

2. Both SQL Servers must be able to register SPNs

The SPN (Service Principal Name), after it’s registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration hasn’t been performed or fails, the Windows security layer can’t determine the account associated with the SPN, and Kerberos authentication isn’t used.

First, to find out whether a SQL Server was able to register itself as SPN or not, you can look in the SQL Server Error Log, right near the start where the SQL instance has just started up. You’ll find messages such as these:

  • The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/******* ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

These messages indicate that the SQL Server logon account did NOT have enough permissions to register itself as SPN.

To make this work, the SQL Server service accounts must be either LocalSystem, NetworkService, or a Domain account with the permissions “Read servicePrincipalName” and “Write servicePrincipalName“.

To give permissions to the SQL Server startup account to register and modify SPN, do the following for each SQL Server machine:

  1. Connect to the Domain Controller machine, and start Active Directory Users and Computers.
  1. Select View > Advanced.
  2. Under Computers, locate the SQL Server computer, and then right-click and select Properties.
  3. Select the Security tab and click Advanced.
  4. In the list, if the SQL Server startup account is not listed, click Add to add it and once it is added, do the following:
    1. Select the account and click Edit.
    2. Under Permissions select:
      • Validated Write servicePrincipalName.
    3. Scroll down and under Properties select:
      • Read servicePrincipalName
      • Write servicePrincipalName
    4. Click OK twice.
  5. The above must be done for each of the SQL Server machines involved and their corresponding service account(s).
  6. Close Active Directory Users and Computers.

Manual intervention might be required to register or unregister the SPN if the service account lacks the permissions that are required for these actions.

For more details on enabling SPN registration, please refer to Register a Service Principal Name for Kerberos Connections at Microsoft Learn.

After enabling SPN registration, you will have to restart the SQL Server service and look for a message in the SQL Server Error Log indicating that the SPN was registered successfully.

To determine the authentication method of a connection, execute the following query:

SELECT net_transport, auth_scheme   
FROM sys.dm_exec_connections   
WHERE session_id = @@SPID;

The expected result should be KERBEROS.

Note that you must run this query from a remote computer, and NOT by running it from within the SQL Server machine itself (i.e. opening SSMS while inside a Remote Desktop session).

If it says NTLM, that means you did something wrong and/or missed a step and/or executed the query from within the SQL Server machine itself instead of remotely.

3. Enable Resource-Based Kerberos Constrained Delegation between the two servers

Resource-Based Kerberos Constrained Delegation (a.k.a. “RBKCD“) was introduced in Windows 2012 and is a way of getting Kerberos authentication to work in a web application for users from multiple domains in a forest (I realize that’s probably a bunch of bizarre buzzwords that don’t mean much to you, but hey that’s what the documentation says!).

Anyways, to make a long story short, below is a Powershell script that enables RBKCD for two specified domain accounts so that they’d be able to delegate Kerberos things to each other. Just replace “SqlSvc1” and “SqlSvc2” with the names of the two relevant SQL Server service accounts (lines 1 and 2):

$SQLServiceAccount1 = Get-ADUser -AuthType Negotiate -Filter 'SamAccountName -like "SqlSvc1"'
$SQLServiceAccount2 = Get-ADUser -AuthType Negotiate -Filter 'SamAccountName -like "SqlSvc2"'

Set-ADUser $SQLServiceAccount1.SID -PrincipalsAllowedToDelegateToAccount $SQLServiceAccount2
Set-ADUser $SQLServiceAccount2.SID -PrincipalsAllowedToDelegateToAccount $SQLServiceAccount1

# Check RBKCD and SPNs:
Clear
$Users = Get-ADUser -AuthType Negotiate -Filter '*' -Properties SamAccountName, DistinguishedName, ServicePrincipalNames, PrincipalsAllowedToDelegateToAccount

foreach ($User in $Users) {
"============================="

( $env:USERDOMAIN + "" + $User.SamAccountName )
$User.DistinguishedName
""
"Service Principal Names:"
foreach( $SPN in $User.ServicePrincipalNames ){ ( "`t`t" + $SPN ) }
""
"RBKCD:"
foreach( $RBKCD in $User.PrincipalsAllowedToDelegateToAccount ){ ( "`t`t" + $RBKCD ) }

"============================="
}

For more information about Resource-Based Kerberos Constraint Delegation, please refer to the following resources:

  • Resource-Based Kerberos Constrained Delegation at MSSQLTips
  • Understanding Kerberos and NTLM authentication in SQL Server Connections at Microsoft Learn

Conclusion

Assuming that you followed the above steps properly, then both servers should now be able to register SPNs and then delegate Kerberos authentication to each other via Linked Servers.

You will be able to configure the linked server(s) using “the login’s current security context” like so:

Which will then delegate (or “forward”) the login’s Windows credentials over to the linked server for authentication, thus getting rid of the NT AUTHORITYANONYMOUS LOGON error.

There is also an alternative way to resolve this using a tool that Microsoft released a long while ago called “Kerberos Configuration Manager for SQL Server (KCM)“. However, it’s an old tool that rarely works in newer environments.

If you’re interested anyway, then please refer to the following for more information about this tool:

  • Using Kerberos Configuration Manager for SPNs Validation at MSSQLTips
  • How to link two SQL Server instances with Kerberos at the SQLShack

I hope this was helpful to you, and good luck!

If you have anything to add and/or you think I missed something and/or didn’t explain something properly, please let me know in the comments below.

Источник

  • Ошибка loft стиральная машина хаер выдает
  • Ошибка lock на стиральной машине haier что делать
  • Ошибка loc на индукционной плите
  • Ошибка loc на духовке gorenje что значит
  • Ошибка loc микроволновка lg