Upcoming solution for Microsoft office Add-in authentication error with Dynamics 365 Finance
In the past few months I have encountered authentication issues while trying to use the Microsoft Office Add-in for Microsoft Dynamics 365 Finance for the customer I am currently working for.
The Microsoft Office Add-In enables you to receive data from the application, edit, enrich and then publish it back into Dynamics 365 Finance. This can be very helpful in situations where you want to process lots of records, which can be time consuming if you want to do this within the application.
Scenario
If you want to open data from Microsoft Dynamics 365 Finance and Operations in Excel you can use the Microsoft Office Add-in. In order to connect with the right environment you will have to enter the server URL first and then click OK to connect. This is where the authentication error can occur.
Error message:
Authentication error.
Details:
Unable to sign in as (user information). Please see the message center for details.
Message center details:
Token renewal operation failed due to timeout
Solution
Although there isn’t any formal cause stated by Microsoft, they have informed me that the product group is working on a solution that is planned to be released in Platform update 31. Besides that there will be an update available for the Microsoft Office Add-in by the end of October 2019.
Platform update 31 is expected to be available with Microsoft Dynamics 365 Finance version 10.0.7 but it has not yet been formally announced. The latest version is 10.0.6 with a release planned for November 2019.
Workaround
With a permanent solution ahead we have a workaround available. Please keep in mind that sometimes you will have to follow up this workaround a view times in order to successfully log in.
Session date in Microsoft Dynamics AX
Accountants would like to easy enter corrections at a month end date. For this reason in previous versions of Microsoft Dynamics AX there was an option in the Tools menu to change the session date and time. However this menu is not implemented in the new cloud based Dynamics AX, the option to change the current work date is still available. The option is even closer than you might think.
Microsoft Dynamics AX Client – One for all
The ‘new’ Microsoft Dynamics AX is now a web based application. There is now a “One for all” client. When you look at previous versions like Microsoft Dynamics AX 2012, there are two features which were also web based. This blog post will inform you about the Enterprise portal and Warehouse Mobile Devices Portal features in the new version.
Merry Christmas and 2016
We wish you all a happy holiday season. Our sincere thanks for your goodwill, loyalty and trust in Kaya services and Kaya team throughout the past year. We look forward to meeting you again next year in another year filled with prosperity and success. Here are our 5 tips for a perfect start to 2016 and to guarantee a perfect year.
1 : as a Dynamics AX Finance consultant 2 : as a Dynamics CRM solution achitect 3 : as a Dynamics AX SCM consultant 4 : as a Dynamics CRM developer 5 : as a Dynamics AX developer
The new Personalization options – part 2
A lot of investments had been done by Microsoft to get a new attractive user interface for Microsoft Dynamics AX using modern browsers. As there is no native client, also the personalization options are reconsidered. In previous previews there was an option to change the theme colors and a setting to display more or less information which actually manages a kind of zooming. With the latest preview (CTP8) there is a new way of doing form personalizations. In part 1 I explained some basic user options. In this second episode I will inform you about form personalizations.
(meer…)
The new Microsoft Dynamics AX (aka AX7)
A new era starts. Microsoft has released its public pre-release of the ‘new’ Microsoft Dynamics AX. This version was also code-named ‘Rainier’ and ‘AX 7’. Together with this release the information is no longer confidential. In this post I will provide some details to get access to this preview and will share my personal favorite enhanced functional features.
Tips on AX 2012 Security Development Tool – Part 8
Last year I posted some tips on the Microsoft Dynamics AX Security Development Tool. Recently my colleague Boye Walters also wrote an episode and named it Tips on AX 2012 Security Development Tool – Part 7. Well… he fanned the fire on his topic and here is another edition: Tips on AX 2012 Security Development Tool – Part 8; focusing on the view type Duties and Privileges.
Lean consulting
Lean Consulting is an attitude, an approach, and an implementation methodology that eliminates nonvalue-add activities in a software implementation, reimplementation, or release upgrade. It empowers the client to control the cost of their software project by economically guiding them toward self-sufficiency and ownership of their ERP system.
One of the most important characteristics of a Lean Consulting team is that it is smaller. At times, a niche specialist may be required to implement a certain complex application, but the Lean Consultant will have a greater breadth and depth of application knowledge. With the breadth of knowledge Lean Consultants have, fewer consultants are needed. A smaller, more knowledgeable team is more efficient. Like familiarity, having fewer consultants reduces communication errors and redundancy, translating directly to savings.
Lean Consulting starts with a highly interactive consulting team. Each member of the lean team is selected for their strengths from a larger roster in order to meet the unique challenges of a project. But the roster from which they are chosen must be small enough that the team members have all worked together on many implementations. Familiarity is the very foundation of any team. A team that has worked together many times before has reduced communication errors and they trust each other’s strengths, thereby eliminating redundant analysis.
Another crucial characteristic of a Lean Consulting team is experience. Lean Consulting is all about cutting out the waste to make the process more efficient, and in the world of consulting, time really is money. The more time a consultant spends learning on the job, whether it’s learning about the client’s industry, or figuring out how to implement an obscure piece of the functionality, the more money is wasted. In Lean Consulting, the learning curve is reduced to a minimum. Lean Consultants have 8 to 15 years of experience with the software platform, so they know how to implement it. They have diverse industry experience, so that they can bring best business practices to every implementation without learning on the job. The deep knowledge and understanding of the software that each Lean Consultant has is invaluable when implementing something as robust as an ERP system.
SQL Trace Flag for Dynamics Ax: Do we need it?
What is a SQL Trace Flag?
A SQL Trace Flag is an indicator that changes the way the SQL engine reacts. Why would we want to that? Well because the DBA always knows best, uh well actually because the standard behavior of the SQL engine will not always give the best possible performance for a Dynamics Ax ERP. Which SQL Trace Flag could be of interest for a Dynamics Ax Database? Well I found 5 that work great for some situations. (-T1117 -T1224 -T2371 -T4136 -T4199). Yes I said “some situations” because which SQL Trace Flag can be enabled depends on lots of things, and should be decided carefully. Let me inform you about these 5 SQL Trace Flags.
SQL Trace Flag T1117: TempDB in multiple files
When you divided the TempDB in multiple data files to match the number of CPU cores as recommended by Microsoft for Dynamics AX, SQL Trace Flag –T1117 comes in handy. It will change the standard behavior of auto growing database files in a file group. The standard behavior is to auto grow one of the files at a time. With this SQL Trace Flag enabled all files in a file group will grow simultaneously.
For example:
1 db divided in 4 data files with an initial size of 100MB and an auto grow of 20MB each. When your reaches the 400MB and all 4 files are full, one of them will auto grow to 120MB. But the next data will be written in the file with the freest space. Guess which one that will be…. The one that grew off course. Now the same example with SQL Trace Flag –T1117 globally enabled: When your reaches the 400MB and all 4 files are full, all of them will auto grow to 120MB. The next data will be written in the file with the freest space. Guess which one that will be…. All of them.
Should we enable this SQL Trace Flag this minute?
No, there is a drawback. This SQL Trace Flag is great for the TempDB but it affects all data files of all databases in the instance. They will simultaneously grow if they’re in the same file group. So think before you act, if you don’t want the files to grow simultaneously put them in different file groups, or don’t enable this SQL Trace Flag.
More information on this SQL Trace Flag: Microsoft Dynamics Ax Performance team
SQL Trace Flag T1224: Running big operation during the day.
When running big operations (5000+ rows) during the day, like master planning, they could be blocking your users from doing their daily business. This is due to lock escalation. SQL locks a page to make sure only one process effects that page at a time. When a certain number of pages of a table are locked by the same process SQL can decide to lock the table if this is less costly on memory. This will make all requests for pages in that table wait till the first process is done. SQL Trace Flag -T1224 Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation.
Should we enable this SQL Trace Flag this minute?
No, if you don’t need to run big operations simultaneously or with users in the system, you’re probably free of blocking by lock escalation. And lock escalation will have a positive effect on the performance of your SQL. So please plan large operations carefully outside office hours where possible.
SQL Trace Flag T2371: Keeping statistics up-to-date on large tables
The SQL query optimizer uses statistics to create query plans that improve query performance. Performance of Dynamics AX depends largely on up-to-date statistics. That’s why it is recommended to set “Auto update statistics” on. But when will the statistics be updated? Well when 20% of the rows are updated/inserted. This SQL Trace Flag is only available on SQL Server 2008 R2 SP1 and all later versions. There was a small update to this SQL Trace Flag in SQL Server 2008 R2 SP2 and SQL Server 2012 SP1 that makes it more precise.
For example:
On a table containing 1,000 rows the statistics are updated when 200 rows are updated. And the same table after a while containing 1,000,000 rows the statistics are updated when 200,000 rows are updated. Did you read that right? Yes you need to update 200,000 rows till the statistics are updated. Imagine even larger tables. We all know that in an ERP like Dynamics AX these large tables are pretty common. Microsoft also recognized that and released SQL Trace Flag –T2371. This SQL Trace Flag will make the 20% rule a dynamic percentage rule that kicks in at 25,000 rows.
For example with SQL Trace Flag –T2317 globally enabled:
SQL Server will update statistics on a table containing 1,000 rows when 200 rows are updated. And on a table containing 1,000,000 rows the statistics are updated when 32,000 rows are updated. In a chart the dynamic percentage rule looks like this:
Should we enable this SQL Trace Flag this minute?
No, first examine if your database has a lot of large tables (above 100.000 rows is a good rule of thumb). Without large tables this SQL Trace Flag is useless.
SQL Trace Flag T4136: Parameter sniffing optimisation
Dynamics AX uses parameterized SQL statements if we don’t use the ‘Forceliterals’ parameter in AX. I personally have never seen a developer who fully understood the working of ‘Forceliterals’ let alone use it in code. It even is best practice not to use it.
Why? Well when using parameterized SQL statements the query plans are cached and reused. SQL Server will use one plan for all similar queries instead of compiling each query. This can significantly reduce CPU overhead and improve throughput. That sounds like a good thing and it is.
But there is a drawback, if the queries are complex and/or the data distribution on the columns against which the parameter is compared vary, the cost of different plan choices can change. A plan that is optimal for one parameter value may perform poorly for another value. The query optimizer still needs to estimate the selectivity and cardinality of predicates using a parameter value. In Dynamics AX especially from version 2012 on the queries are almost always complex.
One way to avoid parameter sniffing is to put the OPTIMIZE FOR UNKNOWN option to all SQL statements leaving AX. Another way is to enable SQL Trace Flag -T4136. This SQL Trace Flag is only available on SQL Server 2008 R2 Cumulative Update 2, SQL Server 2008 SP1 Cumulative Update 7 and SQL Server 2005 SP3 Cumulative Update 9 and later.
For example:
If we have 2 companies in our Dynamics Ax Application and one only 2 customers and the other a couple thousand. The following could happen: if the first query we send to loop through customer is done in the company with a couple thousand customers , it could look like:
SELECT * FROM CUSTTABLE WHERE DATAAREAID = ‘@P2’
And SQL Server Engine will solve it by doing a full table scan because almost all records are needed.
Next time we do the same loop in the other company due to the parameter on dataareaid (and partition in AX2012) the query is identical. That is why SQL Server will solve it in the same manner, with a table scan, where a page scan could be more appropriate. If only SQL could store a queryplan per dataareaid….
Should we enable this SQL Trace Flag this minute?
No, because Microsoft provided a way to use parameters but not for dataareaid (and partition in AX2012). This makes SQL server store a queryplan per dataareaid, making SQL Trace Flag T4136 completely unnecessary.
AX2009 SP1: KB 3000700
AX2012 RTM: KB 2920058
AX2012 R2: KB 2969229
You can find more information on the blog of Dynamics in the field.
More information on this SQL Trace Flag: Microsoft Support on SQL Trace Flag T4136
SQL Trace Flag T4199: Query execution plan updates
Microsoft turned off All query execution plan updates/hotfixes by default to make sure that an update will not affect the way your query executes. Several of these hotfixes will positively boost performance for Dynamics AX. Microsoft controls all future query processor fixes that are scheduled to be ‘On-By-Default’ in a later release with trace flag t4199. This SQL Trace Flag is only available on Cumulative update package 6 for SQL Server 2005 Service Pack 3, Cumulative update package 7 for SQL Server 2008, and Cumulative update package 7 for SQL Server 2008 Service Pack 1 and all later versions.
Should we enable this SQL Trace Flag this minute?
No, there is are 2 drawbacks. 1: Your DBA will send you off to an insane asylum because of the next drawback. 2: If you enable this SQL Trace Flag you enable all future hotfixes/updates to the query execution plans, because we all know you’re going to forget about enabling this SQL Trace Flag.
More information on this SQL Trace Flag: Microsoft Support on SQL Trace Flag T4199
Security: Past, current and future
When you read the title of this blog post you might think this post is about changes during the lifecycle of Microsoft Dynamics AX 2012. Or maybe it is related to the security framework in older versions (AX 2009), the current version (AX 2012) or the new Microsoft Dynamics AX (aka ‘AX 7’). Well, it’s not. This post will tell you about a standard feature to restrict access on past, current and future records for tables which supports date effective data. It is called “Date effective policies”.