Using Powershell to backup your AX 2012 code

One of the repetitive tasks when working with Dynamics AX 2012 is getting out the models out of an environment. Lucky for us this is pretty easy to do using PowerShell. Inspired by the great blog post by Gary Holsopple where he shares a script he made to get the models out, I made this script covering my needs. In my version I iterate over the layers instead, and I prefer to display some feedback while doing so. The export will work each time, since the model-files will get unique names each time. Here's my version:

import-module "C:\Program Files\Microsoft Dynamics AX\60\ManagementUtilities\Microsoft.Dynamics.ManagementUtilities.ps1"

#region Variables
$server = "myserver"
$database = "AX2012_Dev"
$axBackupFolder = "c:\axModelBackup\AX2012_Dev\"


[System.Collections.ArrayList] $layerList = New-Object System.Collections.ArrayList
$layerList = @("USR";"USP";"CUS";"CUP";"VAR";"VAP";)

[System.Xml.XmlDocument] $xml
[System.Xml.XmlNode] $obj
[System.Xml.XmlNode] $property

#endregion

#region Functions
Function New-BackUpFolder($destinationFolder)
{
 $dte = get-date
 $dte = $dte.tostring() -replace "[:\s/]", "."
 $backUpPath = "$destinationFolder" + $dte
 $null = New-Item -path $backUpPath -itemType directory
 return $backUpPath
} 


Function fBackup([string]$model, [string]$fileName, [string]$server, [string]$database)
{
 Export-AXModel -Model $model -File $fileName -Server $server -Database $database
}
#endregion

#region do the work

$backupFilePath = New-BackupFolder($axBackupFolder)
$xml = (Get-AXModel -Server $server -Database $database | convertto-xml )
$nodes = $xml.SelectNodes("Objects/Object")

foreach ($obj in $nodes)
{
 $backupFileName = ""
 $elementCount  = ""
 
# Loop all properties
 foreach ($property in $obj.SelectNodes("Property"))
 {
  if ($property.GetAttribute("Name").Equals( "Name" ))
  {
   $nameToBackup  = $property.InnerText
   $backupFileName = $backupFilePath + "\" + $nameToBackup + ".axmodel"   
  }
  
  if ($property.GetAttribute("Name").Equals( "Layer" ))
  {
   $layer = $property.InnerText         
  }
  
  if ($property.GetAttribute("Name").Equals( "ElementCount" ))
  {
   $elementCount = $property.InnerText         
  }
 }
 
 if ($layerList.contains($layer.ToUpper()) -and $backupFileName -ne "")
 {
  "Exporting " + $elementCount + " elements from " + $nameToBackup + "..."
  fBackup $nameToBackup $backupFileName $server $database
 }
 else
 {
  "Skipping " + $backupFileName + " in layer " + $layer 
 }
}

"Completed!"

#endregion

Further inspired, I made this small script to export the entire modelstore:

import-module "C:\Program Files\Microsoft Dynamics AX\60\ManagementUtilities\Microsoft.Dynamics.ManagementUtilities.ps1"
$server  = "myserver"   # Format: "server\instance"
$db   = "ax2012_dev" 
$folder  = "c:\axModelBackup\"   # Remember trailing slash (\)

#region Do Work 

$dte   = get-date
$dte   = $dte.tostring() -replace "[:\s/]", "."
$file  = $folder + $db + "_" + $dte
Export-AXModelStore -Server $server -Database $db -File $file

#endregion

If you have several AOSes, just copy the files and make one for each AOS where you modify the variables at the top. Fun, fun, fun!

Assigning user groups to the Analysis Database Roles using PowerShell

One of the steps involved when installing a full-fledged Dynamics AX 2009 installation is the setting up the security schema for OLAP database. If you do not grant the domain users access to the predefined roles, they will not be able to load the Analysis Reports and Role Center KPI's. When you run the reports and test the Role Centers as administrator, you are essentially bypassing these security checks. You need to test using a regular user to make sure everything is working as expected.

In most cases I end up with simply granting either Domain Users or one specific user group access to all the predefined roles. If you really want to harden security, you would create one Active Directory Group for each role and add users to those roles.

The manual solution

Now adding user groups to each of these roles is a tedious task. The procedure is described on MSDN, but I'm going to repeat the steps here:

1. Open SQL Server Management Studio
2. Connect to the Analysis Services
3. Open the Dynamics AX OLAP Database
4. Open the Roles
5. For each role, repeat these steps
1. Right-click and choose Properties
2. Open Memberships
3. Click Add
4. Click Object Types and make sure "Groups" is marked. Press OK.
5. Click Locations and navigate to the Active Directory you will search in. Press OK.
6. Write on the name of the group you want to add. For example "Domain Users" is a system group that includes all registered domain users.
7. Click Search Names so it validates your input.
8. Click OK
9. Click OK again in the Edit Role Dialog. 

There are 26 roles in my test environment at the moment. Having to add these Memberships will take me about 10 minutes or so, but is extremely boring. 

A simpler solution - PowerShell

Just recently I was about to do this one more time, but then I started thinking about solving this with PowerShell.
PowerShell will be an important part of Dynamics AX 2012, since most of the installation, maintenance and configuration will available with PowerShell commands. So I might as well start to get PowerShell in my fingers.

I searched on the Internet and came across this article by Darren Gosbell about replacing group members from roles in SSAS. By taking his example, I made some minor modifications and here is my script for adding one group to all roles:

[System.reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") 

$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect("localhost") 
$axssasgrp = "CONTOSO\Domain Users"
foreach ($db in $svr.Databases)
{
    # Print the Database Name
    "Database: " + $db.Name
    If ($db.Name -eq "Dynamics AX")
    {
        foreach ($role in $db.Roles)
        {
            "   Role: " + $role.Name    
            $foundMember = $null
            foreach ($member in $role.Members)
            {
                if ($member.Name -eq $axssasgrp)
                {
                    $foundMember = $member
                }
            }
            If ($foundMember -eq $null)
            {
                "      Adding access to " + $axssasgrp
                $newRole = New-Object Microsoft.AnalysisServices.RoleMember($axssasgrp)
                $role.Members.Add($newRole)
                $role.Update()
            }
            else
            {
                "      Usergroup " + $axssasgrp + " already added"
            }
        }
    }
}
$svr.Disconnect()

Now you might need to run PowerShell as administrator for this to work. Change the server name and user group name to make it work in your scenario. I never change the name of the Dynamics AX OLAP.

For cleaning up I also made another script that removes a usergroup. This script looks like this:

[System.reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") 

$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect("localhost") 
$axssasgrp = "CONTOSO\Domain Users"
foreach ($db in $svr.Databases)
{
    # Print the Database Name
    "Database: " + $db.Name
    If ($db.Name -eq "Dynamics AX")
    {
        foreach ($role in $db.Roles)
        {
            "   Role: " + $role.Name    
            $foundMember = $null
            foreach ($member in $role.Members)
            {
                if ($member.Name -eq $axssasgrp)
                {
                    $foundMember = $member
                }
            }
            If ($foundMember -ne $null)
            {
                "      Removing access to " + $axssasgrp
                $role.Members.Remove($foundMember)
                $role.Update()
            }
            else
            {
                "      Usergroup " + $axssasgrp + " not found in role"
            }
        }
    }
}
$svr.Disconnect()

Now I would like to improve this script by adding the ability to read groups from a CSV-file where you could differentiate what user group gets added to what role. I am just learning PowerShell, so if you are a hard core PowerShell scripter and see ways to improve this script, please let me know. 

How to run these scripts

I have only tested this on Windows Server 2008 R2 and PowerShell has its shortcut right on the QuickLaunch. Right-click and run it as administrator. Edit the script so user group reference is correct. If Analysis Services is running on a named instance you will need to modify the connect statement with servername\instancename. Copy your final script and from the top left in the PowerShell window, find the Paste command. After the script is pasted, just press a final Enter and let the script do its work.

Deploying Reporting Services Reports dependent on X++ helper classes

Today I was having trouble with deploying a custom Reporting Services Report. One of the changes I made to the report was adding a helper class in the AOT and calls a method on this class from one of the data methods within the report.

The deployment was successful, but when I tested the report it threw a Dynamics AX error stating it could not call my custom method. Lucky for me, this customer had Visual Studio installed on the server running SSRS, so I was able to load the Report Library and test it within Visual Studio. The report ran perfectly.

I tried first to delete the report in the Report Manager Web interface, and then redeploy it from scratch. The documentation clearly states:
"Reports often rely on code that is defined in business logic assemblies. During deployment, business logic assemblies, as well as satellite assemblies containing translated resources, are built and deployed to the report server. After the assemblies have been deployed to the report server, the security policy file for the report server is updated to allow.NET Business Connector secure access to the business logic assemblies."
http://msdn.microsoft.com/en-us/library/cc519490.aspx

The report still failed with the same error. But since the report worked within Visual Studio I started thinking about whether or not the added X++ class was truly available for any managed code in SSRS. Those of us who work with Enterprise Portal development knows this always isn't true, and you are some times forced to refresh managed code generated from the AOT. So I pulled up the Reporting Services Configuration Manager and picked my instance, connected to it, and restarted it.


Now the report worked perfectly!


I hope this helps someone.

Property value is not valid when setting datamethod in Dynamics AX SSRS Report

UPDATE January 2012: This is resolved in version 6 of JetBrains Resharper.

I was working with building Reporting Services reports for Dynamics AX 2009 in Visual Studio and when I tried to set the Query property of the Dataset I got this very strange and not very helpful error:
"Property value is not valid" with the details containing "Object reference is not set to an instance of an object".

I was pretty sure my Data Method was valid, but I spent some time trying to strip out any possible problems with my code. In the end I tried to test my report in a different environment (which I was lucky enough to have set up earlier). The report worked perfectly in my second environment. I then figured out it had to do with Visual Studio and went back to my development environment for a closer look. Lucky for me, one of the first things I did was trying to suspend ReSharper (www.jetbrains/resharper) and that was it.  After suspending ReSharper, the DataMethod returned the DataTable as expected and everything was back to normal. I have notified the ReSharper developers about this problem, so maybe one day this will get fixed. In the meantime, I hope this post will sort things out for any other developers out there struggling with the same problem. This is version 5.1.2, and I know there is a 5.1.3 version out there. If any later versions of ReSharper fixes this I will update this post (if I remember).

As a side note, ReSharper is my favorite addon for Visual Studio, and if you're working with SharePoint development (Enterprise Portal to be specific), then I'd highly recommend ReSharper. Try it out, you won't regret it! Then again, when working with the Dynamics AX Report Model for Visual Studio, you will simply have to suspend it.

Error when adding Business Connector user to SharePoint 2010 as Managed Account

While installing Enterprise Portal on SharePoint 2010 you might get this strange error when you are trying to add the Business Connector user as Managed account in SharePoint:

"The given key was not present in the dictionary" 

Now you're simply trying to get your site to run properly under the bcproxy account, and you don't want to struggle with strange mysterious errors.
The solution isn't very obvious, at least not in my opinion, but if you search the web for solutions to this error, you might find the same answer as me.

You need to head over to you Active Domain Controller and open "Active Domain Users And Computers", then follow these steps:

1. In the View menu, choose "Advanced Features". Unless you do this, the next steps won't be available.
2. Find your Business Connector user (aka. bcproxy) in your Active Domain. I prefer to use the search function.
3. Right click the user and select "Properties"
4. Select the "Security" tab
5. In the "Group or User names"-field, scroll down and select "Authenticated Users"
6. Check "Allow" in the "Permissions for Account Operators".
7. Press OK to save.

If you're lucky, the SharePoint server will be aware of the change in matter of seconds. In some installations with multiple Domain Controllers, you might have to force the update over to the next Domain Controllers.

With that small change, you should be able to add the new Managed Account, and verify the Enterprise Portal site is running with a Application Pool with the Identify of the Business Connector user.

For more tips on possible solutions when installing Enterprise Portal on SharePoint 2010 I would recommend checking out this site: http://blogs.msdn.com/b/emeadaxsupport/archive/tags/enterprise+portal/

Here are some screenshots.

Viewing Managed Accounts:

Registering the Managed Account:

The Error:

The Workaround:

 I hope this helps somebody.

Dimension Permission Error after successfully processing the Dynamics AX OLAP

Introduction

I have recently been involved in hunting down a strange "bug" in all the Reporting Services reports which reported data from the General Ledger Cube. The error message itself was "The 'Measure' attribute for the 'Measure' dimension has generated dimension security expression that is not valid. DimensionPermission (1, 209) the member '[Cheque Transaction Amount - Bank]' was not found in the cube when the string, [Measures].[Cheque Transaction Amount - Bank], was parsed.". Any users who were not members of the Local Administrators Group on the SSAS server would get this error on reports trying to report from the General Ledger Cube.

Configuring the Default OLAP cubes

Those who have worked with the deployment of standard OLAP cubes for Dynamics AX knows the main job is to make the OLAP process. This can only be achieved by removing any elements in the Analysis database that still reference missing elements from the OLTP (the Dynamics AX relation database). You probably also know the fact that if you turn off configuration that represents database elements like tables and/or fields, these elements will effectively be removed upon database synchronization. So basically any table or field removed from the OLTP will result in possible errors when processing the OLAP. If you can process the OLAP without errors, you're good to go (well, almost).

The 'Measure' referenced in my error was in fact based on the "BankCheque" Configuration, which was turned off in this particular installation. I could verify the measure missing from the OLAP as expected. So why did the report throw this error?

The solution

I'm not entire sure why, but the solution was in the definition of each Role connected to the General Ledger Cube. With the default OLAP there are numerous predefined Roles. These roles more or less resembles the default installed Roles in Dynamics AX. They have a semantic relation, but that's all. Adjustments to role membership in Dynamics AX are not automatically copied to the Roles in the OLAP. They are separate. The Roles in the OLAP contains various settings for limiting the access to the analysis data. There are settings for limiting access to cubes and all the way down to limiting access to specific dimensions. Given all this, there was indeed a setting on several Roles naming this particular dimension in its security context.

Under Properties for the CFO role and under Dimension Data, you'll find a dropdown for available dimensions you may specify security restrictions for. These dimensions are limited to those common for the entire OLAP, and those specific for any cubes this role has access to. Under each Cube there is even a Measures Dimension which allows you to define security context down to attributes. This is the source for the reference to the invalid 'Measure' attribute, namely the [Measures].[Cheque Transaction Amount - Bank]. After taking this reference out the "Allowed member set", and saving the Role, everything was working perfectly. Well, I actually had to remove this reference from all Roles granting access to the General Ledger Cube.

Finally...

Why the users in the Local Administrators Group are not effected by this, and why the OLAP may process without errors even when the Roles reference invalid dimensions, that I do not know. Please feel free to message me or comment under this article.

I hope this helps someone!

Troubleshooting Kerberos on Enterprise Portal – Wrong NTAuthenticationProviders

I’ve been helping with setting up Kerberos authentication for a couple of installations so far. This is normally just a matter of reading the installation guide and also the Kerberos setup guide. If you’re reading this post, you probably already know why you need to set up Kerberos; it’s because Enterprise Portal is running on another server than your SQL Server. This is a pretty normal setup. The configuration needed to get this to work might be tedious, but when you’ve done it a couple of times, it isn’t really that hard.

What if it still doesn’t work?

Kerberos errors are by default not logged, so you will have to do some modification in the Registry to turn on logging. This is not difficult. You would want activate this on the Enterprise Portal Server. The procedure is described on Microsoft’s Customer Support site, and it’s a matter of adding LogLevel (DWord) with the value of 1 to the Registry location HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters
When this entry is saved, the logging will start immediately. No restart of server or services is needed.

Now there are quite a few errors that might throw you off, so keep that in mind when you open the Systems log in Event Viewer.

Remember to turn off the debugging when you’re done.

NTAuthenticationProviders matters

The procedure for setting NTAuthenticationProviders is located on Microsoft Customer Support site. It is quite similar to the instructions on how to setup IIS when SSRS is running on SQL 2005. The topology I was troubleshooting had Windows 2008 Server on both the SharePoint server (where Enterprise Portal was installed) and on the SQL 2008 Server. When loading Enterprise Portal in a browser on the SharePoint server everything was working perfectly. The SSRS reports were loaded and displayed KPIs and fine graphs. If we tried to load the same page from any other machine all the webparts displaying reports showed an error saying it could not load the report and I should “Validate that the Report Manager URL is correct”. Obviously the setup was correct since I could load the reports perfectly if I tried load the page from the SharePoint server. This was a Kerberos problem!

Since we had already checked the installation and setup several times and searched for any discrepancies, we had to involve Microsoft to hunt down the problem. For some reason the NTAuthenticationProviders on the SharePoint server was set to “NTLM” only, and not “Negotiate, NTML”. After setting it to “Negotiate, NTLM” everything was working as expected.

If you don’t want to read the support article at Microsoft, here’s what you need to do:
1. Click Start, click Administrative tools, then click Internet Information Services (IIS) Manager.
2. In the left pane, click the Web sites directory and locate the SharePoint web site hosting EP on the right-hand side.
3. Locate the ID (Identifier) column and write down the ID of the web site
4. If running Windows 2008/R2 then run an elevated cmd prompt
5. Change folder to c:\inetpub\adminscripts folder by entering the following command and press Enter: cd \inetpub\adminscripts
6. Use the following command to determine if both the "Negotiate,NTLM" authentication providers are setup or not. In the command, replace <identifier> with the ID identified in step 3 above. Then enter the commands in the command prompt and press Enter:
cscript.exe adsutil.vbs get w3svc/<identifier>/root/NTAuthenticationProviders
7. If the response does not show "Negotiate, NTLM" then use the following commands to set it:
cscript.exe adsutil.vbs set w3svc/<identifier>/root/NTAuthenticationProviders "Negotiate,NTLM"
8. The providers are now set. We need to restart the IIS services. In the command prompt, type iisreset.exe and then press Enter.

So basically, this needs to be checked on the SharePoint server if you are setting up Kerberos as authentication to the Enterprise Portal. I hope this helps someone.