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
CodeWarriorCodeWarrior
7,3187 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.
15k22 gold badges104 silver badges164 bronze badges
answered Sep 18, 2012 at 3:00
Code MagicianCode Magician
22.8k7 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
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 SSaurabh R S
2,9871 gold badge32 silver badges43 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
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
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
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
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
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
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
- 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’.
- Cannot create a connection to data source ‘SQL1DEV’.
Ответы
-
-
Помечено в качестве ответа
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 issuethis 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 charactersif 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 worksIt 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 UserI 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 reportthe credential are:
Use current Windows user. Kerberos delegation may be requiredI have save as my Report on the Server :
http://val-t-sql/ReportServerI 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
thanksBis
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.
приложение, которое работает без проблем (и не было никакой активной разработки сделано на нем около 6 месяцев или около того) недавно начал не удается подключиться к базе данных. Администраторы операций не могут сказать, что могло бы измениться, что вызвало бы проблему.
клиентское приложение использует жестко закодированную строку подключения с Integrated Security=True, но когда приложения пытаются создать соединение с базой данных, оно выдает исключение SQLException со словами » ошибка входа для пользователя ‘NT AUTHORITYANONYMOUS LOGON».
Я могу войти в базу данных через Management Studio на этой учетной записи без проблем. Все вещи, которые я видел для этой проблемы, предназначены для ASP.NET проекты, и это, по-видимому, «проблема двойного прыжка», которая является клиентским приложением чертовски хорошо, лучше не быть проблемой. Любая помощь будет очень признательна.
Edit
клиентская машина и серверная машина, а также учетные записи пользователей находятся на одном и том же домен.
Это происходит, когда Брандмауэр Windows выключен.
ведущая теория:
Сервер был перезапущен около недели назад, и не удалось зарегистрировать имя участника-службы (SPN). Сбой регистрации имени участника-службы может привести к возвращению встроенной проверки подлинности в NTLM вместо Kerberos.
922
5
5 ответов:
Если ваша проблема связана с серверами, вы должны смотреть на несколько вещей.
во-первых, ваши пользователи должны иметь делегирование включено, и если единственное, что изменилось, это, вероятно, они делают. В противном случае вы можете снять флажок «учетная запись чувствительна и не может быть делегирована» — это свойства пользователя в AD.
во-вторых, ваша учетная запись(ы) службы должна быть доверенной для делегирования. Поскольку вы недавно изменили свою учетную запись службы, я подозреваю, что это виновник. (http://technet.microsoft.com/en-us/library/cc739474 (v=ws.10).aspx)
вы упомянули, что у вас могут быть некоторые проблемы с SPN, поэтому обязательно установите SPN для обеих конечных точек, иначе вы не сможете увидеть вкладку делегирование в AD. Также убедитесь, что вы находитесь в расширенном виде в «Active Directory-пользователи и компьютеры.»
Если вы все еще не видите вкладку делегирование, даже после исправления SPN, убедитесь, что ваш домен не в режиме 2000. Если это так, вы можете «повышение уровня функционирования домена.»
теперь вы можете отметить учетную запись как доверенную для делегирования:
в области сведений щелкните правой кнопкой мыши пользователя, которому вы хотите доверять
делегирование и нажмите кнопку Свойства.перейдите на вкладку делегирование, выберите Учетная запись доверена для делегирования
установите флажок и нажмите кнопку ОК.наконец, Вам также нужно будет установить все машины как доверенные для делегирования.
Как только вы это сделаете, снова подключитесь к sql server и протестируйте свои любимые серверы. Они должны работать.
во-первых: моя проблема не такая же, как у вас, но этот пост-первое, что появляется в google для
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'ошибка в то время, когда я написал это. Решение может быть полезно для людей, ищущих эту ошибку, поскольку я не нашел это конкретное решение в любом месте в интернете.в моем случае я использовал Xampp / Apache и PHP sqlsrv, чтобы попытаться подключиться к базе данных MSSQL с помощью проверки подлинности Windows и получил
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'ошибка, которую вы описали. Я, наконец, нашел проблему быть самой службой Apache, работающей под пользователем «LOCAL SERVICE»вместо учетной записи Пользователя, в которую я вошел. Другими словами, он буквально использовал анонимный аккаунт. Было принято решение ехать в сервис.msc, щелкните правой кнопкой мыши службу Apache, перейдите в Свойства, перейдите на вкладку Вход в систему и введите учетные данные для пользователя. Это соответствует вашей проблеме, связанной с SPN, поскольку ваши SPN настроены для запуска от определенного пользователя в домене. Поэтому, если правильное имя участника-службы не работает, окна аутентификация будет по умолчанию для неправильного пользователя (вероятно, пользователя «локальной службы») и даст вам анонимную ошибку.вот где это отличается от вашей проблемы. Ни один из компьютеров в локальной сети не находится в домене, они находятся только в рабочей группе. Чтобы использовать проверку подлинности Windows с рабочей группой, как компьютер с сервером (в моем случае MSSQL Server), так и компьютер со службой запроса данных (в моем случае Apache) должны иметь пользователя с одинаковым именем и идентичный пароль.
подведем итоги:
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'ошибка в обоих наших случаях, по-видимому, вызвана не запущенной службой и/или не на правильном пользователе. Обеспечение правильного SPN или другой службы выполняется и под правильным пользователем должно решить анонимную часть проблемы.
Я думаю, что должно было быть какое-то изменение в группе объявлений, используемой для аутентификации в базе данных. Добавить название веб-сервера, в форматесервера домен$, в группе объявлений, которые имели доступ к базе данных. Кроме того, также попробуйте установить веб.config атрибут «false». Надеюсь, это поможет.
EDIT: идя по тому, что вы редактировали.. это, скорее всего, указывает на то, что протокол проверки подлинности вашего SQL Server откатился от Kerberos(по умолчанию, если вы использовали встроенную проверку подлинности Windows) для NTLM. Для использования Kerberos имя участника-службы (SPN) должно быть зарегистрировано в службе каталогов Active Directory. Имя участника — службы (SPN) — это уникальные идентификаторы служб, запущенных на серверах. Для каждой службы, которая будет использовать проверку подлинности Kerberos, необходимо задать имя участника-службы, чтобы клиенты могли идентифицировать службу в сети. Он зарегистрирован в Active Directory под учетной записью компьютера или учетной записью пользователя. Хотя Протокол Kerberos используется по умолчанию, если по умолчанию происходит сбой, процесс проверки подлинности будет опробован с помощью NTLM.
в вашем сценарии клиент должен устанавливать tcp-соединение, и он, скорее всего, работает под учетной записью LocalSystem, и для экземпляра SQL не зарегистрировано SPN, следовательно, используется NTLM, однако учетная запись LocalSystem наследует от системного контекста вместо истинного пользовательского контекста, таким образом, не удалось выполнить «анонимный вход».
чтобы решить эту проблему, спросите свой домен администратор вручную зарегистрировать имя участника-службы, если SQL Server работает под учетной записью пользователя домена.
Следующие ссылки могут помочь вам more:
http://blogs.msdn.com/b/sql_protocols/archive/2005/10/12/479871.aspx
http://support.microsoft.com/kb/909801
У одного из моих заданий SQL была такая же проблема. Он включал в себя загрузку данных с одного сервера на другой. Ошибка произошла, потому что я использовал учетную запись службы агента sql Server. Я создал учетные данные, используя идентификатор пользователя (который использует проверку подлинности окна), общий для всех серверов. Затем создал прокси-сервер, используя эти учетные данные. Использовал прокси-сервер в задании sql server, и он работает нормально.
вам, вероятно, просто нужно указать имя пользователя и пароль в вашей connectionstring и установить Integrated Security=false
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
- 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.
- The SQL Agent job is configured to «Run As» a SQL Agent service account, having sa priveleges.
- The code that is deployed comes from a .sql file on the Operations server.
- The cmd file calls SQLCMD to execute the code in the .sql file against Server A.
-
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.
- Remove From My Forums
-
Question
-
Hi,
I am getting intermittent error message Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’
while connecting azure sql db from web app. I am using SQL AAD authentication. I have given all necessary permission to user.Please help me here.
All replies
-
Hi Kapil,
I have created user in Master db as well. But issue is occurring after continuously hitting db for 1-2 Hrs.
Thanks,
Ashutosh
-
-
Proposed as answer by
Monday, June 4, 2018 6:13 PM
-
Proposed as answer by
-
I’m having the same problem, and I can see by logging on using the SQL Server authenticated normal server admin that my Azure AD user was indeed created successfully by Azure portal in the master database as a user, and I can see that it lists that same
user as the «Active Directory Admin» in the Azure portal for that server, so Azure portal thinks everything is okay — just that the login always fails (after successful password and MFA code is verified) with the Login
failed for user ‘NT AUTHORITYANONYMOUS LOGON’ error in SSMS 2017. -
I’m having the same issue — it just started failing recently. I put up a question on ServerFault about it here:
https://serverfault.com/questions/923752/importing-bacpac-via-ssms-to-azure-sql-server-fails-with-azuread-user
Here is the full text:
Recently, trying to import a BACPAC to an Azure SQL Server using SSMS (SQL Server Management Studio) has been failing with the following error:
Failed to connect to server ___.database.windows.net. (Microsoft.SqlServer.Smo) Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’. (Microsoft SQL Server, Error: 18456)
The steps I have used to import the BACPAC are as follows:
- Connect to server in SSMS using my AzureAD credentials.
- Right click on Databases and choose «Import Data-tier application»
- Select my BACPAC file and click next.
- Leave default settings for S2 Azure DB and click next.
- Error appears before it gets to the Summary page.
The above steps have worked great for the last 2 years, and just recently stopped working. I’ve tried on 2 different computers, different networks, and different versions of SSMS (that have worked find in the past) but they all still fail.
Using a regular SQL server password login (non AzureAD account) imports the BACPAC successfully, as does importing via Azure Portal.
Any idea what could be causing this issue?
-
Hello,
If you could please create a new forum thread, you may find you get more help. This is an old thread.
Hope this helps.
Regards,
Alberto Morillo
SQLCoffee.com -
Have you checked whether you have signed into Visual Studio? If you are not, then you could get this error. There should be an account configured for Azure
Service Authentication. Once you sign into an account in Visual Studio this will be automatically taken care. -
I was able to solve the connection issue on my Azure SQL service by adding an Azure AD Group as a user on my master database. This gives the AAD group CONNECT privilege on the server.
To do this you need to login to your Azure SQL service with an Azure AD account that has full privileges. This will be the same AAD user that created the service, or a user with the role of Admin on the AAD Directory.
Once logged in, run this on Master «CREATE USER [MY_AAD_USER_OR_GROUP_NAME] FROM EXTERNAL PROVIDER;»
You will then be able to login with the AAD account or an AAD account belonging to the group. Now you will need to adjust AAD groups and SQL users to grant the specific access you need for each database. I didn’t see a way to grant access at
the server level.Cheers,
John
-
Edited by
John R Love
Wednesday, January 8, 2020 10:40 PM -
Proposed as answer by
Mike Ubezzi (Azure)Microsoft employee
Saturday, January 11, 2020 1:24 AM
-
Edited by
Problem
-> One of the SSIS package was failing with below error when validated from SSMS,
Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’. Reason: Could not find a login matching the name provided.
-> I tried validating the package and it succeeded. Spoke to the developer who was experiencing this issue and understood below details.
-> SSIS package is stored in “Integration Services Catalog” on SQL Server instance JBSERVER1SSIS.
-> The SSIS package has a “Data Flow” Task that connects to SQL Server instance JBAG1.
-> Developer has a RDP session to Database Server JBSERVER3. Opens up a SSMS in database server JBSERVER3 and connects to SQL Server Instance JBSERVER1SSIS and is validating the SSIS package and encounters this error.
-> When the developer validates the package connected from Database Server JBSERVER1, there are no issues.
-> This error is due to double-hop authentication issue. Please refer articles “Understanding Kerberos Double Hop” and “Double-hop authentication: Why NTLM fails and Kerberos works” for more details on Double-hop authentication.
Analysis
-> Connected to SQL Server instance JBAG1, queried SQL Server errorlog and found below errors, which is same when SSIS package validation fails,
2019-07-19 12:15:16.500 Logon Error: 18456, Severity: 14, State: 5.
2019-07-19 12:15:16.500 Logon Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.15.12]
2019-07-19 13:07:27.200 Logon Error: 18456, Severity: 14, State: 5.
2019-07-19 13:07:27.200 Logon Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.15.12]
2019-07-19 13:17:08.790 Logon Error: 18456, Severity: 14, State: 5.
2019-07-19 13:17:08.790 Logon Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.15.12]
-> “ping -a 192.168.15.12” resolves to SSIS server JBSERVER1.
-> Connected to SQL Server instance JBAG1 again, queried SQL Server errorlog and searched for keyword “Service Principal Name (SPN)” and found below details,
2019-07-18 14:49:03.810 Server SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
2019-07-18 14:49:03.810 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/JBAG1.JBS.COM ] 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.
2019-07-18 14:49:03.810 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/JBAG1.JBS.COM:1433] 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.
-> SPN is not properly registered. Execute below code on server JBAG1 remotely (RDP to a different server other than JBAG1 and then use SSMS to connect to JBAG1. This is just to use TCP) and look for “net_transport” and “auth_scheme” in your output,
select @@servername as ServerName, session_id,net_transport, auth_scheme from sys.dm_exec_connections where session_id= @@spid
-> Ideally, you should see net_transport as TCP and auth_scheme as KERBEROS. Since SPN is not configured properly in SQL Server instance JBAG1, auth_scheme is NTLM and this is the reason for the error message. Configuring proper SPN should solve this problem.
SPN
-> SPNs are objects registered within AD which allow Kerberos to work. On startup SQL Server attempts to register 2 SPNs against the service account. On shutdown, it attempts to re-register these.
-> These operations can fail, so SPNs are either not registered, or deleted. Both of these can cause problems.
Checking SPNs exist against the service account
-> There should be 2 SPNs configured per instance. One for instance name and one for port number. These need to be against the servername listed in ‘select @@servername’.
-> You can query the SQL Server port using below query,
SELECT MAX(CONVERT(VARCHAR(15),value_data)) as Port FROM sys.dm_server_registry WHERE registry_key LIKE '%MSSQLServerSuperSocketNetLibTcp%' AND value_name LIKE N'%TcpPort%' AND CONVERT(float,value_data) < 0;
Add SPN manually
-> To query the SPNs registered against a service account run the following from an administrative command prompt:
setspn -L JBSJBSQLServer-> You will see output similar to below,
Registered ServicePrincipalNames for CN=JBSQLServer,OU=Service,OU=Generic Accounts,OU=User Environment,DC=JBS,DC=com:
MSSQLSvc/JB1.JBS.COM:15734
MSSQLSvc/JB1.JBS.COM:IN01
MSSQLSvc/JBAG1:15734
MSSQLSvc/JBAG1
MSSQLSvc/JBSERVER1.JBS.COM:15734
MSSQLSvc/JBSERVER1.JBS.COM:SSIS
MSSQLSvc/JBSERVER2.JBS.COM:15734
MSSQLSvc/JBSERVER3.JBS.COM:IN01
-> SPN entries for database server JBAG1 is not created using FQDN (Fully qualified domain name) and has a wrong port number.
-> Correct SPN can be added using below command from administrative command prompt,
setspn -A "MSSQLSvc/JBAG1.JBS.COM:1433" JBSJBSQLServer
setspn -A "MSSQLSvc/JBAG1.JBS.COM" JBSJBSQLServer-> If it is a named instance, Example : JBSERVER123IN2017 with port 15223. Below command can be used,
setspn -A "MSSQLSvc/JBSERVER123.JBS.COM:15223" JBSJBSQLServer
setspn -A "MSSQLSvc/JBSERVER123.JBS.COM:IN2017" JBSJBSQLServer-> Delete the bad SPNs using below command,
setspn -D "MSSQLSvc/JBAG1:15734" JBSJBSQLServer
setspn -D "MSSQLSvc/JBAG1" JBSJBSQLServer-> It can take some time for the changes to take effect, but you should be able to reconnect to SQL Server, and get a KERBEROS connection.
-> Execute below code on server JBAG1 remotely (RDP to a different server other than JBS and then use SSMS to connect to JBSIN2017. This is just to use TCP) and look for “net_transport” and “auth_scheme” in your output. You should see net_transport as TCP and auth_scheme as KERBEROS.
select @@servername as ServerName, session_id,net_transport, auth_scheme from sys.dm_exec_connections where session_id= @@spidAdd SPN using “Microsoft® Kerberos Configuration Manager for SQL Server®”
-> Download the tool and install it.
-> Double click “C:Program FilesMicrosoftKerberos Configuration Manager for SQL ServerKerberosConfigMgr.exe”
-> To connect to a local server, leave all fields blank and click connect.
-> To connect to a remote server, enter the Server name, username and password as below,
-> After connecting to the server, you will see all SPNs. Scroll to right and check the status of the SPN. In our case it is Missing.
-> Click on “Fix All” button, and confirm YES.
-> Once created, the status should change to Good.
-> Testing SSIS package should be fine now and you shouldn’t be seeing the issue any more. In my case the issue got resolved after I have added the SPNs.
-> In case the issue is still not resolved after adding SPNs. Check below things,
Checking for multiple SPNs for an instance
-> Sometimes the same SPN is registered against multiple Service Accounts. This will break KERBEROS, as it expects the SPN to be unique.
-> To check for multiple SPNs, run the following,
setspn -Q "MSSQLSvc/JBAG1.JBS.COM:1433"
setspn -Q "MSSQLSvc/JBAG1.JBS.COM"-> Output,
Checking domain DC=JBS,DC=com
CN=JBSQLServer,OU=Service,OU=Generic Accounts,OU=User Environment,DC=JBS,DC=com
MSSQLSvc/JBS1.JBS.COM:15734
MSSQLSvc/JBAG1.JBS.COM:1433
MSSQLSvc/JBAG1.JBS.COM
CN=svc-Prod-SQLServer,OU=Service,OU=Generic Accounts,OU=User Environment,DC=JBS,DC=com
MSSQLSvc/JBAG1.JBS.COM:1433
MSSQLSvc/JBAG1.JBS.COM
MSSQLSvc/JBS09903.JBS.COM:15734
MSSQLSvc/JBS09900.JBS.COM:15734
MSSQLSvc/JBS09909.JBS.COM:15734
MSSQLSvc/JBS09909.JBS.COM:INSQL01
MSSQLSvc/JBS09911.JBS.COM:INSQL01
CN=svc-Prod-build,OU=Service,OU=Generic Accounts,OU=User Environment,DC=JBS,DC=com
MSSQLSvc/JBS09982.JBS.COM:15734
MSSQLSvc/JBS09982.JBS.COM:PMGBVSQL01
MSSQLSvc/JBS19609.JBS.COM:DMGBVSQL01
MSSQLSvc/JBS09612.JBS.COM.net:64363
MSSQLSvc/JBAG1.JBS.COM:1433
MSSQLSvc/JBAG1.JBS.COM
Existing SPN found!
-> In this case I am able to see SPNs created also for service account JBsvc-Prod-SQLServer and JBsvc-Prod-build. May e the service account was changed multiple times. Currently Service account for SQL Server instance JBS is JBsvc-JBS-SQLServer. So in this case we will delete the SPN’s created for servcie account JBsvc-Prod-SQLServer and JBsvc-Prod-build using below command,
setspn -D "MSSQLSvc/JBAG1.JBS.COM:1433" JBsvc-Prod-SQLServer
setspn -D "MSSQLSvc/JBAG1.JBS.COM" JBsvc-Prod-SQLServer
setspn -D "MSSQLSvc/JBAG1.JBS.COM:1433" JBsvc-Prod-build
setspn -D "MSSQLSvc/JBAG1.JBS.COM" JBsvc-Prod-buildDelegation
If instances shows KERBEROS connections, and it still doesn’t work, check that the service account is set to allow delegation.
Similar Issues
-> Linked Servers configured may also fail with this error, since Linked Server needs a ‘double-hop’ which requires you to be connected to the instance using Kerberos. For example, On SQL Server instance SERVER1IN01, you have configured a linked server to SERVER2IN02. Everything works fine when connected to Server1. But connection fails with message “Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’ ” when You have a RDP session to SERVER3 (Any other server other than SERVER1, SERVER2) and connected to SERVER1IN01 using SSMS from SERVER3 and test a connection for the configured linked server which points to SERVER2IN02. You can solve this by configuring proper SPN as mentioned in this post.
Thank You,
Vivek Janakiraman
Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.
Если вы внедряете Microsoft Dynamics CRM, то с уверенностью могу сказать, что уже сталкивались с керберос дабл хоп, ну, или обязательно с этим столкнётесь.
Что же это такое? Вкратце, это НЕвозможность транслировать от сервера к серверу ваши имперсонированные учётные данные (полученные от клиентской станции). В контексте CRM это часто встречается при обращении вашего ASP.NET приложения (которое хостится на application-сервере CRM) к SQL-серверу или серверу отчётов.
Частым признаком данной проблемы является сообщение «Login failed for user ‘NT AUTHORITYANONYMOUS LOGON'», например, вот такой невиданной красоты:
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735043
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
Подробнее о Kerberos Double Hop можно прочитать в этой статье. Руководство по решению этой проблемы хорошо описано в статье Understanding Kerberos and NTLM authentication in SQL Server Connections.
Но кому понравится скурпулёзно, шаг за шагом проверять множество настроек аж на трёх серверах? Действительно, малоприятное занятие, поэтому берём и ставим DelegConfig (Kerberos/Delegation Configuration Reporting Tool).
Диагностическая утилита (по-сути, ASP.NET приложение, которое надо развернуть на «проблемном» сервере) представляет собой пошаговый мастер, помогающий обследовать и настроить все аспекты делегирования.
Привожу основные скриншоты, из которых будет понятно, как это работает.
Инструкция пользователя:
Статус HTTP-службы:
Статус SQL-службы:
Статус SQL-службы (продолжение):
At one of my customer sites, I recently started having an issue logging onto an Azure SQL Database. The customer had configured Azure Active Directory (AAD) and were using Multi-factor Authentication (MFA).
I had previously been using it OK.
I would connect to the server using SSMS, enter my username and password, and then be prompted for the MFA authorization. After I authorized the logon, I would then receive the error shown in the main image: Login failed for user ‘NT AUTHORITYANONYMOUS’ and Error 18456.
Now when I’ve seen this error before with on-premises SQL Server, it’s usually a Kerberos problem or a double-hop problem. But this was direct to Azure SQL Database.
Same error occurred on 17.9.1 and 18.3 for SSMS, and for Azure Data Studio.
Turns out that one of the admins at the site had removed my database user (and others) from the master database, AND, that was my DEFAULT database. That then led to this error. Putting the user back into master fixed the issue.
Other Causes
While researching this error though, I came across some other potential causes. The main one was that if you need to connect to a specific database, not to master, you need to ensure that you’ve typed in (i.e. not tried to select) the appropriate database name on the Options tab when trying to log in. If you have no database, or have the wrong one there, you’ll get the same error.
Now one other error was worth noting. There used to be a text box and checkbox on the Options tab that let you enter the domain suffix for your AAD credentials. What you need to do now, is to enter your full email address (AAD credential) as the Username after selecting to logon using MFA. If you don’t do that, again all goes but then right at the end, you get this error:
The critical part is that it says that the ‘User yourname@yourcompany.com’ returned by service does not match user ‘yourname’ in the request. If you see this, it means that you didn’t include the full address in the Username box.