Password Protecting a Database

  • Article

The SQL Server Compact Database Engine lets you enforce that a password be supplied when you access a local database. In SQL Server Compact, 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 databases should be Strong Passwords. The password should have following characteristics:

  • 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 '<enterStrongPasswordHere>'

  • 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=<enterStrongPasswordHere>"

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

  • Using OLE DB

    To create an encrypted database by using the OLE DB provider for SQL Server Compact, you must specify the encryption mode with the provider-specific property DBPROP_SSCE_ENCRYPTIONMODE 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

    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=<enterStrongPasswordHere>"

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

  • Using OLE DB

    SQL Server Compact supports a file-level access-control mechanism that requires that a password be presented to access a password-protected SQL Server Compact 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.