Authenticating users to SQL Server (MS Dynamics Great Plains 9) Across Domains and Subnets - only works for SA

We're trying to figure out a "workaround" for a client of ours that still has a Great Plains 9 database running on an SBS 2003 server.

We've configured a new Server 2012 R2 Essentials server for them and are ready to migrate most of their data, but they don't want to migrate Great Plains just yet and it's holding up our migration of the rest of their infrastructure. For a few more months they want to keep running their SBS 2003 server just for Great Plains, have all the client workstations join their new Server 2012 domain, and somehow still be able to access Great Plains running on the old SBS box. Anyone that knows anything about SBS, knows you can't have it join a domain as a member server and that it really shouldn't operate in the same office, let alone Subnet as another SBS or Essentials server.  

So... as an experiment, since we know we don't want both "SBS" servers on the same Subnet, we created a VLAN and placed their SBS 2003 server on a 192.168.100 subnet and the new Server 2012 R2 Essentials box on a 192.168.111 subnet. We then added a workstation to the .111 Subnet and added it to their new 2012 server's domain. We installed Great Plains 9 SP2 on our test Win 7 Pro 64-bit station and successfully authenticated to the Great Plains database running on the other domain and .100 subnet, but can only get it to authenticate when we use "SA" as the Great Plains/SQL user. Whenever we try a different Great Plains/SQL user credentials in Great Plains, it fails to authenticate.

When we configured the ODBC SQL Server client on the workstation, we simply used the IP address of the SBS 2003 server to find the database across the VLAN. No problem there. And we typically test with the  SA account. I figured we wouldn't need DNS name resolution across subnets since we authenticated fine with the IP and SA account... but, lo and behold when we try with any other SQL server user from the SQL database running on the SBS 2003 Great Plains server, we can't get it to authenticate the credentials.  

Anyone know why that would be? It's quite perplexing.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Victoria YudinOwner / Dynamics GP ConsultantCommented:
I believe the issue is the new ODBC connection. When a user is created in Dynamics GP, the application encrypts the password using details from the ODBC connection. When the user goes to log in, if the ODBC connection is not set up exactly the same, the password will not work because of the encryption.

The solution should be to log into Dynamics GP as 'sa' on the newly installed computer (with the new ODBC), then reset a regular user's password (Microsoft Dynamics GP | Tools | Setup | System | User). Now log out of Dynamics GP and log in as the user with the changed password. If that works, you just need to reset all the user passwords on a machine with the new ODBC. Then you will need to change the ODBC (or create a new one) on each machine to be exactly the same.

Hope that helps.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
philodendrinAuthor Commented:
Wow thanks, Victoria! That was spot-on. Worked like a charm.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Dynamics

From novice to tech pro — start learning today.