Connecting to SSAS from Report Builder Query Designer – A Connection Cannot Be Made

Introduction

Recently I was attempting to create a dataset in the SQL Server Reporting Services Report Portal. I created my data source, then launched Report Builder to create my Dataset.

Report Builder connected to my SQL Server Analysis Services Tabular database OK, the Test Connection button worked, but when I tried to go into the query designer I kept getting the error:

A connection cannot be made. Ensure that the server is running.

I knew my server was running, I could connect to it and run queries from SSMS (SQL Server Management Studio). I found some solutions that suggested I change my SQL Server Browser service to log on using the Local System account. Tried it, didn’t work.

I found another solution recommending I add a firewall rule to allow inbound traffic on port 2383, but no love there either.

The Solution

It turned out it was all related to the way I’d formatted my data source connection string in the SSRS Report Portal. I had entered it as:

Data Source = acdev;initial catalog = WWI-SSAS-Tabular

When I used the Test Connection button in the Report Portal, it worked fine. It even let me connect when I launched Report Builder. But when I tried to launch the query designer in Report Builder, it gave me the aforementioned error:

A connection cannot be made. Ensure that the server is running.

I came upon my solution by launching Report Builder, and telling it I wanted a data source embedded in my report. On my first attempt I simply copied what you saw above from the Report Builder, and was faced with the same crushing disappointing result.

On the second try I used the Report Builder feature to actually build my connection string. Report Builder produced:

Data Source=acdev;Initial Catalog=WWI-SSAS-Tabular

And by golly, it worked! I was able to use the query builder to create a DAX query.

To be sure I was still sane, I went back to the Report Builder and replaced my connection string with the one above. Still in the Report Portal, I added a new Dataset which launched Report Builder.

I picked the Data Source I’d just updated in the Report Portal, and this time I was able to get into query builder, create a new query, and save it back to the server as a dataset.

Conclusion

I can only guess it was the extra spaces around the equal signs that were messing things up. I’d added the spaces thinking it made it a bit more readable. Readable, but as it turns out non-functional.

Some of you maybe going “you big dummy” at this point, and perhaps justifiably. I still think it’s odd though that the test connection buttons in multiple tools all worked, yet the query designer crashed.

Regardless, I’m happy I was finally able to find the solution. I’d spent almost five hours on this, so hopefully this will save you a little time and get you back to creating queries.

What is My COM Port?

Introduction

I’m a ham radio operator, and I recently gave a presentation to my local club on how to program your radio using software. To do this, we need to connect our radios using a USB cable.

Most of the radios use a concept called a VCP or Virtual COM Port. You install the VCP driver, then can connect your USB cable to your computer, plug the other end into the radio, and launch the software.

The software will want to know, what is the COM port number you are using. I have a variety of radios, and it seems each cable wants to use a different COM port, and every so often the cable will wind up using a different COM port than it did last time.

So how do you find out your COM port? Well it’s pretty easy if you know where to look.

Finding Your COM Port

The best place to find this is in the Windows Device Manager. Click on the magnifying glass next to the Start menu icon.

I have my Windows toolbar set to hide the search entry box, but some systems will have a text box right next to the search. Which ever way yours is configured, start to type “device manager” into the search.

You should see a pop up like you do in the image above when it finds the Device Manager application. Just click on it to run it.

Scroll down in Device Manager until you find the entry for “Ports (COM & LPT)“. Click the arrow beside it in order to expand the list, and you should now see the COM port for your cable, in this case COM10.

Note that your cable will need to be plugged in for the entry to appear.

Conclusion

There you go, I told you it was easy, the trick is knowing where to look.

While I did write this with my fellow amateur radio operators in mind, there are all kinds of devices that need to use a COM port to access them from your PC. Using this quick guide you can easily find where to look to get the right COM port number for your situation.

Solving “An error happened while reading data from the provider” When Connecting to SQL Server From Visual Studio 2019

Introduction

Recently I was working on a SQL Server Analysis Services Tabular project in Visual Studio 2019. In attempting to connect to a SQL Server database to import data, I got the following error.

An error happened while reading data from the provider: 'Could not load file or assembly 'System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0x80070542)'

Let’s see the steps I went through to get to this point…

Reproducing the Error

Start by opening your SSAS Tabular project in Visual Studio 2019. In the Tabular Model Explorer, right click on Data Sources, then pick New Data Source.

In the Get Data window, pick Database, then “SQL Server database” and click Connect.

In the “SQL Server database” window, enter the name of the server, for example “localhost”. Click OK.

In the credential window, with the default of Windows credential, use Impersonate Account for the Impersonation Mode.

Enter your credentials and click OK.

You get a dialog titled “Unable to connect“.

You get this, despite knowing you’ve entered your credentials correctly. I actually found the solution in a PowerBI issue on Stack Overflow, they were having a similar problem.

The Solution

The solution, as it turned out, worked for both PowerBI and Visual Studio 2019. Simply run Visual Studio 2019 in administrator mode.

In the pic above, I have VS2019 in my toolbar. I right clicked on the icon, then in the menu right clicked on Visual Studio 2019. I then picked the Run as administrator option.

Following the steps in the Reproducing… section above I entered my credentials and clicked OK.

After clicking on OK, instead of the error I got an Encryption Support error, that it was unable to connect using an encrypted connection. I believe that was because, in my case, Visual Studio and SQL Server are both on the same box, in a development VM. As such, I’d not bothered with the overhead of setting up encrypted connection support in SQL Server. In this case I was OK with that so just clicked OK.

Now the Navigator window appeared, and I was able pick a database to import from.

Conclusion

I hope this simple fix works for you. I know I spent forever looking for an answer, and was lucky that trying the same solution that worked for PowerBI, running in admin mode, also worked for Visual Studio 2019.