Using SqlServer CE without installation

Question

Given:

• A clean machine, no SQL Server CE present.
• A set of *.sdf files (Sql Server CE databases), never mind how they got there
• The DLLs of the relevant Sql Server CE (sqlceca35.dll, sqlcecompact35.dll, sqlceer35EN.dll, sqlceme35.dll, sqlceoledb35.dll, sqlceqp35.dll, sqlcese35.dll)

Question:

• How to make available the Sql Server CE OLEDB provider implemented by the aforementioned DLLs. I am looking for a programmatic way to do so when running as a limited account.

In other words, assuming we are talking about Sql Server CE 3.5, how to make the following code succeed:

IDBInitializePtr spDBInitialize;
HRESULT hr = spDBInitialize.CreateInstance(CLSID_SQLSERVERCE35, NULL);

Note, that the machine is clean and the code is running as a limited account.

EDIT

There is another catch. The code is C++, I cannot use Ado.NET

Answer 1

spDBInitialize.CreateInstance() does the following:

1. Looks up your CLSID in the Windows Registry under HKEY_CLASSES_ROOT\CLSID
2. Determines DLL from InProcServer
3. Calls LoadLibrary() on the DLL
4. Calls GetProcAddress for "DllGetClassObject"
5. Calls DllGetClassObject to get an IClassFactory
6. Uses returned IClassFactory to handle your CreateInstance(NULL, IID_IDBInitialize, (void**) &spIDBInitialize) request

In your scenario, you do cannot get pass the first step because your DLL isn't registered in the Windows Registry.

However, because you know where the SQL Server CE DLLs are you can get around this by making your code just implement 3, 4, 5 and 6.

Here's a C++ console application that opens a SDF using CoCreateInstance replacement:

#include <stdio.h>
#include <tchar.h>
#include <windows.h>
#include <oleauto.h>
#include <atlbase.h>
#include "C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5\Include\sqlce_oledb.h"
#include "C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5\Include\sqlce_err.h"

//----------------------------------------------------------------------
// Creates a COM object using an HMODULE instead of the Windows Registry
//----------------------------------------------------------------------

HRESULT DllCoCreateInstance(HMODULE hModule, REFCLSID rclsid, LPUNKNOWN pUnkOuter, REFIID riid, LPVOID FAR* ppv)
{
    HRESULT hr = S_OK;

    if (hModule == NULL)
    {
        return E_INVALIDARG;
    }

    BOOL (WINAPI*DllGetClassObject)(REFCLSID, REFIID, LPVOID) = NULL;
    (FARPROC&) DllGetClassObject = GetProcAddress(hModule, "DllGetClassObject");
    if (DllGetClassObject == NULL)
    {
        return HRESULT_FROM_WIN32(GetLastError());
    }

    CComPtr<IClassFactory> spIClassFactory;
    hr = DllGetClassObject(rclsid, IID_IClassFactory, &spIClassFactory);
    if (FAILED(hr))
    {
        return hr;
    }

    return spIClassFactory->CreateInstance(pUnkOuter, riid, ppv);
}

//----------------------------------------------------------------------
// Open a close a SDF file
//----------------------------------------------------------------------

int _tmain(int argc, _TCHAR* argv[])
{
    HRESULT hr = S_OK;

    hr = CoInitializeEx(NULL, COINIT_MULTITHREADED);

    // Need a loaded library so we can CoCreateInstance without the Windows Registry.
    HMODULE hModule = LoadLibrary(L"C:\\Program Files (x86)\\Microsoft SQL Server Compact Edition\\v3.5\\sqlceoledb35.dll");

    // Open a SQL Server CE 3.5 database without using Windows Registry.
    CComPtr<IDBInitialize> spIDBInitialize;
    //hr = spIDBInitialize.CoCreateInstance(CLSID_SQLSERVERCE_3_5);
    hr = DllCoCreateInstance(hModule, CLSID_SQLSERVERCE_3_5, NULL, IID_IDBInitialize, (void**) &spIDBInitialize);
    CComPtr<IDBProperties> spIDBProperties;
    hr = spIDBInitialize->QueryInterface(IID_IDBProperties, (void**) &spIDBProperties);
    CComVariant varDataSource(OLESTR("InsertYourSampleDatabase.SDF"));
    DBPROP prop = { DBPROP_INIT_DATASOURCE, DBPROPOPTIONS_REQUIRED, 0, DB_NULLID, varDataSource };
    DBPROPSET propSet = {&prop, 1, DBPROPSET_DBINIT};
    hr = spIDBProperties->SetProperties(1, &propSet);
    spIDBProperties = NULL;
    hr = spIDBInitialize->Initialize();

    // @@TODO: Do your regular OLEDB code with the opened database.
    //...

    // Close COM objects
    spIDBInitialize = NULL;

    CoUninitialize();
    return 0;
}

Some things that's missing from the code snippet:

• Call FreeLibrary() when you're completely finished with the library (usually prior to program termination)
• Handle bad HRESULT hr return codes
• Handle LoadLibrary() failures

To get meaningful error messages for SQL Server CE operations you should consult Microsoft MSDN article Using OLE DB Error Objects (SQL Server Compact Edition). I usually start with a condense version of it here:

if (FAILED(hr))
{
    CComPtr<IErrorInfo> spIErrorInfo;
    GetErrorInfo(0, &spIErrorInfo);
    if (spIErrorInfo != NULL)
    {
        CComBSTR bstrError;
        spIErrorInfo->GetDescription(&bstrError);
        // @@TODO: Do stuff with bstrError
        wprintf("%s\r\n", (BSTR) bstrError);
    }
}

It's easier and safer to just deploy the entire SQL Server CE 3.5 folder, but, if you want a minimal set, I believe the following files are the important ones for your scenario:

• sqlceoledb35.dll - SQLCE OLEDB Provider
• sqlceqp35.dll - SQLCE Query Processor
• sqlcese35.dll - SQLCE Storage Engine
• sqlceer35EN.dll - SQLCE Native Error Strings and Resources
• sqlcecompact35.dll - SQLCE Database Repair tool

For reference, the files I believe you don't need are:

• sqlceca35.dll - SQLCE Client Agent (for Merge Replication to SQL Server)
• sqlceme35.dll - SQLCE Managed Extensions
• System.Data.SqlServerCe.Entity.dll - Managed Assembly