Securing Database Access

Using a connection string is necessary to connect to a database, but the security risks are high if that connection string contains database credentials. For example, a previous version of the CML application made this setting in the file global.asa in the Application_OnStart subroutine, called when the CML application is started:

Application("FmLib_ConnectionString") = "DSN=FmLib;SERVER=MyServer;UID=sa;WSID=MyComputer;DATABASE=FMLib"

In this line, the FmLib_ConnectionString variable was assigned an OLE DB connection string to connect to the FmLib database. All the parameters needed to connect to a database were assigned to this string and stored in this application variable.

As you can see, FmLib_ConnectionString contains the username "sa", but no password. An account with the "sa" user name is created by default for every SQL Server installation. If you have used the tools supplied with Microsoft SQL Server, you have likely used this account to log on. Because this account is widely known, it is not secure.

One idea for increasing security would be to edit global.asa and add a password parameter to the "sa" account. For example:

Application("FmLib_ConnectionString") = "DSN=FmLib;SERVER=MyServer;UID=sa;PWD=Gremlin;WSID=MyComputer;DATABASE=FMLib"

The connection string that appears in this subroutine is generated when adding a connection to the Visual InterDev™ project used to create the CML application. Because this application uses RDS for client-side data access, the connection string is sent in plain text to the client. (Fortunately, this string appears in an IFrame that can be made invisible by setting the Debugging Mode application setting to Off.) But because the connection string contains the Microsoft SQL Server password for the "sa" account, it is a considerable security risk to send it at all.

There are two ways this problem can be resolved.

• The first is to use a read-only account for RDS access. (You can find out how to add accounts in the online Help for Microsoft SQL Server.) Because some client requests (such as a full-text search or book request) result in records or tables being added to the database, this approach is a little harder to implement than it might at first seem. However, it may be the solution of choice if the application just needs read-only database access.
• The second method is to edit the connection string generated by Microsoft Visual InterDev to remove the specifics of the database credentials. This sanitized connection string, which contains only the OLE DB DSN, can safely be sent to the client because it includes nothing to indicate how the application attaches to the database. For example:

  Application("FmLib_ConnectionString") = "DSN=FmLib"
  

• Unfortunately, the ODBC drivers installed with SQL Server 7.0 ignore the connection information stored in the registry as part of the System DSN. Instead, they send the Windows NT credentials of the user, which usually means the connection is made in the Anonymous IIS user account context (IUSR_computername). So this method will not work for the CML application.
• The third method is to set up Windows NT account access privileges in the SQL Server Enterprise Manager, and limit access to database objects by user. Although this method is the most secure, it requires more administration than traditional SQL account authentication. You can learn more about this in the SQL Books Online installed with SQL Server 7.0.