SQL Query for JDBC Invisible Characters

Identity Security Cloud (ISC) ISC Discussion and Questions
, ,
1

I’m connecting ISC to a SQL DB through a JDBC connection. There are only two columns in the view.
The name is the account id and name. Entitlements is set to Access, and it is multivalued.

Name Access
Store Milk
Store Bread
Store Sugar
Store Flour
House Milk
House Sugar
House Flour

In the SQL view the Name column has invisible characters.

I’ve tried this query which works in SSMS but when used in Sailpoint, Sailpoint appears to still bring in the characters. After I run an account aggregation, and export the data, there appears to be a extra line and only one entitlement is connected to the account when there should be many.

SELECT LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(Name, CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),CHAR(160), CHAR(32)),CHAR(9),CHAR(32)))) AS Name, Access FROM TestView

Any possible solutions to this?

2

I would say, test the SQL query before using it in ISC or ask your SQL team to give the correct query if you don’t have access to SQL DB.

Thanks
Krish

3

Isn’t that a encoding problem?

Try this!

best

4

We asked the DBA to rebuild the View and remove all the invisible characters so that Sailpoint could pull it with a simple query similar to
Select * FROM TestView

Seems as if Sailpoint is unable to replace/remove the invisible characters.