Got this from one of my consultants from a client. Any of you PHP guru's? Thanks.
Per our conversation, it would be most helpful to get some technical assistance on connecting to a Sage 100 database using PHP.
Specifically, we're attempting to connect via ODBC to a Sage 100 database from PHP code running on Internet Information Services (IIS). In my attempts to do this I've received a Logon failed message despite trying many different approaches. The IIS/PHP computer, referred to as the ""OSI"", is on the same local network and a member of the domain as the Sage server.
Background
A DSN was created on the OSI computer by installing Sage workstation on that computer. This ODBC connection (""SOTAMAS90"") works. SOTAMAS90 defaulted to a user DSN so a new system DSN was created and tested using the same settings as in SOTAMAS90. This new DSN was tested on the ODBC configuration screen and works properly. The system DSN (""SAGE100_32"") was further tested by creating an Access database on the OSI computer and connecting to the Sage database with linked tables. This worked properly.
Failure Messages
The connection that works properly under Sage workstation, in ODBC data source administrator, and through Access fails when attempting to connect using PHP. This uses the built-in PHP ODBC library. When attempting to connect the following message is returned:
Warning: odbc_connect() [function.odbc-connect]: SQL error: [ProvideX][ODBC Driver][PVKIO]Logon failed, SQL state S0000 in SQLConnect in C:\inetpub\wwwroot\code\test\odbc-test.php on line 23
The PHP code used to make the connection is :
$db = odbc_connect(""SAGE100_32"", ""MYUSERNAME"", ""MYPASSWORD"");
-- OR --
$dsn = 'DSN=SAGE100_32; UID=MYUSERNAME; PWD=MYPASSWORD; Directory=S:\MAS90; Prefix=S:\MAS90\SY\, S:\MAS90\==\; ViewDLL=S:\MAS90\HOME; Company=WSC; LogFile=\PVXODBC.LOG; CacheSize=4; DirtyReads=1; BurstMode=1; StripTrailingSpaces=1; SERVER=NotTheServer';
$db = odbc_connect($dsn, ""MYUSERNAME"", ""MYPASSWORD"");
(actual user name and password used)
In the second example, the DSN string was copied directly from the ODBC data source administrator tool. Also, the ODBC DSN was configured both with and without embedded logon credentials. In all cases the result was the same. Many variations on this have been attempted none successful. These include using a fully-qualified server path instead of the S: drive path (this was the original configuration).
Connecting Through Access
The next logical attempt was to set up an ODBC connection to the Access database created on the OSI computer. This database already connects successfully to the Sage database. After setting up an ODBC DSN to the Access database and using that from PHP to connect to that Access DB, I was able to connect to the Access database and query the local Access tables. When I attempted to query the linked Sage tables, however, an error was thrown:
Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'SAGE100_32' failed., SQL state S1000 in SQLExecDirect inC:\inetpub\wwwroot\code\test\odbc-test.php on line 32
Note that DSN SAGE100_32 was set up in Access; PHP was using a different DSN to connect to the Access database.
The code used was:
$db = odbc_connect(""SAGEVIAACCESS_32"", ""MYUSERNAME"", ""MYPASSWORD"");
$ds = odbc_exec($db, ""SELECT * FROM SO_SalesOrderHeader"");
Other Considerations
Since this appears to be very much singling out the PHP attempts to query the data, it's tempting to consider that Windows permissions may be at play, overriding the ODBC permissions. Attempts have been made to permit access to the database by IIS_USERS (group) and IIS_USR.
Thank you for any suggestions you may offer.