Quick Tip #1: Debug with reduced permissions

I discovered this neat trick when troubleshooting Security issues in AX 2012.

I’m not sure about you, but finding issues related to restricted security for a user is sometimes like looking for a needle in a haystack. Suffice to say that this often leads to users having more permissions than they should.

It would be nice if security related failures were at least logged somewhere, but currently they can present themselves in many different forms, often disguising the real issue.

Debugging code can be one way to troubleshoot security related issues, and is often a last resort. However if you remove yourself (or the user you are testing) from the System Administrator group, you cannot debug. And even if you didn't need to debug, it is inefficient to say the least. So here's how you can have your cake and eat it too.

How to steps

1. Start AX and modify the security for the user you are logged in as. This user must be a member of the system administrator role.
2. Open a developer workspace (Ctrl + Shift + W)
3. Ensure you set breakpoints in the code that you wish to debug.
4. Create a job and run the following code:
   

   SecurityUtil::sysAdminMode(false);
   

5. Open an application workspace from the development workspace (Ctrl + W)
6. Perform the task you are troubleshooting and the debugger will start when it encounters a breakpoint.

To revert to system administrator privileges, close out of AX and then launch the application how you normally would. Or alternatively, Change the code in your job to:

SecurityUtil::sysAdminMode(true);

and run. This will put the session back in admin mode. Just open an application workspace from the development workspace and you once again have full admin privileges. 

More information about this can be found in the Microsoft Dynamics AX 2012 White Paper: Role-based Security Use Patterns for Developers.

Lifecycle Services’ Issue Search making life easier

I’ve been playing with Lifecycle Services (LCS) for Microsoft Dynamics AX that was release in June of this year and have become a big fan of the issue search feature. LCS is a cloud-based portal for customers and their partners to manage projects, from pre-sales through implementation and operation.

To use the issue search feature, you need to create a project, from which you can then click on the Issue Search tile to begin searching.

From the search page, enter you search criteria. The KB article number if you know it,

or a search term.

Once you've found what you're looking for, click on the article link to get more information about the issue.

And here's what I love most about this new feature. From this page I can download the hotfix and evenb view the changes that have been made to the AX objects and code.

Code that is highlighted red is the code in the original version that ha changed, code that is highlighted green, is the new code provided as part of the hotfix. You can see in the example above that the change to this method is the deletion of 4 lines of code.

So why am I a big fan?

Well I think it is more to do with how poorly Microsoft has provided this feature previously. Searching for issues through Partner/Customer Source has been painful to say the least. Even armed with the KB article number often yields no results. Recently I did manage to find a KB article on Partner Source of which the content proved to be extremely helpful. The following day I could not find that same article. I had to trawl through my internet history to find it. Why didn’t I just use LCS then you ask? Well, here lies the gotcha with early adoption, while I did find the KB article easily on LCS, Partner Source had much more information. So obviously not all the KB article information has been transferred to LCS at the time of the writing of this article.

Other than just a seemingly powerful search engine for finding information about logged issues, I especially like that I can review the objects and code affected by the hotfix, without having to install it first. In my experience, it is rare to find a hotfix that only includes code for your specific issue and on the specific version of AX you require. Code analysis is usually required at some point, and to be able to do this upfront without a deployment, is a great advantage.

Still ironing out some issues with issue search

Even though my experience to date has been mostly positive, I have stumbled across the odd issue with this feature. For example, clicking on the title link of some of the issues gives an error. Also, not all KB articles provide the ability to view object and code changes.

More information please

It would be great if Microsoft provided information about the specific build the hotfix was intended for. At the moment the only information you get is major release versions, e.g. Dynamics AX 2012 or Dynamics AX 2012 R2. I deployed a hotfix on AX 2012 RTM only to find out by trial and error that it depended on CU1.

Further still, some narrative about the actual issue reported and any configuration requirements or new features introduced to address the issue would be helpful. I guess we can expect to see more of this with time.

Conclusion

The new issue search feature in LCS, though not perfect, is a vast improvement on what the Dynamics AX community has had before. Further enhancements to this, should only help to improve the often time consuming, costly and tedious process of issue resolution.

Database Engine Tuning Advisor (DTA) gotcha with AX and indexes

Performance tuning AX SQL queries seems to be the norm these days since the release of AX 2012 and today I was bitten by my efforts to reduce a query's execution time.

I often use Microsoft SQL Server Profiler to collect information about database activity, in order to detect performance bottlenecks. Sometimes I discover something blatantly obvious that only takes a quick tweak of the query or new index to make right. Other times when the answer is less obvious or there are far too many results to analyse manually, I opt to see what recommendations the Database Engine Tuning Advisor (DTA) will come up with. To date, this has served me well and I haven't had any issues, until today.

At some point after finishing my analysis on a test environment under load, a full database synchronisation was performed as part of a deployment, and it failed with the following error message.

SQL error description: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot drop the index 'CUSTTABLE._dta_index_CUSTTABLE_6_1915921947__K94_K93_K83_K96_K152_K1_K149_2_3_4_5_6_7_8_9_10_11_12', because it does not exist or you do not have permission.

SQL statement: DROP INDEX CUSTTABLE._dta_index_CUSTTABLE_6_1915921947__K94_K93_K83_K96_K152_K1_K149_2_3_4_5_6_7_8_9_10_11_12

Here's the screenshot.

So from a little research, it seems that if the DTA fails it does not clean up hypothetical indexes, and leaves them in the sys.indexes table of the database you are tuning.

As the code that generates the errors is in the AX kernel, I'm not sure exactly why it detects these indexes however I hypothesise that AX is doing a look up on the SQL system table indexes to get a list of indexes, that are then compared to those on the AOT. AX will then attempt to generate DROP INDEX SQL statements to clean them up. So why then wouldn't it just drop the indexes and be done. It looks to me like there is a size limit on the number of characters for either the SQL statement that AX generates or part there of. As such, the complete index name is truncated and the index that AX is trying to drop cannot be dropped as it truly does not exist.

As it turns out, this is a known 'feature' of the DTA and the deletion these also documented by Microsoft.

To fix the issue, I wrote the following SQL query to generate my DROP INDEX statements:

SELECT 'DROP INDEX ' + i.name + ' on [' + o.name + ']'
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE i.is_hypothetical = 1
  AND o.type = 'u'

You may find also that the DTA has also left behind some hypothetical statistics that need cleaning up. Here is the SQL query to generate the DROP STATISTICS statements:

SELECT 'DROP STATISTICS [' + object_name(i.[object_id]) 
        + '].['+ i.[name] + ']'   
FROM sys.stats i 
WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1 
  AND i.[name] LIKE '_dta%' 
  AND user_created = 0

Run these queries in SQL Server Management Studio (SSMS) against your AX database, copy the output into a query window in SSMS and execute. You may wish to review these before actually executing, that is why I prefer to generate the code first and then run, rather than run a script that runs dynamically generated code.

Now run the database synchronize in AX and you should no longer get errors.

AxBuild.exe: A new build tool for AX 2012 R2

With the release of cumulative update 7 (CU7) for AX 2012 R2 comes a neat little build tool, AxBuild.exe, which has the potential to significantly improve the performance of the X++ compilation.

Located at %Program Files%\Microsoft Dynamics AX\60\Server\<AXAOSName>\Bin\ AXBuild.exe is a command line tool with just one command, xppcompileall that runs a full X++ compile for the Dynamics AX model store.

Compiling on the server tier, AxBuild starts several temporary AOS processes (called workers) that run concurrently and complete parts of the full compile. By default the number of workers is based on the number of available processors.

What excites me most about this advancement for AX is the opportunity it gives for automating builds (and even deployments). While this is not new for AX, a significant decrease in compile time makes it more practical for deploying built code quickly and provides a more compelling reason to use tools such as Team Foundation Server (TFS) as a means to control the release of code changes across environments. Another step further away to the sometimes tedious and error prone task of xpo deployment.

For a complete overview of AxBuild.exe, visit http://msdn.microsoft.com/library/d6da631b-6a9d-42c0-9ffe-26c5bfb488e3.aspx