SSRS Azure VM Checklist

Recently I set up an Azure VM running SSRS for my students to host their database and report projects. My goal was to set up a custom domain name and use SSL. I ran into a few issues and a couple of wrong paths so I thought this would be helpful for others trying to accomplish the same. I’m not going to do a step-by-step walkthrough, but instead a checklist to help you troubleshoot

1.      Create a VM in Azure.

In this case, I selected the Standard D2s v3 image running Windows 10 Pro. You might want to run a bigger machine or switch to Windows Server. I selected this particular configuration to save costs while also having enough resources to perform well enough for these tiny databases and reports.

When creating the VM, you may want to create a new resource group. Add any other objects associated with the VM to the resource group. That way, if you want to delete the VM, you delete the resource group so that all objects are cleaned up.

2.      VM Configuration

In order to use a custom domain, make sure that the IP address is Static. You can find this setting on the VM’s IP properties.

1

You need to make sure that the Network Security Group allows traffic on the ports. Click the Networking settings of the VM and select “Add inbound port rule.” You should allow 443 for SSL, but you may also want to open 80 for testing to make sure you can connect to SSRS without SSL first.

2

Make sure that you also open the Firewall settings inside the VM to allow the ports. This is where I messed up. I had forgotten to open 443.

3.      Software Installation

This list is simple:

  • SQL Server 2019 Developer Edition (Install the Database engine only unless you need additional features)
  • SQL Server Reporting Services
  • SSMS or Azure Data Studio

Configure SSRS as you would in any other situation. You may need to specifically grant administrator rights to your account in the SSRS web portal while remoting the VM. Run the browser as an administrator to do this. Note that if these are production reports instead of for class projects, you’ll need a different version of SQL Server.

4.      Domain Name

I purchased a new domain name from GoDaddy.com which took just a few minutes, and it’s less than $20 for one year. To get this to work, I created an Azure DNS Zone. This ties the domain name to the IP address. I followed instructions here and here. I also set up the A and CNAME records in the DNS properties on GoDaddy. For whichever provider you use, you should be able to find some help on their site for managing sites hosted by other companies.

5.      SSL Certificate

I also purchased my SSL certificate from GoDaddy. I used to deal with domain names and SSL certificates 20 years ago, but this took me a few times to get right.

After purchasing, you must generate a certificate signing request (CSR) on your server that is then submit it to the provider. I followed the instructions here to create the CSR. On GoDaddy, there are some instructions found here on requesting the SSL certificate. In this case, follow the steps found at “Request a certificate with a CSR that has already been created.” Using a CSR generated by GoDaddy was one of the mistakes I made.  SSRS could never see the certificate in that case.

If you buy yours from another provider, they should have some instructions. Just make sure that the CSR is generate on the server, not by the provider.

6.      Installing the Cert on SSRS

I’m having trouble finding the resource I used, but you will need to import the certificate from your provider using the MMC snap-in for Certificates. Right-click the Personal folder –> All Tasks –> Import which will launch a wizard. You’ll import the CRT file.

3

Now that the certificate is installed, it must be set up on SSRS but restart SSRS first. I used this article to help me configure the certificate on SSRS.

Once that is done, you should be able to connect to your Web Portal with the new domain name and using https.

Conclusion

It all seems so simple now! While this doesn’t give step-by-step instructions, I hope it is helpful for anyone else trying to accomplish the same task.

Posted in SSRS | Leave a comment

News from Aunt Kathi

Now that writing and editing are a big part of my job at Redgate, I rarely seem to find time to post anything here. Since there is a lot going on over the next two weeks, I thought this would be a great time to post. You can find most of what I write now days here.

new book

Once I’m home from PASS Summit, that will probably be the end of my speaking engagements for the year, but who knows? Contact me to speak at your user group remotely if you need a speaker in December.

PASS_19_Summit_Speaking_Generic_Architecture_Banners_v1-2_Email_Signature

Posted in Life in IT | Leave a comment

May 2019 T-SQL Tuesday #114: Puzzles

This month’s T-SQL Tuesday is hosted by Matthew McGiffen. His challenged is to come up with a puzzle.  My entry involves a simple request that is more difficult to solve than it seems on the surface.

TSQL2SDAY-300x300

I was recently asked by one of my siblings during a family party how to find the row with the maximum count from a dataset. Yes, I do have that geeky of a family. He was working with MySQL, but I figured the solution would be similar to T-SQL. Since neither type of database was handy during dinner, I just explained that you have to separate the logic with something like a subquery.

He had his own data, but I’ll explain with a similar scenario in AdventureWorks. The requirement is to find the customer and order count for the customer or customers who have placed the most orders. You could write a query like this which returns the rows first along with all the other rows.

 

SELECT CustomerID, COUNT(*) CountOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY COUNT(*) DESC;

One row is returned for each customer, not exactly what is needed.

puzzle1

You could use TOP to return just one row, but this is still not accurate since two customers have the maximum count, and only one of them will show up in these results.

SELECT TOP(1) CustomerID, COUNT(*) CountOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY COUNT(*) DESC;

puzzle2

If nesting aggregate functions was possible, you could filter in the HAVING clause. This query won’t work!

SELECT TOP(1) CustomerID, COUNT(*) CountOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
HAVING COUNT(*) = MAX(COUNT(*));

puzzle3

What about using a CTE (common table expression) or derived table to find the customers and counts first? You might think a query like this would work:

WITH Counts AS (
SELECT CustomerID, COUNT(*) CountOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
)
SELECT CustomerID, CountOfOrders, MAX(CountOfOrders) AS MaxCount
FROM Counts
GROUP BY CustomerID, CountOfOrders
HAVING CountOfOrders = MAX(CountOfOrders);

puzzle4

Unfortunately, since it’s grouped by CustomerID and CountOfOrders, the Max(CountOfOrders) is returned for each group. This is not the solution.

What about my favorite type of function, windowing functions? By adding an OVER clause to MAX(CountOfOrders), the overall max count can be returned.

WITH Counts AS (
SELECT CustomerID, COUNT(*) CountOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
)
SELECT CustomerID, CountOfOrders, MAX(CountOfOrders) OVER() AS MaxCount
FROM Counts
GROUP BY CustomerID, CountOfOrders
HAVING CountOfOrders = MAX(CountOfOrders);

puzzle5

The problem is that you cannot directly filter a windowing function. This returns an error:

WITH Counts AS (
SELECT CustomerID, COUNT(*) CountOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
)
SELECT CustomerID, CountOfOrders, MAX(CountOfOrders) OVER() AS MaxCount
FROM Counts
GROUP BY CustomerID, CountOfOrders
HAVING CountOfOrders = MAX(CountOfOrders) OVER();

puzzle6

The beauty of window aggregates is that you can have multiple levels of aggregation in the same query. What about moving the MAX(Count) to the CTE? You may think this looks strange since you can’t nest aggregate functions, but in this case, the window function is operating on an aggregate function!

WITH Counts AS (
SELECT CustomerID, COUNT(*) CountOfOrders,
MAX(COUNT(*)) OVER() AS MaxCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
)
SELECT CustomerID, CountOfOrders, MaxCount
FROM Counts;

puzzle7

The only thing left to do is to add a filter, and this time it will work since the aggregations are completed in the CTE.

WITH Counts AS (
SELECT CustomerID, COUNT(*) CountOfOrders,
MAX(COUNT(*)) OVER() AS MaxCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
)
SELECT CustomerID, CountOfOrders, MaxCount
FROM Counts
WHERE CountOfOrders = MaxCount;

puzzle8

There are many other ways of solving this. You could use a view or a derived table (subquery) for example, but I think this is the best method. Another thing to note is that the solution will require three levels if you don’t use the windowing function. Here is an example:

WITH Counts AS (
SELECT CustomerID, COUNT(*) CountOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
),
MaxCount AS (
SELECT MAX(CountOfOrders) AS MaxCount
FROM Counts
)
SELECT CustomerID, CountOfOrders, MaxCount
FROM Counts
CROSS APPLY MaxCount
WHERE CountOfOrders = MaxCount;

puzzle9

The first level returns the list of customers and counts. The second finds the overall maximum count. The outer query uses CROSS APPLY to join the two CTEs. Now that the aggregation is completed before the outer query, the filter works.

I thought this was a nice puzzle for T-SQL Tuesday because the initial requirements sound so simple. The solution is not difficult but also not intuitive.

 

Posted in Uncategorized | 4 Comments