Whitelisting IPs for FnO Dev Environments

I got a question today from a customer… “Could you show me how to add IPs to the whitelist for our FnO Dev Mashines?”.. Here Goes

  1. Log in to the Azure Portal
  2. Find the Azure VM that you would like to change
  3. Go to Network Settings and locate the rdp-rule


  4. Open the rule and add your IP adress to the “Source IP addresses/CIDR ranges” field. You you have more than one IP, add a comma between the IPs.


  5. Click Save

Protecting your Dev VMs are important for a couple of reasons… The most important being that there are search engines on the internet that indexes RDP endpoints available to the Internet and if your VM is in that database, bad guys will start to try to break into them… and even if they might not succeed (LCS generates fairly good credentials) it will trigger a policy that will make the VM unavailable for logins for a while which, if nothing else, will stop your developers from doing their job.

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