SQL Connection Error

We had an interesting support case last week that seemed worthy of a blog entry.

At one of our more secure clients we were presented with an odd error message thrown by Great Plains that we had never seen before. The text of the message was "An error occurred creating the pass-through SQL connection: 75" and occurred as the client was printing a standard GP Purchasing distribution report.

The user was for the most part able to transact normally for the day with just an occasional fail with this same error message. After the standard guesses as to what the issue was (ODBC, network timeout, server timeout) we were able to isolate the error and also rationalize why GP mostly worked for the day for this user.

On review of the SQL logs we were able to see about 100 'access denied for expired password' errors for this user on the server which led us to our 'ah ha' moment as to what this meant, and exposed GP a bit on how it just doesn't quite use the SQL security settings as you might think.

Here is what we put together:

• The client uses the Advanced SQL Server options to enhance password security
  This will expire the password at a date and time set by the security policy of the system
• When a user logs into GP their login is validated but further interaction with SQL from the client is done through the DYNGROUP permissions as the connection has been established.
• As the pass through SQL process opens a new connection to the database that connection is then denied as the password has expired. Unfortunately the error message is too cryptic to help resolve the issue.
• Once the user logged out then back in GP did prompt them to change their password but this was actually the next day as it was not apparent to the user or initially to us what this error actually meant.
  

We're ready for it now. I'd be interested if others have seen this or similar issues when using the Advanced SQL Server options.