Tag Archives: SQL Server Reporting Services

Opening Port 80 in Windows Firewall to Support Calling SSRS From Another Computer

Recently I was working on another article for RedGate’s SimpleTalk site. As part of it, I had SSRS installed on a Windows 10 computer, and needed to connect to it from another computer. I was having a lot of issues connecting, until I remembered SSRS connects using Port 80, and by default Windows 10 (and previous versions) block Port 80 for incoming traffic.

The solution was to, obviously, open Port 80 on the Windows 10 computer. Doing so was not difficult, but did require quite a few steps, and of course administrator rights on the computer.

First, open the Windows 10 Settings. Then, click on Network & Internet.


On the Status window, click on Windows Firewall.


From here, click on Advanced settings.


If prompted confirm you do wish to make changes. When the Windows Defender Firewall dialog appears, click on Inbound Rules.


Now click on New Rule


In the New Inbound Rule Wizard window, change the type of rule to be Port. Then click next.


On the next window, leave the rule applying to the default of TCP. For the port, assuming you are using the default setup, enter 80 for the port number. If you setup SSRS on a different port then obviously use that port number instead.


On the action page we tell Windows what we want to do if it finds incoming traffic on this port. For this development environment we will take the default of Allow the connection. If you had setup https service on your report server, then you could take the second option of allow if secure.


Next, we need to specify what network type the rule should apply to. For the scenario, I am on a small network, such as you might have at home, and that network was setup as private. Thus I am leaving Private checked on, and unchecking Domain and Public.

Unchecking public is especially important if you plan to take your laptop out to a coffee shop, you don’t want someone trying to hack into your machine via port 80. When done just click next.


On the last screen we’ll give the firewall rule a name, and a description. When done, click finish.


As you can see, the new rule now appears in our Inbound Rules area.


Once you have completed working with your SSRS server, I’d suggest you return here, right click on the rule, and either disable it, or if you know it will no longer be needed, delete it.

And with that you should now be able to connect to the computer running SSRS from another computer on your network.


SQL Server 2016 Reporting Services Cookbook–On Sale Until 2018!

SQL Server 2016 Reporting Services CookbookHappy  Holidays! As a gift to everyone, PACKT Publishing has put the e-book version of my SQL Server 2016 Reporting Services Cookbook on sale for just $5.

Yes, that’s right, for the same price as a pumpkin spice chai tea latte with extra whip cream, and a quick trip to http://bit.ly/ssrscook, you could be reading this magnificent tome of wisdom.

Now, I can year some of you going “but Robert, SQL Server 2017 is out now, is this still relevant?” Yes, absolutely! Very few changes were made to the 2017 version of SSRS, so everything in this book is just as valid.

And it’s not just my book that is on sale, all the e-books at PACKT are on sale for the same low low price. So after you get done adding the SQL Server 2016 Reporting Services Cookbook to your shopping cart, be sure to check out the other awesome titles on PACKT Publishing.

This book, by the way, makes an excellent companion to two of my Pluralsight courses.

The SQL Server Reporting Services Playbook, https://www.pluralsight.com/courses/sql-server-reporting-playbook, is designed for people who are totally new to SSRS and need to get up to speed FAST.

Already know SSRS, and just want to learn what’s new with SSRS 2016? Then my appropriately named What’s New in SQL Server 2016 Reporting Services course, https://www.pluralsight.com/courses/sql-server-2016-reporting-services, is just the thing for you. It goes over just the new features in SSRS 2016 (and still applicable to 2017) and does it fast. With this short course you can be up and running with 2016/2017.

What? You don’t have a Pluralsight subscription? Well I’m shocked, but OK I can help you out. Just send me an email to


and I can fix you up with a code good for 30 days during which you can watch my courses, or any of the fine courses at Pluralsight. (If you want to see all my courses, just click the About Me link at the top, or go to http://arcanecode.me).

If you have some free time during the holiday season, and want to avoid the in-laws, then curling up with a great book (like the SQL Server 2016 Reporting Services Cookbook) beside the yuletide fire can be a great way to spend your time!

SQL Sever 2016 Reporting Services Cookbook has arrived!


SQL Server 2016 Reporting Services Cookbook by [Priyankara, Dinesh, Cain, Robert C.]I’m proud to announce my latest book, the SQL Server 2016 Reporting Services Cookbook, has been released! This was a real labor of love, it consumed most of my summer and well into the fall.

This book was published via Packt Press, and my coauthor was Dinesh Priyankara (blog | twitter).

In this book we cover recipes for almost all aspects of SQL Server Reporting Services. What’s inside? Just take a look:

Chapter 1 – Getting it Ready – Configuring Reporting Services.

Chapter 2 – Authoring Reports with SQL Server Data Tools

Chapter 3 – Advanced Report Authoring with SQL Server Data Tools

Chapter 4 – Authoring Reports with Report Builder

Chapter 5 – Improving User Experience – New Designing and Visualization Enhancements

Chapter 6 – Authoring Reports with the Mobile Report Publisher

Chapter 7 – Consuming Reports – Report Access Enhancements

Chapter 8 – Reporting Solutions for BI – Integration

Chapter 9 – SharePoint Integration

Chapter 10 – Administering and Managing Reporting Services

Chapter 11 – Securing Reports in Reporting Services

Chapter 12 – Custom Programming and Integration to .NET applications

That’s a lot of great material, over 500 pages of Reporting Services fun.

You can get the book through the publisher site:


or use this shortcut: http://bit.ly/ssrscook

You can also get it on Amazon.


Or use the shortcut: http://bit.ly/ssrscookbook

Note as of this blog post Amazon has the Kindle version ready, the print version still shows as a preorder, but that will be out shortly. If you want the print version consider going to the publisher site as you can get both the print and e-book version for one low price.

I want to thank my coauthor, Dinesh, who did a great job on his half of the book, as well as in designing the overall contents. Also a shout out to our editor, Amrita, who kept us in line and on track.


SSRS 2012 Report Manager can’t load Microsoft.ReportingServices.SharePoint.ObjectModel

So I did it again, I broke my SQL Server. Well, sort of. I have a Hyper-V VM of Windows Server 2012R2 I use for development. On it I had SQL Server 2012 Developer Edition with all the latest service packs. I recently needed to do some work with 2014 as well, so installed SQL Server 2014 Developer Edition side by side. Everything seemed happy, until I opened up the SQL Server 2012 Report Manager webpage. It looked OK at first, but when I started clicking on things I started getting this error:

System.Configuration.ConfigurationErrorsException: Could not load file or assembly ‘Microsoft.ReportingServices.SharePoint.ObjectModel’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference

Icky. So a web search turned up one hit, a connect item filed by Brian Judge:


At the bottom, Brian gives the clue on how to fix the issue when he says:

If I change the redirect to stay on for the following policies then the problem appears to be resolved:






Alas, there are no specific instructions on just how to change the redirect. For those not familiar with the way these things work, I wanted to amplify his fix.

First, open a command window in administrator mode. I used the one that came with Visual Studio (the Developer Command Prompt for VS2012).

Next, change directory by using the “cd” command to the first item in the list above. (Click on the pic for a bigger image, should you have poor eyesight).


Using the DIR command, we can see one directory with a version number followed by what appears to be a hash value of some type. Issue another CD into that folder.


Using the DIR command again you will find two files in that folder:


Use notepad to edit the one with the .config extenstion.


When it appears, you will see something like:


Simply change the number in the newVersion from 12, to 11.


Repeat the steps for all four of the folders in the list above.

Next, and this is important kids, you need to stop and restart your SQL Server 2012 Reporting Services service, or simply reboot the computer. After that, your SSRS 2012 Report Manager should start to behave normally again. I’ve also tested the 2014 Report Manager, and it seems to work fine after the changes were applied. (In theory it shouldn’t have been affected, but you can never be too careful).

If you found this post useful, do us a favor. Go to the Microsoft Connect article linked at the top and give it an up vote, so Microsoft will begin to take notice. Also thanks again to Brian Judge (whom I do not know but hope to meet) for filing the original bug and giving the clue to fixing it.

SQL Saturday #111–Atlanta

Today I’m presenting not one but two sessions at the Atlanta SQL Saturday. I wanted to provide copies of my slide decks here.

Configuring SQL Server 2012 Reporting Services

The Decoder Ring to Data Warehousing / Business Intelligence

Hope you enjoyed the sessions, and thanks for coming.

SSRS Training Resources

I’ve been asked to provide links to some useful resources for learning about SQL Server Reporting Services. Below are a list of my favorite blogs, books, and other sites to learn from.

A quick disclaimer, some of the links below are by co-workers or other people I have an affiliation with, financial or otherwise. That’s because I’m lucky enough to work with some of the best people in the field. Also, in the case of the books I’ve linked to the Kindle version where possible, mostly because I’m a Kindle junkie. There are paper versions of the books, and you are free to buy from your favorite retailer.


Microsoft SQL Server 2008 Reporting Services Step by Step – A great beginner book, loaded with good examples.

Pro SQL Server 2008 Reporting Services – This book goes much more in-depth with SSRS, delves into many advanced topics.

Microsoft SQL Server Reporting Services Recipes – 2008 or 2012 version of book. This is a great book, especially if you are doing Business Intelligence reporting. Note Amazon says the 2008 version is no longer available in the US, but I’m betting you can find it in your local bookstore or from other retailers. The 2012 version is available for pre-order.

Applied Microsoft SQL Server 2008 Reporting Services – Great book, like the book above covers many aspects of SSRS including BI reporting. Note Amazon only sells the paper version, you can also get it in PDF format direct from the publishers website.

Professional SQL Server 2012 Administration – I mention this book because I wrote the chapter on SQL Server Reporting Services. I don’t go deep into creating reports, although I briefly cover Report Builder. I do go into configuring SSRS and how to do scale out deployments, the total chapter is around 50 pages.


Paul Turley – Paul is an active blogger and fellow Microsoft MVP. He is also co-author of the Reporting Services Recipes book I listed above.

Tep Lachev – An active blogger, Teo is not only a good resource for SSRS but for other BI tools such as PowerPivot. He is also author of the Applied Microsoft SQL Server 2008 Reporting Services book, listed above.


Pragmatic Works Webinars – On our website we have a big catalog of past webinars (all of which are free to watch), many of which focus on SSRS.

Pluralsight – Pluralsight has an extensive catalog of courses, including some great SSRS content. It’s subscription bases so there is a modest fee (starts at $29 US per month last I checked) but well worth it for the training you can get. There’s also a free trial.

For a quick link direct to this post, you can use http://bit.ly/arcanessrs

SSRS Quick Tip – An item with the same key has already been added

I was in the process of creating a new report in SQL Server Reporting Services today. I was loading my dataset from a stored procedure, and when I hit the “Refresh Fields” button I recieved the following error:

“Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct.”

When I clicked the details button I got this further information:

“An item with the same key has already been added.” Here’s a screen shot of my error.

Well this had me scratching my head, as I had made sure to run the stored procedure, and it executed with no errors. After doing some considerable research I finally found a question in the Technet forums that was tangentially related to the error. This gave me the clue to figure out what I had done.

In my stored procedure, I had inadvertantly included the same column name from two different tables. My query looked something like:

SELECT a.Field1, a.Field2, a.Field3, b.Field1, b.field99
FROM TableA a JOIN TableB b on a.Field1 = b.Field1

SQL handled it just fine, since I had prefixed each with an alias (table) name. But SSRS uses only the column name as the key, not table + column, so it was choking.

The fix was easy, either rename the second column, i.e. b.Field1 AS Field01 or just omit the field all together, which is what I did.

As it took me a while to figure this out, tought I’d pass it along to anyone else who might be looking.