Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement 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!!