[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 1.9.0.5
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
Environment:

APS 1.9
MS SQL Server v14


Bug Priority:
Category 2
ACT Numbers:

00989421

Premier Customer:
Yes

 Description   

Summary
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.