Microsoft Dynamics GP Tip: How to Resolve the Active Directory GUID of a GP Web Client Enabled User Account

Many of you may have already heard about all of the cool new features of Microsoft Dynamics GP 2013 R2. One feature that I really like is the ability to log in to Microsoft Dynamics GP with only your Windows password. The one caveat is that you can only do this through the Microsoft Dynamics GP Web Client, but even so, it’s a really cool feature. So how do you do it? In Microsoft Dynamics GP you are still able to create a user the way you normally do but with the changes to Microsoft Dynamics GP 2013 R2, there are now two tabs on the User Setup Window.

The first tab, SQL Server Account, is what you are currently used to. This will create the SQL login for the user to be able to login through the Microsoft Dynamics GP Desktop Client. On the Windows Account tab, you are presented with only one field, Windows Account.

Once you hit the lookup window, you can enter the Windows account name.

Note: You may expect the binoculars icon to open an Active Directory search like the one below, but this is not the case. Once you hit the icon, it will verify that what was typed in is a valid account, sort of like the “Check Names” button does in other applications.

To find the account you want, type in the account name and hit tab or the binoculars icon. Don’t worry, if you have multiple accounts with similar names, a dropdown of the possible values is displayed. Select the name you want and click OK.

Once the account is selected, the Windows Account field will show the user’s name. This information is actually pulled Active Directory and corresponds to the Display Name listed in Active Directory.

I don’t like this. I want to know what the Windows account is in “DOMAIN\USERNAME” format (technically called the down-level logon name) to make sure I have the right one mapped. I decided to take a look at the User Master table (SY01400) on the SQL server to see if I could find it there. To my surprise, I was only able to find the GUID of the Windows Account under the ADObjectGuid field.

This is very strange and even less helpful than a display name if you’re trying to verify that the Windows account associated to the SQL login is correct. To get past this problem I created a SQL script that will query active directory to resolve the GUID to the account format I want. In order to do this, you first need to create a linked server that will be used to query Active Directory. As you can see below, “ADSI” is the name that I gave the linked server.

Next, you need  to specify what account credentials will be used during authentication when querying Active Directory. You have two choices. You can either use the current credentials that were used to log in to SQL Server Management Studio (if you logged in with Windows Authentication), or you can specify the user name and password. I have provided both scripts, but you only need one.

Next, you run the following SQL script to display the GP login name and the corresponding domain account for that login.

Here’s what the results look like. Now that’s exactly what I was looking for!

If you want to remove the linked server for security reasons, run the following script.

Although the Microsoft Dynamics GP Web Client has the ability to map GP user to a Windows account, I think there are at least two features that are missing: an Active Directory lookup window when selecting the user, and the down-level logon name to be displayed on the Windows Account tab of the User Setup window. For now, I have my SQL script, which also gives me all of the accounts in one query result, so I’m not complaining.

For more information about the new features of Microsoft Dynamics GP 2013 R2, contact a partner like The Resource Group.

by The Resource Group