We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

SQL Server Integration

rmartinez
New Contributor III
New Contributor III

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!

14 REPLIES 14

rushikeshvartak
All-Star
All-Star

Did you specified Database name in URL

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


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

@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? 

You can define same in XMLs


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

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

I appreciate your response. 

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


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

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>

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

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


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

@rushikeshvartak  I am getting the following error:

rmartinez_0-1702413592219.png

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.

rmartinez_1-1702413745135.png

any inputs?

 

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

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

@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>

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


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

@rushikeshvartak Now I have the following error:

rmartinez_0-1702417627540.png

Invalid column name 'accountname'

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

rmartinez_1-1702417722477.png

and it has an underscore. Very strange. 

 

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


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

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