Unpacking bacpac file throws error

When moving data from a sandbox environment to your dev-box you need sqlpackage to unpack the bacpac file to a database. This could give you an error message stating that the file contains corrupted data:

Skærmbillede 2020-03-24 kl. 12.49.06.png

To get yourself passed this you can download the .net core edition of the SQLPackage from here:

https://docs.microsoft.com/en-us/sql/tools/sqlpackage-download?view=sql-server-ver15

Unzip the downloaded file to a folder of your flavor. In that folder there’s another version of the sqlpackage.exe and that should allow you to continue:

Skærmbillede 2020-03-24 kl. 12.52.33.png

Edit:

After this was posted a new bug started appearing:

The error text is:

*** Error importing database:Could not import package.

Warning SQL0: The source contains users that rely on an external authentication provider that is not supported by the target. These users will be treated as users without logins.

Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 102, Level 16, State 30, Line 1 Incorrect syntax near ‘type’.

Error SQL72045: Script execution error. 

The solution is the same as the above mentioned but just a newer version of the SQLPACKAGE software:

https://go.microsoft.com/fwlink/?linkid=2109019

Azure SQL firewall access

Some things were easier in the good old days before Dyn365 and Azure and some are better. Often the things that were better before tends to be things we need to relearn.

Allowing firewall access to Azure SQL (for example regarding BI) is easily scripted and here are 3 useful scripts for that purpose:

List current firewall rules:

SELECT * FROM sys.firewall_rules

If you need to grant access to the IPs 111.222.111.222 to 111.222.111.250:

EXEC sp_set_firewall_rule N'The_Name_That_Will_Be_Shown_On_The_List', '111.222.111.222', '111.222.111.250';
Getting rid of the above rule is just as simple:
EXEC sp_delete_firewall_rule N'The_Name_That_Will_Be_Shown_On_The_List'

The three above is all server rules so you need to run them towards the master database.

You can do the same tricks on database level:

Listing

SELECT * FROM sys.database_firewall_rules

Granting

EXEC sp_set_database_firewall_rule N'The_Name_That_Will_Be_Shown_On_The_List', '111.222.111.222', '111.222.111.250';

Deleting

EXEC sp_delete_database_firewall_rule N'The_Name_That_Will_Be_Shown_On_The_List'

 

Remember to be run them on the desired database.

 

Please keep in mind that you are messing with security and I – as usual – don’t take any responsibility in any damage you might make by using the above.

Prompting for values in SQL script

This is something that I forget 2 seconds after I’ve used it so now I’ll put it here as a note to self.

I have a series of TSQL scripts that I use once in different circumstances and often it’s a while between each go. In some of the I need to add some environment specific information and instead of having to scroll down through the text the query editor offers a way of prompting for parameters through a tagging of text like this:

select <Parameter name, Data type, Default value>

Before executing the script you press CTRL+SHIFT+M (this is the part I usually forget) and that makes the editor prompt you like this:

Screenshot 2019-02-19 at 13.49.43

Replace the Default value with the appropriate text and click OK. This will update your SQL script to this:

select 1234

Not the most mind-blowing thing in the world, but a great help.

 

POS not showing offline data jobs

In Dynamics 365 the offline story on the MPOS has been significantly improved since AX 2012. Now it’s more or less a click on the register in Dyn365, install the POS and distribute data. A bit simplified approach to life but anyways.

Here’s something I’ve seen a couple of times now. There’s just no jobs and nothing to process …No offline jobs

The event log gives it away a bit:

Failed to get offline sync data in offline database due to Exception. Error Details: Data Source=localhost\SQLEXPRESS;Initial Catalog=RetailOfflineDatabase;Integrated Security=True;Persist Security Info=False;Pooling=True;Encrypt=True;TrustServerCertificate=True

offline error 1

And in the details it’s clear that we’re facing a rights issue here. But in SQL Express with no management tools on the POS machine we don’t have many configuration options and even less when we’re in a setup with a large number of registers.

All you need to do is add the user logged in to Windows to these two groups on the local machine:

User groups

Log off and on again to activate the changes. Start your POS and check the database connection status. Hopefully, you should see a lot of jobs now:

Offline jobs

RDP access to Dyn365 servers denied

You know that the server is running, but your RDP connection request is denied.

Connection waitingConenction denied

This might be due to the introduction of the IP access check introduced not so long ago and on production environment servers. To get around this you need to add your IP address to the white list for the environment.

To do this click Maintain and Enable access:

Click maintain

Select Enable access:

Enable access

Click + to add a new rule:

Add

Fill in the form with a relevant name and the IP address to white list:

Add form

To get your IP you can ask Google or use one of the many web sites offering that service. For example www.whatsmyip.org:

whatsmyip

As soon as your have created the new rule you’re good to go (and connect):

Rules

RetailTenantUpdateTool.ps1 misses prerequisites

After moving data from one server to another you need to run the RetailTenantUpdateTool powershell script. Doing this might present you with this error:

Please download and install below prerequisites:
Microsoft Online Services Sign-In Assistant for IT Professionals, download link: http://go.microsfot.com/fwlink/?LinkID=286152
Azure Active Directory Module for Windows PowerShell (64-bit version), download link: http://go.microsoft.com/fwlink/p/?linkid=236297

PowerShell error

One of them actually links to a proper download but the other one forwards you to a site telling you that what you’re looking for has retired …

To get moving install this module before executing the script:

Install-Module -Name MSonline

Install MSOnline

After completing this install you can run the script without any issues.

Login prompt

 

Updates tiles in LCS not updated

When looking at the hosted environments in LCS sometimes the tiles showing updates doesn’t seem to update probably. The symptoms could be that the numbers indicate that you’re missing some updates after updating it all or that the Last run date isn’t updating.

Tiles update outdated.PNG

The status is updated through a scheduled task on the server.

Task scheduler.PNG

It runs a powershell script in C:\LCSDiagnostics\ called CollectData.ps1

Script.PNG

You can run it manually (through an elevated powershell) and that should hopefully update the figures.

It would have been nice to be able to see the run history on the task in the scheduler but for some reason that has been disabled:

History disabled.PNG

That is easily fixed. Open the Task scheduler as Administrator and that gives you the option in the right most part of the form:

Enable task history.PNG