OleDb connection to Azure SQL Server using MFA - error DB_SEC_E_AUTH_FAILED "User option must be specified, if Authentication option is 'ActiveDirectoryInteractive'. "

Question

We have a utility app written in C++ (MFC/ATL) which connects to SQL Server databases to perform various functionality.
It connects via ODBC using the CDatabase::OpenEx method with DSN for the particular database passed in the connection string.
It also connects via OleDb (for certain functionality) using the CDataSource::Open method with the connection details passed in a CDBPropSet object.

We are now attempting to support databases hosted on Azure SQL Server. It is configured to require MFA, the connection to the corporate network is via a VPN.

When opening the databases via the ODBC connection method it works as expected, the MFA authentication dialogs are displayed and if correct details are entered the connection is established. (The DSN has been modified such that the Server name is now in the form myserver.database.windows.net rather than sqlserverinstancename, the authentication method is now Azure Active Directory Interactive, previously it was Windows Integrated).

However, when attempting to use OleDb the CDataSource::Open method returns the error "DB_SEC_E_AUTH_FAILED Authentication failed."
Calling GetErrorRecords we see the following information "User option must be specified, if Authentication option is 'ActiveDirectoryInteractive'. "

The code being called is shown below

	CDBPropSet	dbinit(DBPROPSET_SQLSERVERDBINIT);  

	dbinit.AddProperty(SSPROP_AUTH_MODE, OLESTR("ActiveDirectoryInteractive"));	  
	dbinit.AddProperty(DBPROP_AUTH_USERID, strWinUserName);  // n.b. added after error as only property related to "User" I could find  
	dbinit.AddProperty(DBPROP_INIT_DATASOURCE, m_strServer);   
	dbinit.AddProperty(DBPROP_INIT_CATALOG, m_strDatabase);  
	dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)DBPROMPT_NOPROMPT);		  

	HRESULT hr = CDataSource::Open(_T("MSOLEDBSQL19"), &dbinit);  

The only property that I could see related to "User option" was DBPROP_AUTH_USERID which is normally set along with DBPROP_AUTH_PASSWORD if using SQL Authentication. I tried it anyway as shown above ( tried adding both before and after the AUTH_MODE entry just in case). The user name here is in the form "myusername@mydomain.com". I've tried using both MSOLEDBSQL19 and MSOLEDBSQL but both report same error. It's obviously recognising the SSPROP_AUTH_MODE setting but asking for additional configuration detail;s, I've just no idea what - anyone know what I'm doing wrong?

Thanks.

Answer 1

I managed to resolve this, I should have been using two separate CDBPropSet objects and passing them as an array, changes shown below :

 CDBPropSet    dbinit(DBPROPSET_DBINIT);  
 CDBPropSet    azureinit(DBPROPSET_SQLSERVERDBINIT);  
  
 azureinit.AddProperty(SSPROP_AUTH_MODE, OLESTR("ActiveDirectoryInteractive"));  // this setting goes in its own PropSet     

 dbinit.AddProperty(DBPROP_AUTH_USERID, strWinUserName);  // n.b. this seems to be needed due to use of authentication method detailed above, not sure why  
 dbinit.AddProperty(DBPROP_INIT_DATASOURCE, m_strServer);   
 dbinit.AddProperty(DBPROP_INIT_CATALOG, m_strDatabase);  
 dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)DBPROMPT_NOPROMPT);  

 CDBPropSet  propSets[2];  
 propSets[0] = dbinit;  
 propSets[1] = azureinit;  

 // n.b. I have installed MSOLEDBSQL19 driver and I am referencing the msoledbsql.h file provided with that in my project so the CLSID resolves to the correct driver from that header.  
 HRESULT hr = CDataSource::Open(MSOLEDBSQL_CLSID, &propSets[0], 2); // changed from a string to a CLSID for first param, array for second and size of array third