Azure Managed Identity support in JDBC Connections - Installer

[lenisha]

Is your feature request related to a problem? Please describe. Enable Pega to SQL Server connections using Azure Managed Identity Authentication (Passwordless)

Describe the solution you'd like JDBC driver supports AuthN mode in url https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=sql-server-ver15

Specifying url in pega values would open connection ,but should not pass usename,password to the driver in this mode

url: "jdbc:sqlserver://<SRV>.database.windows.net:1433;database=<DB>;authentication=ActiveDirectoryMSI;msiClientId=<MI ID>;selectMethod=cursor;responseBuffering=full;sendStringParametersAsUnicode=false;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"

Running installer fails

[echo] Rules schema has been set to 'RULES'[pega:testconnectioninfo] 2022-03-10 06:35:18,607 (com.microsoft.sqlserver.jdbc.intern) SEVERE - ConnectionID:1 Cannot use "Authentication=ActiveDirectoryMSI" with "User", "UserName" or "Password" connection string keywords.

Describe alternatives you've considered

Looking at configuration stored in config map, need to remove DB_USERNAME ,DB_PASSWORD from that config

apiVersion: v1
data:
migrateSystem.properties.tmpl: "# Properties File for use with migrateSystem.xml
\ Update this file \n# before using migrate.bat/sh script.\n# Set the DB connection\n\n###################
COMMON PROPERTIES - DB CONNECTION ##################\n########################################################################\n\n#The
system where the tables/rules will be migrated from\npega.source.jdbc.driver.jar={{
.Env.DRIVER_JAR_PATH }}\npega.source.jdbc.driver.class={{ .Env.JDBC_CLASS }}\npega.source.database.type={{
.Env.DB_TYPE }}\npega.source.jdbc.url={{ .Env.JDBC_URL }}\npega.source.jdbc.username={{
.Env.DB_USERNAME }}\npega.source.jdbc.password={{ .Env.DB_PASSWORD }}\n#Custom
connection properties\npega.source.jdbc.custom.connection.properties={{ .Env.JDBC_CUSTOM_CONNECTION
}}\n\npega.source.rules.schema={{ .Env.RULES_SCHEMA }}\n#Set the following property
if the source system already contains a split schema.\npega.source.data.schema={{
.Env.DATA_SCHEMA }}\n# Used for systems with a separate Customer Data Schema\n#
The value of pega.source.data is the default value for pega.source.customerdata.schema\npega.source.customerdata.schema={{

[APegaDavis]

@lenisha based on the link you shared, it looks like this authentication method requires an additional library (Azure Identity client library for Managed Identity) and some special handling at the java level that I don't think is supported in the Pega Platform at this time (at least for deployments).

[kishorv10]

The enhancement has been addressed through the above-linked PRs. Kindly go through them and revert in case of any further issues.