Password Protecting a Database

  • Article

The Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) Database Engine lets you enforce that a password be supplied when you access a local database. In SQL Server Compact Edition, one password is created for the database that is being secured. A password is not created for each user of a database. Passwords for SQL Server Compact Edition databases:

  • Can be up to 40 characters long.
  • Can contain letters, symbols, digits, or a combination.
  • Cannot be recovered.

Note

The password does not prevent the reading of any data in the database file as clear text. By using both encryption and a password, you can store the data in an encrypted format and restrict programmatic access to the database.

Creating Password-Protected Databases

Password-protected databases are created by supplying a password property when the database is created. Password-protected databases can be created by the following methods:

  • Using SQL Syntax
    To create a password-protected database through SQL syntax, specify the database password in the CREATE DATABASE statement. The password must follow the DATABASEPASSWORD keyword and be enclosed in single quotation marks, as in the following example:

    CREATE DATABASE "secure.sdf" DATABASEPASSWORD '<myPassword>'

  • Using ADO .NET
    To create a password-protected database by using the SqlCeEngine.CreateDatabase method, you must specify the password property in the connection string, as in the following example:

    "data source=\ssce.sdf; password=<myPassword>"

    For more information, see the System.Data.SqlServerCe.SqlCeEngine class in the Microsoft .NET Compact Framework software development kit (SDK) in Microsoft Visual Studio 2005.

  • Using OLE DB
    To create an encrypted database by using the OLE DB Provider for SQL Server Compact Edition, you must pass the provider-specific property DBPROP_SSCE_ENCRYPTDATABASE as VARIANT_TRUE and specify a password by using the provider-specific property DBPROP_SSCE_DBPASSWORD.

Accessing Password-Protected Databases

A password must be supplied to open a password-protected database. Password-protected databases can be accessed by the following methods:

  • Using the data provider for SQL Server Compact Edition
    To access a password-protected database by using the SqlCeConnection.Open method, you must specify the password property in the connection string. For example:

    "data source=\ssce.sdf; password=<myPassword>"

    For more information, see the System.Data.SqlServerCe.SqlCeConnection class in the .NET Compact Framework SDK in Visual Studio 2005.

  • Using OLE DB
    SQL Server Compact Edition supports a file-level access-control mechanism that requires that a password be presented to access a password-protected SQL Server Compact Edition database. This password must be passed every time the database is opened. Use the DBPROP_SSCE_DBPASSWORD property in the DBPROPSET_SSCE_DBINIT provider-specific property set to specify the password. When you create a database, this property can be used to specify a database password on the database. Encrypted databases are always password-protected.