[MNT-20760] Introduce support for MS SQL Data Source using windows authentication login Created: 09-Jul-19  Updated: 27-Apr-20

Status: Open
Project: Service Packs and Hot Fixes
Component/s: APS Kickstart/Studio
Affects Version/s: Alfresco Process Services
Fix Version/s: Alfresco Process Services .N

Type: Feature
Reporter: Anna Qin Assignee: APS Team
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

APS 1.9
MS SQL Server v14

Bug Priority:
Category 2
ACT Numbers:


Premier Customer:


Introduce support for MS SQL Data Source using windows authentication login.

Steps to reproduce
1. Install MS SQL server
2. Provide the required MS SQL JDBC driver for activiti-app
3. As admin in activiti-app add a new data source via "Identity Management" -> "Tenants" -> "Data Source":

Jdbc url=jdbc:sqlserver://MSDWVA-SUPPORT01\SUPPORT_DEV 
Username =domainAD\loginID 
Driver Class=com.microsoft.sqlserver.jdbc.SQLServerDriver 

4. Click "Test connection"

Current Behavior
Test fails with message "Login failed for user 'domainAD\loginID'. ClientConnectionId:08526f30-f827-476a-a9c7-db517338acaa"

In APS logs: 

ERROR com.activiti.service.idm.AlfrescoIdmDataSourceService - Login failed for user 'domainAD\loginID'. ClientConnectionId:6968357e-fba4-490b-a547-2d509f6a91ee 

In TCP dump:

{"id":1,"name":"NTI Data","config":\{"jdbcUrl":"jdbc:sqlserver://MSDWVA-SUPPORT01\\SUPPORT_DEV","driverClass":"com.microsoft.sqlserver.jdbc.SQLServerDriver","username":"domainAD\\loginID","password":"XXXX"}}cøÚ\#}^ 

The cause of the issue is the userName being (MS SQL login ID) in "domain\loginID" format using windows authentication. 

What happens is that APS auto-escapes a "\" character with "\" resulting in the actual value of userName as "domain
loginID" being sent to MS SQL Server for authentication from APS->IDM->Data Source->Test Connection. 

Expected Behavior
The connection test should be successful.

Generated at Thu Jul 09 10:07:33 BST 2020 using JIRA 7.6.3#76005-sha1:8a4e38d34af948780dbf52044e7aafb13a7cae58.