Friday, July 22, 2016

Using a VPN service while working in China

By Steve Endow

In my last post, I discussed some of the things I learned about working remotely while travelling in China.  In that post, I mentioned that using VPN services in China to get past The Great Firewall and access resources in the US was frustrating at best.

In this post, I'll share which VPN services I used and discuss my experiences trying to use them in China.

Here are the three VPN services that I purchased and used:

1. VPN Unlimited by KeepSolid
2. VPN Secure
3. ExpressVPN

This is not a recommendation or endorsement of any of these services--they are just the three that I semi-randomly tried, out of the dozens and dozens of offerings.  If you ask 10 people which VPN service they use in China, you are likely to get 10 difference answers or recommendations.

Based on my experience, it doesn't really matter which service you choose.  Just find a few that seem to have a good price, the features you need, and some positive reviews.  I'll explain what I mean shortly.

I used the services on iOS and Windows 10.  I didn't bring my Android device with me, so I don't have any feedback about using them on Android.


Shopping

I would recommend asking the following simple questions when shopping for a VPN service for use in China.

1. Does the VPN service work from within China?
2. Does the service support the devices you use? (iOS, Android, Windows, etc.)
3. How many devices can you use on your account?
4. If you need to use a corporate VPN (Cisco, SonicWall, etc.) while in China, can you connect to the VPN service first, and then connect to the corporate VPN through the service's tunnel



My impression is that virtually all of the VPN services will claim they work in China, and the ones I looked into all had Windows, iOS, and Android clients.  So the first two questions should be easy.  For the last question, you are on your own to test dual VPNs.

All of the VPN services seem to claim that they are the best, have a bunch of servers worldwide, and that they are fast.  One I used claimed to have "stealth mode" to avoid detection by The Great Firewall.  I can attest that feature didn't seem to make any difference.

If you are working in China, ignore all of the sales and marketing claims and just set your expectations very low.  If you are able to connect to the VPN and get over 1mbps, consider yourself lucky.

I first signed up for VPN Unlimited based on a recommendation by Scott Hanselman.  VPN Unlimited offers a lifetime subscription for a very reasonable price on Stack Social, so I purchased it.

https://stacksocial.com/sales/vpn-unlimited-lifetime-subscription

Since I wanted to have a second service as a backup, I signed up for 1 month with VPN Secure, as it was one that received good marks in this rather extensive review of VPN services:

https://thatoneprivacysite.net/simple-vpn-comparison-chart/


Near the end of my 3 weeks in China, I had a day where I was having a lot of difficulty connecting to either of the two VPN services from two different residential networks.  Frustrated, I signed up for the third service, ExpressVPN.  ExpressVPN didn't work that day either, so I pretty much confirmed the problem was not with the VPN services.  But that's how I ended up testing 3 different services.


Of the three services that I tried, VPN Unlimited was probably the one I preferred the most, but for a pretty trivial reason.  On iOS, all three of the VPN services prompt for a username and password when launched.  VPN Unlimited was the only one of the three that supports Touch ID on the iPhone and iPad.  So when I had to kill the app or reboot my iOS device (which I had to do constantly in China), it was much easier to launch VPN Unlimited and use Touch ID than to have to login by typing my username and password repeatedly.


This may seem trivial, but when in China, I had to shut down the apps and relaunch them a dozen times a day, at least, to try and get them to work.  Logging in every single time gets really old.  So for that reason alone, I used VPN Unlimited the most.

I just tested VPN Unlimited and ExpressVPN on Android, and neither app prompts me for a password every time the app is launched or after the phone is restarted.  So that's a bonus for Android users.

Signing Up

I recommend signing up for at least two services prior to going to China.  They aren't expensive, so having two or three handy is worth it.  You will want to have your account setup and paid for, the client software installed and configured on all of your devices, and you will want to test the VPN on all of your devices.

I would recommend using the same simple password for all of the services.  Since I had to constantly re-enter my login on iOS, it was annoying having to type long passwords every time I launched the apps.


Using the VPN

The vast majority of the time, I used the VPN services on my iPhone and iPad.  It is easy to launch the apps and they are relatively quick to connect on iOS.

Using the VPN services on Windows 10 was a different story.  For some reason, they were horribly slow to connect on Windows, and the Windows apps were very bad at refreshing or updating their status.  So I couldn't tell if the app had hung, was stuck, was connecting, or was connected.  Sometimes they worked okay, but most of the time it was a hassle to use them.

And for some reason I had a much harder time successfully connecting on Windows.  One day I used over 50% of my Surface Pro 4's meager battery just trying to connect to the VPN, and I eventually gave up.  I dreaded having to use my Surface Pro to connect to the internet, since it wasted so much time, and I had such little success with any of the VPN services on Windows 10 while in China.

So if you use iOS and Windows, I would recommend trying to get everything you need setup on your iPhone and iPad, as I found the VPNs to work much better on iOS.  I'm guessing Android is better than Windows as well.


Using Multiple VPN Services on iOS

If you install multiple VPN services on iOS, there is one thing you'll want to be aware of.  In the iOS VPN configuration settings for each VPN service, there is a setting called Connect On Demand.  The idea is that whenever you connect to a WiFi network, the VPN service will detect it and automatically connect for you.  This is fine when you only have one VPN service installed.  But if you have multiple services installed on an iPhone, they will fight with each other.  If you turn on your phone and try and connect with one service, the second service may be trying to connect behind the scenes, preventing either connection from working.  So you have to make sure to turn off Connect On Demand.


Unfortunately, it seems that every time the VPN apps refresh and download their configurations and server lists, they re-enable the Connect On Demand setting.  So you have to constantly turn it off again.  Normally, you'd only need one VPN service, and Connect On Demand would be a good feature, but when in China and using multiple VPN services, it isn't.


The reality of using a VPN in China

The VPN services do work.  You can test them while in the US and easily connect to different cities and different countries just fine.  When you test from the US, you'll likely see decent speeds without too much latency.

And technically, the services do work in China.  But for me, they didn't always work, and some days they didn't work at all.

All of the 3 services I tried had issues, so one wasn't better than another or less prone than another to have problems.

I'll let these screen shots speak for themselves.


 

 

 

 

 



While all of the VPN services claim to work from China, clearly they don't always work from China. Between The Great Firewall, local network firewalls, flaky WiFi, and questionable internet connectivity, it was a constant battle to get and maintain a decent connection with any of the 3 services I used.

And even when one of the VPNs did appear to connect, sometimes the connection just didn't work, or the speed was so slow, or so high latency, that it was unusable.  Many times my phone would appear to be connected to the VPN service, but I couldn't connect to any web sites or retrieve my email.

I would disconnect, turn WiFi off, turn WiFi back on, then try and reconnect to the VPN.  I would also regularly reboot my iPhone in the hopes that would work.  I don't know if any of those things actually made a difference.  It was a constant battle.

Based on my experience, it seems that China has become fairly good at blocking VPN traffic.  As I mentioned in my last post, it seemed as if it became harder for me to connect over the course of my three weeks in China, and I speculate that there are MAC address filters that start to completely block all traffic that is associated with VPN usage.

Given how frustrating it was for me to get a decent connection and get any work done remotely while in China, I don't know how business travelers can work in China.  Either everyone is suffering and coping like me, or there is some secret that I'm missing.

If you have to travel to China, just be prepared to potentially have limited or intermittent connectivity to web sites or internet services outside of China, and for your productivity to be severely limited.

Good luck.



You can also find him on Google+ and Twitter




Tuesday, July 19, 2016

The Challenges of Working Remotely While Traveling in China and Singapore

By Steve Endow

I just returned from a one month vacation (June / July 2016), spending 3 weeks in China and one week in Singapore.


While in China, I traveled over 2,000 km to four different cities and lived in apartments, houses, and several hotels.  During my trip, I attempted to monitor my email (hosted in Canada), access all of my usual US web sites, make phone calls to the US and Australia, and connect to my office network in Los Angeles.

I was familiar with The Great Firewall of China that is known to block internet traffic to the rest of the world, so I anticipated that I would have difficulties working remotely.  To prepare for this, I signed up for three VPN services that claimed to work in China.  I tested the VPN services in the US, but there was no way for me to know how well they would work in China.

There was not a whole lot I could do to anticipate what it would be like to work remotely from China, so I just warned my customers and colleagues that I would have limited internet access and may not be able to respond to emails for several days.  I would recommend that you set your own, and your customers' expectations, very low, and assume you will have limited connectivity.

I learned quite a bit about trying to work remotely in China and experienced several challenges and frustrations, so I thought I would share my experiences in case some other poor soul has to work from China.

Here's what I'll cover:

1. Before you go
2. While traveling
3. Using VPNs
4. What worked and what didn't
5. Workarounds
6. After you return


Before You Go

VPN Service

I'll do a separate post about the VPN services that I used and how well they worked, but here's the short version.

You will want to sign up for at least 2 VPN services before you leave for China.  And you will want to have them installed and configured and fully tested on every single device before you leave.  Fortunately they are fairly cheap and there are tons of options, and based on my experience, it isn't critical which ones you choose.  Technically they do appear to work, but set your expectations very, very low.


If you're old enough to have used a 56k (or 28.8k) modem, you might remember the days when it sometimes took 20 minutes of redialing to connect to an ISP (Compuserve, AOL, Prodigy, WorldNet, etc.), and after you were connected for 5 minutes at those blazing fast speeds, the modem would disconnect randomly, and you would start the process all over again.  That's exactly what it's like to use a VPN service in China.  It is a frustrating, time wasting, and very unproductive process.

In short, do not plan on being productive with any online or internet-dependent resource.  Do not plan on having quick access to any foreign internet service.  Do not assume that you can connect to foreign internet services daily--it may be 1 or 2 days before you can get connected to even retrieve your email.

And if you anticipate needing to connect to a corporate, work, or customer VPN, then you'll also need to test dual VPN connections.  First your need to connect to the VPN service to tunnel out of China, and you'll then attempt to connect to the corporate/client VPN.  In some cases, this dual tunnel setup will simply not work.  In other cases, the dual tunnel setup may work fine outside of China, but will not work from within China.  Be prepared for this.


I had some success in using OpenVPN over a VPN service on my iPhone and iPad to connect to my office network.  But while in China, I was unable to use OpenVPN on my Windows laptop to connect over the VPN service.  It just wouldn't work from China.  This may vary based on the corporate VPN client, but I would recommend assuming that you may be unable to connect to a private / corporate VPN from China using a Windows laptop.

Once I got to Singapore, the VPN services started working again.  They were slow and flaky, but they worked much better than in China.


Mobile Number and Two Factor Authentication

This is one that I did not prepare for.  It applies to any international travel, and being in China just makes it a little more complex.


The third day I was in China, I had to send a wire transfer.  I was able to connect to a VPN service, and then connect to the BofA web site.  I setup my wire transfer, but when I went to submit it, I realized that I needed to receive a one-time passcode--something I forgot about.  The Bank of America web site requires a one-time text message passcode for all wire transfers, and this feature cannot be disabled.  But guess what?  My account is setup to send the passcodes to my US mobile number, and I did not setup international roaming on my mobile phone, nor did I want to.

I checked to see if I could add a new mobile number to my BofA account, but of course, that also requires me to receive a passcode on my main mobile number to authorize the request.  And even if I could add a number, it would have to be a US mobile number, and I didn't have direct access to a US mobile number that could receive text messages in China.

So I had to call BofA customer support, authenticate myself and then have them remove my mobile number completely from my account.  I then had to contact my sister in the US and add her cell number to my BofA account.  I then had to coordinate with her so that when I submitted my wire transfer request, she could quickly send me the confirmation code that I would then enter in the BofA web site.  It was comical.


So before leaving, try and think about any web site or service or login that uses mobile text messages for two-factor authentication.  If you need to use any of them, you'll need to either have international roaming on your cell and be sure that roaming will work in China and that you can receive text messages while in China (good luck verifying that before you leave), or you'll need to have a virtual phone number that can receive text messages (preferred).  Even if you have roaming, I would strongly recommend also setting up a virtual phone number as a backup and setup that additional virtual number on all of your accounts.


Virtual Phone Numbers and Text Messages

I clearly did not anticipate the need to receive text messages while in China, and that was a gaping hole in my preparation.

After realizing this, I checked to see if my Google Voice number would receive texts.  Based on my testing and forum posts on the topic, it will not.  Google Voice is unable to receive text messages sent by web sites.  Apparently Skype cannot either.

There are virtual phone services, similar to Google Voice, that provide you with a virtual phone number and let you make calls with an app on your mobile device.  Some also claim to allow you to receive text messages.  I only had time to test one, KeepSolid Phones, and in my limited testing with a trial phone number, the KeepSolid Phones app on my iPhone was unable to receive a text message from a web site.  I didn't have time to do more testing, so I gave up on it and didn't try any other such services.


To my surprise, I stumbled across a solution that did work for me.  In my office, I use Vonage Business for my VoIP phone service.  I realized that as part of my Vonage Business service, I have an iPhone app called Vonage Business Essentials.  Through the iPhone mobile app, I found that I was able to send and receive standard SMS text messages using my office phone number.

I also made a few calls using the Vonage Business Essentials iPhone app and was typically able to make and receive calls without having to use a VPN.  If I used the VPN, the calls were very choppy and unreliable due to the slow VPN speeds.

However, I was unable to make an international call from the Vonage Business Essentials app, so I was unable to call Chinese phone numbers.  I  don't know if that is a restriction in the application, or if that option was just disabled or blocked on my account.  I instead used Skype to call Chinese phone numbers, and that generally worked fine.


SIM Cards

You can get prepaid SIM cards in China, but because I did not get one, I can't provide any details.

In the Shanghai airport, there were vending machines that dispensed the prepaid SIM cards, but there were many different types, apparently by region and plan type, and I don't know what they required in terms of setup or activation.


If you try and get a prepaid SIM card from a retail store, you'll want to bring a local with you who can translate and potentially provide their contact info and ID on your behalf--I don't know what the current requirements are for foreigners who wish to purchase a SIM card.

If you do plan on using a China SIM card with your mobile phone, try and verify that your phone will work in China.  China apparently uses its own unique mobile frequencies, and apparently different carriers use different frequencies, like in the US, so not all phones will work on all Chinese mobile networks.

And you will need to unlock your phone before you go to China.  In the case of AT&T, that requires that you own your phone outright with no outstanding contracts or payments, and that you unlock it via the AT&T web site.

I have relatives in China who provided my wife with an extra SIM card from their mobile account, so I can report that an unlocked US AT&T iPhone 5S worked just fine with that particular Chinese carrier--I believe it was China Telecom.  While in China, I was able to access the AT&T consumer unlock web site over VPN and unlock my wife's phone successfully.  So that step can be done while in China, but I recommend doing it before you leave.

In Singapore, SingTel offered a prepaid SIM card for $30.  It is good for 10 days and includes 14gb data.  I didn't note how many voice minutes were included.


While Travelling

Public WiFi

If you have layovers at Chinese airports and will be spending a few hours waiting around, I would recommend signing up for a Boingo Wireless hotspot account.  I believe that Boingo is available in most Chinese airports.  I signed up while waiting in the Shanghai airport and found the Boingo service to work fairly well in the main terminal areas.  I did occasionally have issues connecting, and the signal strength was weak in some of the odd gate boarding areas in the Shanghai airport, but overall it was well worth the nominal $9.95 monthly fee, and you can cancel once you return if you no longer need it.

When setting up the Boingo account, choose a simple username and a relatively simple password, as you will need to re-enter it nearly every time you connect using a mobile device, and on my iPhone I was unable to copy and paste the password from my password manager into the Boingo authentication page.


While traveling in China, many businesses and malls claimed to have free WiFi, but most required a Chinese mobile number that could receive a passcode via text message.  They then required that you enter the passcode to login to the WiFi service.  So I was unable to use most of the free WiFi services.

Singapore was similar, with a fair number of locations offering WiFi, but some requiring registration of some form.  Starbucks stores in Singapore offered WiFi, but required you to setup an account with a social media company.  I was able to sign up from my phone to get access.


Security

I was concerned about connecting to public WiFi while travelling.  I assumed that I would be at risk, but I didn't have much choice.

Other than the standard advice of making sure to have anti-malware type software running on your laptop and being careful about what you access, I did the following:

1. Occasionally cleared all web browser cache, cookies, history, etc. on my laptop and mobile devices
2. Reset (wiped out) the network settings on my iPhone and iPad
3. Regularly shut down and restarted my iPhone and iPad

Here is a good article on securing an iPhone for travel:

https://blog.filippo.io/securing-a-travel-iphone/

You don't necessarily have to do all of the steps, but try and do as many as you can.


Using VPNs

I'll cover VPNs in more detail in a separate post, but as I mentioned earlier, VPNs in China are very unreliable.  As in roll-the-dice and pray unreliable.

Over the course of three weeks, I regularly attempted to use three different VPN services and connected to several dozen residential / private and public WiFi networks in four different cities.

In summary:  Sometimes they work.  Sometimes they don't.  Assume a 50% success rate at best and you might not be disappointed.

They worked fairly well on my iPhone and iPad.  They barely worked at all on Windows 10 on my Surface Pro 4, and were painfully slow to connect on Windows.

Trying to use the VPN services and access foreign sites while in China was extremely frustrating, and I would say that I spent more time trying to get the VPNs to connect and get them to work than I did actually using the VPN connection.  And that is not an exaggeration.


One day in China I repeatedly attempted to connect to a VPN over the course of 12 hours and was unable to get any of the 3 services to work on two different WiFi networks.  I had used the same two residential private WiFi networks a week earlier and the VPN services worked fine.  But that day, despite wasting several hours trying on multiple devices, absolutely nothing worked, and I was unable to retrieve a single email or connect to any web sites.

My overall impression was that over the course of my 3 weeks in China, my ability to connect to the VPNs decreased.  When I first arrived, the VPNs connected relatively quickly and easily.  But the last few days I was there, it seemed like I couldn't connect at all.

Based on this, I speculate that the Chinese firewall may have tracked the MAC addresses of my devices and flagged them as being associated with VPN usage.  After 3 weeks in China, it felt like my MAC addresses ended up on a blacklist and the devices would no longer connect most of the time.  The issues were inconsistent, so it could be that the blocking occurred at the ISP level, and not centrally at a national level.  But it was definitely much harder to connect at the end of 3 weeks.


If this is indeed what occurred, there may be some software that allows you to spoof your MAC address on Windows and Android.  I'm assuming that MAC spoofing is not possible on iOS without jail breaking.  The alternative might be to have multiple devices and only use them one at a time.  When one device stops working, try using the next device.  Not very practical, but a potential workaround if you are concerned about the issue and will be in China for several weeks.


What Worked and What Didn't

This is a quick list of things that I tested or used while in China.  In Singapore, I don't think I had any significant issues accessing anything.


Worked Without VPN

Skype iPhone app messaging and phone calls
Vonage Business Essentials iPhone app


Did Not Work Without VPN

Just about every major US site or online service
Google
Gmail
Hosted Exchange
Twitter
Yahoo Mail
YouTube
OpenVPN to my office network


Sometimes Worked Without VPN

iPhone Telegram notifications only (I had to use a VPN to retrieve messages, which was odd)
iPhone WeChat notifications only (I had to use a VPN to retrieve messages, which was very odd, since most people in China use WeChat)
iMessage
Kindle book downloads



Workarounds

When I needed to access a web site that was blocked, or needed to search for something without using Google, I used Baidu.com.

If you perform a search on Baidu, you can sometimes get access to an equivalent site that will have what you need.  So for instance, I needed to lookup exchange rates, and was able to find an exchange rate web site via Baidu when I was unable to access Google or use a VPN.

Sadly, despite hours of trying, I was unable to find any workarounds, hacks, or tricks that would allow me to reliably connect to a VPN service or access US sites without a VPN.  Zero workarounds in that category.



After You Return

As a result of using my iPhone, iPad, and Surface Pro in China and Singapore on numerous WiFi networks, I'm assuming that all of my devices have been compromised in some manner.  I'm also assuming that any logins that I used while travelling are also compromised.

Now that I'm back, I'm planning on resetting the password for every web site, service, or account that I may have accessed.  Not my idea of a fun time, but not particularly difficult.

I will also be completely resetting and wiping the iPhones and iPads and setting them up from scratch, not from a backup.  I use both iCloud and the Eye-Fi app on my iOS devices to automatically backup my photos and videos, so once I confirm those are backed up, I'll wipe the devices and reinstall the apps.  Again, I'm not looking forward to doing it, but it's not rocket science, and I've done it many times before.

My Surface Pro 4 is a little trickier, as Windows is a pain to wipe and reconfigure.  I didn't use it much, and it was nearly impossible to get a VPN service to connect on Windows, but in the little time that I used it, my Chrome web browser was somehow hijacked to redirect traffic.  Any time I tried to access nytimes.com, Chrome would redirect me to facebook.com.  No matter what I tried, I was unable to access nytimes.com from Chrome.  The Windows Edge browser did not have the same issue, and using ping on the command line, I confirmed that DNS lookup was not affected.

After poking around, I finally cleared the cache, history, etc. in Chrome, and that resolved the issue.  But it was evidence that something had messed with my Surface Pro, so I'm assuming it could be compromised in other ways as well.  Definitely not looking forward to wiping it and reinstalling everything.


So that was my experience of trying to stay connected while in China for 3 weeks.  I chatted with a friend who happened to be in China at the same time I was there, and he had similar issues and challenges with his VPN service, so it was somewhat reassuring to know that it wasn't just me.

I cannot imagine how people get any significant work done remotely while travelling to China.  I know there are thousands and thousands of people who travel to China for business, so I'm wondering if there is some magic secret for staying connected, or if they are as frustrated as I was.

If you have traveled to China recently (2016 or later), I'm interested in hearing if you had similar experiences and if you have any tricks that allowed you to be productive and actually get work done remotely.


You can also find him on Google+ and Twitter



Friday, June 10, 2016

What do you use to integrate Dynamics GP and Dynamics CRM?

By Steve Endow

Today I was asked what tool I recommended to integrate Dynamics GP and Dynamics CRM.

Unfortunately, I've never done a GP to CRM integration, so I didn't have a recommendation.

I've never searched extensively for GP to CRM integration solutions, so I've only heard of 3 options.

1. Microsoft's Connector for Microsoft Dynamics
2. Scribe and Scribe Online
3. SmartConnect

I'm vaguely familiar with the Microsoft connector, as I recall a version was created many years ago and then discontinued.  And I see that the current version is also apparently being discontinued, and will only work with CRM up to version 2016.

https://mbs.microsoft.com/customersource/northamerica/GP/downloads/service-packs/mdgp_dynamicsconnector

The appeal of the Microsoft Connector is that it is free.  Naturally that would be great for customers, if it works well and does what they need.  But if it won't support CRM releases after version 2016, that is probably not a good option going forward.

So that leaves Scribe / Scribe Online and SmartConnect that I've heard of for GP to CRM integrations.

I just saw one post about Jitterbit being an option, but I don't think I've personally come across a Dynamics GP customer that has used Jitterbit.

Are there other tools are out there for integrating Dynamics GP and Dynamics CRM that you would recommend?

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter







Yup, Dynamics GP user names are case sensitive

By Steve Endow

I occasionally get a call from a customer who is having trouble logging in to Dynamics GP.  They created a new user, they know they have the password correct, but GP keeps saying the login failed.


If you encounter this issue, one of the things to check is the capitalization of the GP user ID.


My login failed when I used "testuser".  Notice that the User ID is actually "TestUser".

Unlike the rest of the software world, Dynamics GP user IDs are case sensitive.  So whenever you have a puzzling login failure, try checking the capitalization of the user ID.



You can also find him on Google+ and Twitter




Wednesday, June 8, 2016

Back to Basics: What is a SQL Server Instance Name?

By Steve Endow

I regularly work with customers who do not know what the SQL Server Instance Name is for their Dynamics GP environment.  For non-technical users, this is totally understandable--it isn't something they would normally need to know.  With IT folks, this is more common than you might think, as many system admins deal with many other technical items, but are just not familiar with SQL Server.  And to top it off, the fact that Dynamics GP still uses ODBC DSNs can further confuse things for people who aren't familiar with how all of the data access plumbing works.

Let's talk about the ODBC DSN first, as it is a very common source of confusion with Dynamics GP.

When you launch Dynamics GP, you will see the main "Welcome to Dynamics GP" login window.


The first field on this login window is labeled "Server".  This field name causes confusion because that value isn't actually a Server name at all.  It's an ODBC Data Source Name, or DSN.  A DSN is just an arbitrary name that is assigned for the connection to the SQL Server.

For typical GP installations, I recommend using the default value, which is currently Dynamics GP XXXX, with the X values representing the version number.  Because the ODBC DSN affects how passwords are encrypted and decrypted for Dynamics GP, you'll want to make sure that you use the same DSN name on all GP workstations.

So where is the ODBC DSN setup and configured?

On 64-bit versions of Windows, which are pretty much the norm these days, you need to launch the 32-bit ODBC Data Source Administrator tool to check the ODBC DSN settings.   This is the second layer that confuses a lot of people.  Since Dynamics GP is still 32-bit, it uses the 32-bit ODBC drivers, and therefore uses a 32-bit ODBC DSN.

On newer versions of windows, in Administrative Tools, there is an "ODBC Data Sources (32-bit)" entry that you can launch.


On older versions of 64-bit Windows, this separate 32-bit option is not listed, and only the 64-bit version is available.  To open the 32-bit Data Sources, you have to use Windows Explorer to open it.  It is located in the unfortunate location of:

C:\Windows\SysWOW64\odbcad32.exe


If you launch via either of the above options, you will see this ODBC Administrator 32-bit window.


Notice that the window title says 32-bit.  You can then select your Dynamics GP DSN and click Configure.


And finally, once that is open, you will see your REAL SQL Server instance name that is being used for your Dynamics GP ODBC DSN, shown in the Server field.

So that was the longer, but safer way to check the SQL Server instance name on a machine where Dynamics GP is installed.  This is safer because some customers have multiple environments, multiple SQL Servers, and multiple SQL Server instances.  Occasionally, there may be a GP machine that is pointed to a test SQL server instance, so it never hurts to double check.

If you are more technical and have access to the SQL Server or to SQL Server Management Studio, there is another way to quickly check the SQL instance name.  When you launch Management Studio and login to the GP SQL Server, you should see the instance name listed in Object Explorer.


In this example, my SQL Server is using a "default instance" and is named "GP2015", which is the same as the server name.

But you may have a server that has a "named instance", in which case you will see the server name followed by a backslash, followed by the instance name.


In this example, my server is named GPDEV2, but I have a named SQL Server Instance called "GPDEV2\GP2010".

As mentioned before, you need to be careful about getting the instance name from Management Studio because there could be multiple SQL Servers and multiple SQL instances.  Just because you see a particular instance when you log into Management Studio doesn't necessarily mean that it is the one being used by Dynamics GP.

When in doubt, just find a machine running Dynamics GP that you are sure points to the SQL Server that you want, and then check the ODBC DSN settings on that machine.

Hopefully that helps explain what a SQL Server Instance Name is and how to find it in a Dynamics GP environment.


You can also find him on Google+ and Twitter









Tuesday, June 7, 2016

Check who is accessing Dynamics GP shared dictionary files

By Steve Endow

There are lots of customers who continue to install the Dynamics GP client application on user workstations, or customers who have GP installed on multiple Terminal Servers.  As a result, many of them use "shared dictionaries", where the FORMS.DIC and REPORTS.DIC files are stored on a centralized network share.

There are some downsides to using shared dictionaries with Dynamics GP, but the convenience generally wins out, so people continue to use shared dictionaries.

One thing to remember with shared dictionaries is that when importing a package file to a shared dictionary (typically FORMS.DIC or REPORTS.DIC), you should make sure that the dictionary is not in use.  While you may be able to import package files to active shared dictionaries many times without any issue, I have seen situations where the shared dictionary was corrupted as a result of an import.

Today I was explaining this precaution to a customer, and he mentioned that he didn't know how to tell if the dictionary files were being accessed by any users.

Here is how you can check if users are accessing the GP shared dictionary files.

Open Computer Management on the server where the network share and dictionary files reside.  On the left, expand Shared Folders and then click on Open Files.


In this example, you can see that my user account is accessing the GP share, and that I am accessing the FORMS.DIC file.

Ideally, you would ask the user to close GP, which should close the connections.  If the users have left for the day but are still connected, you can select one ore more of the the open files, right click, and select Close Open File.  You can also right click in the white space below the open files and select Disconnect All Open Files.


You can also find him on Google+ and Twitter



What's the deal with the leading zeroes?

By Steve Endow

I received a PO from a customer this morning and this was the PO number:  00001445

Not one leading zero. Not two.  But four leading zeroes.  Why?  Is 1445 not a good enough for a PO number?

Another common one is checks.  I received a customer check numbered 000095220.  My personal and business checks are nice and simple, with no leading zeroes, so I don't understand why people feel compelled to add zeroes at the front of document numbers.

It seems like the Fabrikam / TWO sample company encourages this, and takes the leading-zero cake with 19 leading zeroes setup in the default checkbooks.  I think that would allow for several quintillion checks.  Good luck having BofA cash a check that has a 20 digit number.


But are the leading zeroes really necessary?  Do they provide any value whatsoever?  Can't we just have a check number without the leading zeroes?

Is there some secret about the leading zeroes that I'm missing?  Assuming the system is able to roll over numbers and add a new digit, like incrementing from 9,999 to 10,000 (which I believe GP does just fine properly in some situations), I'm just not seeing the value of having a bunch of zeroes in front of the document numbers.

[Update: So Jen and Roger submitted comments below, and not surprisingly, they both point out that there are modules and records where GP is not able to roll numbers over and add a digit. For those situations, yes, you're stuck with the leading zeroes.]

Many of the default document numbers in GP have alpha prefixes, so it's not quite the same, but they do have an excessive number of zeroes in them.  One of the first things I do when working with a new Fabrikam / TWO database is to change the default next document numbers to values that are usable and don't require me to count a bunch of zeroes.  The default numbers are a nightmare to test with, so I usually pare them down to no more than 6 digits after the alpha prefix.


I'm open to explanations as to why this might be an even remotely valid idea.  I can only think of one, which I'll keep to myself so as to not encourage the practice, but even that one seems like a very weak justification.


Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter