Monday, June 30, 2008

The Next Advantage Technical Summit

ADS Technical Summit Logo The next Advantage Technical Summit will be held in Boise, Idaho on September 10th and 11th. We have changed the breakouts to provide more flexibility in scheduling and allowing you to get to the sessions your most interested in. We will now be providing four separate sessions on SQL which will be available both days. You can register for the Technical Summit here.

We will also be offering an additional half-day session on Friday September 12th. This will be a hands-on session where we will walk through creating an Advantage Data Dictionary. You will need to bring a laptop to this session so you can work through the various labs. I will be putting the materials together for this session in July so if you have any suggestions please let me know.

Friday, June 27, 2008

Troubleshooting Advantage Connection Issues

Connection errors have been reduced over the years by improvements to the Advantage communication layer. However these errors still occur. There is a lot of great information in the Knowledge Base on the most common connection errors such as 6420, 6060 and 6610. If these suggestions do not resolve the problem then you can use the Environment Check Utility included with Advantage Data Architect (ARC).

The Environment Check Utility will attempt to make a connection and generate a file called ENV_LOG.TXT which will be saved in the ARC directory (C:\Program Files\Advantage 9.0\ARC). This file contains information about the client operating system and the dlls which are in use. This file will be very useful for Advantage Support if you cannot resolve the issues on your own.

Environment Check Utility

The Advantage comm layer goes through a hierarchy to discover the server. As soon as the server is discovered a connection is attempted. Specifying an IP Address and Port number in the connection path (i.e. \\\Data) or in the ADS.INI file will cause the comm layer to attempt a connection without discovery.  This connection method is the quickest since there is no polling of the network, however, if the servers IP address changes then all the paths must be updated. The Advantage comm layer hierarchy follows these steps.

  1. Check if ADS is on the same machine. This checks to see if we can make a connection using shared memory rather than the network (note: Advantage 8.1 and newer)
  2. See if the server name is an IP address and port or server name and port. If IP address and port, attempt to connect directly to it. If the address is in this format: \\<name>:<port>\share\xxx then attempt a DNS look up. If address is found then attempt to connect directly to it.
  3. Attempt to read the ads.ini file for an IP and Port number. If found attempt to connect directly, If fail return 6097.
  4. Start the Advantage discovery process
    1. Create a mailslot discovery thread
    2. Create an SAP discovery thread
    3. Create a multicast discovery thread
  5. The first discovery thread to successfully return wins and a connection is attempted using this value.
  6. If all the discovery threads fail then an error code is returned.

If you need more detailed information is required add COMMCALLTRACE=1 to the ADS.INI file under the [Settings] heading. This will generate a file called ADSCOM.TXT in the TEMP directory (c:\Windows\Temp) which is defined by the Temp environment variable. This file is generated by the Advantage communications layer (axcws32.dll) and logs all the steps taken to connect to Advantage. A snippet from the file is below

  0.696   5844  WIN32     32-bit Windows comm dll version :
  7.975   5844  Special Build : BD0817a
 14.947   5844  WSCOMM32  Winsock 32-bit dll
 21.928   5844  Start ____axCommInit____
 31.334   5844  End ____axCommInit____SUCCESS
 38.840   5844  Start ____axConnect81____
 46.295   5844      axConnect81 - pucServerName : Server
 53.817   5844  axConnect81 - Connection Type : 2    Hex: 0x00000002
 61.535   5844      axConnect81 - !usFoundViaWS
 69.458   5844  Start ____AdsIsLocal____  Server Name:  : Server
 78.459   5844  Start ____GetAddressFromSharedMemory____
 86.758   5844  Start ____OpenIPCSharedMem____
 94.944   5844  End ____OpenIPCSharedMem____  SUCCESS
103.113   5844  GetAddressFromSharedMemory - Shared mem name does not match:  : CFRANZXP
111.439   5844  End ____GetAddressFromSharedMemory____  No match found
119.825   5844  End ____AdsIsLocal____  Result:  : 0    Hex: 0x00000000
128.239   5844  Start ____WSFindAdvantage81____
136.753   5844  Start ____GetRemoteAddress____
146.141   5844  Start ____QuickDiscovery____
156.498   5844  Start ____ServerNameHasAddr____  : Server
166.249   5844  End ____ServerNameHasAddr____ Server name does not have a port value
175.911   5844  Start ____GetAddressFromSharedMemory____
185.711   5844  Start ____OpenIPCSharedMem____
195.630   5844  End ____OpenIPCSharedMem____  SUCCESS
205.747   5844  GetAddressFromSharedMemory - Shared mem name does not match:  : CFRANZXP
215.761   5844  End ____GetAddressFromSharedMemory____  No match found
228.758   5844  QuickDiscovery - pstSin->sin_port == 0 
243.513   2468  Start ____MailSlotDiscoveryThread____
254.666   2468  MailSlotDiscoveryThread - CreateMailslot SUCCEEDED : 203    Hex: 0x000000CB
268.448   5332  Start ____MulticastDiscoveryThread____
287.190   4576  Start ____SAPDiscoveryThread____

This file seems a bit cryptic but it can give you some information which may help diagnose your issue. As you can see it is divided into three columns; the first column is the time in milliseconds, the second column is the thread number and the third column is the action being performed. By reading through the file you can:

  • verify that the Server name was specified correctly (see: axConnect81 – pucServerName: …).
  • verify the connection type (see axConnect – Connection Type: …).
  • see the specified server’s IP address if it is found on the network.
  • see all the addresses on a multi-homed server and/or client
  • determine which discovery method located Advantage

If the multicast fails then a Winsock error will be reported. Look for the line containing WSAGetLastError which reports the Winsock error code. You can get a description of the Winsock error codes here or by doing a Google search.

Near the end of the file the actual error message will be reported for example:

WSFindAdvantage81 - GetRemoteAddress FAILED or ucFoundAdsAddr(0 means mailslot or multicast found ADS address) : 6420    Hex: 0x00001914

 While this file can seem daunting there is some very valuable information contained in the file. Run some tests on your own since you can learn from both successful and unsuccessful connections. After you have read through several of these files you will be able to locate the important information quickly. If your still stuck send the files to Advantage Support and they can assist you.

Wednesday, June 25, 2008

Using the Field Description Property

When using Advantage Data Dictionaries every object (Tables, Indexes, Fields, Views …) has a description property. This allows developers to enter information about the object for other developers to see. Access to the various description (comment) property varies depending on the dictionary object.  Changing these properties requires the ALTER permission. These fields are a great mechanism for storing a revision history or design notes.

Cary Jensen discussed using the field description property as a way to provide tool tips to users during one of his presentations about Advantage. I thought this was a fantastic idea so I incorporated it into some of my training and created an example application.

To get the field description property use either the AdsDDGetFieldProperty API or the System.Columns table. I chose to use the system.columns table because I find it easier to implement. The field description can be read by any user or group who has READ, UPDATE, INSERT or DELET permissions for the field.

First I create a function which takes two string parameters and returns a string containing the comment. This function uses the following query: SELECT Comment FROM system.columns WHERE Parent = :Table AND Name = :Field.

   1: function TForm1.GetFieldComment( sTable: String; sField: String): String;
   2: begin
   3:   // Set the parameter values
   4:   qryComment.ParamByName('Table').Value := sTable;
   5:   qryComment.ParamByName('Field').Value := sField;
   7:   try
   8:     // Execute the Query
   9:     qryComment.Open;
  11:     // Read the comment value
  12:     if qryComment.IsEmpty  then
  13:       Result := 'No comment for field: ' + sField
  14:     else
  15:       Result := qryComment.Fields[0].asString;
  17:     // Close the query
  18:     qryComment.Close;
  19:   except
  20:     on E: EDatabaseError do
  21:          begin
  22:             if ( E is EADSDatabaseError ) then
  23:               Result := 'ADS Error: ' + E.Message
  24:             else
  25:               Result := 'Error: ' + E.Message;
  26:          end;
  27:   end;
  29:   GetFieldComment := Result;
  30: end;

To set the hint for a particular control we simply provide the table and field name to the function. For example: dbeCustID.Hint := GetFieldComment( ‘Customer’, ‘CustID’);. The table name is included since many forms may use views or queries to retrieve the data. By supplying the table name we can get the description for fields from the proper table.

An example form is shown along with the information from the data dictionary.

Tool tip displayed on the form

LastName field properties

This example project is be posted on Code Central under the Delphi category.

Monday, June 23, 2008

Advantage Configuration Parameter Memory Usage

A knowledge base article was recently posted entitled “How Much Memory Does the Advantage Database Server Generally Use?” It gives a great overview of how memory is allocated when Advantage starts up based on the configuration settings. A table summarizing the bytes used by each configuration option and the default values for Advantage version 9.0 is below. You can click on any of the configuration settings links for more information about the setting.

Configuration Setting Memory (bytes) Default Value
Connections 5976 User Option
Work Areas 1080 25 * Connections
Tables 680 100
Index Files 1048 150
Data Locks 240 40 * Connections
Worker Threads 1000000 8

For most applications the default values should be sufficient, however, connections is frequently modified. If you make multiple connections from your application or if you provide a suite of applications you will need to increase this value. Increasing the number of connections does not automatically increase Work Areas or Data Locks so you may need to adjust these settings as well.

Worker Threads require the most memory allocation of the configuration parameters. Generally the default value (8) is sufficient for most implementations. However, if you have a busy system or a multi-processor system the general rule is to configure 8 – 16 worker threads per processor.

With Advantage 9.0 these configuration settings are used for the initial startup values. The server will dynamically allocate connections, work areas, tables, indexes and locks if the initial values are exceeded. This virtually eliminates 7004 (maximum work areas exceeded), 7005 (maximum tables exceeded), 7006 (maximum indexes exceeded) and 7007 (maximum locks exceeded) errors. I recommend checking these values whenever you add more users or update your application. Set your initial values above the Max Used if you are consistently using more than your configured values.

Advantage Configuration Utility

You can modify the default configuration parameters during the install by providing a custom ADS_CFG.REG file, details can be found here.

Friday, June 20, 2008

Advantage and High Availability - Warm Standby

High availability (HA) is a topic that I encounter frequently when talking to database developers. With high quality hardware at very reasonable prices smaller companies are considering high availability. This becomes critically important for companies that provide 24X7 access to information. For these companies down time means money lost and unhappy customers.

Earlier this year I wrote a white paper about high availability and Advantage. It discusses HA in very general terms and offers information about planning an HA solution. You can read the white paper here. William Brewer wrote a very good article on "Database High-Availability: Soup to Nuts" which is also worth a look. Finally there is a Tech-Tip which highlights the Advantage features which can be used for providing an HA solution.

Since this topic comes up quite often I thought I would revisit some of these features. In particular I will be discussing a Warm Standby solution using replication or server-side aliases.

Advantage Replication provides an excellent mechanism for providing a Warm Standby solution. By configuring the database to replicate to a standby server a current copy of the data is always available. As changes are made on the primary server these changes will be pushed to the standby server in real time. If the primary server fails users will simply need to connect to the standby server and continue working.

Replication Solution

There are a few caveats to this solution. First users will receive an error when the primary server fails, most likely 6610 "Client timeout". You will have to have some sort of mechanism within your application to allow users to change to the standby server path. Second the data on the standby server should never be changed unless the primary server is not available. Replication is not the same as synchronization so altering the data on both the primary and standby servers at the same time may produce different data at each server. Therefore you may need to control when users can connect to the standby server which can be accomplished by disabling logins to the standby database. When the primary server fails the administrator enables logins on the standby server and tells the users to change the connection path for the application.

When the primary server has been repaired you will need to copy the data from the standby server back to the primary server. You should do this when all users are logged off the system. Copy the data from the standby server to the primary server and verify that replication is setup and enabled. Once this is finished both servers will have the same data and users can be allowed onto the primary server again.

Another way to provide warm standby is the use of Network Attached Storage (NAS) or Storage Area Network (SAN) devices. These devices are designed to be shared between multiple systems allowing two or more machines to access the same data. Advantage uses server-side aliases to connect to external devices for data access. With this solution the database remains in the same location and is simply accessed by the available server.

NAS/SAN Solution

Advantage data dictionaries and tables can only be opened by one server at a time. Therefore, both servers can not have the data open simultaneously which means that whichever server opens the data first is the one that all users must connect to. Another issue with this solution is the potential for a single point of failure. If the NAS/SAN is not available then the data will not be available whereas with the replication solution the data is stored on both servers. To use this solution a fault-tolerant NAS or SAN would reduce the risk of failure but raising the expense.

Both of these solutions can greatly increase the availability of your data. Having a second Advantage server running and ready to accept requests will greatly reduce the amount of down time your users experience. You will need to educate your users on how to respond to a failure of the primary server. Your IT staff or "computer guy" will also need to respond quickly to determine why the primary server failed so it can quickly be fixed. HA solutions are resource intensive and require training and setup but for companies who need them they are invaluable.

As a final note; an HA solution is not a substitute for regular backups and a disaster recovery plan. The solutions I described above would commonly be located at the same location, therefore, you should keep an off site backup in case something happens to your office. Consider using the Advantage Online Backup features to generate backups on a scheduled basis.

Wednesday, June 18, 2008

Storing Passwords Securely with Salt

In a previous article on Securely Storing Passwords I discussed encrypting passwords before storing them in the database. This provides an additional level of security since it prevents users from viewing passwords. However, there are still potential security problems with this mechanism. If someone got a copy of the database and the hash key they could run queries against the database to discover passwords. This could be done by hashing common passwords and using these results to query the database. All the usernames for a password that matched would be returned potentially compromising many user accounts.

The use of a Salt with the password can make the passwords less vulnerable to these types of attacks. Basically you add an additional string to the password before doing the hash. Ideally this string would be random and unique for every user, ensuring that every user has a unique hash value even if they choose the same password as another user.

I like to use GUIDs as unique identifiers which gives me a strong unique value. Another benefit of using GUIDs is that I have a long string value which I can use to salt the passwords entered by the user. Since I am already storing this value as my primary key I do not have to add another column to my table or come up with an algorithm to generate random text. To demonstrate this I’ll start with a simple membership table outlined below:

Column Data Type Notes
UserID Char(36) Primary Key GUID
UserName CiChar(100) User Name with Unique Index
Password Char(30) Hashed password and Salt
UserEmail CiChar(255) Users e-mail address
IsLockedOut Logical Flag to lock-out user
LastLogin Timestamp Date/Time of last successful login

To encode our password for storage or to validate it first we need to add the salt to the password. Once the proper salted password is established we encode it with our SHA-1 algorithm.

   1: private string EncodePassword(string password, string salt)
   2: {
   3:     string saltedPassword = password + salt;
   5:     // encode the password using SHA-1
   6:     HMACSHA1 hash = new HMACSHA1();
   7:     hash.Key = HexToByte(ValidationKey);
   8:     // note: the encoded value is a 28 character base64 string
   9:     return Convert.ToBase64String(hash.ComputeHash(
  10:            Encoding.Unicode.GetBytes(saltedPassword)));
  11: }

First let’s create a function to add a user to the table. We will use the encode password function to ensure that the password is stored in its encoded form.

   1: public bool CreateUser(string username, string password, string email)
   2: {
   3:     AdsConnection conn = new AdsConnection(connectionString);  
   5:     // Check to see if the user already exists   
   6:     AdsCommand cmd = new AdsCommand("SELECT COUNT(*) FROM Membership " +   
   7:         "WHERE Username = :username", conn);  
   8:     cmd.Parameters.Add("Username",   
   9:         System.Data.DbType.String).Value = username;  
  11:     try
  12:     {
  13:         int UserCount = cmd.ExecuteScalar();
  15:         if (UserCount != 0)
  16:             return false;
  17:     }
  18:     catch (AdsException e)
  19:     {
  20:         // Handle Advantage errors here  
  21:         return false;
  22:     }
  23:     finally
  24:     {
  25:         conn.Close();
  26:     }
  28:     string uID = Guid.NewGuid().ToString();
  30:     cmd.CommandText = "INSERT INTO Membership " +
  31:         " (UserID, Username, Password, UserEmail, IsLockedOut" +
  32:         " Values(:UserID, :Username, :Password, :Email, true)";
  34:     cmd.Parameters.Add("UserID", System.Data.DbType.String).Value = uID;
  35:     cmd.Parameters.Add("Username", System.Data.DbType.String).Value = username;
  36:     cmd.Parameters.Add("Password", System.Data.DbType.String).Value = 
  37:                        EncodePassword(password, uID);
  38:     cmd.Parameters.Add("Email", System.Data.DbType.String).Value = email;
  40:     try
  41:     {
  42:         int recAdded = cmd.ExecuteNonQuery();
  44:         if (recAdded > 0)
  45:             return true;
  46:         else
  47:             return false;
  48:     }
  49:     catch (AdsException e)
  50:     {
  51:         // Handle Advantage errors here
  52:         return false;
  53:     }
  54:     finally
  55:     {
  56:         conn.Close();
  57:     }
  59:     return true;
  60: } 

Now that we have our new EncodePassword function we can create a function to validate the user. In this case we will need to return a resultset containing the user information. We need this to verify that the user is not locked out and we will be using the UserID as our salt value.

   1: public bool ValidateUser(string username, string password)
   2: {
   3:     bool isValid = false;
   4:     AdsConnection conn = new AdsConnection(connectionString);
   5:     AdsCommand cmd = new AdsCommand("SELECT UserID, Password, IsLockedOut " +
   6:         "FROM Membership WHERE Username = :Username", conn);
   8:     cmd.Parameters.Add("Username", System.Data.DbType.String).Value = username;
  10:     AdsDataReader reader = null;
  11:     bool isLockedOut = false;
  12:     string pwd = "";
  13:     string salt = "";
  15:     try 
  16:     { 
  17:         conn.Open();
  18:         reader = cmd.ExecuteReader();
  20:         if (reader.HasRows)
  21:         {
  22:             reader.Read();
  23:             salt = reader.GetString(0).Trim();
  24:             pwd = reader.GetString(1).Trim();
  25:             isLockedOut = reader.GetBoolean(2);
  26:         }
  27:         else
  28:         {
  29:             // User not found so return false
  30:             return false;
  31:         }
  32:         reader.Close();
  33:         // if user is locked out so return false
  34:         if (isLockedOut == true)
  35:             return false;
  37:         // Check the password
  38:         if (pwd == EncodePassword(password, salt))
  39:         {
  40:             // Password is valid
  41:             isValid = true;
  42:             // Update the LastLogin field
  43:             AdsCommand updateCmd = new AdsCommand("UPDATE Membership SET " +
  44:                 "LastLogin = NOW() WHERE Username = :UserName", conn);
  45:             updateCmd.Parameters.Add("Username",
  46:                 System.Data.DbType.String).Value = username;
  47:             updateCmd.ExecuteNonQuery();
  48:         }
  49:         else
  50:         {
  51:             // Password is not valid
  52:             isValid = false;
  53:         }
  54:     }  
  55:     catch (AdsException e)
  56:     {
  57:         // Handle any Advantage errors here
  58:     }
  59:     finally
  60:     {
  61:         if (reader != null) { reader.Close();}
  62:         conn.Close();
  63:     }
  65:     return isValid;
  66: }

In this case we simply return false unless the username and password are correct. This function could easily be modified to return an integer value which could provide additional information. For example: 0 = Success, 1 = Invalid password, 2 = Invalid username, 3 = User locked out. You could even create an enum for these return values.

I recommend reading “Could you Pass the Salt?” by Scott Mitchell and Thomas Tomiczek which describes the perils of a hashed password without “salting”.

UPDATE: When I looked back through the code for this post I realized that I used another helper function to convert the validationKey,a hexidecimal number from 16 to 48 characters in length, to a byte array.  I posted the HexToByte function below.

   1: private byte[] HexToByte(string hexString)
   2: {
   3:     byte[] returnBytes = new byte[hexString.Length / 2];
   4:     for (int i = 0; i < returnBytes.Length; i++)
   5:         returnBytes[i] = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
   6:     return returnBytes;
   7: }