A big aspect of using any SQL backend is establishing secure access to the data, so all access requires a login.
Logging into the database normally occurs when the application is started, although later in the course we’ll touch on dynamic connections, which can switch databases on the fly. Logins through managed connections are required (the user will not be able to take further action if it fails) and can be done silently if the system is set to store the credentials in the configuration file.
Note that any stored credentials must be secured using encryption. The use of Windows Authentication (also known as a Trusted Connection) to access SQL Server eliminates the need to store credentials in the configuration file, but are less secure and may not be allowed by company policy.
Storing encrypted passwords in the configuration file creates some challenges…
The login process must be available for your applications and the DataFlex Studio and other utilities. But what to do about password encryption? The encryption used by your application should be fully configurable and only known to you and your organization. If you develop an application for multiple companies, they may require different encryption methods.
Also, since the encryption logic is compiled into the application, we need a separate encryption method for the Studio and tools we supply.
We solved this complication by storing two sets of encrypted passwords; PWD is where your encrypted password is stored, and DFPWD is the one we use for the tools.
We saw an example of this in action when we created the SQL login for the cookbook database and used that in our cookbook managed connection.
You may notice that the two encryptions are similar, but not identical. This is because the default encryption algorithm we provide in the LoginEncryption package is similar to the one we use in the DataFlex tools.
Of course, as we build up the full connection string with user credentials (even when using Trusted Connections), you may want a user interface to allow them to be entered. This functionality is provided by the Database Login Tool, or dialog.
The login dialog is designed to only be invoked when needed – so, if the managed connection information stored in the configuration file contains everything necessary to complete the connection it is not displayed. Each workspace has its own, unique and randomly generated seed for the encryption algorithm and, as we mentioned earlier, the seed or encryption method is fully customizable.
The login dialog, and all the logic behind it, is completely code-based, so you can use it stand-alone, embed it as-is in your application, or work the functionality into your own custom user interface easily.
As you can see in this example the code required to implement a managed connection that uses the default password encryption and login dialog is four simple lines; we instantiate the connection object based on the cConnection class and use the LoginEncryption and DatabaseLoginDialog packages. This code is placed just inside the application object.
In the next lesson we’ll explore using multiple connections.