SQL Server Integration

[rmartinez]

All,

Good afternoon. I am trying to integrate a SQL database into Saviynt. The connection is successful; however, within the database, we have different tables. I been trying to find information of how to select the specific table in the database. 

Has anyone encountered this situation?

Thanks!

[rushikeshvartak]

Did you specified Database name in URL

jdbc:sqlserver://host:port;DatabaseName=<dabase name>;authenticationScheme=JavaKerberos;integratedSecurity=true;userName=${USERNAME};password=${PASSWORD} 

[rmartinez]

@rushikeshvartak  Thank you for your replying. Yes. I am able to connect to the database successfully. However, within the database, I need to specify the table where the accounts and the entitlements are located.  How can I do this in Saviynt? 

[rushikeshvartak]

You can define same in XMLs

[rmartinez]

@rushikeshvartak Is there any example of how to structure this?

I appreciate your response. 

[rushikeshvartak]

https://docs.saviyntcloud.com/bundle/DB-v2020x/page/Content/Configuring-the-Integration-for-Importin...

[rmartinez]

Thank you for the reply. however, I was looking at the documentation and I still confused how to call the specific table. for instance, and I want to call a table called employees. where do I specify this information in the section of the xml?

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[select accountname,securitysystem,endpoint,entitlementtype,entitlementvalue,status from accounts]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="accountName" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"/>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character"/>
</mapper>
</dataMapping>

[rushikeshvartak]

You can define query in instead of below query in above post you mentioned

select accountname,securitysystem,endpoint,entitlementtype,entitlementvalue,status from accounts

[rmartinez]

@rushikeshvartak  I am getting the following error:

If I have a table called testtable

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="account_name"><![CDATA[select account_name as 'User', 'Inv-test' as endpoint, 'Inv-test' as securitysystem, '1' as status from [dbo].[testtable];]]></sql-query>
<mapper description="This is the mapping field for Db Import" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="account_name" type="character" />
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character" />
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="end_point" type="character" />
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="end_point" type="character" />
</mapper>
</dataMapping>

This is the schema of the table.

any inputs?

[rushikeshvartak]

• Remove underscore from alias 
• mapper should have same alias and mapping

[rmartinez]

@rushikeshvartak thank you for your time and support. Where should I remove the information. like this?

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="accountname"><![CDATA[select accountname as 'User', 'Inv-test' as endpoint, 'Inv-test' as securitysystem, '1' as status from [dbo].[testtable];]]></sql-query>
<mapper description="This is the mapping field for Db Import" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="accountname" type="character" />
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character" />
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="end_point" type="character" />
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="end_point" type="character" />
</mapper>
</dataMapping>

[rushikeshvartak]

<dataMapping>
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="accountname"><![CDATA[select accountname as 'User', 'Inv-test' as endpoint, 'Inv-test' as securitysystem, '1' as status from [dbo].[testtable];]]></sql-query>
<mapper description="This is the mapping field for Db Import" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="User" type="character" />
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character" />
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character" />
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character" />
</mapper>
</dataMapping>

[rmartinez]

@rushikeshvartak Now I have the following error:

Invalid column name 'accountname'

The columns in our SQL Server table has the name of account_name which contains the account of the application.

and it has an underscore. Very strange. 

[rushikeshvartak · Accepted solution]

<dataMapping>
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name"><![CDATA[select account_name as 'User', 'Inv-test' as endpoint, 'Inv-test' as securitysystem, '1' as status from [dbo].[testtable];]]></sql-query>
<mapper description="This is the mapping field for Db Import" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="User" type="character" />
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character" />
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character" />
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character" />
</mapper>
</dataMapping>

[rmartinez]

@rushikeshvartak  Thank you for all your time !! it worked!!