ODBC Acting Strange - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

ODBC Acting Strange

Author

Topic

tbrothers
Yak Posting Veteran

83 Posts

Posted - 2009-10-06 : 16:32:07

Hi,

We are using Microsoft Great Plains accounting system. The database server is Windows 2008 and SQL 2005 Ent. Each WinXP workstation has the Great Plains client installed.

If on a workstation I set ODBC to use the hostname of the SQL server then some users can connect and login just fine while others cannot.

If I change ODBC on the same workstation to use the IP address of the SQL server then it reverses ... Those that could can't ... and those that couldn't can.

This is duplicated on every workstation I've tested.

Any ideas? I am really confused!

Thanks,
Terry

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-10-06 : 16:38:22

Sounds like some of them are using Named Pipes instead of TCP/IP. On a workstation that doesn't recognize the IP address, add the following key to the ODBC connection string:

Network=DBMSSCON;

If that works, add it to everyone's ODBC configuration. You should also use the fully resolved server name as the data source (SQLServer.domain.com instead of SQLServer).

tbrothers
Yak Posting Veteran

83 Posts

Posted - 2009-10-06 : 17:09:06

Where would I add the connection string?

I am used to Control Panel --> ODBC Data Sources
Then adding or editing a System DSN where the driver is SQL Native Client

Thanks,
Terry

tbrothers
Yak Posting Veteran

83 Posts

Posted - 2009-10-06 : 17:12:43

But what's strange is that Mary and Bob can login to Great Plains but George and Lynn cannot ... on the same workstation. If I change the ODBC to use the IP address instead of hostname that reverses.

Thanks,
Terry

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-10-06 : 17:29:57

Assuming Windows Authentication, check for the SQL Server Client Network utility on the workstation and see if there's an alias set up for the Great Plains server. Check each user.

tbrothers
Yak Posting Veteran

83 Posts

Posted - 2009-10-06 : 17:50:26

OK - I set the alias and that didn't work.

But again - When I launch the Great Plains client I get a login window with three fields.

Server (ODBC data connection)
User ID
Password

I select SQLServer (ODBC) and enter my user id and password and get an error stating "Login failed - contact your administrator". I click OK and the error popup goes away ... But the login window is still there and SQLServer is still selected. I enter a different user id and password and I get logged in.

If I change the ODBC from hostname to IP address the user that could login now cannot and the user that couldn't can.

It's like the system is saying these users can login only with the ODBC set to hostname and those users can login only if set to IP address.

I would think that if the ODBC worked for 5 users then it would work for ALL users.

Thanks,
Terry

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-10-06 : 19:26:40

I think you should contact Microsoft support.

tbrothers
Yak Posting Veteran

83 Posts

Posted - 2009-10-07 : 16:35:18

I figured it out.

Great Plains encrypts the SQL password. The server name is part of the encryption algorythm that GP uses.

When a user logs in with a blank password, Great Plains forces the user to set a password and uses the ODBC server name (whether it the hostname or IP address) as the server name for the encryption.

Therefore, if the password is created from a system where the ODBC is set to IP address then the user cannot login from a machine where the ODBC is set to host name ... and vice versa. A check of the SQL logs indicates an invalid password.

Thanks,
Terry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources