“Your Google account can’t go here” when logging into Chromebook with family link

I didn’t find a good answer online when I ran into this issue, so hoped I can be the answer for people who have this same issue in the future.

We recently got Chromebooks for our kids to support their remote school for the rest of the school year and I wanted to set them up with monitoring – Google has a tool to do that called “Family Link“. We set up accounts for our kids, but unfortunately, we received an error when we tried to log into the Chromebook:

There weren’t any details (and my parent account could log in just fine) – searching online came up with a bunch of frustrated posts that were almost a year old and talking about the change from supervised accounts and how Family Link didn’t work on Chromebooks. I received that error when trying to add a new user or log in – I couldn’t complete either process.

  Looks like your google account can't go here

The secret ended up being that the ChromeOS on my Chromebook was CRAZY out of date – even though it was ordered brand new from Amazon, it was running ChromeOS 56 when the CURRENT VERSION (as of March 2020) IS 80! All of the documentation I saw on Family Link said it that required at least ChromeOS 71 and this obviously wasn’t cutting it. I would have expected a brand new Chromebook to be updated to something near current, but obviously not – according to https://www.chromium.org/developers/calendar, version 56 was released in November 2016 and is over 3 years old at this point

ChromeOS version 56, installed on our Asus Chromebook when we received it.

It doesn’t prompt you to automatically update to a newer version of ChromeOS – you have to do it manually. To do that, go to the system info in the lower-right corner and click on the gear to open settings, then click “About Chrome OS” at the top, then “Check for updates” and apply anything it shows. In our case, it updated to 72, rebooted, and the applied a few more security updates and a few more reboots before it finally landed on 80. Once it did, I could log in with the family link child account, approve my child’s sign on, and we were on the way with no more problems.

Good luck!

Power BI Server – Resolving “Web Portal URLs and Web Service URLs don’t match”

After replacing an expired SSL certificate on Power BI Server, we were no longer able to browse the site – we received an error that “The report server isn’t configured properly… The report server Web Portal URLs and Web Service URLs don’t match”:

We hadn’t changed the Power BI Server settings aside from updating the certificate, but now couldn’t load the site without getting this error message.

We were able to resolve it by manually updating both URLs (Web Service and Web Portal) in Report Server Configuration Manager to the Fully Qualified Domain Name (FQDN) – normally, the HTTP version of the URL is set to “All Assigned” (and shows the server name) and the HTTPS version shows the name on the certificate (in our case, both the old and new certificate had the FQDN). This is how it looked on the other servers that worked:

No obvious errors, but manually changing the HTTP URL for both Service and Portal to FQDN fixed the Power BI Server site – things started to work again instantly. This bothered me, though – none of the other servers needed this setting changed, and they were all perfectly fine with the server short name in the HTTP URL.

Cause/Solution:

The issue ended up being Internet Information Services installed on the same server – even though the primary site was disabled, it was bound to port 80 on all IPs without a host name, so it was claiming the “HTTP://+:80” that Power BI Server was also trying to bind. Because this bind was failing, Power BI couldn’t register matching URLs and gave us the error.

To resolve it, we added a phony hostname to the Default Web Site in IIS (even though it was stopped and shouldn’t have caused an issue) so that that it didn’t claim everything at Port 80:

Once this was done, we set the Power BI configuration back to “All Assigned” and it re-registered its own URLs, and then things started working perfectly again. I’m not sure if IIS was installed by our server admins during our certificate update, or if some other configuration change caused IIS to change how it registered the URL, but this resolved this issue.

Hopefully it helps somebody else!

Installing Access Database Engine 2016 x64 alongside Office x86

In Power BI, you can open XLSX files without any additional drivers, but opening XLSB files requires you to first install the Access Database Engine driver (available for free at https://www.microsoft.com/en-us/download/details.aspx?id=54920) or you’ll get an error like this:

We encountered an error while trying to connect. Details: "Excel Workbook: the 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit bersion of the Access Database Engine 2010 Access Database Engine OLEDB provider may be required to read.
‘Microsoft.ACE.OLEDB.12.0’ provider error message

If you have Microsoft Office installed, you already have this component, but most people use a version of Office that’s x86 – since Power BI is x64, you’ll need the x64 version of the driver as well. However, if you try to install it with Office x86 on your computer, you’ll get this error and you’ll be stuch:

Microsoft Access database engine 2016 x64 Installation Error screenshot - "You cannot install the 64-bit version of Microsoft Access Database Engine 2016 because you current have 32-bit Office products installed. If you want to install 64-bit Microsoft Access Database Engine 2016, you will first need to remove the 32-bit installation of Office products. After uninstalling the following product(s), rerun setup in order to install 64-bit version of Micorsoft Access Database Engine 2016: Office 16 Click-to-Run Extensibility Component"
Microsoft Access database engine 2016 x64 Installation Error screenshot

There’s a way to install both! To avoid this error and install the x64 database engine without removing Office x86, you need to download the installation for the Access Database Engine x64 and run the installation with a command line switch:

AccessDatabaseEngine_X64.exe /quiet
Command to install Access database engine using the "/quiet" switch to run to installation without an error

Once you run this command, you’ll get a black command window for a few seconds, and then it will close once it’s done. To confirm that it’s installed successfully, you can either check for “Microsoft Access database engine 2016” in your Windows “Programs and Features” window, or you can just launch Power BI and attempt to import an XLSB file!

I haven’t noticed any ill effects to doing this, but if you do have issues, comment below.

SQL Differential backups failing with “current database backup does not exist” error

I recently set up an Azure VM and installed SQL Server 2017 – it worked great until it came time to set up the database maintenance plans. My normal routine is to set up a weekly full database backup, daily differential database backups, and hourly log backups. Thanks to Azure, I was able to send them to URL so they went directly to my BLOB storage container.

After setting up the backups, I tested them and everything went time – I ran the weekly full successfully, then the daily differential, then the logs, and didn’t get any errors. However, that night I got an email that the differential had failed, and the agent log had this note in it:

Code: 0xC002F210
Source: (Maintenance plan)
Execute SQL Task
Description: Executing the query "BACKUP DATABASE [MyDatabase] TO Uā€¦" failed with the following error: "Cannot perform a differential backup for database "MyDatabase", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I had tested everything earlier, so I wasn’t sure why the differential backup was failing now (the log backups were working without issue the whole time, so it wasn’t storage). I executed the differential again and it failed with the same error. I ran the full backup successfully and then tested the differential backup a few times – it now ran successfully each time. I shrugged and went on my way, chalking it up to something going wrong, only to have the differential backup fail again that night with the same error.

To see what was going on, I took a look at the backup sets and noticed some backups I hadn’t scheduled going a device I didn’t recognize (all of my scheduled backups were to URL, so they were easy to spot because they all start with “HTTPS://”):

select top 100 *
from msdb.dbo.backupmediafamily mf
where TRY_CONVERT(uniqueidentifier, physical_device_name) is not null

The GUID indicates a backup taken through the VSS service – in this case, they were being created externally by the Azure VM Snapshot process. However, these backups are by default official database backups, and they interrupt the database backup chain – had I attempted to restore one of my full database backups followed by the log backups, I would have found that I couldn’t restore past the VSS backup timestamp because I didn’t have access to that media.

The good news is that there’s a registry key you can use to tell the VSS service that database backups should be taken as COPY_ONLY (meaning they don’t interrupt your backup chain). You can do this with the following registry key:

Windows Registry Editor Version 5.00 

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BcdrAgent]

"UseVSSCopyBackup"="True"

This tells the server that when VSS takes a snapshot, it should do it as a non-authoritative copy-only backup. This also means it won’t reset any attributes in the VM that say a backup has happened, but in my case, I didn’t want that to happen.

To learn more, check the Microsoft articles that provide details about snapshot backups for Azure VMs – neither refers to the error message specifically, but they provide some additional details about configuration. They’re https://docs.microsoft.com/en-us/azure/backup/backup-azure-vms-introduction#snapshot-creation and https://docs.microsoft.com/en-us/azure/backup/backup-azure-vms-troubleshoot#troubleshoot-vm-snapshot-issues (both linked to the most relevant section).

I hope this saves you the hours of aggravation that it cost me – let me know if it helps or if you have any issues!

Slicing overlapping subgroups of data with a synthetic “Filter Table”

I saw a post from Marco Russo (Twitter) showing a trick to use a slicer to filter across multiple columns at once in Power BI with some DAX and a new dynamic filter table. I’ve done something similar in the past to use a slicer not to slice data directly, but to change a view in the data or change filtering criteria indirectly – I wanted to share an example here to show a slightly different use case than Marco’s for a similar technique.

I want to create a slicer that switches the dataset between different subsets of data, but with overlap – like just my sales, sales for my group, and sales for the company. This is normally accomplished with a calculated column for the group the data is in, along with a slicer where if you want to see everybody, you have to select all three values like this:

But what if you want to create slicer options that aren’t exclusive? Where selecting “My Department” includes you too? Here’s how you do it!

In my example, I’ll need a fake sales and person table, so I’ll just use the “Enter Data” interface to create them:

Creating a sales table
Creating a user table (just a list from the sales table)

At this point, we could just join them on name and add some slicers, but here’s where we get more advanced by creating the filter table. The names repeat, but they show once for each slicer option they belong with, so whatever you select, you’ll see the relevant people:

Creating the filter table with all the slicer options in it

Once you have those three tables, you need to link them up – note the two-way relationship between the filter table and the person table. Without that, filtering on the last table won’t flow through to the sales table and our slicer won’t work:

Create the relationship and make sure the filter-user link to bi-directional

That’s all it takes – now you can create a slicer on the “Filter” column in the “Team Filter” table, and you’ll get whatever non-exclusive subset of the data you’re looking for!

I’m looking at only Bob’s sales
I’ve added Sally’s sales, but can still see Bob
Now I’m looking at everybody!

I made the slicer single-select in this example, which I do to avoid confusion – while this still works if you leave it a normal multi-select, it can lead to some confusion since users will expect the options to be exclusive and if they’re already got “Everybody” selected, neither of the other two options will do anything šŸ™‚

If you want it, grab the PBIX file here.

I hope this is helpful to somebody – if you have any suggestions or other use cases for this, please add them below!

Choosing which version of Power BI Desktop opens a PBIX file

When you have both the “Report Server” and “Regular” version of Power BI Desktop installed, double-clicking on a PBIX file will open it in whichever one you updated last (most likely the non-RS version, since its updated every month). Unfortunately, I want to open PBIX files in my RS version, since that’s where most of the development for my job occurs.

I got tired of fixing the shortcut links every time I updated the Desktop, so I just added two new right-click options for PBIX files – “Start in RS version” and “Start in regular version. When it’s done, it looks like this (with the new options highlighted in red):

To add them, just take the code below and save it into a file with a .REG extension, and then double-click it to add it to your registry.

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\PowerBI.File]
@="Microsoft Power BI Desktop Document"

[HKEY_CLASSES_ROOT\PowerBI.File\DefaultIcon]
@="\"C:\\Program Files\\Microsoft Power BI Desktop RS\\bin\\PBIDocument.ico\""

[HKEY_CLASSES_ROOT\PowerBI.File\shell]

[HKEY_CLASSES_ROOT\PowerBI.File\shell\Open in RS Version]
@="Open in RS Version"

[HKEY_CLASSES_ROOT\PowerBI.File\shell\Open in RS Version\command]
@="\"C:\\Program Files\\Microsoft Power BI Desktop RS\\bin\\PBIDesktop.exe\" \"%1\""

[HKEY_CLASSES_ROOT\PowerBI.File\shell\Open in regular version]
@="Open in regular version"

[HKEY_CLASSES_ROOT\PowerBI.File\shell\Open in regular version\command]
@="\"C:\\Program Files\\Microsoft Power BI Desktop\\bin\\PBIDesktop.exe\" \"%1\""

[HKEY_CLASSES_ROOT\PowerBI.File\shell\Open]

[HKEY_CLASSES_ROOT\PowerBI.File\shell\Open\Command]
; Uncomment this one if you want the public version to be the default
;@="\"C:\\Program Files\\Microsoft Power BI Desktop\\bin\\PBIDesktop.exe\" \"%1\""

; Uncomment this one if you want the server version to be the default
@="\"C:\\Program Files\\Microsoft Power BI Desktop RS\\bin\\PBIDesktop.exe\" \"%1\""

You can also download the .REG file (you may need to right-click and save it).

(Update – added the switcher at the end so you can choose which edition opens up the PBIX file when you double-click on it!)

Let me know if you have any issues!

Import first sheet of an Excel workbook into Power Query, regardless of its name

I recently had to import a large number of Excel workbooks into Power Query (using “From Folder” – if you haven’t used this feature, it’s a huge time-saver when you’ve got a ton of matching files to import). The problem was that each workbook had a different name for the first tab and the import process gets data from tabs with the same name (assuming all your files match).

By default when you create a workbook, Excel names the first sheet “Sheet1”, but if the files you’re importing have sheets with different names, you can tell Power Query to load the first sheet of each file, regardless of the name – here’s the process (skip to step 5 to see the specific M-query solution):

  1. Select “Get Data” -> “From File” -> “From Folder” – this lets you import all the files from a folder at once (assuming their format matches).
  2. Select the folder where your files are stored (I have three files in the folder in this example).
  3. Select an example file and which sheet you’d like to import – this is what you’ll use to create your transformations (before the files are merged together) – in this example, my sheet is called “Bob’s sheet” (which is specific to just that single file).
  4. When you select “Import”, you’ll receive the error “[Expression.Error] The key didn’t match any rows in the table.” – this is because the other workbooks don’t contain any sheets with that name (though the error is a bit cryptic).
    If you double-click on the “Data Files” query with the error, you can see that it loaded the data from the first file, but nothing from the others:
  5. To correct this issue and tell Power Query to just use the first tab in the workbook in every case, open the “Transform Sample File from Data Files” query and click “Advanced Editor”.
  6. You’re now looking at the M-query behind this object (if you want it, here’s a great video on M if you’ve never worked it before and want a primer – I watched a bunch of different videos and this one really connected the dots for me). The section with the sheet name is highlighted:
    Replace the highlighted text with “Item=Source{0}[Item]” so that it looks like this:
    (The “0” refers to the first sheet – if you want the second sheet, you’d use “1” and so on)
  7. Once you make change, you can see in the Power Query window that it’s now getting data from every file.
  8. Once you click “Close and Load” to get back to Excel, click on the “Refresh All” button to force a data refresh and you’ll see it import all the files, regardless of their sheet name:

That’s it – you’re pulling in all the workbooks regardless of their sheet name!

I’d never used the “From Folder” feature, but I’d definitely recommend trying it out – it was a huge time-saver for me and allowed me to import hundreds of matching Excel files into a single model in minutes!

Update: When you’re using older XLS files

Somebody brought to my attention that this technique doesn’t work when you’re using XLS files, only when you’re using the XLSX/XLSB format (I won’t call it new as it’s been around since Office 2007, but still – newish).

If you’re using XLS files, you need to change step 6 slightly as M uses a different syntax to drill into sheets in those files. Here’s what the M query looks like when you drill in manually:

Replace the highlighted portion ([Name=”Sheet1″]) with just an index number – “0” in this case:

Once you do that, Power Query will drill into the sheet in the the first row of your dataset.

However, I did notice an issue – the sheet in position “0” wasn’t the first sheet in the workbook. I added a number of sheets in my Excel doc, but I did it them in a random order (I renamed “Sheet1” to “Original” and then created the others in the order they’re named):

However, when you import the workbook in Power Query, the physical order isn’t reflected – it seems to be the order they were created, but with the original sheet always last (not sure what the thinking is there – maybe something in the physical file format?):

If you want the “Original” sheet, you can use the Power Query step “Keep Bottom Rows” and give it a value of “1”:

If you want one of the other sheets, though, and the name keeps changing, I’m not sure how you’d handle ensuring you get the right one. If you’ve got suggestions, I’d love to hear them and I’ll include them here!

Update 2

Another question from the comments – what if you want to get the last sheet in the workbook, not the first one? We can make that happen with one additional line right after Source and right before the line where you select the sheet:

"Kept Last Rows" = Table.LastN(Source, 1),

This is the command you get when you select “Keep Rows” -> “Keep Bottom Rows” from the toolbar up top, and you’ll be left with a single row to drill into. You’ll still need to drill into it as Row 0 so that it will work no matter what the sheet name is.

Client certificate error with Office 365 and Blackberry Hub

Our office recently implemented two-factor authentication with Office 365 and it caused some issues with my Activesync account in Blackberry Hub (running on my Android Pixel 2. I would receive email intermittently – an hour on, a few hours off, and then a random bunch of email again. Then I started receiving the following error message:

The client certificate for (your mail account) must be allowed for use in establishing secure email connections. Do you want to do this now? [Password may or may not be required.]

Before the two-factor change, out email configuration required that we user a client certificate that was issued to our phone through Microsoft Intune (called “Company Portal” on our phones). Once two-factor was enabled, leaving the certificate selected gave that error message. Instead, email needed to have the certificate removed as follows:

Here was the big change: instead of my domain password, Hub required me to generate an app-specific password using the following process:

https://support.office.com/en-us/article/create-an-app-password-for-office-365-3e7c860f-bda4-4441-a618-b53953ee1183

The mobile version of office.com doesn’t allow you to generate an app password, but you can tell the Chrome on your phone to view the full desktop version of the site and it will allow you to generate a password. Copy it, use it in Blackberry Hub (without the certificate selected), and you’re back to receiving your email!

Passing multi-value report parameters to SSRS using the URL querystring

Passing parameters to reports in SSRS using the URL can be really helpful, especially when you want to link to a report with some of the data aleady filled out. We had an email alert that some accounts in active directory were incorrect, and we wanted a way to link to a report that provided some additional detail for those accounts. Querystring parameters aren’t very well documented (and I couldn’t find anything on multi-value parameters), and I hope this saves you some time if you’re in the same situation.

There’s a pretty good reference on Technet, but it’s got some gaps (for me, the biggest was even mentioning multi-value/multi-select parameters). The basic syntax is pretty straightforward – normally, a report is called with a URL like the following:

http://YourSsrsServer/Reports/Some%20Folder/YourReportName

From there, you can select the parameters and run the report. To provide a link to the report with the parameters already filled out (if they’re blank or if you gnat to override the defaults), just append this to the end of the URL:

?ReportParam1=SomeValue&ReportParam2=SomeOtherValue

You can pass any type of parameter with this same syntax – text, datetime, integer, float, or boolean (use Yes/No). Your full url would look like this:

http://YourSsrsServer/Reports/Some%20Folder/YourReportName?ReportParam1=SomeValue&ReportParam2=SomeOtherValue

To do a multi-value parameter, you repeat the name of the parametr to provide it multiple values:

http://YourSsrsServer/Reports/Some%20Folder/YourReportName?ReportMultiParam=SomeValue&ReportMultiParam=SomeOtherValue

If this is the only parameter on the report and it doesn’t need any other user input, the report will automatically execute because it has everything it needs. If you want the report to wait for the user to hit “View Report”, you need to leave at least one parameter empty.

Two quick notes if you’re having trouble:

  • Use the parameter name, not the prompt text. If your parameter is called “Office” but the prompt says “Which office?”, use “Office”. Parameter names aren’t always intuitive and you may need to open the report in Visual Studio to get these names if you can’t guess them.
  • In the same way, you need to use the field’s value, not the label. If the dropdown contains the full name of the office, but the value behind the list is the office number, you’ll need to provide the numbers on the querystring.

Good luck!

Microsoft Connect item for an SSRS pre-report canvas

Current state:

About six months ago, I submitted a connect request for SQL Server (my first one) to suggest an improvement to the SSRS interface – a customizable canvas that’s displayed before an SSRS report renders, while parameters are being entered. My issue was that there was information I wanted report users to have (like expected wait time, suggestions for the parameters, the report title and a short description that might help them, etc.) and there was no way to get it to them.

With a giant empty screen staring at them, it seemed like a great place to add that information:

SSRS Report pane current layout

The suggestion:

Ideally, while the user is considering parameters, I’d love to see something like this (ignoring that it’s not a super-photogenic report):

proposed-layout

This would be a great place for details about the report that help the user before (or while) they run it, like:

  • The name of the report (not always clearly visible in the URL or browser title bar, especially when you’re Sharepoint-integrated. Especially helpful if they’ve left multiple report tabs open or if report generation fails, they’re often left with a white screen and no easy way to tell the tabs apart.
  • A short description of the report, including when you’d use it or any notable caveats.
  • An estimated runtime – this isn’t always possible exactly as parameters vary, but even a general estimate (or better yet, an average or a 95% confidence range, pulled from the report server database) would be better than the complete blank they get today.
  • Suggestions/restrictions on the parameters – If a report can’t be run for all offices at once, you can say so here. If running it for everybody at once means you can’t export to Excel because it’s too large, let the user know.
  • A mock-up/screenshot of the report – so they user can see if it’s the one they want. They may be looking for a report with a specific chart, and waiting for 3 minutes for each one to render may not be ideal.
  • A link to any other documentation/resources, or related reports, or pretty much anything else.

It wouldn’t need to be anything fancy – I’d even settle for text you can format, or some kind of additional panel on the report that you can design, but isn’t rendered with the report itself. Access to data (to pull estimated on execution times or dynamic owner/description details, for example) would be nice, but not required – I’d rather have a design-only, no-data canvas than nothing at all.

The plea:

t-sql-tuesdayThanks to the motivation from the folks at Brent Ozar Unlimited, I’m asking for your help in voting for this suggestion (if you think it would be beneficial, of course). As part of T-SQL Tuesday, they’ve asked people to find a connect request that they’d like to see filled and post about it, so here I am asking humbly for your support. : )

If you like the idea, please vote for it – if you don’t, please comment and let me know why not (I’m always open to understanding the opposition). There are a ton of great Connect items out there and this is only one, but I think it would help out (especially for our longer-running reports).

Thanks for your help!

Direct URL for the Connect item: https://connect.microsoft.com/SQLServer/feedback/details/2809098/ability-to-customize-report-splash-screen-during-parameter-submission

Side note:

I’ve attached the two images (current/proposed) to my connect item three different times and they’re still not showing up there – if anybody knows how to get those images published, people can see what I’m talking about when I describe my suggestion! : )