July 31, 2017 at 3:47 am
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
July 31, 2017 at 3:52 am
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.
Larnu.uk
July 31, 2017 at 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.
August 1, 2017 at 1:50 am
kazim.raza - Monday, July 31, 2017 7:28 PMMy 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.
Larnu.uk
August 1, 2017 at 4:25 am
Thom A - Tuesday, August 1, 2017 1:50 AMkazim.raza - Monday, July 31, 2017 7:28 PMMy 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
August 1, 2017 at 4:32 am
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.
Larnu.uk
August 2, 2017 at 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
March 3, 2018 at 1:27 pm
kazim.raza, How did you resolve it?
March 9, 2018 at 12:49 pm
kazim.raza - Wednesday, August 2, 2017 9:27 PMThanks 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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply