Tech Tip: Avoid a blank On SQL Authentication database method

PRODUCT: 4D SQL | VERSION: 11 | PLATFORM: Mac & Win

Published On: June 4, 2009

If you are connecting to your database's SQL Server from an outside source, the On SQL Authentication database method is called, if it exists. In that case it must return True in the $0 parameter, or you will receive an error and be unable to connect.

This is normally an obvious situation and the intended behavior, but for developers who use 4D's User and Password system for access, the On SQL Authentication method is unnecessary. In this case it must not exist. Sometimes the On SQL Authentication can be accidentally created and left blank. This is easy to overlook. The following two screenshots show examples of the difference between a blank On SQL Authentication method and a non-existent one:

No On SQL Authentication Method (the desired state):

Blank On SQL Authentication Method (a state that may lead to errors):

Some of the errors you might see if you have a blank On SQL Authentication method are Error 9900 (Server rejected the connection) or Error 9917 (Generic SQL Error).

Commented by James Rowe on December 10, 2010 at 9:37 AM

What this note doesn't say is that you have to restart 4D server to have these changes take effect. The On Database methods are stored into memory On Server Startup.

However you can have a method call in the On SQL Authentication and then make changes to that method and have them take effect without restarting 4D.
-James

Commented by Timothy Penner on June 8, 2009 at 5:44 PM

In addition to the On SQL Authentication database method, the On Web Authentication database method also expects a Boolean value to be returned in $0 when custom code is present in the database method. However, in the case of On Web Authentication, the default behavior is to accept the connection.

To revert back to 4D's default behavior for either of these methods, simply delete the database method.