philodendrin
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER