BizTalk System.EnterpriseServices.TransactionProxyException

Are you tired of reading article after article on this error telling you to check the firewall settings? Tired of vague statements about checking network settings? Directions to use some crazy tools to blah this and blah blah that? Well my friend this post is for you.

I hit the dreaded System.EnterpriseServices.TransactionProxyException during my BizTalk install and spent 2 days trying to figure out the problem. Hopefully this article will save you some time.

I did download and use the DTC Tester as part of troubleshooting. So at least one crazy tool was used in this process.

• Find the DTC Test tool on the google. 
• Setup an ODBC connection on your App server to point to the DB Server (you may create a test account to mess with. I created a temporary sysadmin account)
• Fire up a Command Line on the APP server and browse tot he folder where you extracted the DTC Tester tool
• Execute the Test Tool
• dtctester <ODBC Name> user password
• Review the output.

If you are like me you will receive some output like the following.

This is where you typically start reading the fun check your firewall posts and the vague network settings checks, blah blah. I exhausted these suggestions pretty quick and finally stumbled on this blog post: http://mindthe.net/devices/2010/12/06/fixing-msdtc-between-two-machines-on-different-domains/

Like a beacon in the night. This fella was having trouble with different domain setup but the fix worked for me.

• Open Command prompt as Admin if you closed it earlier
• Execute the following commands
• msdtc -uninstall
• msdtc -install
• msdtc -resetlog
• Restart the Distributed Transaction Coordinator service
• Execute the DTC Tester Tool using the steps above again
• Crack open that celebratory Mt. Dew and give yourself a high five:
  
  

The second DTC Test was successful. I then tried to configure the BizTalk Group configuration and it was successful. All other BizTalk Components were also successful.

Hopefully this article saves you a little trouble and time. Good luck.

Read More

Install AX Enterprise Portal on SharePoint 2013 Multi Server Farm

How I Installed Enterprise Portal on a SharePoint 2013 Multi Server  Farm

Brief overview of SharePoint 2013 farm.

• 1 Application server on Windows 2012 R2
• 1 WFE server on Windows 2012 R2
• 1 Index server on Windows 2012 R2
• 1 SQL Server 2014 on Windows 2012 R2

Permissions:
          After fighting with permissions for a while I finally resolved my permissions issues.  Now this was my first successful install and it is a Dev environment so I’m was not really worried about giving too many permissions.  There are 2 accounts AX uses that I had to grant permissions to in my SharePoint Environment and on my servers.  AXBCProxy account and the AX_Install account.  I entered both of these accounts in the Server Admins group on all the SharePoint Servers in the farm.  I also made them Farm Administrators and Assigned them full control in the Web Application.  In SQL I gave them Db Creator role and in the SharePoint _Config and SharePoint_AdminContent DB’s I gave them WSS_Content_Application_Pools Role.

Note: My normal SharePoint configuration I would be running Microsoft SharePoint Foundation Web Application only on the WFE server but for the install of EP I will run it on the Application server as well while I set this up.

We are running AX 2012 CU 9. The AX environment was already configured. I then built a SharePoint 2013 Farm pathed to May 2016 CU.

Now I tried installing the AX software just on the WFE server initially, howerver this did not work. The EP portal template option never appeared when selecting SharePoint 2010 Custom Template while creating the EP Site Collection.  Some of the documentation I’ve read said to install EP on a multi-server farm you had to create a DNS record and point it to the WFE. That did not work for me so I came up with this alternative.

SharePoint Instructions:
         Log on to the App Server with your SP_Farm admin account. Create a new Web Application.  I called mine http://WFEMachineName:Port but I used the WFE’s Machine Name and not the App Servers. I then created a root site collection. This allows you to see that SharePoint is working correctly before installing Enterprise Portal (EP).  I always temporarily turn off Anti-Virus on the App\WFE servers before starting the install. 

AX Instructions:
              Now logon to the App Server and WFE Server with your AX_Install account and run the Prerequisites checker for all the pieces of AX you wish to install.  After the prerequisites have been installed.

I deployed the following AX components on the App Server and the Web Server. I started the Dynamics AX wizard and installed Client Components> Client & Remote Desktop Services integration Note: I did not install Office add-ins

Next I installed  Management Utilities and finally I installed EP on both the WFE and App servers. The EP install is pretty straight forward until you get to the Configure a Web Site for Enterprise Portal page.  Select the Web Application that you recently created. Then uncheck all of the check boxes and Click Next> Click Install>

I tried the install with the boxes checked and it was never successful. It cost me a lot of time troubleshooting and I never got it to complete successfully.  I found this blog helpful but it did not answer all my questions: https://blogs.msdn.microsoft.com/


After successfully installing EP on both servers.  I edited the Hosts file on the App server.  I entered the IP address for the App Server and the name of the WFE server.

Example: 10.1.1.2    WFEServerName

Create the EP Site Collection:
      Login to Central Admin with the AX Install
             Create Site Collection>
                      Select the Web Application you created for EP: Fill in the required fields.
Example:
     Title:  EP AX Dev
     URL: http://MachineName:Port/sites/DynamicsAX
     Select Experience Version> 2010>
     Click on Custom Tab>

    Select Microsoft Dynamics Enterprise Portal>

    Add Site Collection Administrators

    Click OK>

Almost done:

          After the site is created test it from the Application Server.  If it works, and it should, double check that the site was also deployed to the WFE server. Test to make sure the site works from the WFE Server as well.  Now delete the entry put in the HOSTS file on the App Server. Back in Central Admin go to Manage Services on Server > Select the App Server > Click Stop Microsoft SharePoint Foundation Web Application This can take a minute or two.  After it has stopped go to IIS on the App Server and check to see that the site has been removed. Now test to make sure you can still get to the EP website. This worked for me and I hope it helps you. 

Regards,

Scott Davis

Read More

Export CRM Users List Using PowerShell

How this came about:

     I was asked by the Manager of the QA team to send her a list of who had access to the QA environment; after we performed a refresh from production. I'm fairly new to CRM and was looking for ways to do this.

1st) I logged into CRM and went to Settings, Security, Users, Selected all users and then clicked Export to Excel.

This would work just fine if it was a one-time deal. You would still need to do it for both Enabled and Disabled users. I don't believe it will be a one-time report so I started looking for a way automate the process.

2nd) Since these are Dev and QA environments I was not too concerned with querying the database. Below is the PowerShell Script I came up with that allowed me to query both environments at the same time and save the files as a .csv

Read More

CRM Audit Counts Report

During our first few deployment weeks, management wanted some metrics showing user activity. If you have auditing turned on CRM will track when Opportunities or Accounts are changed and will show who changed them. The actual change is not logged but you can at least so who made a change.

Management decided this metric would bring them some value so we created a quick SQL Job to send out a daily Audit report. This query will give you a count of all the Opportunity and Account changes over the last 5 days.

The script contains a hack needed to make the CSV display correctly in Excel. So the [sep=," portion of the script is a special character the Excel will ignore. If you open the CSV in a text editor you will see this special character. The consumers of this data would only use Excel to open the CSV the hack works great for this purpose.

Read More

Azure Speed test using DiskSpd

The decision was made to utilize Azure as IaaS for a large portion of our AX project. We learned some quick lessons in IO speed using Azure right off the bat. I created this quick post to document some of the tools and steps we used to measure IO speed that helped us identify various IO bottlenecks and re-engineer some of our environment settings.

Microsoft released a tool a while backed called DiskSpd. This tool replaced the old tool SQLIO. It is pretty easy to use but will require some tweaking of parameters to get it to simulate various read/write payloads.

100% Reads 8K Blocks

We started off using 100% Read Payloads using 8KB Blocks since SQL Server is organized in 8KB Pages.


You can review the documentation of DiskSpd for details on the various parameters and options.

The initial results were awful. The MB/s were below 10, AvgLat was above 80ms. We contacted Microsoft with our results and they sent us over some build guides to help us maximize performance. After we applied their settings the speed was better but still terrible compared to our On-Prem storage.

We ended up moving some of our DEV Servers to a Azure Data Center that had premium storage. One key take away from the Azure experience is that if you need high performing IO (for SQL Server and such) you will need to use Premium Storage and get ready to spend some cash.

I wrote a PowerShell script that would execute a DiskSpd test every 20 minutes and then store the results in a Database. We had several DEV servers in the project and i wanted to see how the disk performed over several days. Here is the PowerShell



The powershell script calls a real simple Stored Procedure on the SQL Server I am using to store the stats.

Read More

CRM Organization Import Could not find stored procedure p_CreateAuditEntry

During our DEV server builds we came across a bug during an import of CRM Organizations. When we would try to import a refresh of production to DEV we received the following error in the Install log.

Could not find stored procedure p_CreateAuditEntry

After some testing we figured out that this error happens when you try to restore a second copy of production on the same DEV machine as a different organization. We wanted multiple organizations on our DEV server to test different upgrades and such. The first copy of production would import just fine. However when we tried to refresh a second copy of prod into a different organization we would hit this error.

The fix is pretty simple, however you cannot run this fix in a production CRM envrionment. Running the fix will void your warranty with Microsoft because we are modifying the MSCRM_CONFIG database.

DO NOT RUN THIS IN YOUR PRODUCTION ENVIRONMENT EVER.

Connect to your MSCRM_CONFIG Database using SQL Server Management Studio and execute the script below.

Now you can try to import your organization again and it should no longer give you an error.

Read More

CRM Refresh using PowerShell

We just started using CRM and wanted to get rid of the manual process of refreshing CRM from Production to QA\Dev for our developers.

Our environments

Developer Boxes
We have 14 dev servers running on Windows 2012 R2.
These boxes run SQL 2012 and CRM 2015.

Dev Servers
We also have 2 Dev Environments that each have a CRM 2015 application server running on Windows 2012 R2 and a SQL 2012 Server.

QA Servers
We have 1 QA environment that is setup identical to the Dev environment and we have 1 QA environment that has 2 CRM 2015 application servers with one SQL 2012 server and one SSRS 2012 server.

Production Servers
 2 CRM 2015 application servers on Windows 2012 R2, 1 SQL 2012 Always On availability group and 1 SSRS Always On availability group. Then we have the same configuration at our Disaster Recovery site.

So with all these different environments we have been asked to refresh them periodically. The all in one developer servers are pretty straight forward since all of the software is installed you do not need to add install additional software.

MSCRM_CONFIG Hack

Proceed with Caution

We found a bug when moving between the various envrionments. Example: If you take the production database backup and restore it to  your DEV environment to create an Organization, then you try to create another Organization with that same backup you will receive the follow error during import:

Could not find stored procedure ‘p_CreateAuditEntry’

We were trying to setup multiple Organizations on a DEV instance to test various upgrades. In order to get around this we found a solution on this blog: http://www.powerobjects.com/2015/04/29/error-importing-organization-after-update-0-1/

The below scripts are a hack that you should NEVER do in a Production environment. Modifying the MSCRM_CONFIG database void your Microsoft support agreement.

Prerequisites for Refresh

• The account you use to do the refresh needs to be a Deployment Administrator and have admin permissions on the application server and the SQL server. 
• It also needs sysadmin rights in SQL and needs to be the same account that runs the "CRMDeploymentServiceAppPool" in IIS. 
• For Multi-Tier Installs (dedicated App server and SQL Server) Install below files on the Application server:
• 1.Microsoft® System CLR Types for Microsoft® SQL Server® 2012 (SQLSysClrTypes.msi)
• 2.Microsoft® SQL Server® 2012 Shared Management Objects (SharedManagementObjects.msi)
• 3.Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012 (PowerShellTools.msi)
• UserMapping.xml file example. This is needed if you are mapping a Production install account to your Dev environment. We use separate accounts for our PROD, DEV, and QA environments. See below for sample file.

Refresh Script 

The below script we wrote after doing a pretty good amount of research and not being able to find all the information we need in one location. Please use this with care we are not responsible for your use\misuse. Open PowerShell as Administrator. I suggest saving the below file as a .ps1 and then executing it from PowerShell. 

Read More