Error 27502.Could not connect to Microsoft SQL Server '(local) Using MSOLEDBSQL 19.0

Yes

Summary:

When user machine has MSOLEDBSQL version higher then 18.6 version installed, below error is observed on installing the installer.

Symptoms:

Error 27502.Could not connect to Microsoft SQL Server '(local)'. Provider cannot be found. It may not be properly installed,

Cause:

This error is known to occur on using old 19.0 or above is due to windows changes introduced in latest version, you can check those changes in below shared Microsoft links

OLE DB Driver 19.0 for SQL Server

Resolution:

To fix this issue setup.exe with SQL script fails in MSOLEDBSQL19.0, we need to do some changes in settings.xml file,

Go to Installshield installed location and Open settings.xml from below path
C:\Program Files (x86)\InstallShield\2021\Support\0409
Edit the xml using notepad or notepad.xml, on line number 296 or
And set the value for MetaDataName like shown below
<IS_MetaData Name="MSSQLServer" DisplayName="Microsoft SQL Server"
we also need to set the provider like shown below
AdoCxnDriver="Provider=MSOLEDBSQL19.1"
and next in the same line we need to change AdoCxnAddtional as shown below

AdoCxnAdditional="Use Encryption for Data=False"
Then Save the Settings.xml file and close Installshield and Reopen Installshield
NOTE: We need to Create the project to see the changes for existing projects this changes wont affect,

To work with existing project:

Open Existing project with SQL script added.
Then go to Direct Editor and select ISSQLDBMetaData table and delete the existing row.
Go to SQL Script view
Add a new SQL script OR delete and re add your SQL script view
After adding SQL script
Go to direct editor and check ISSQLDBMetaData table
Check values are added like shown below, this 2 values are important, if its not added it wont work
Then save your project and build
Install it a machine where OLDDBSQL 19.0 or higher version is installed, now the setup.exe will work as expected.