Let me get this out of the way upfront before we get to the good stuff.
**This is not yet officially supported by Microsoft. We will do an official post when it is**
There we go.
While you can’t run the front end of SQL Server Reporting Services on Linux, many folks would potentially like to host the backend catalogs on SQL Server on Linux. I was wondering over the weekend if this worked quite yet, considering that SQL Server on Linux had just introduced SQL Server Agent support as of CTP 1.4. So, thanks to Microsoft Azure and some spare time, I decided to give it a go.
First, I used the SQL Server vNext on Red Hat Enterprise Linux 7.2 image in Azure to setup my Linux VM. This is the easiest way to get started by far, and there is a complete walkthrough how to set this up end to end here – https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-azure-virtual-machine
I used PuTTY to connect to my Linux instance via the IP address, and I didn’t install the SQL Server Tools on the Linux box.
You’ll need to install the SQL Server Agent on the Linux box as well, which I did by following the steps here under the part titled “Install on RHEL” – https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-sql-agent. Go ahead and do this right after you get SQL Server up and running and are still logged into the machine with PuTTY.
To confirm everything was up and running properly, I connected to the server using SQL Server Management Studio on my local PC. Everything looked good so far.
Now, I had to setup my new SSRS instance on a separate machine. I did this using an Windows Server 2016 Azure VM and simply installing the latest Technical Preview from January on it. Next up, you need to set the database catalog location in the Reporting Services Configuration Manager. Keep in mind, you’re limited to using SQL Server authentication to connect to the Report Server database in this scenario (and SQL Server on Linux in general right now). Everything looked good until I got an error at the “Generating rights scripts” part of the config process for the new database.
I figured I was stuck here until I found this really old blog post from Adam Saxton about the error message I was getting. The blog post itself wasn’t relevant (sorry Adam), but the very last comment in the thread WAS helpful from Carlos Shepardos.
“If you use a SQL alias to connect to the SQL Server server you have to ensure that the local computer is also able to resolve the SQL alias name via a DNS resolution request. If the local computer is not able to do this you get the error message shown above.
The easiest way to ensure the SQL alias name is resolvable to the IP address of the SQL Server is to create an A record entry in DNS or add a line to the local hosts file.”
So with that in mind, I went to my HOSTS file on the server and added an entry for my SQL Linux instance. You can navigate the the HOSTS file on your RS server here – C:\Windows\System32\drivers\etc
I then used that name instead of my IP address for my SQL Server instance entry for Reporting Services, and the wizard finished without issue.
I navigated to my report portal, and it loaded just like you’d expect.
To test the SQL Server Agent, I created a simple report and dataset while also setting up some subscriptions and cache refresh plans. Sure enough, they ran successfully and the jobs showed up as expected when I looked in SSMS as well!
As I mentioned earlier, this still isn’t officially supported quite yet, but I was able to use it without any issues in my (admittedly limited) testing. Would love to hear about your experiences trying this scenario out as well. Thanks for reading!
Christopher Finlan 