SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'


Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Author
Message
kazim.raza
kazim.raza
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1956 Visits: 739
Hi guys

I have installed SQL Server 2016 development edition with CU3 including SQL Database Engine, SSRS, SSAS, SSIS all on one box called SQLDEV16. As part of my test, I have ported all existing databases from production to SQLDEV16, as well as migrated SSRS. Initial tests of SSRS turns out to meet expectations.

We are able to browse the new report portal, and reports with following types of data sources work;
- Data sources that are set with credentials explicitly specified, both database and Windows domain accounts
- Data Sources set to use Integrated Windows Security and are connecting to a database on local box as SSRS (DEVSQL16)

Any data source that uses Integrated Windows Security and connects to database that is hosted on another box ,say existing production which has SQL Server 2014 Standard Edition, does not work.

I am not sure if this is exactly related to enabling Kerberos Authentication, so thought I'll ask the experts here.

Thanks
Kaz

Thom A
Thom A
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86766 Visits: 22262
How are you connecting to the other server? Is the datasource set to the other server, or is datasource the same as the SSRS server, and then the query run uses 4 part naming convention to reference another server? if the latter, then yes, Kerboros is going to be your problem here.

If the former, then i would take a guess that your datasources have been set up to to connect anonymously, rather than using any kind of credentials. Setting the credentials on the datasources will fix that.


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
kazim.raza
kazim.raza
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1956 Visits: 739
My data source is on another server, SQL Server 2014 instance, and this data source does not work with Integrated Windows Security. I don't get to the point of executing queries as I am getting errors at the time of setting up the data source.

The same database after porting into SQL Server 2016, works fine where SQL Server & SSRS are on the same box.
Thom A
Thom A
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86766 Visits: 22262
kazim.raza - Monday, July 31, 2017 7:28 PM
My data source is on another server, SQL Server 2014 instance, and this data source does not work with Integrated Windows Security. I don't get to the point of executing queries as I am getting errors at the time of setting up the data source.

The same database after porting into SQL Server 2016, works fine where SQL Server & SSRS are on the same box.

You say you aren't using Integrated Security, but NT_AUTHORITY/ANONYMOUS is an integrated security account. This sounds like you either have the datasource set use integrated security, and having a kerboros double hop issue (where your query connects to one server, and then queries a second in the T-SQL), or connecting anonymously.



Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
kazim.raza
kazim.raza
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1956 Visits: 739
Thom A - Tuesday, August 1, 2017 1:50 AM
kazim.raza - Monday, July 31, 2017 7:28 PM
My data source is on another server, SQL Server 2014 instance, and this data source does not work with Integrated Windows Security. I don't get to the point of executing queries as I am getting errors at the time of setting up the data source.

The same database after porting into SQL Server 2016, works fine where SQL Server & SSRS are on the same box.

You say you aren't using Integrated Security, but NT_AUTHORITY/ANONYMOUS is an integrated security account. This sounds like you either have the datasource set use integrated security, and having a kerboros double hop issue (where your query connects to one server, and then queries a second in the T-SQL), or connecting anonymously.

Hi Thom,

Thanks for the reply. I guess I wasn't clear enough before, my bad. I'll try again, and start over.

I have got two servers, Server A & Server B. Server A has SQL 2016 developer edition, with both SQL Server 2016 DB Server & SSRS 2016 installed on the box. Server B has SQL Server 2014 standard edition installed. My test database, dm_dwh exists on both Server A & Server B.

My data source on SSRS 2016 is using integrated security, to connect to database that is located on the same box (server A) as SSRS 2016. This works, no problems here.
If I change the data source to Server B from Server A in the connection string of the same data source, I get the NT_AUTHORITY/ANONYMOUS error.
My guess is that it is related to kerberos double hop issue, though, need confirmation from experts like yourself. I also want to understand why am I able to setup my data source successfully when I use the database on Server A?

I hope I have laid it out in clearer way. Looking forward to your reply.

Thanks
Kaz

Thom A
Thom A
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86766 Visits: 22262
If you're simply changing the connection string from one server to the other, then double hopping won't be a problem. Your connection would be going directly to the other server (it won't be authenticating to the first), so there is no opportunity to double hop.

Normally, if you have haver two locations to get data from it is better to have two data sources, rather than one and editing it. If you actually create a proper data source from scratch to the other server in your SSRS portal, rather than amending the original, does that work?

Edit: It would, also, however, be worth asking your network team to check that Kerboros is enabled between your two servers, just in case. I can''t guide you on that bit though, as it's not something I know how to do myself.


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
kazim.raza
kazim.raza
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1956 Visits: 739
Thanks for the reply Thom.

I tried creating a new data source from scratch, and got the same error, "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.". I am scheduled to meet my network team some time this week, will keep you posted.
Thanks
Kaz

Mahesh Hello
Mahesh Hello
SSC-Addicted
SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)

Group: General Forum Members
Points: 487 Visits: 375
kazim.raza, How did you resolve it?
sgmunson
sgmunson
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98287 Visits: 7259
kazim.raza - Wednesday, August 2, 2017 9:27 PM
Thanks for the reply Thom.

I tried creating a new data source from scratch, and got the same error, "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.". I am scheduled to meet my network team some time this week, will keep you posted.
Thanks
Kaz

The problem is that the local account of the DEV server is not going to useful in ANY WAY for logging in to the remote server. You can still use integrated security, but you'd have to change the service account for SSRS to a domain account.for that to work,


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search