Subscribe to News

From TechnologicalWiki

Contents 1 Introduction 2 Standard database 3 Protected database 3.1 Password 3.2 Encryption 4 Code sample 5 References

[edit] Introduction

Databases are files where we save data. Sometimes this data isn't very important so you wouldn't mind loosing it or even letting other people read it, but in other cases databases have confidential information we want to protect. In these cases it's important to provide encryption and protection mechanisms.

In this article we are going to show how to create a SQL CE data base using the OLE DB provider for SQL CE Server using different security levels.

[edit] Standard database

This is the basic way of creating a database. In this case we only need the name of the database, since it will be the only property programmers will have to set in the code to start using it.

[edit] Protected database

[edit] Password

As we have said in the introduction, sometimes we save important information in our databases which we don't want other users to read. To provide our database with the minimum level of security we need to set it up with some kind of safety mechanism, so the first approach to protect our database is to introduce a password. With this, we will protect our data from curious users, that will need to know the password to open the database and modify it.

[edit] Encryption

If we think our data is so important that password protection isn't enough to keep safe our data, or if our database file is going to be sent to other devices using an unprotected communication channels, we should increase the security level of our database using encryption.

[edit] Code sample

Now that we have explained the different ways of creating a database file, the necessary code to do this is just below. The following sample works for both standard and protected databases, so if you don't need to set a password nor enable encryption you can just delete or comment those parts of the code:

     // Object declarations
     HRESULT            hr                   = NOERROR;
     DBPROPSET          dbpropset[2];
     DBPROP             dbprop[1]; // Property array to initialize the provider.
     DBPROP             sscedbprop[2]; // Property array for SSCE security properties
     INT                i                    = 0;
     IDBDataSourceAdmin *pIDBDataSourceAdmin = NULL;
     IUnknown           *pIUnknownSession    = NULL;
     //
     //Create an instance of the OLE DB provider.
     //
     hr = CoCreateInstance(	CLSID_SQLSERVERCE_3_0,
                               0,
                               CLSCTX_INPROC_SERVER,
                               IID_IDBInitialize,
                               (void**)&pIDBInitialize);
     if(FAILED(hr))
     {
         goto Exit;
     }

Initialize structures

     VariantInit(&dbprop[0].vValue);
     for (int i = 0; i < sizeof(sscedbprop) / sizeof(sscedbprop[0]); i++)
     {
        VariantInit(&sscedbprop[i].vValue);
     }

Properties to initialize the data base:

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

     // Specify the property for encryption.
     sscedbprop[0].dwPropertyID = DBPROP_SSCE_ENCRYPTDATABASE;
     sscedbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
     sscedbprop[0].vValue.vt = VT_BOOL;
     sscedbprop[0].vValue.boolVal = VARIANT_TRUE;
     //
     // Specify the password.
     sscedbprop[1].dwPropertyID = DBPROP_SSCE_DBPASSWORD;
     sscedbprop[1].dwOptions = DBPROPOPTIONS_REQUIRED;
     sscedbprop[1].vValue.vt = VT_BSTR;
     sscedbprop[1].vValue.bstrVal = SysAllocString(L"mypassword");
     if(NULL == sscedbprop[1].vValue.bstrVal)
     {
        hr = E_OUTOFMEMORY;
        goto Exit;
     }

     // Initialize the property sets.
     dbpropset[0].guidPropertySet = DBPROPSET_DBINIT;
     dbpropset[0].rgProperties  = dbprop;
     dbpropset[0].cProperties  = sizeof(dbprop)/sizeof(dbprop[0]);
     dbpropset[1].guidPropertySet = DBPROPSET_SSCE_DBINIT ;
     dbpropset[1].rgProperties  = sscedbprop;
     dbpropset[1].cProperties  = sizeof(sscedbprop)/sizeof(sscedbprop[0]);

     // Create and initialize the database.
     hr = pIDBDataSourceAdmin->CreateDataSource(sizeof(dbpropset)/sizeof(dbpropset[0]),
                                                dbpropset, NULL, IID_IUnknown, &pIUnknownSession);
     if(FAILED(hr))
     {
         goto Exit;
     }
     // At this point, the new encrypted database is created.

     Exit:
     // Do cleanup tasks here.
     return;

[edit] References

http://technet.microsoft.com/en-us/library/aa237914(SQL.80).aspx

http://www.codeproject.com/KB/mobile/atl_ole_db_ppc.aspx?msg=1038797#xx1038797xx