Can CSA use LDAP Active Directory authentication connect to MS SQL DB?

Idea ID 2770243

Can CSA use LDAP Active Directory authentication connect to MS SQL DB?

Hi folks,

CSA version: 4.99
OS: windows server 2016.
DB: MS SQL 2016

I found this QCCR which was created from:

  •   Creation Date: 15-Jan-2013
  •   Modified Date: 20-Sep-2017

               https://softwaresupport.softwaregrp.com/doc/KM00302938

            Support Domain Account for MS SQL Authentification

Is anyone know another status of the same issue.

Customer has modified some parameters as below:

We have our local CSA working with the windows authentication/AD user against the MS SQL server database, could you please help to check whether the steps we took are correct or not? Is this official support?

Change the <CSA HOME>\Jboss-as\standalone\configuration\standalone.xml file as below, this is for Both CSA (including MPP) and IDM

1. Change the domain username and password for below:

<security-domains>
<security-domain name="csa-encryption-sec" cache-type="default">
<authentication>
<login-module code="org.picketbox.datasource.security.SecureIdentityLoginModule" flag="required">
<module-option name="username" value="<USERNAME>"/>
<module-option name="password" value="<Encoded PASSWORD>"/>
<module-option name="managedConnectionFactoryName" value="jboss.jca:service=LocalTxCM,name=mssqlDS"/>
</login-module>
</authentication>
</security-domain>
<security-domain name="idm-encryption-sec" cache-type="default">
<authentication>
<login-module code="org.picketbox.datasource.security.SecureIdentityLoginModule" flag="required">
<module-option name="username" value="<USERNAME>"/>
<module-option name="password" value="<Encoded PASSWORD>"/>
<module-option name="managedConnectionFactoryName" value="jboss.jca:service=LocalTxCM,name=IdMDS"/>
</login-module>
</authentication>
</security-domain>

2. Add the domain parameter for the jtds definition

<datasources>
<datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">
<connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
<driver>h2</driver>
<security>
<user-name>sa</user-name>
<password>sa</password>
</security>
</datasource>
<datasource jndi-name="java:jboss/datasources/csaDS" pool-name="mssqlDS" enabled="true">
<connection-url>jdbc:jtds:sqlserver://flexsql01.dev.local:1433/csadb499;ssl=request;loginTimeout=300;socketTimeout=300;domain=<Domain Name></connection-url>
<driver>mssqlDriver</driver>
<pool>
<min-pool-size>10</min-pool-size>
<max-pool-size>200</max-pool-size>
<prefill>true</prefill>
</pool>
<security>
<security-domain>csa-encryption-sec</security-domain>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
<validate-on-match>true</validate-on-match>
</validation>
</datasource>
<datasource jta="true" jndi-name="java:jboss/datasources/idmDS" pool-name="IdMDS" enabled="true" use-java-context="true" use-ccm="true">
<connection-url>jdbc:jtds:sqlserver://flexsql01.dev.local:1433/idmdb499;ssl=request;loginTimeout=300;socketTimeout=300;domain=<Domain Name></connection-url>
<driver>mssqlDriver</driver>
<pool>
<min-pool-size>10</min-pool-size>
<max-pool-size>200</max-pool-size>
<prefill>true</prefill>
<use-strict-min>false</use-strict-min>
<flush-strategy>FailingConnectionOnly</flush-strategy>
</pool>
<security>
<security-domain>idm-encryption-sec</security-domain>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
<validate-on-match>true</validate-on-match>
</validation>
</datasource>

Restart CSA, MPP

Change the db properties for OO designer inside of CSA.
Similar as windows authentication for OO central.
1. Download the latest sqljdbc dll and copy into folder <CSA HOME>\workflow-designer\java\bin.
2. Update the <CSA HOME>\workflow-designer\designer\conf\database.properties (for MSSQL 2014)
db.username=<Domain Name>\<Username>
jdbc.url=jdbc\:sqlserver\://<SQLServer>\:1433;databaseName\=<DBName>;sendStringParametersAsUnicode\=true;integratedSecurity\=true;
db.password=<Encrypted Password>

3. Configure the windows service of OO designed setup the logon user as the AD domain account. (The account need to be assigned with relevant role to be able to start the service up.)

Restart OO designer.

The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.