Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

[kashyapdhar]

We have been facing a certain issue while trying to execute a report which has Linked Server connection being used in the SQL statement that's being fired. 

App Server: XXXXXX

Reporting database: YYYYYY

Transaction Database: ZZZZZZ

Ex: We have a select statement which is hit on reporting database and then using linked server fetches data from certain tables in the transaction database (Reason for doing this is, two of the tables are not available in reporting database but transaction database which are required to fetch data)

SELECT

           *

FROM

            [Servername].[DatabaseName].dbo.tablename

The SQL is failing to execute in the production QlikView server and giving below error. Kindly note all other reports which are hitting the reporting database (like this one) are running fine (they don't have linked server connection) without any issues. 

Error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
2019-04-23 11:38:49 ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
2019-04-23 11:38:49 Execution Failed
2019-04-23 11:38:50 Execution finished.

RCA: We have raised it with out helpdesk team to check why only the reports with linked server connection are failing to run and others run absolutely fine. Still no answers

In the mean time we have tried restarting the QLikView services and tried running the reports and they ran fine. However the reports again started failing at / after certain time in the day.

Has anyone else faced such issue and how was it resolved? any guidance much appreciated.

QlikView 12.10.20602.0 SR8

[kashyapdhar]

Any help / guidance much appreciated

[Sonja_Bauernfeind]

Hello,

The error message that you are receiving indicates that the database itself is returning a login failure, as it is being done anonymously. Is it expected to be anonymous? 

Does this reload work fine if you do not execute it based on a schedule or run a task in the QMC, but simply reload the document in the QlikView Desktop client?

[kashyapdhar]

Hi Sonja,

We use windows authentication for the QlikView service account to fetch data from SQL database. We have not got this issue till last week. To answer your question, the login can't be anonymous

If i simply reload the document from the app server 

> using my login, it fails

> using QlikView service account login, it fails

> using SSMS on my login, it fails

> using SSMS on QlikView service account it fails

[kashyapdhar · Accepted solution]

Hi,

Just to make sure anyone who is facing this issue on QlikView server, we may have found the problem & its solution.

Here goes the entire checks and workarounds that we tried before identifying what the issue was

We use windows authentication for the QlikView service account to fetch data from SQL database. We have not got this issue till last week. To answer your question, the login can't be anonymous

If i simply reload the document from the app server 

> using my login, it fails

> using QlikView service account login, it fails

> using SSMS on my login, it fails

> using SSMS on QlikView service account it fails

Workaround:

The DR server for QlikView which we have setup, was used to run a temporary QlikView app for the linked server connection. This was setup as a random app just connecting to linked server and fetching the data. Once the reload of this app is done, we used to trigger the actual app from the QMC and it used to run fine (next instance of run used to fail)

Identifying the problem:

We worked with the SQL team to identify if the login issue was due to some changes on AD or maybe the SQL server was not accepting SQL connection for windows authentication of the service account. All these checks made sure that the authentication is not an issue and linked server should work fine

We had windows patching done on the servers over the weekend wherein certain KB's were installed. We worked with the Windows support team and tried to identify the particular KB which might be causing this issue. Below link should give you a fair idea on what the issue could be.

https://techcommunity.microsoft.com/t5/SQL-Server-Support/SQL-SQL-linked-server-connections-fails-af...

This is not a QlikView issue either with the app or server and also not a SQL issue. The issue lies with the KB that Microsoft provided for installation. There is a security patch update that Microsoft is planning to release which should resolve this issue. For now we uninstalled the affected KB and restarted the server. Reports with linked server connection are now running fine.

Affected KB: https://support.microsoft.com/en-us/help/4489878  - March 12, 2019—KB4489878 (Monthly Rollup) 

[kashyapdhar · Accepted solution]

Hi,

Just to make sure anyone who is facing this issue on QlikView server, we may have found the problem & its solution.

Here goes the entire checks and workarounds that we tried before identifying what the issue was

We use windows authentication for the QlikView service account to fetch data from SQL database. We have not got this issue till last week. To answer your question, the login can't be anonymous

If i simply reload the document from the app server 

> using my login, it fails

> using QlikView service account login, it fails

> using SSMS on my login, it fails

> using SSMS on QlikView service account it fails

Workaround:

The DR server for QlikView which we have setup, was used to run a temporary QlikView app for the linked server connection. This was setup as a random app just connecting to linked server and fetching the data. Once the reload of this app is done, we used to trigger the actual app from the QMC and it used to run fine (next instance of run used to fail)

Identifying the problem:

We worked with the SQL team to identify if the login issue was due to some changes on AD or maybe the SQL server was not accepting SQL connection for windows authentication of the service account. All these checks made sure that the authentication is not an issue and linked server should work fine

We had windows patching done on the servers over the weekend wherein certain KB's were installed. We worked with the Windows support team and tried to identify the particular KB which might be causing this issue. Below link should give you a fair idea on what the issue could be.

https://techcommunity.microsoft.com/t5/SQL-Server-Support/SQL-SQL-linked-server-connections-fails-af...

This is not a QlikView issue either with the app or server and also not a SQL issue. The issue lies with the KB that Microsoft provided for installation. There is a security patch update that Microsoft is planning to release which should resolve this issue. For now we uninstalled the affected KB and restarted the server. Reports with linked server connection are now running fine.

Affected KB: https://support.microsoft.com/en-us/help/4489878  - March 12, 2019—KB4489878 (Monthly Rollup) 

[Sonja_Bauernfeind]

This is some great stuff! Thank you for coming back to us with the resolution.