SQL SSRS Express Edition Cannot Connect to Azure SQL Databases

This has bit a couple of clients recently, figured I'd put out a PSA.

Express editions of SQL Server Reporting Service, from SQL 2016 on down, cannot connect to Azure SQL Databases. Turns out, getting something for free does have some significant limitations.

For example, you'll see an error message "The Report Server has encountered a configuration error" on a data source page, when creating a new SSRS data source in the Report Manager website. What you may have not noticed on this page was the possible values in the Data Source Type drop down list.

In express edition, "Microsoft SQL Server" is the only option. In Standard and higher editions, there are many data types to choose from, including "Microsoft SQL Azure". Remember that Azure SQL Databases can't accept OLEDB connections.

But why? On this page (and see caption right), Microsoft lists a large number of "Built-in data extensions," Azure SQL Database included. The explanation lies here, where under possible "Supported data source", Express edition only supports Express edition, while Standard and above supports "All SQL Server editions."

This is what you're looking at in SSRS Express:

This is what you could be doing, in SSRS Standard or higher:

So what's the solution?

Upgrading the edition of a SSRS Express edition instance in-place is possible. 

Remember you can install various SQL Server features (Database, RS, IS, AS) on different Windows servers, each only once, as part of your license. So if you already own a Standard+ edition SQL Server somewhere but aren't using the SSRS feature, you now have a good use for it. 

Myth BUSTED: The above is incorrect! In the Licensing Guide for each recent version of SQL Server you'll find this: "The software components of a single SQL Server 2016 license cannot be separated. Any OSE running any of the licensed components of SQL Server 2016, requires a license. For example, if the SQL Server DB is deployed in one OSE and SQL Server RS is deployed in another, both OSEs must be fully licensed for SQL Server 2016 accordingly." The language was different and less specific for SQL 2005.

There are also a large number of fantastic (and also fantasy) ideas that developers will have, including SSRS alternatives. Listen to them, sure. But keep in mind that if your platform is SSRS and all the features that come with it, you will need to pay for it at some point. SQL Express edition and never been and never will be intended or suitable for standalone production usage. 

"SQL Server Permissions and Security Principals" at SQLSaturday #560 Charlotte 2016

I'm proud to have been a part of SQLSaturday Charlotte 2016 this year, and happy that I was joined by a number of my colleagues from Sparkhound. We're establishing a presence in Charlotte and already have a couple big clients in the area, so Sparkies from both Baton Rouge and Charlotte joined me this weekend.

My presentation slide deck and demo scripts can be downloaded via the SQLSaturday website here.

The session is a ground-floor introduction to SQL Server permissions starting with the basics and moving into the security implications behinds stored procedures, views, database ownership, application connections, contained databases, application roles and more. This class is perfect for DBA's, developers and system admins, and we'll cover security basics for Azure SQL Database as well.

As a SQLSat organizer in Baton Rouge myself, I know exactly how much work and sweat equity it takes to put on a SQLSaturday event. The Charlotte team knows what they're doing after five years or so, and did a great job in 2016. Excellent work by the entire organizing team!

"SQL Server Permissions and Security Principals" at SQLSaturday Charlotte This Weekend!

I'll be presenting my talk on SQL Server Permissions and Security Principals this weekend at SQLSaturday #560 Charlotte, NC.

As a SQLSat veteran and organizer, I regularly receive feedback that there needs to be "more" content at the elementary level for new folks in the field, students, etc. I try to give this presentation as often as possible to make sure there is useful and practical content at any community conference for entry-level technical folks. They are, after all, our future coworkers.

The session is a ground-floor introduction to SQL Server permissions starting with the basics and moving into the security implications behinds stored procedures, views, database ownership, application connections, contained databases, application roles and more. This class is perfect for DBA's, developers and system admins, and we'll cover security basics for Azure SQL Database as well.

This will be my first time at SQLSat Charlotte, looking forward to meeting everyone there. Fun Fact: this will actually be my first time speaking at a SQLSaturday in the US Eastern Time Zone.

My colleagues from Sparkhound will also be presenting in the same room immediately afterwards, Using PowerBI and SQL Server to Create Management Cadence Dashboards.

See you there!