Cost for Azure Hosted Build Pipelines

In Dynamics 365 For Finance and Supply Chain we rely heavily on Azure DevOps for managing aspects of our projects, especially development and deploying packages.

Today a customer told me that they hit the roof on the free/included alotment for parallell build in Azure Hosted Pipelines. They wanted to understand what it had been used for. I found a very handy new preview feature called Historical Views for Pipelines.

To turn it on you you click on the settings icon in the top right corner and click Preview Features. The feature can be turned on for single users or entire organizations.

This feature will help you understand your usage.

The first thing you need to in order to purchase more parallel jobs is to set up billing for your Azure DevOps organization. Go to Organization Setting – Billing. The billing options are the same ones that you are using for “regular” Azure.

After you have set up billing, go to Organization Settings – Parallel Jobs and select Purchase Parallel jobs.

The pricing for additional paralell jobs are $40/month which gives you 1 paralell job with unlimited minutes.

Links
Historical graph for agent pools – Azure Pipelines | Microsoft Docs
Microsoft-hosted agents for Azure Pipelines – Azure Pipelines | Microsoft Docs
Configure and pay for parallel jobs – Azure DevOps | Microsoft Docs
Manage preview features – Azure DevOps Services | Microsoft Docs
Set up billing for your organization – Azure DevOps Services | Microsoft Docs
Azure DevOps Services Pricing | Microsoft Azure

Missing Azure Subscriptions

Today one of my colleagues set up a new subscription in Azure and sent me a link to it. When I tried to access it through the link everything worked but when I tried to find it in the Azure Portal I did not see it:

There should be three subscriptions here… After fiddeling around with the filters without success I resorted to some Google research. It turns out there is Über-Mega Filter called the Global Subscription Filter that you find here:

When clicking it, I found the missing subscriptions

Once this was checked I was able to see the missing subscription in UI in the Azure Portal.

Flow Friday: Posting Azure VM Auto Shutdown notifications to Microsoft Teams

When you have Azure VMs up and running there is a function to auto shut them down when they are not being used. You can do that in a couple of ways. One of the newer ones is to use the Azure DevTest Labs functionality to do this. Half an hour before the VM is shut down Azure sends an email to a pre-defined adress where you have the option to post-pone or cancel the shutdown.

Today one of my colleagues asked me if it was possible to get this email, with the links, into Teams. My first thought was to send it to the Teams Channel email. Unfortunately did not display correctly…

My next try was to use the Webhook in DevTest Labs functionality and the incoming webhook connector in Teams. When I did this the message did not look very “user friendly”

So I thought I would give Power Automate a try. I set up DevTest Labs to send the email to my mailbox. The I creating a trigger for an incoming email and with a filter for the email adress that Azure DevTest Labs user

The I add a block posting to Teams Channel. In order to get it to work I had to cut down the message to size. I used the title tag in the email and the phrase “Note that” at the end of the message to cut away the beginning and the end of the message body to fit the message in the Teams post.

The Expression:
substring(triggerBody()?[‘Body’],indexOf(triggerBody()?[‘Body’],'<H1′),sub(indexOf(triggerBody()?[‘Body’],’Note that’), indexOf(triggerBody()?[‘Body’],'<H1′)))

Finally I move the message to my Archive folder.

The message is now in Teams 🙂

Adding users WITHOUT an Azure AD Accounts to Dynamics 365 for Finance and Operations

Normally when using Dynamics 365 for Finance and Operations all you users are part of an Azure AD. This is created when ordering your license and is used for authentication. This AD is either stand-alone or synced with you OnPrem AD.

There might be situations where you need to add external users to your Dynamics installation and if these are part of an Azure AD you just use this guide but if the external part does not have Azure AD then it is a bit more problematic… You do not want to add them to your organization since this might add security issues and sometimes licensing costs, and you might not be able to force them to get their own Azure AD tenant.

There is however another way to do this. It will require the user to get a Microsoft Account, that should not be an issue since it is a free account.

  1. Log into the azure portal and go to Azure AD – All Users
  2. Click New Guest User
  3. Add the users Microsoft account (eg *@hotmail.com, *@outlook.com)
  4. Go to D365FO and choose Import users
  5. Select the new account, import it and give it the correct user role.

Now the user can log in using their hotmail adress

That’s all for today

/Johan

Copy database from a OneBox environment to an Azure Sql Environment

Most of this post is a copy of the Microsoft Article (which is referenced below) but with my remarks, clarifications adn some changes for making the process easier.

1. Create a Backup copy of the Source database

BACKUP DATABASE [AxDB] TO DISK = N'D:\Backups\axdb_original.bak' WITH NOFORMAT, NOINIT, NAME = N'AxDB_golden-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
RESTORE DATABASE [AxDB_CopyForExport] FROM DISK = N'D:\Backups\axdb_original.bak' WITH FILE = 1, MOVE N'AXDBBuild_Data' TO N'F:\MSSQL_DATA\AxDB_CopyForExport.mdf', MOVE N'AXDBBuild_Log' TO N'G:\MSSQL_LOGS\AxDB_CopyForExport_Log.ldf', NOUNLOAD, STATS = 5

Note: You need to change the paths for the database files

2. Prepare YOUR COPY of the database for moving to Azure SQL

update sysglobalconfiguration set value = 'SQLAZURE' where name = 'BACKENDDB' 
update sysglobalconfiguration set value = 1 where name = 'TEMPTABLEINAXDB'
drop procedure XU_DisableEnableNonClusteredIndexes
drop schema [NT AUTHORITY\NETWORK SERVICE]
drop user [NT AUTHORITY\NETWORK SERVICE]
drop user axdbadmin
drop user axdeployuser
drop user axmrruntimeuser
drop user axretaildatasyncuser
drop user axretailruntimeuser
drop user axdeployextuser

3. Export the prepared database to a BacPac file

cd C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\
md D:\Exportedbacpac
SqlPackage.exe /a:export /ssn:localhost /sdn:AxDB_CopyForExport /tf:D:\Exportedbacpac\my.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false

Note: This operation will take quite a while

4. Copy the bacpac file to the destination server
Note: If you have a large file you can use an Azure Storage Blog

5. Import the bacpac as a new database

cd C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\
SqlPackage.exe /a:import /sf:C:\Temp\my.bacpac /tsn:azuresqlserver.database.windows.net /tu:sqladmin /tp:<passwordforsqladmin> /tdn:AxDB_New /p:CommandTimeout=1200 /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P2

Note: This operation will take quite a while

6. Update the database with users, passwords and some other settings using the script below:

CREATE USER axdeployuser FROM LOGIN axdeployuser
EXEC sp_addrolemember 'db_owner', 'axdeployuser'

CREATE USER axdeployextuser WITH PASSWORD = '<password from LCS>'
IF EXISTS (select * from sys.database_principals where type = 'R' and name = 'DeployExtensibilityRole')
BEGIN
EXEC sp_addrolemember 'DeployExtensibilityRole', 'axdeployextuser'
END

CREATE USER axdbadmin WITH PASSWORD = '<password from LCS>'
EXEC sp_addrolemember 'db_owner', 'axdbadmin'

CREATE USER axruntimeuser WITH PASSWORD = '<password from LCS>'
EXEC sp_addrolemember 'db_datareader', 'axruntimeuser'
EXEC sp_addrolemember 'db_datawriter', 'axruntimeuser'

CREATE USER axmrruntimeuser WITH PASSWORD = '<password from LCS>'
EXEC sp_addrolemember 'ReportingIntegrationUser', 'axmrruntimeuser'
EXEC sp_addrolemember 'db_datareader', 'axmrruntimeuser'
EXEC sp_addrolemember 'db_datawriter', 'axmrruntimeuser'

CREATE USER axretailruntimeuser WITH PASSWORD = '<password from LCS>'
EXEC sp_addrolemember 'UsersRole', 'axretailruntimeuser'
EXEC sp_addrolemember 'ReportUsersRole', 'axretailruntimeuser'

CREATE USER axretaildatasyncuser WITH PASSWORD = '<password from LCS>'
EXEC sp_addrolemember 'DataSyncUsersRole', 'axretaildatasyncuser'

ALTER DATABASE SCOPED CONFIGURATION  SET MAXDOP=2
ALTER DATABASE SCOPED CONFIGURATION  SET LEGACY_CARDINALITY_ESTIMATION=ON
ALTER DATABASE SCOPED CONFIGURATION  SET PARAMETER_SNIFFING= ON
ALTER DATABASE SCOPED CONFIGURATION  SET QUERY_OPTIMIZER_HOTFIXES=OFF
ALTER DATABASE <imported database name> SET COMPATIBILITY_LEVEL = 130;
ALTER DATABASE <imported database name> SET QUERY_STORE = ON;

update [dbo].[SYSSERVICECONFIGURATIONSETTING]
set value =''
where name = 'TENANTID'

update dbo.POWERBICONFIG
set TENANTID = ''

update dbo.PROVISIONINGMESSAGETABLE
set TENANTID = ''

Note: Use these SQL queries to get the TENANTID:
select * from [dbo].[SYSSERVICECONFIGURATIONSETTING]
where name = 'TENANTID'
select TENANTID from dbo.POWERBICONFIG
select TENANTID from dbo.PROVISIONINGMESSAGETABLE

7. Stop services locking the original database

  • World wide web publishing service (on all AOS computers)
  • Microsoft Dynamics 365 for Finance and Operations Batch Management Service (on non-private AOS computers only)
  • Management Reporter 2012 Process Service (on business intelligence [BI] computers only)

Note: this needs to be done on all servers for the environment

8. Run this script to switch the databases

ALTER DATABASE [axdb_123456789] MODIFY NAME = [axdb_123456789_original]
ALTER DATABASE [importeddb] MODIFY NAME = [axdb_123456789]

9. Start services locking the original database

  • World wide web publishing service (on all AOS computers)
  • Microsoft Dynamics 365 for Finance and Operations Batch Management Service (on non-private AOS computers only)
  • Management Reporter 2012 Process Service (on business intelligence [BI] computers only)

10. Syncronize the database

F:
cd F:\AosService\WebRoot\bin
Microsoft.Dynamics.AX.Deployment.Setup.exe -bindir "F:\AosService\PackagesLocalDirectory" -metadatadir F:\AosService\PackagesLocalDirectory -sqluser axdbadmin -sqlserver <azure sql database server name>.database.windows.net -sqldatabase <database name> -setupmode sync -syncmode fullall -isazuresql true -sqlpwd <sql password> >log.txt 2>&1

11. If the environment is running Retail you will need to run the Retail Reprovisioning tool, see instructions in this document

12. Reset the financial Database using these instructions

Note: I have noticed that some integrations are having problems accessing some DLLs after a refresh. This is usually solved by a restart of the environment. To do this easier we usually do it from LCS

Links:
https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/database/copy-database-from-sql-server-to-azure-sql
Resetting the financial reporting data mart after restoring a database.

Azure SQL SqlPackage error

Today I tried to do a restore of database to an Azure DB in a Dynamics 365 for Operations environment. When I ran SQLPackage.exe I got the following error:

‘Unable to connect to master or target server ‘AxDB_New’. You must have a user with the same password in master or target server ‘AxDB_New’

The issue here is that the version of SQL Management Studio that is provided in the Azure VM is version 16 and Azure SQL requires version 17… with that background I am not convinced that the error message is totally relevant…

The solution is to upgrade Management Studio to the latest Version.

image

/Johan

Activating Dynamincs AX 2012 VMs deployed to Azure from LCS

Our company has retired all of our lab hosts which we used to have internally which means thet we need to have our lab servers on Microsoft Azure. To install the servers we use LCS (Lice Cycle Services) which deploys a VM in our Azure Subscription. The problem is that the VM is not activated, which on an internal server would require a product key and a Windows Server License… When running VMs internally we used to handle this by rearming the VMs 3 times which gives us a total of 180 days and then set up a new one which was a little hassle but it worked. But since we now have deployed VMs on Azure there is actually a license included in the Azure VM which means that there is no need to run a server which is not activated. Here is a short description on how to activate the AX VM…

  1. Install the new VM on Microsoft Azure using LCS
  2. Log into the the server using RDP (the logon info is in LCS)
  3. Start an elevated command prompt
  4. Find the edition of the VM by running this command:

    DISM /online /Get-CurrentEdition

    We get: Current Edition : ServerDatacenterEval

  5. Find out which edition you can upgrade to by running:

    DISM /online /Get-TargetEditions

    We get: Target Edition : ServerDatacenter

    This means that you can upgrade from the evaluation edition of datacenter edition to the full version of datacenter edition. Now we need a license key… Microsoft uses Automatic Virtual Machine Activation to license the VMs in Azure… This means that if the host is activated (which it hopefully is Smile ) the guest gets activated but to use this feature the guest VM still need a product key. The keys are available on Technet.

  6. Use the correct key to activate the VM by running this:

    DISM /online /Set-Edition:ServerDatacenter /ProductKey:XXXXX-XXXXX-XXXXX-XXXXX-XXXXX /AcceptEULA

  7. Restart the VM and verify that is is not using Evaluation Edition anymore

    image

That’s all for today

Links:

https://technet.microsoft.com/en-us/library/dn303421.aspx

Trying to give my AX 2012 demo VM in Azure Internet Access

Today little issue was a bit frustrating…. to say the least

I have set up a new AX 2012 Vm in Azure for my colleagues to do some troubleshooting on. The need to install a Hotfix there and thus needs Internet Access from the VM. Normally this is not a problem… there are normally no issues accessing the internet from a correctly configured VM… the key phrase being correctly configured

I installed the VM from LifeCycle Services which is the normal way for AX machines which means I get a preconfigured VM with AX 2012 and it also has Active Directory installed locally to decrease dependencies. I am able to access the VM but no internet access… so I do some basic testing and I can do nslookup when using an external DNS which tells me 1) the VM has internet Access and 2) there is something wrong with the DNS settings. The local dns setting is pointing to the address is pointing to 10.20.12.4 which has nothing to do with my VM. So I tried to change it to 127.0.0.1 and as soon as I saved the settings the connection to the VM dropped… that is strange. I tried again after restarting but got the same result. So what to do…

I tried changing it using Powershell… Success!! Restarted the VM and it was back to 10.20.12.4… Fail!! Something is rotten in the state of Denmark…

I started by looking through Group policies for something strange but could not find anything. So I fired up Sysinternal Autostart and found a strange script:

image

Hmmm… Looking at the script:

image

I found it!! This is not OK!!! Well, well… I changed the script and restarted and it worked… So if your AX 2012 VM is behaving strangely look at the c:\scripts\start.ps1 script. It also fiddles with services autostart settings and some other things

/Johan

Configure PowerBI for Dynamics 365 for operations

Today I helped two of my colleagues setting integrating Power BI for Dynamics 365 for operations. There are a couple of guides online which I have used to create this post, but I thought I would create one from my experience.

There are a couple of main steps we need to go through:

Create a Azure AD Application
Configure Dynamics 365 for operations for Power BI
Configure Power BI
Configure your workspace

Create a Azure AD Application

There are two ways of doing this… you can do it using the Power BI Developer registration tool or you can do it directly from the Azure Portal. We did it from the Azure Portal like this:

  1. Log into the Azure potal… You can use both the old and the new one
  2. Goto Azure Aztive Directory and select your Azure Active Directory Instance
  3. Go to Applications/App Registrations
  4. Create a new application with these settings:
    1. Name: ChooseYourName
    2. Application Type: Web app/API
    3. Sign-on URL: https://theadressforyourdynamics365foroperationsinstance/oauth
    4. App ID URI: https://theadressforyourdynamics365foroperationsinstance  Anything EXEPT https://theadressforyourdynamics365foroperationsinstance . You can for instance use https://DynamicsBI, it only has to be unique for each Azure AD Application
      Edited 27/1 2017
    5. Reply URL: https://theadressforyourdynamics365foroperationsinstance/oauth
  5. When this is created you will get a Client ID/Application ID and you will create a key. You need to save these because you will need the later. Note that the keys are only visible when they are created, Once you leave the page and go back you cannot see them again. If this happens and you have not saved it you can simply create an new one.Old portal
    SNAGHTML221ce22New Portal:
    SNAGHTML22b2d3e
  6. Once you have set up the application in Azure ID you will need to give it some permissions. This is where we got stuck because in one of the guides there was a screenshot which showed to few permissions and it simply did not work. You add the application Power BI Services and give it the correct permissions. We used the following permissions:
    1. View all Groups
    2. View All Reports
    3. Create Content
    4. View content Properties
    5. Read and Write all datasets
    6. View All Datasets
    7. View All Dashboards
    8. Add Data to User’s Dataset

Configure Dynamics 365 for operations for Power BI

  1. In Dynamics 365 for Operations, go to System Adminstration – Power BI
  2. You need to Enable the configuration and then you change these values
    1. Azure AD Tennant (this you will find in the Azure Portal)
    2. Client ID (you saved it above)
    3. Application key(you saved it above)
    4. Redirect URL: ps://theadressforyourdynamics365foroperationsinstance/oauth (This one is incorrect and will not work… ever… I do not understand how there can be an incorrect default value)The other values are correct

Configure Power Bi

  1. Every user that is going to use Power BI (more or less every user that is usign AX) will need at least a Power BI free License. As the name implies it is actually free and you addit in the Office 365 Admin Portal. Once you have added the wou will have to do this for every user:
  2. Log into Power BI (https://powerbi.microsoft.com)
  3. Click Get Data in the lower left corner
  4. Click Services – Getimage
  5. Click Get it now on each of  the three (for now) AX Apps
    image
  6. On each of the Apps enter the URL for your AX instance (https://theadressforyourdynamics365foroperationsinstance ) and flick next. If you get prompted for authetication select OAuth2image   image

Configure your workspace

  1. Log into Dynamics 365 for operations
  2. Go to the Workspace called Cost Administration
  3. Scroll to the right and click on the Power BI paneimage
  4. If this is your first time you need to authorize Power BI. Click the link, log in using your credentials and approve the permissions.image   image
  5. Return to the Dynamics Tab and click closeimage
  6. Select the Power BI tiles you want from the Tile catalog and click OK to add them to your workspaceimage    image

This should be it… Business Intelligence glory!

Links:
https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/analytics/configure-power-bi-integration