AX 2012 Security Architecture

Since the release of AX 2012 the security model has completely changed to the new role based concept. Simply put this is where you assign users one or more roles to define the entry points (explained here) they have access to. A role is made up of the following objects.

• Duties
• Privileges
• Table permissions
• Server methods
• Sub roles

Out of the box AX has a set of ‘standard’ security roles that cover all areas of a business. Obviously what MS classes as standard and what you as an organisation actually do will not be exactly the same so from my experience I have found that using the out of the box roles is not always the best approach, however using the pre built standard duties and privileges is (with minor tweaks of course).

The way I find easiest to explain the break down of the objects is below.

• Role – is a job title within the organisation
• Duty –  is a business process within a job
• Privilege – is an action within a process, often only one or two clicks in AX

MS have been kind enough to create privileges / duties for every area in AX, so the bulk of the work is done for us. We just have to create our roles after defining what permissions are needed and then match together the standard privileges and duties to our unique roles.

One thing to be very careful of though is that since the change MS now license you based on what access each user has (explained here).

AX 2012 Auditing Security Role Assignment/Allocation

Over the years I have had countless requests for AX to log the allocation of security roles to users. This can be a very sensitive topic, I have spoken to companies where the supplier has allocated admin rights to certain users in non-production systems for testing purposes which gives them full access to all legal entities. Roles finding their way onto users in production with no formal requests and anything in between.

I found it quite surprising how AX could not do this by default…but upon investigating it turns out the functionality is already there but with a condition that is only logs the change if the legal entity is Country region code ISOEE.

I have never understood why but…

EePersonalDataAccessLogging.logUserRoleChange()

So to get this working all we need to do is take out comment out the statements above as shown below.

 

Then when we next allocate a role to a user we can see that the changes are being logged in the table.

Now we just need to add this to an area we can easily view or report off.

We have numerous options for this, one is on the user log which I have seen another blog mention but this does relay on the User Log config key being turned on (which it normally is) config key shown below. If this is not turned on you will not see the user log form in:

USMF/System administration/Common/Users/Users (Action pain)

Instead what i do is create a brand-new form and display menu item looking at it then add it to the action pain of the SysUserInfoPage as shown below.

My whole project consists of:

EePersonalDataAccessLogging.logUserRoleChange() = change shown above

One new form: SysUserSecurityAudit

One new display menu item: SysUserSecurityAudit

Adding the menu item to the SysUserInfoPage form as shown below.

I am more than happy to you the XPO for this change, either comment on this post of find me on LinkedIn.

LinkedIn

 

 

 

AX 2012 Missing Indexes

Indexes can play a big part in the performance in AX and not always in a positive way. This post will take us through finding out what missing indexes SQL has noticed, formatting the data so that we can better understand which would have the biggest impact on AX based on how often they were used and the cost of the query.

*Update, the attached SQL query gives you the same output but without the manual creation of file.
“USE YourDatabase
GO

SELECT db.[name] AS [DatabaseName]
,id.[object_id] AS [ObjectID]
,OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName]
,id.[statement] AS [FullyQualifiedObjectName]
,id.[equality_columns] AS [EqualityColumns]
,id.[inequality_columns] AS [InEqualityColumns]
,id.[included_columns] AS [IncludedColumns]
,gs.[unique_compiles] AS [UniqueCompiles]
,gs.[user_seeks] AS [UserSeeks]
,gs.[user_scans] AS [UserScans]
,gs.[last_user_seek] AS [LastUserSeekTime]
,gs.[last_user_scan] AS [LastUserScanTime]
,gs.[avg_total_user_cost] AS [AvgTotalUserCost] — Average cost of the user queries that could be reduced by the index in the group.
,gs.[avg_user_impact] AS [AvgUserImpact] — The value means that the query cost would on average drop by this percentage if this missing index group was implemented.
,gs.[system_seeks] AS [SystemSeeks]
,gs.[system_scans] AS [SystemScans]
,gs.[last_system_seek] AS [LastSystemSeekTime]
,gs.[last_system_scan] AS [LastSystemScanTime]
,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
,gs.[avg_system_impact] AS [AvgSystemImpact] — Average percentage benefit that system queries could experience if this missing index group was implemented.
,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
,’CREATE INDEX [IX_’ + OBJECT_NAME(id.[object_id], db.[database_id]) + ‘_’ + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ”), ‘, ‘, ‘_’), ‘[‘, ”), ‘]’, ”) + CASE
WHEN id.[equality_columns] IS NOT NULL
AND id.[inequality_columns] IS NOT NULL
THEN ‘_’
ELSE ”
END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ”), ‘, ‘, ‘_’), ‘[‘, ”), ‘]’, ”) + ‘_’ + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ‘]’ + ‘ ON ‘ + id.[statement] + ‘ (‘ + ISNULL(id.[equality_columns], ”) + CASE
WHEN id.[equality_columns] IS NOT NULL
AND id.[inequality_columns] IS NOT NULL
THEN ‘,’
ELSE ”
END + ISNULL(id.[inequality_columns], ”) + ‘)’ + ISNULL(‘ INCLUDE (‘ + id.[included_columns] + ‘)’, ”) AS [ProposedIndex]
,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id]
WHERE db.[database_id] = DB_ID()
–AND OBJECT_NAME(id.[object_id], db.[database_id]) = ‘YourTableName’
ORDER BY ObjectName, [IndexAdvantage] DESC
OPTION (RECOMPILE);

”

Manual Way:

First we check the missing index report from the performance dashboard in SQL.

Load up SQL, right click and open up the dashboard as shown below.

Once the dashboard is loaded you at the bottom right you will see a hyper link for “Missing indexes” as show below

Once you have the report generated right click and export to excel and save the file, upon opening the file you will see something similar to the below.

We don’t want to go adding all the mixing indexes in, just the ones we deem fit from an AX perspective and also performance increase. The issue with the data is that it might well show a very high Avg Total User Cost but then a very low Overall impact and User seeks. This would not always be the top of our list to add as with a low impact it may not improve the user experience, however on the flip side if we have an impact of 99% but a low Avg Total User Cost then it would be saving high amounts of nothing.

The best way to get an accurate understanding of the data we have is to create a new column (weighted) and do “Avg Total User Cost * Overall Impact” this will give us a figure that we can filter on then cross reference with user seeks to get our shortlist for testing.

The current Excel sheet has merged columns so we need to create the new “Avg Total User Cost * Overall Impact” column (weighted) then copy all the data into a new sheet without the merges and you will hopefully see something like the following.

I have sorted the data by the “Weighted” column, which cross referencing it with the “User Seeks” column we can make an educated call with which indexes we should be applying.

Testing: Personally before we add these to the code to be applied during the next code release I would create them in SQL against a non-production database and have users running testing on processes that will hit it. Once it is approved they would then be converted to AX code and applied that way.

AX 2012 Performance tips

AX 2012 Performance tips:

Common issues with Dynamics AX 2012 (RTM,R2,R3) performance that are not infrastructure related.

Linked Blogs:   AX 2012 Missing Indexes

 

• Debugging enabled in PROD
  • This will cause roughly a 10% decrease in system performance

 

• Parameter sniffing (R2)
  • This is mostly an issue when you have uneven data distribution between legal entities in AX
  • It can be improved by the following KB and then SQL script: KB 2920058.
    After applying the fix you can re-enable literals for DATAAREAID using the following SQL statement.

• UPDATE SYSGLOBALCONFIGURATION SET [VALUE] = 1 WHERE NAME = ‘DATAAREAIDLITERAL’

 

• SQL indexes / stats maintenance (if you want any of my standard scripts feel free to message)
  • I recommend using the Ola Hallengren scripts, these I have found are a great start and the best free pre built maintenance jobs
  • Run a check index fragmentation script periodically for a short time frame logging all the data into a DB or a notepad file, this allows you to see how your tables fragment. From this make amendments to your scripts to make them as tailored as possible to your AX system

 

• • AX can often be sped up with the creation of new indexes, I have created a separate blog for this (link at the start)

 

• SQL Trace Flags Enabled
  • 1117
  • 1118
  • 1224
  • 2371
  • 4199

 

• Record level \ XSD security
  • Try to avoid using either but sometimes there are needed
  • XDS security is Microsoft’s replacement for record level security and now allows you to build policies with table relationships that can be applied to roles to restrict data
  • XDS processes on the AOS rather than the client which record level security does

 

• Database logging
  • This is a necessary evil but a few tips below
  • Set it up on a field level where possible rather than a table
  • Try and avoid high transactional tables or ones that are affected by batch jobs
  • Setup a clean-up job for dbo.sysdatabaselog
  • Either archive the data from the above table or make sure you have run the database log report and actioned or have all the changes in your audit system

 

• Update config keys
  • If you have been through an upgrade you will have had to turn on extra config keys for the upgrade path
  • They create a lot of ‘DEL_’ objects in the AOT which are no longer needed after the upgrade, it is advised you turn the config key off and drop all these objects

 

• Buffer size
  • This is the communication allowed between the AOS service and SQL
  • It will default to 24 for RTM and 48 for R2 and R3
  • Only increase this if you get an error saying the operations requires you too
    • This predominantly will be due to a mod, make sure the coding is as efficient as possible
    • Only increase it to the minimum it asks for

 

• Batch job occurrence
  • Only set batch jobs to run as often as they need, not more
  • This can be the cause of SQL blocks as the batch job continually locks down tables / fields
  • It can drastically decrease performance especially if you do not have a dedicated batch AOS service
  • Always set save history to ‘error’ this means that it will save the batch job history if it errors and not every time it runs as this can cause huge build ups of data in the batch job history table

 

• AOS Garbage collector config
  • I have found that as standard the AOS service will keep grabbing RAM and release it very slowly

• To resolve this change the following:
  “As standard the AOS uses the server .NET garbage collector. The .NET framework also has a client garbage collector. You change between then by adjusting the ax32serv.exe.config file, editing gcserver=true to false. The server garbage collector is lazy, as server applications are expected to be able to use lots of memory and not give it back, no need to balance all the other applications running on a machine. The client garbage collector is aggressive, it’ll try and give back memory sooner and keep the overall footprint lower. The client GC can be a useful option when machines don’t have much memory in them.”Go to the AOS BIN folder, normally found and make the change stated above C:\Program Files\Microsoft Dynamics AX\60\Server\<aosname>\bin

 

• Extra Tips:

• The following can be turned on to speed up the rendering of forms with a lot of data and tabbing / scrolling down that data. System admin, Client performance options
  • Fact boxes
  • Background data fetch
  • Preview panes
  • Form pre loading

 

• For data quality is to setup a routine for database consistency check which can check / fix data inconsistencies, system admin consistency check

 

• SSRS warm up issues
  • Often we get worse performance on reports for user in the morning, this can be due to SSRS going to ‘sleep’
  • To resolve this set up a few batch jobs to run reports before your users log on in the morning normally warms SSRS up

 

• AOS Server performance table cache set to 128k
  • System admin \ setup \ system \ Server Configuration
  • This is a commonly used form but in the grid there are hidden from view two extra areas, scroll down on the form and you will see the Performance Optimisation area

 

• AOS Server (VM) Power plan – high performance

 

• AOS Server (VM) LTRIM/Hints disabled in registry

 

Data quantities is a big contributor to performance, this is one that can gradually bring your production system to a halt so I have create a separate blog post for this with a link at the start of this blog.

 

 

 

 

 

 

 

AX 2012 The formatter threw an exception while trying to deserialize the message

The formatter threw an exception while trying to deserialize the message Dynamics AX 2012 R3 SSRS

For anyone who has had the misfortune of seeing this error it will mean that your AX system is currently throwing this error for all users for all SSRS reports.

If you have looked at other forums (there are lots out there) you will see most people jump straight to a full restart and potentially a CIL and some SQL table clear downs such as the following:

– Restart AOS

– Restart SSRS service

 

Maybe needed    – Delete records in table SysClientSession

From my experience a restart of all AOS’s will normally resolve the issue but for those of you who cannot afford repeated downtime in production there is a way around this normally without downtime.

Solution without downtime:

Essentially, we have to look at what exactly occurs when you restart the AOS and try and find the fix. On an AOS service restart it restarts (deactivates/activates) all the AIF inbound ports. These are located:

System administration > Setup > Services and Application Integration Framework > Inbound ports.

You will see I have highlighted one called BIServices, this is the inbound port AX 2012 uses to communicate with SSRS.

If you have multiple AOSs (PROD should have) you will need to look at which AOS SSRS is looking at for its communication. As by default SSRS will not load balance its traffic between AOS’s as the net services does not load balance unless there is an NLB (or similar) in place.

The solution is to:

1. Go to the inbound port called BIServices on the AOS that SSRS is using
   
2. Deactivate it
   
3. Activate it
   

This will normally resolve the error. If it does not then your best bet is to try the original restart all AOS’s and SSRS

Any feedback / questions please send them through.

 

AX 2012 Access denied to ‘table’ for system admins

By far the most frequent access denied to table error is related to security roles but during my time working with AX 2012 I have seen it twice affect system admins which was confusing for a few reasons…

• System admins cannot be restricted by security, the sysadmin role is an override command in the system giving access to everything without even considering if there is security setup for the area/action
• When this error occurred it was for all reports and was showing different tables with each different report

 

Essentially I found the problem to be in the CIL generation, I found this by restoring the  system with the issue over a different system

 

The way to fix it was to bring the whole system down, full Build Compiler , AOT sync then CIL compile.

On both occasions the issue has occurred due to not all best practises being followed with fixes going into systems.  The error message is very misleading so hopefully this quick blog will help.

AX 2012 Access denied to method within class

This type of error is a common in AX 2012, it occurs when the user has access to the correct entry points but is missing a method to execute the code.

Thankfully MS have been kind enough to give us the class and method the user has not got access to.

To fix this we need to choose the appropriate security role and go to the AOT. Open up the security role you wish to make the change to and also the classes node of the AOT, find the class referenced in the error. In this case the SysOperationFrameworkService class. If you expand the class you will find the method validateDataContract. Drag and drop this into the security roles server method section, by default it will give the invoke access right to this method which means it can execute the code. There are only two levels of access to methods, invoke and no access.

Invoke = has the rights to execute the code

No Access = cannot run this code

AX 2012 Field level security

An example of field level security is when your user can see all the data on a form but you want to hide one field. The same process would apply if you had full control to all fields but you wanted one field to be set to just read access.

First off you would find out the security role which you need to make the change to, once you have this log into AX as an administrator and navigate to where the field is in AX. Click edit on the form, now you can right click in the field you wish to set field level permissions on and click ‘Personalise’.

What you can see in the ‘system name’ area is the table and field name. In this case the DirPartyTable is the table and the field is Name. Once you have this information you can do either of the following.

Go into the AOT we can open up the security role in a new window and drag and drop the table in the error into it. Please note once you have done this you will have set every field within that table to the same permissions, you can however change this, explained below.

If you check the properties of the table in the security role you will see the effective access defaults to read for the whole table. you can change this by using the drop down. But if you want to only change this for one field you can right click on the table under the security role and click ‘new field’ you can then change the permissions just for that field.

Front end AX:

Go to the system administration module, setup, security, security roles

on the left hand side you will get a list of all the security roles, highlight the one you wish to add the table permission to and then click on the ‘override permissions’ button you will see at the top of the form.

You will then get the option to say if you are adding a table / field or server method. On the top bar click the ‘Add tables / fields’ from here you will be able to search for your table and add it to the role with the appropriate permissions. You will also be able to expand the table out and break it down to field level as well. As shown below.

 

AX 2012 Access denied to ‘table’

The above is an example of a common error with AX. The issue comes from the users security roles having access to the menu items to perform an operation  but not to the table.

There is two ways you can resolve this, either via the AOT or from the wizard in the front end AX. This blog will take you through both.

AOT:

The error message is kind enough to tell us which user is having the issue and which table. Our first step would be to check the user to see which security roles they have assigned and choose the appropriate one to add the table to.

Then going into the AOT we can open up the security role in a new window and drag and drop the table in the error into it. Please note once you have done this you will have set every field within that table to the same permissions, you can however change this, explained below.

If you check the properties of the table in the security role you will see the effective access defaults to read for the whole table. you can change this by using the drop down. But if you want to only change this for one field you can right click on the table under the security role and click ‘new field’ you can then change the permissions just for that field.

Front end AX:

Go to the system administration module, setup, security, security roles

on the left hand side you will get a list of all the security roles, highlight the one you wish to add the table permission to and then click on the ‘override permissions’ button you will see at the top of the form.

You will then get the option to say if you are adding a table / field or server method. On the top bar click the ‘Add tables / fields’ from here you will be able to search for your table and add it to the role with the appropriate permissions. You will also be able to expand the table out and break it down to field level as well. As shown below.

 

 

AX 2012 Command Buttons Security

On a previous blog I made a comment that most of the time when you navigate in AX you will be using menu items. Well the purpose of this post is to explain the most common situation when that is not the case.

Command buttons are common buttons throughout the system that have the same functionality, for example.

You are in a listpage and can see the ‘delete’ button on the action pane at the top. If you go to a different listpage the same delete button will be present. The way MS have done it is create standard functionality for this operation and allowed us to add the button to areas of AX as command buttons not menu items.

The way the security works for this is as follows.

The command button will inherit the security that you have set for its parent, so in the properties of the command button there is a field called ‘Needed permission’ if you have the needed permission access to the form that allows you to see the command button it will be present.

Please note that MS will not always have set this us as I would have thought, in some areas you will find a delete button with needed permissions set to none. This means if you allocate read access to the listpage you can see the delete button.

 

The way to get around this is to elevate the settings of the needed permissions so that it requires a higher level of access to the form before the command button is present but do not worry as

 

 

 

 

 

 

 

 

AX 2012 Entry Points

The easiest way to describe what an entry point is to start by explaining menu items. When you navigate through the AX client you will click on a variety of objects, forms, buttons, list pages, reports but they all have one thing in common. They have a menu item behind them in the AOT ( there are some oddities ).

I like to think of the menu item as the securable object, they are the fixed points you need to grant access to. This is where entry points come in.

They are references to menu items where you set the level of access.

So if you imagine you have one menu item to the custtablelistpage, it is not as simple as saying a security roles either has access to this area or it doesn’t. You need to option to give multiple levels of access to it.

So what entry points allow you to do is create multiple references to the same menu item but set different levels of access, different levels below.

• NoAccess
• Read
• Update
• Create
• Correct
• Delete

The only security objects you can assign entry points to are privileges.