Use Case
The end User submits an ARS request to create a service account in the SQL Server application. During request submission and as per the customer naming convention the requestor enters the account name as "ABC\9kushw5"(Also applies when the account name is generated in a similar fashion from an accountNameRule and also applies if the use case is to create a regular user account with this naming convention)
Pre-requisites
- A successful connection from Saviynt to the SQL Server application has to be established using a DB connector
- Should be able to create a service account(or regular user account) without any special characters(specifically '\') in the account name
Applicable Version(s)
ALL
Solution
The SQL queries to perform this operation on the SQL Server application are as below. These queries can also be validated directly by ruining them in SQL Server Management Studio.
"CREATE LOGIN 'ABC\9kushw5' FROM WINDOWS WITH DEFAULT_DATABASE=master";"CREATE USER 'ABC\9kushw5' FOR LOGIN 'ABC\9kushw5';
Ideally, when the same query is transformed to createAccountJOSN within the Saviynt DB connector, it will look like the below...
createAccountJOSN
{
"CreateAccountQry": [
"CREATE LOGIN \"${accountName}\" FROM WINDOWS WITH DEFAULT_DATABASE=master",
"CREATE USER \"${accountName}\" FOR LOGIN \"${accountName}\""
]
}
Since the account name in this example has '\' in it, the operation will fail on the SQL Server application as the payload would not have transformed as expected. Saviynt logs will print the below error message
Sample log snip:
360 [quartzScheduler_Worker-6] ERROR provisoning.DBProvisioningService - Error occured in Excecuting Query\n"
360 [quartzScheduler_Worker-6] DEBUG provisoning.DBProvisioningService - Closing statements\n"
360 [quartzScheduler_Worker-6] DEBUG provisoning.DBProvisioningService - Closing stmt\n"
360 [quartzScheduler_Worker-6] DEBUG rest.RestUtilService - Got showLogs = true\n"
360 [quartzScheduler_Worker-6] ERROR provisoning.DBProvisioningService - Exception occurred in insert ABC\\9kushw5-Exception occured while Excecuting Query\n"
360 [quartzScheduler_Worker-6] DEBUG provisoning.DBProvisioningService - Status after inserts... false\n"
360 [quartzScheduler_Worker-6] ERROR provisoning.DBProvisioningService - Error while creating account -ABC\\9kushw5 stopping the Job\n"
361 [quartzScheduler_Worker-6] INFO provisoning.DBProvisioningService - End createAccountDB\n"
We will have to handle/escape the '\' special character
As a resolution, you can escape/handle '\' in the CreateAccountJSON as below...
{
"CreateAccountQry": [
"CREATE LOGIN \"${accountName.replaceAll('\\\\', '\\\\\\\\')}\" FROM WINDOWS WITH DEFAULT_DATABASE=master",
"CREATE USER \"${accountName.replaceAll('\\\\', '\\\\\\\\')}\" FOR LOGIN \"${accountName.replaceAll('\\\\', '\\\\\\\\')}\""
]
}
