Administering SQLCE

Because SQLCE is separate from the Compact Framework, it must be administered separately. The administration tasks take the form of security administration, database maintenance, and installation and deployment.

Security

As with any database, it is important that the data in SQLCE be secure. This is particularly the case because the device on which SQLCE is running is inherently mobile and can easily fall into the hands of someone who is not the intended user. As a result, it is important that Compact Framework applications be able to present an authentication dialog to users before providing access to the data and that the data itself can be encrypted on the device.

SQLCE supports these requirements by offering both password protection for the entire database file and encryption for the entire file using a 128-bit key.

Password Protection

Password protecting a SQLCE database can be done only when the database is created or compacted (as discussed in the next section) and can be done with either the CreateDatabase method of the SqlCeEngine object or the CREATE DATABASE DDL statement.

When using the CreateDatabase method, the password attribute is simply appended to the connection string passed into the constructor of the SqlCeEngine class. As a result, the CreateDb method shown in Listing 5-1 could be altered as shown in the following snippet to accept a password of up to 40 characters to use when creating the database.

Public Shared Function CreateDb(ByVal filePath As String, _
  ByVal pwd As String) As Boolean

   ' Code ommitted for brevity

   Dim eng As SqlCeEngine
   Try
       eng = New SqlCeEngine("Data Source=" & filePath & _
        ";password= & pwd)
       eng.CreateDatabase()
       Return True
   Catch e As SqlCeException
       ' Code ommitted for brevity
   End Try
End Function

Once the password has been created, there is no way to recover it; however, the password can be changed by compacting the database, as will be discussed later in this section.

If the application is executing DDL to create a database, a CREATE DATABASE statement like the following can be issued:

CREATE DATABASE 'mydb.sdf' DATABASEPASSWORD 'sdfg53$h'

Encryption

Just as with password protection, encrypting a SQLCE database can be accomplished with the CreateDatabase method, the process of compacting, or the CREATE DATABASE DDL statement.

To encrypt using CreateDatabase, the encrypt database attribute needs to be added to the connection string in addition to the password, as shown in the following snippet, where the CreateDb method from Listing 5-1 is once again modified to support an argument to determine if the database should be encrypted. Note, however, that the attribute needn't be provided when the database is opened.

Public Shared Function CreateDb(ByVal filePath As String, _
  ByVal pwd As String, ByVal encrypt As Boolean) As Boolean

   ' Code ommitted for brevity

   Dim eng As SqlCeEngine
   Try
       Dim connect = "Data Source=" & filePath & _
        ";password= & pwd
       If encrypt Then
           connect &= ";encrypt database=TRUE"
       End If
       eng = New SqlCeEngine(connect)
       eng.CreateDatabase()
       Return True
   Catch e As SqlCeException
       ' Code ommitted for brevity
   End Try
End Function

The password attribute must be included because SQLCE uses the MD5[15] hashing algorithm to create the 128-bit key required by the RC4[16] algorithm used to encrypt the database. For this reason it is important that the password chosen be of a reasonable length to avoid easy cracking by hackers.[17] Although it would be cumbersome to force users to input 40-character passwords, passwords of at least 8 characters (including letters, numbers, and at least once special character) should suffice to offer a reasonable amount of protection. Changing passwords periodically via compaction is also a good strategy because it moves the target for any potential hacker. [15] A message-digest algorithm developed in 1991 by RSA Security. [16] A symmetric encryption algorithm designed by RSA Security in 1987 and used in Secure Sockets Layer (SSL) and other commercial applications. [17] Hackers can extract the hash value from the .sdf file and then run either a dictionary or a brute-force attack to discover the password. Longer passwords are recommended because the effort required in using a brute-force method increases exponentially. For example, two-character passwords take seconds to break, while eight-character passwords can require years.

To encrypt the database file using the CREATE DATABASE statement, the ENCRYPTION ON clause is used as follows:

CREATE DATABASE 'mydb.sdf' DATABASEPASSWORD 'sdfg53$h' ENCRYPTION ON

Database Maintenance

As alluded to earlier, the SqlCeEngine class also supports the CompactDatabase method, which can be used to compact and reclaim wasted space that collects in the database as data and objects are deleted and tables are reindexed. It is recommended that SQLCE databases be periodically compacted because this also leads to improved query performance through index reordering and the refreshing of statistics used by the query processor to generate execution plans.

Compacting a database can also be used to change the collating order,^[18] encryption, or password for the database, as mentioned previously in this section. This method creates a new database and requires that the source database be closed and that the destination file not exist. It is also important to remember that because a copy is created, the device will need to have enough room to make the copy or an error will result.

^[18] If not specified in the CREATE DATABASE statement or the destination database connection string, the default collation assigned is Latin1_General. This collation uses Latin 1 General dictionary sorting rules, code page 1,252, and is case-insensitive and accent-insensitive. All databases in SQLCE are always case-sensitive and accent-insensitive. To see the available collations, see the Books Online for SQLCE.

Once again, it makes sense to wrap the CompactDatabase functionality in a method that checks for the existence of the source database and then automatically copies the destination back to the source when completed, as shown in Listing 5-11, which takes advantage of the FileSystem class in Listing 3-5 to create the temporary destination that is ultimately moved back to the original file name.

Listing 5-11 Compacting a SQLCE Database. This method compacts a database, reclaiming wasted space, and copies the newly created database back to the old name.

Public Shared Function CompactDb(ByVal filePath As String) As Boolean

   If Not File.Exists(filePath) Then
      MsgBox("Source database does not exist = " & filePath, _
        MsgBoxStyle.Critical)
      Return False
   End If

   Dim eng As SqlCeEngine
   Try
       eng = New SqlCeEngine("Data Source=" & filePath)
       eng.Compact("Data Source=" & _
        FileSystem.GetSpecialFolderPath(ceFolders.PERSONAL) & _
        "\temp000.sdf")
       File.Delete(filePath)
       File.Move(FileSystem.GetSpecialFolderPath( _
         ceFolders.PERSONAL) & "\temp000.sdf", filePath)
   Catch e As Exception
       _lastException = e
       MsgBox("Could not compact the database at " & filePath, _
        MsgBoxStyle.Critical)
       Return False
   Finally
       eng.Dispose()
   End Try

   Return True

End Function

It should also be noted that SQLCE creates a temporary file each time the database engine is initialized and attempts to delete it when the engine terminates normally. This file is used for storing pages that exceed the SQLCE buffer cache, as well as interim results and tables used in queries. By default, the file is created in the Temp directory on the device, although its location can be specified using the temp file directory attribute of the connection string as shown here:

Dim connect = "Data Source=\mydb.sdf;temp file directory=\StorageCard"
Dim eng As New SqlCeEngine(connect)

This may be required if the need to store the temporary file on a storage card, rather than in RAM, arises. The file will grow the most when transactions and large UPDATE and DELETE statements are executed. However, keep in mind that accessing storage cards is typically slower than accessing RAM; so, query performance may suffer as a result.

Installation and Deployment

To use SQLCE in a solution, components must be installed both on the development machines as well on the device. Fortunately for Compact Framework developers, all the required SQLCE components are installed and configured with VS .NET 2003. This allows a developer to reference the System.Data.SqlServerCe.dll assembly from any SDP and begin coding against SQLCE.

When an SDP that accesses SQLCE is deployed to either an emulator or an actual device from VS .NET using the Build menu, two .cab files are automatically copied to the device and extracted. Which .cab files are deployed is determined by the processor type and version of Windows CE running on the device. They include a development-only time .cab (Sqlce.dev.platform.processor.cab) that contains Query Analyzer and error string files, as well as the .cab file that contains the SQLCE database engine (Sqlce.platform.processor.cab).

When an application is ready for final deployment, the SQLCE .cab file must be added to the deployment and extracted on the device, as discussed in Chapter 10. The amount of space required on the device varies with the platform and processor, but it ranges from 1 to 3MB.

Deploying a SQLCE Database

Finally, it's important to note that in many instances it is more efficient and reduces load on the database server to prebuild a SQLCE database and deploy it to the device, rather than forcing clients to perform an initial synchronization using RDA or replication, as discussed in Chapter 7. This benefit only increases as the number of deployed devices in a solution increases. For example, a field service solution could be initially deployed with parts lists and geographic data.

To prebuild a SQLCE database, a developer can write an administrative application that creates the database on the device or the emulator and pulls in the appropriate data using RDA. The database can then be copied back to the development machine using ActiveSync and included in a VS .NET project as a content file using the Properties window. In this way, the database will be deployed with the application, as discussed in Chapter 10. Although it would be a welcome addition, at this time there is no desktop- or server-based utility to allow developers to create and populate SQLCE databases.

Alternatively, and especially if the database is large, the database file can be distributed on CompactFlash memory and CompactFlash disk drives, both of which are supported by SQLCE.