Accessing Password-protected Databases with OLE DB

Microsoft SQL Server 2005 Compact Edition (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 each time the database is opened.

Accessing a Protected Database

Use the DBPROP_SSCE_DBPASSWORD property in the DBPROPSET_SSCE_DBINIT provider-specific property set to specify this property. When you create a database, this property can be used to specify a database password on the database. Encrypted databases are always password protected.

When you compact a database, this property can be used to change the database password to a new value.

The following code example shows how to open a password-protected SQL Server Compact Edition database. The password is requested from the user by displaying a dialog box, the code for which is not included in this sample.

Examples

// Object declarations
HRESULT          hr              = NOERROR; 
DBPROPSET        dbpropset[2]; 
DBPROP           dbprop[1]; 
DBPROP           sscedbprop[1]; 
BSTR             pwdPassword; // user input password

// Declare the provider interfaces.
IDBInitialize *  pIDBInitialize  = NULL;
IDBProperties *  pIDBProperties  = NULL;

// Initialize the data source.
hr = CoCreateInstance(CLSID_SQLSERVERCE_3_0, 0, CLSCTX_INPROC_SERVER,
    IID_IDBInitialize, (void**) &pIDBInitialize);
if (FAILED(hr))
{
    //Send an error-specific message and do error handling.
    goto Exit;
}

// Initialize property structures
VariantInit(&dbprop[0].vValue);
VariantInit(&sscedbprop[0].vValue);

// Initialize Property with name of database.
dbprop[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
dbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbprop[0].vValue.vt = VT_BSTR;
dbprop[0].vValue.bstrVal = SysAllocString(L"ProtectedData.sdf");
if(NULL == dbprop[0].vValue.bstrVal)
{
    hr = E_OUTOFMEMORY;
    goto Exit;
}

// Second property set has one property containing the provider-specific
// database password in the pwdPassword variable that was obtained from a
// dialog box (not shown).
sscedbprop[0].dwPropertyID = DBPROP_SSCE_DBPASSWORD;
sscedbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[0].vValue.vt = VT_BSTR;
sscedbprop[0].vValue.bstrVal = SysAllocString(<pwdPassword>);
if(NULL == sscedbprop[0].vValue.bstrVal)
{
    hr = E_OUTOFMEMORY;
    goto Exit;
}

// Initialize property set.
dbpropset[0].guidPropertySet = DBPROPSET_DBINIT;
dbpropset[0].rgProperties = dbprop;
dbpropset[0].cProperties = sizeof(dbprop)/sizeof(dbprop[0]);

// Initialize the provider-specific property set.
dbpropset[1].guidPropertySet = DBPROPSET_SSCE_DBINIT;
dbpropset[1].rgProperties = sscedbprop;
dbpropset[1].cProperties = sizeof(sscedbprop)/sizeof(sscedbprop[0]);

// Set the properties into the provider's data source object.
pIDBInitialize->QueryInterface(IID_IDBProperties,(void**)&pIDBProperties);

hr = pIDBProperties->SetProperties(sizeof(dbpropset)/sizeof(dbpropset[0]), 
    dbpropset);
if(FAILED(hr))
{
    goto Exit;
}

// Initialize the data source.
hr = pIDBInitialize->Initialize();
if(FAILED(hr))
{
   goto Exit;
}

Exit:
//Clean up resources here

return;