How To Use ATL Consumer Template with Secured Jet Database by Using Jet 4.0 OLEDB Provider (235301)

MORE INFORMATION

A Jet database can be protected by using a database password, Jet security file (containing user ID and password information), or both. When working with such a database by using ATL consumer templates, you must add additional code if a wizard was used to add a Data Access Consumer component to an ATL Component Object Module (COM) object. The following information is also helpful if a wizard was not used and the code is written from scratch. Use one of the following two techniques to open a secured Jet database:

Technique 1

The user can open the connection by calling OpenFromInitializationString on CDataSource object. This technique is not exposed by the wizard.

• If the database C:\Test.mdb has the password "Demo":

  CDataSource db;
  HRESULT hr = db.OpenFromInitializationString(SysAllocString(OLESTR ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\test.mdb;Persist Security Info=False;Jet OLEDB:Database Password=Demo")));
  						

• If Jet security is used, and the system database file is C:\Winnt\System32\System.mdw:

  CDataSource db;
  HRESULT hr = db.OpenFromInitializationString(SysAllocString(OLESTR("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\test.mdb;Persist Security Info=False;Jet OLEDB:System database=C:\\WINNT\\System32\\system.mdw;")));
  						

• If the database has the database password "Demo", User Id=user1, and Password=password1, then use the following setting:

  CDataSource db;
  HRESULT hr = db.OpenFromInitializationString(SysAllocString(OLESTR("Provider=Microsoft.Jet.OLEDB.4.0;User ID=user1;Password=password1;Data Source=C:\\test.mdb;Persist Security Info=False;Jet OLEDB:System database=C:\\WINNT\\System32\\system.mdw;Jet OLEDB:Database Password=Demo")));
  						

Technique 2

Open the connection by adding property sets to CDataSource object. This method is exposed by the wizard, but needs some modification to work correctly with the secured Jet database.

The following code snippet is a modified form of wizard-generated code, while adding a Data Access Consumer component to an ATL/COM project. It is not recommended that you just copy-and-paste the exact code, because it might not work correctly. You need to modify the code to fit your project.

User ID=user1, Password=password1, Database password = Demo, system database = "C:\Winnt\System32\System.mdw"

#include "Msjetoledb.h"
HRESULT Open()
	{
		CDataSource db;
		CSession	session;
		HRESULT		hr;

		
		CDBPropSet dbinit[2];
		dbinit[0].SetGUID(DBPROPSET_DBINIT);
	         //Set Jet OLE DB provider specific properties
	 	dbinit[1].SetGUID(DBPROPSET_JETOLEDB_DBINIT);

		dbinit[0].AddProperty(DBPROP_AUTH_CACHE_AUTHINFO, true);
		dbinit[0].AddProperty(DBPROP_AUTH_ENCRYPT_PASSWORD, false);
		dbinit[0].AddProperty(DBPROP_AUTH_MASK_PASSWORD, false);
		dbinit[0].AddProperty(DBPROP_AUTH_PASSWORD, "password1");
		dbinit[0].AddProperty(DBPROP_AUTH_USERID, "user1");
		dbinit[0].AddProperty(DBPROP_INIT_DATASOURCE, "C:\\test.mdb");
		dbinit[0].AddProperty(DBPROP_INIT_MODE, (long)16);
		dbinit[0].AddProperty(DBPROP_INIT_PROMPT, (short)4);
		dbinit[0].AddProperty(DBPROP_INIT_PROVIDERSTRING, ";COUNTRY=0;CP=1252;LANGID=0x0409");
		dbinit[0].AddProperty(DBPROP_INIT_LCID, (long)1033);
		dbinit[0].AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);

		//Database password
		dbinit[1].AddProperty(DBPROP_JETOLEDB_DATABASEPASSWORD, "Demo");
		//System database
	        dbinit[1].AddProperty(DBPROP_JETOLEDB_SYSDBPATH, "C:\\winnt\\system32\\system.mdw");		

		hr = db.OpenWithServiceComponents("Microsoft.Jet.OLEDB.4.0", dbinit,2);
		if (FAILED(hr))
			return hr;

		hr = session.Open(db);
		if (FAILED(hr))
			return hr;

		CDBPropSet	propset(DBPROPSET_ROWSET);
		propset.AddProperty(DBPROP_CANFETCHBACKWARDS, true);
		propset.AddProperty(DBPROP_IRowsetScroll, true);
		propset.AddProperty(DBPROP_IRowsetChange, true);
		propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE );


		hr = CCommand<CAccessor<CTitle> >::Open(session, "SELECT * FROM Title", &propset);
		if (FAILED(hr))
			return hr;

		return MoveNext();
	}
				

IMPORTANT: If you are using the datalink connection wizard for JET 4.0 OLE DB Provider, you must set these properties correctly under the All tab if you are connecting to a secured database:

• Jet OLEDB:Database Password = "mydbPwd"
• Jet OLEDB:System database = "C:\winnt\system32\system.mdw" (or proper location of System.mdw)
• Password = "myJetpwd"

Visual C++ wizards do not put Database Password inside generated code. You must do this manually as shown in code snippet.

NOTE: If you get the following compiler error, please see the Microsoft Knowledge Base article in the References section to acquire the proper Msjetoledb.h header file: