On Unique Password Creation In Dynamics GP Implementations

For the last several months, much of my time has been focused toward the implementation of Microsoft Dynamics GP10. Over the course of this project, the team I work with and I encountered many challenges, some of which I hope to turn into content on this blog. I am writing about this topic first primarily because Michael Johnson was talking to Mariano Gomez about a password creation macro I created. Mariano wanted to write a blog article about it and I realized this would be a good opportunity to inject some content into this blog by writing an article myself.

One my tasks with the implementation of GP10 was to reset all the users' passwords to keep them from connecting to the system while we upgraded it, then make this password known to the user to allow entry into the new system. To do this I needed 5 things.

• A GP macro to change one user's password
• A list of all users
• A list of all users' email addresses
• The new password for all users
• An email template informing users of the change

Let's start with the first requirement. To change all the passwords, I would record a GP macro to change one user's password. After I had that template, I would use the mail merge technique first taught to me by my team members to point to a data source (an excel spreadsheet) containing the rest of their usernames and create one big macro for all users.

This leads me to the second requirement, the list of all users. I'm a big fan of dynamic data (who isn't?), and I wanted to create a solution that would work through months of testing, user additions and deletions, and still work in production on implementation weekend -- with as little modification as possible. Pulling the USERID column on Dynamics..SY01400 would give me something to copy and paste into Excel, but that would have to be done every time I wanted to run the macro. I wanted to take things a step further.

From my experience, data connections in Excel are an underutilized tool, and that's where I created my solution. By creating a ODC to SQL in Excel (Data Tab | From Other Sources | From SQL Server | (Choose Dynamics Database and any table)), I could dump an entire table to Excel. Even better, by clicking 'Properties' on the 'Import Data' screen, I could change my Command Type to 'SQL'. Now, I could run a SQL query and have its result output to Excel.

You'll want to click 'Properties'

The query I used was
SELECT USERID, USERNAME FROM Dynamics..SY01400

WHERE (USERID NOT IN ('sa', 'DYNSA', 'myusername'))

ORDER BY USERID
Simple; all the query I needed. Below is a screenshot of my connection settings.

Click to enlarge

Before I go any further, I'd like to note that one can change this connection easily to pull data from a different server by changing the string 'Data Source=SERVERNAME' in "Connection string" (see above) and modifying SERVERNAME to whatever database name you need data from.

After I click OK and add this into a workbook, I was given the data I needed:

Adding the email addresses was not a complex task for my specific situation, as the USERID in SY01400 was the same as the local-part of all email addresses. Adding an email address column was as simple as concatenating the local-part in Column A with a hard coded domain.

Click to enlarge

The next piece I needed was a password. This could have easily been done by adding a static password such as 'welcomeTOgp10' into the macro template and using that for all users. Aside from the security risks of this, I have never been a fan of giving everyone a blank password or the same password, as is occasionally done with mass user transitioning. By using Excel I could generate unique, semi-random passwords somewhat easily. Creating a truly high security password would take more time than I had allotted for this (In retrospect, I could have quickly and easily copied and pasted a list from random.org into a new column), so I quickly whipped some code together to create a random password for each user. I would use CHAR() to turn a random ASCII decimal code into a character. Then I would use CONCATENATE() to put them all together. The pieces that I used to generate random characters were these:

• CHAR(RANDBETWEEN(48,57))
• CHAR(RANDBETWEEN(65,90))
• CHAR(RANDBETWEEN(97,122))

The first gives a random digit, the second a random uppercase character, and the last a random lowercase character. A full 12 character password could look like this:

=CONCATENATE(CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(97,122)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(48,57)),CHAR(RANDBETWEEN(48,57)))

You may have already noticed a potential issue with the method I used. In the example above, for all passwords, the first character will be a digit. Without seeing the other passwords, a person would not be able to know this, but a person looking at two or more passwords would quickly notice the passwords have a pattern of #xXxX#XxX###. The entropy of the example 12 character example password is 49 bits (log₂ 10*5+log₂ 26*7). However, if a person suspected that this was a 12 character alphanumeric password (perceived by viewing only one password), the perceived entropy of the password would jump to 71 bits (log₂ 62*12). Even if I just used all lowercase letters to generate the password, the entropy would jump from 49 bits to 56 bits. But upon first glance, which password looks like the bigger hurdle for an attacker? "mmucqptqgfsc" or "5dAkF5RsT471"? At 1 billion password a second, it would take 3 years to search the keyspace of a 12 character lowercase password. To search a 12 character case-sensitive alphanumeric password (which my example password is not, but only appears to be!) would take 100 millennia. Maybe I should have randomly added in a #, $, or %. (CHAR(RANDBETWEEN(33,37))) Ooh.

Click to enlarge

Tangent over. After I close and save my Excel workbook, I mark it as read-only to ensure that nothing touches the workbook and regenerates the passwords. Now that I have my password change template macro, usernames, passwords, and email addresses, I can create my full password change macro. First, I will open my template macro that I recorded from GP10 in Word and use the Mail Merge tool to link to my Excel Workbook (Mailings Tab | Select Recipients | Use Existing List) and replace my user name and password with the fields from the workbook (Mailings Tab | Insert Merge Field). After previewing my results to be sure I added all the correct fields into the correct places, I'll cut the header block (Starts with  # DEXVERSION=...) out of the macro, click Finish & Merge | Edit Individual Documents. This will generate a multiple page document that should have substituted those fields for my data. Before I save, I will paste the header block back at the top, and save that file as a .mac. Be sure to save as a 'Text-Only' file and change the extension to .mac.

Now that my macro is completed, I will prepare an email to all the users. Using the same method as above, I will write one email, and insert the merge fields into it. You can use your imagination for what the email will say, just be sure to put the username and password into the email! Mine contained screenshots for the users to help them change their password, and gave them an outlet for where to go in case there were issues.

After all my files are prepared, I'll log into GP (I should be the only one) and run the merged change password macro. After that finishes successfully, I will open my email template in Word and click Finish & Merge | Send Email Messages. When prompted for an email address, use the field in the Excel workbook that you concatenated. This will push all the messages to your Outlook outbox and send them.

After this has been completed, wipe the traces of all your users' new passwords. Securely delete the emails you just sent out of Outlook's Sent Items, securely delete the merged password macro, and lastly, mark the Excel workbook as editable, open it, change the contents of a cell not in your table, (which should regenerate the passwords) and save and close it. If you have versioning turned on, delete any old versions.

That should be it! Keep an eye on your inbox for any returned email or replies!

I'm looking to improve my writing and these articles. If you have any questions or comments, I encourage you to comment below, or email me at the address on my profile page.