Thursday, August 21, 2014

Mariano Gomez does the ALS #IceBucketChallenge

Thanks to my good friend David Musgrave over at Developing for Dynamics GP for nominating me to the ALS #IceBucketChallenge. David was originally nominated by MVP Jivtesh Singh. See his blog post and video here:

David completed his challenge (and donation yesterday) and posted this article on his blog as proof of his accomplishment. You can see his challenge video below:

Of course, supporting the cause and accepting the challenge is what this is all about so here is my poor attempt at self-filming along with drenching myself - and no, I don't have a pool and no, it's not 62 degrees, but the water was ice chilling!

Since I forgot all about nominating anyone in the video, I take these few extra lines to nominate my kids Laura, Angie, and Miguel Gomez and the Reporting Central team headed by Gianmarco Salzano and Shane Hall. You have 24 hours to complete the challenge.

Until next post!

Mariano Gomez, MVP
Intelligent Partnerships, LLC

#GPUG Summit 2014 St. Louis Schedule

I want to begin drawing some attention to my presentation schedule at the upcoming GPUG Summit 2014 in St. Louis, MO where I will be once more delivering some cool and thoughtful sessions around some very relevant topics.

CodeSessionRoomDate and Time
TOT02Mariano's Toolbox: Integration Manager, Please!
Session Level: Intermediate
231Oct 15, 11:00 AM
STR04Mariano's Toolbox: Web Client Deployment for You!
Session Level: Intermediate
240Oct 15, 4:30 PM
ITP06Mariano's Toolbox: Why the Support Debugging Tools is a Customer Favorite!
Session Level: Intermediate
229Oct 16, 9:30 AM
UPG07Mariano's Toolbox: Upgrading to Microsoft Dynamics GP 2013 for Dummies
Session Level: Intermediate
242Oct 16, 2:00 PM

To make your participation more enticing, all my sessions are eligible for CPE credits, so please visit the Registration page and sign up. You can check out the full sessions schedule here.

If you want something to do before the event, there are pre-conference training classes available on October 13 and 14 and offered by the GPUG Academy.

Finally, this year I have been nominated to the GPUG All Stars and would appreciate your vote. Please help me attain this important achievement.

Until next post!

Mariano Gomez, MVP
Intelligent Partnerships, LLC

Tuesday, August 19, 2014

Microsoft Dynamics GP 2013 R2 Installation: Utilities changes

When executing Dynamics Utilities for a brand new installation of Microsoft Dynamics GP 2013 R2 you may have noticed a new window in the guiding wizard. This window is the Web Client SQL Server Login window, which allows you to specify a common SQL account that will be created during the system database setup process.

Web Client SQL Server Login window

In GP 2013 R2, users accessing the web client exclusively no longer require a SQL account to access the underlying data in the system and company databases they have been assigned to. However, their Active Directory credentials must be associated to their Microsoft Dynamics GP account (stored in the Users Master table (SY01400). Access to data is afforded via a common SQL Server login once the Active Directory credentials and the Microsoft Dynamics GP user credentials have been validated. This provides users with a single sign-on experience from the web client.

User Setup

The above is described in more detail by Jason Lech, Escalation Engineer at Microsoft in his article on Identity Management.

Until next post!

Mariano Gomez, MVP
Intelligent Partnerships, LLC

Thursday, August 14, 2014

#reIMAGINE2014 Keynote Speaker: Doug Burgum

Doug Burgum in his days

Yes, THE Doug Burgum will be the keynote speaker at Microsoft's #reIMAGINE2014 conference in Fargo, ND., hosted by Dynamics Partner Connections. If this is not enough reason to go to Fargo in November, then I don't know what is.

Of course, Doug has changed a bit from his days at Great Plains Software and Microsoft and this is probably the man you will see on stage at the Historic Fargo Theater.

Doug Burgum today

So sharpen up on your Microsoft Dynamics GP history and join us there. You can register by visiting the reIMAGINE 2014 site. Not only will the keynote be off the charts, but you will get to learn a ton.

Still need a reason to attend, then have a look at the series of posts by Pam Misialek on the Inside Microsoft Dynamics GP Blog:

Until next post!

Mariano Gomez, MVP
Intelligent Partnerships, LLC

Wednesday, August 6, 2014

Deploying Business Analyzer Companion App Services on Windows Azure Service Bus

Business Analyzer App for Windows 8.1

If you are not using Business Analyzer today, shame on you. Last year sometimes I wrote a small article on how to deploy the Business Analyzer Windows 8 application on a personal laptop, which is typically how most of us road warriors need it to be for demo purposes. However, here at Intelligent Partnerships, we've changed our approach a bit: all our infrastructure and demo environments now reside on Windows Azure, which gives all of our people the flexibility of having a single environment and consistent across the board, while allowing our accounting staff to do work from anywhere in the world.

Today, I want to talk about another one of those cool features in Windows Azure: The Azure Service Bus and how you can leverage it to run the Business Analyzer app for Windows 8.1.

I really don't like to oversimplify anything, but think of Azure Service Bus as cloud based message queuing system, akin to MSMQ, but running on a much more robust platform. The advantage is that applications are no longer bound by myriads of layers (like firewalls, complex authentication, etc.) to communicate with each other and can reside anywhere and on any device that can communicate to the Internet.

So let's get started...

1. To setup a Service Bus you sign into Azure's management portal, then click on the Service Bus option on the left navigation bar.

Service Bus service
2. Click the Create button to add a namespace for your service bus. The namespace identifies the service in Windows Azure and is assigned an address. You can then choose the region where your service will be hosted.

Service bus namespace

By clicking the Ok button, Windows Azure proceeds to activate that namespace if it's available. If it's not available, you will be prompted to enter a new namespace, before you can continue.

3. You can then proceed to install Business Analyzer Companion App Services on one of your virtual machines (preferably not your SQL Server). The installation is straight forward and all you need to do prior to running the setup executable program (setup.exe) is to install the reporting services configuration reports for Business Analyzer. Those are provided in the Companion App services SSRS zip file (

4. Once the Companion App Services application is installed, you can proceed to launch the configuration app, which can typically be found in the C:\Program Files\Microsoft Dynamics\GP Companion App Services\ folder.

Welcome screen
The Welcome screen displays the Companion App Service current connection information, which should later on be replaced by the settings you establish for this instance.

5. After the Welcome screen, you are presented with the Windows Azure configuration screen.

Windows Azure Service Bus Configuration window
In this window you will enter information about the Service Bus you previously configured using the Azure management portal. For the most part you specify the namespace, issuer, and issuer key which can be found

Service Bus connection information
Note the default issuer and default key must match the issuer name and issuer key, respectively, in the Azure configuration window. Click Next to continue - you will experience a short delay while the wizard validates the Azure service bus information you provided.

6. On the Host Configuration screen you can enter the host name and port of the machine that's going to be running the service - typically, just accept the default port. The beauty here is, since we already configured access via the Azure service bus, it's not necessary to expose the public name ( of the host and create an end-point for the port, thus exposing our servers to the world - this is why we created the service bus to begin with!

7. Next on is to select the Companion Apps that will be used with the service. You will want to mark both of them here.

Select Applications

8. You will then want to choose the Data Connections to use with Companion App Services. In this case you have a choice of either Excel Reports or SQL Server Reporting Services reports.

Data Connections
9. Next you must identify where to find the Excel reports using UNC path to specify the folder. My reports happen to be at \\servername\gpfiles\excel reports\reports.

Shared Excel Reports folder

10. You are now asked to enter the address to Report Server and specify a folder where the reports can be found, if you happen to have multiple instances of GP deployed.

If you have deployed SharePoint Integrated mode, then click the checkmark and specify the library address for the reports.

11. Once the report server information is validated, you will receive a confirmation page with the address to be used when configuring the Business Analyzer application.

Configuration Complete window
The address we are particularly interested in is the Service Bus address, which in this case is simply ip-contoso. (with the period at the end).

12. Open the Business Analyzer application, the go to the Configuration window from the Charms on the right. You can then enter the ip.contoso. address in the service configuration.

Business Analyzer Configuration

Sweet! Now, we have configured BA without really exposing the public address of our Azure VM, which should make system administrators very happy.

Mariano Gomez, MVP
Intelligent Partnerships, LLC

Monday, August 4, 2014

Implementing a Windows Azure Site-To-Site VPN

Hi everyone! I'm back! The past few months have seen an increase in projects here at Intelligent Partnerships so I have been shouldering some very challenging projects involving some very complex integrations. Thank goodness for eOne Business Solutions' SmartConnect and SmartPost products which have made for some very happy customers and some extremely robust integrations. In addition, I've been working on quite a bit of customizations migration from Visual Basic for Applications (VBA) to some cool hybrid solutions using Microsoft Dexterity and Visual Studio Tools (VST) for Microsoft Dynamics GP. If you remember, I was one of the pioneers of the technique to replace VBA customizations for hybrid Modifier and Visual Studio Tools. You can read more about this technique in the articles:

Hybrid development for the Managed Code developer
Hybrid development for the Managed Code developer (cont.)

Well, today I wanted to talk about Microsoft Azure Virtual Networks and specifically, the steps needed to setup a Site-to-Site VPN between your Windows Azure virtual network and your Local Area Network.

As you may recall from previous articles I've written on the subject of deploying Microsoft Dynamics GP on Windows Azure, a pre-requisite step before provisioning the VMs that will host SQL Server and your Microsoft Dynamics GP application is to provision and configure a Virtual Network, to be used to communicate the VMs. Also, if you are not going to have your Azure virtual network talk to your on premise LAN, chances are you also need to provision a VM to run Active Directory Services and Domain Name System (DNS) for name resolution - I guess, the typical networking stuff.

As with everything else, life is never this ideal, and most organizations have an on premise AD DC and DNS already in place, so the question is "Why can't we simply use Azure as an extension of our network without having to provision these additional services?" The answer is, of course, you can!

To make the setup process happen smoothly, you must first have the following information at hand:

1. IP address and name of your DNS server
2. Internet facing address of your VPN server or appliance
3. Address space and subnet masks for your local area network (in layman's terms, the range of IPs covered by your TCP/IP network)
4. Optional: Address space you want to use to setup your Azure virtual network.
5. An AD account with privileges to join computers to the domain

To get started, logon to the Windows Azure management portal ( Once in the management portal follow these steps:

1. The first task at hand is to identify our on premise network. Click on the Network Services and Local Network to define your on premise network. Once on the page, click on Add a Local Network.

On premise Network (LAN) setup

2. For this example, we will call the network CONTOSO-ONPREM. You will be asked to enter the public IP address for the VPN. For this example, our CONTOSO-ONPREM network's can be reached at Click the checkmark to proceed to the next screen.

LAN details

Note that this is simply an Azure identifier for your on premise network and not necessarily the actual physical name of it.

3. Now, you must define the range of valid IPs of your on premise LAN. Particularly important here is to know the starting IP address of the on premise network and the Classless Inter-Domain Routing (CIDR) method use for IP address allocation.

Address space

We will assume the CONTOSO-ONPREM routing prefix is and that the usable address range is anywhere from to which gives us a subnet mask of (or 24 "1" bits). Please consult with your on premise network administrator if unsure.

4. Once the on premise network has been defined, you can proceed to register the DNS server for by clicking on the DNS Servers option or clicking the +NEW action button on the lower left corner of the portal.

DNS Server registration

In our example, the CONTOSO-ONPREM DNS server can be found at My preference is simply to assign the FQDN name of the DNS server, though, again, this is just used in Azure to identify the object and rather not necessarily related to the domain name of the machine.

5. Provision virtual network. For our example, we will call this network CONTOSO-AZURE.

CONTOSO-AZURE network provisioning
We can the click next to continue. In the next page of the network provisioning, you will be asked to identify the DNS server and establish the VPN connection to the on premise network.

Azure does a pretty cool job at showing a graphical representation of what you are configuring. In the next page, you can pretty much setup your address spaces and subnet masks. However you must add a subnet mask for your Azure gateway, by clicking the Add Subnet button.

For the purpose of our example, our Gateway IP address will be

Click ok to continue.

6. Azure will now provision the network and make the setup based on the choices you selected. Once the network is provisioned, you can now go to the Dashboard and should see the following:

7. Next steps now involve setting up the VPN tunnel on your firewall to communicate to the Azure gateway.  For this you have a choice of downloading a number of ready to go VPN device scripts (supported devices include Cisco, Juniper, and software-based as Microsoft's). If you cannot find your specific device, it's always good to do a search over the Internet as someone has probably already figured out some configuration for the specific model you have. Important as well is to record the Azure network key, which you will need when setting your VPN.

Once you have established connectivity with your on premise VPN, you can begin provisioning your VMs, making sure you select your virtual network in the process.

Hopefully you found this article useful now that Azure is becoming more and more a choice for hosting Microsoft Dynamics GP.

Mariano Gomez, MVP
Intelligent Partnerships, LLC

Wednesday, June 11, 2014

Using Microsoft Dynamics GP Business Intelligence deployment utility to deploy custom SSRS reports

Using Microsoft Dynamics GP Business Intelligence deployment utility to deploy custom SSRS reports - Part 2

In the previous installment, I outlined a technique to deploy your custom SQL Reporting Services reports using the standard Business Intelligence deployment utility provided by Microsoft Dynamics GP (Administration | Setup | System | Reporting Tools Setup). In this installment, I will provide the scripts needed to update the Microsoft Dynamics GP system database tables, sySrsReports and sySrsReportDataSources, respectively.

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Generic license.
declare @objectId uniqueidentifier;
declare @rptContent varbinary;
declare @reportName varchar(255);

-- custom report name information
set @reportName = 'Contract Deferred Revenue Detail Report';
select @rptContent = Content from ReportServer.dbo.Catalog where Name = @reportName;

-- setup a new GUID for the custom object
set @objectId = NEWID();

-- adds record for custom SRS report
insert into sySrsReports(ObjectID, LanguageID, DictionaryID, ObjectType, ObjectDescription, FolderName, DisplayName, TableName, CurrentVersion, MinSrsVersion, IsKpi, IsIsv, IsMultiCompany, IsCRM, IsConfigurationFile, BinaryBlob)
values(@objectId, 0, 0, 2, '', 'Sales', @reportName, 'MainTableNameHere', '12.00.1295', '10.5.1600', 0, 0, 0, 0, 0,  @rptContent)

-- add data source information with the following DataSourceType and DataSourceID values
-- DataSourceType DataSourceID
-- 1   DataSourceGPSystem
-- 2   DataSourceGPCompany
-- 3   Custom data source name
-- 4   DataSourceCRMOrganization

insert into sySrsReportDataSources(ObjectID, DataSourceType, DataSourceID, ModelID)
values (@objectId, 3, 'YourDataSourceName', 'Model_ID_Value');

A bit about the script...

The first step is to declare a couple variables that will host the report name and actual report definition language (rdl) content for the report. This information can be found in the dbo.Catalog table in the ReportServer database.

Next, we need to create a unique identifier (GUID) value for the report that we need to deploy. With this information, we can proceed to add the SRS report information to the sySrsReports table.

We can then add the data source information to the sySrsReportDataSources table. Keep in mind that there needs to be an entry for each data source listed.

That's it!

Until next post!

Mariano Gomez, MVP
Intelligent Partnerships, LLC

Monday, June 2, 2014

Using Microsoft Dynamics GP Business Intelligence deployment utility to deploy custom SSRS reports

Using Microsoft Dynamics GP Business Intelligence deployment utility to deploy custom SSRS reports - Part 1

I was in Johannesburg, South Africa this past week visiting a customer with the distinct challenge of working with an extensive number of company databases - one for each company division. While I was there to provide Microsoft Dexterity training, undoubtedly other topics of conversations tend to surface at various points.

In this occasion, I was the one doing some learning as the customer pointed me to a method they devised to deploy SQL Reporting Services Reports (SSRS) to multiple companies by using the standard Microsoft Dynamics GP Reporting Tools Setup window. The company have over 50 custom reports of all kind which can take more than a day to deploy if they were to do so manually, once a new division database is rolled out.

Reporting Tools Setup window
Under their current situation, the customer simply want to click the Deploy Reports button on the setup window and watch their custom reports added to any new company, along with the standard SSRS reports bundled with the Microsoft Dynamics GP application.

BI Deployment Progress window

I was very intrigued as to how they accomplished this and in doing some research, I launched Microsoft SQL Server Profiler to perform a trace while the reports deployment was in progress. The trace constantly showed the deployment process reading report information from the sySrsReports and sySrsReportDataSources tables located in the system database.

SQL Profiler trace (click to expand)
Neither of these two tables, nor the Business Intelligence Deployment Progress window seem to be a standard part of the Microsoft Dynamics GP dictionary (Dynamics.dic), but rather Visual Studio Tools add-ins to the Microsoft Dynamics GP application, therefore it was necessary to run a couple T-SQL SELECT statements to determine the content of each table mentioned above, given they could not be determined via standard tools like the Support Debugging Tool's Resource Information or the Microsoft Dynamics GP's Resource Description window.

Upon inspecting the sySrsReports, the table description is as follows:

sySrsReports description

There's also the sySrsReportDataSources table, which points to each individual report:

sySrsReportDataSources description

Remember, they may be more than one data source being used on a report, in which case you will need to have more than one entry from the same Object ID.

These two tables serve as repository of all base reports needed for deployment across companies. The Dexterity portion of the code (the Reporting Tools Setup window) takes care of retrieving the missing reports from each company.

In the next installment, I will provide the queries required to fill these two tables with custom SRS information that can be used to deploy the reports across various companies using the standard deployment window.

Until next post!

Mariano Gomez, MVP
Intelligent Partnerships, LLC

Sunday, May 25, 2014

The Dynamics GP Blogster in Johannesburg, South Africa

My travels will take me to the beautiful and cosmopolitan city of Johannesburg, South Africa to conduct Microsoft Dexterity training for customers in the area. I will be there from Sunday May 25, 2014 through Saturday, May 31, 2014 and I am looking forward to meet a number of you.

I will be available in the afternoons after 6:00 PM local time and as time permits. For those of you who decide to tag along for dinner or otherwise, table topics are fairly open. If you are a prospective customer considering Microsoft Dynamics GP and would like some insight, please feel free to drop by as well. Please feel free to contact me by adding your comment to this blog and I will get in touch with you or click the email button on the right.
Until next post!

Mariano Gomez, MVP
Intelligent Partnerships, LLC

Tuesday, May 20, 2014

reIMAGINE 2014: You are the difference!

No, my post title it's not an official slogan for the conference, but the reIMAGINE 2014 conference in Fargo promises to be just what Microsoft Dynamics GP partners had been expecting for years: and all out sales, technical consulting, and software developer extravaganza that combines the likes of the former Great Plains Software's Stampede to Fargo and the Microsoft Dynamics GP Technical Airlift.

But there's a caveat: as a partner, YOU get to provide feedback on the content and topics you want to see and learn about. So here is where I ask for your support: David Musgrave and I would like to team up once more to bring you some of the best development content you could ask for and why not, even to convince you (again!) as to why you should be using and deploying the Support Debugging Tool with all your implementations - yes, David Musgrave is coming!!

If you would like to see us both at the conference, presenting in a fast paced, but really fun environment, we encourage you to provide feedback. Heck, I'm going out on a limb and say even if you don't want to see us, go and provide feedback nonetheless.

reIMAGINE 2014 Microsoft Dynamics GP, Hosted by Dynamic Partner Connections

Fargo, North Dakota
The conference will take place from November 10 - 13, 2014 in the lovely city of Fargo, North Dakota, with training classes being conducted on or around the same time of the event (please check the schedule for updates).

Until next post!

Mariano Gomez, MVP
Intelligent Partnerships, LLC