Export Saviynt user data to an external Azure SQL database

[Bernd]

Hello,
we are new to Saviynt and currently implementing a new environment.
We are searching for some guidance/documentation to export active user data from Saviynt to an external Azure SQL database table which must be updated once a day.

We have currently built a connection to the Azure SQL server using the MASTER_SQLSERVER_TEMPLATE (DB) but now we would need some guidance how to write the date to the SQL table.

It would be great if someone could give some guidance or rough steps on such an implementation or have a link to a documentation.

Thanks.

[adarshk]

Hi @Bernd 

This can be achieved, using the template mentioned. 
You can setup any DB Connector to provision the data from Saviynt. DB Connector uses SQL queries to provision/reconcile data. 

For further understanding of how Database connector works with saviynt, please refer the below guide:
https://docs.saviyntcloud.com/bundle/DB-v2021x/page/Content/Database-Integration-Overview.htm

Along with setting connector, you also need to setup Database application. 
To setup application and connect with database, you can leverage Design Module.

https://docs.saviyntcloud.com/bundle/SSM-Admin-v55x/page/Content/Chapter04-Onboarding-and-Managing-A...

[Bernd]

Hello adarshk,

Thanks a lot for the reply. We will check the links.

[axelB]

Thank you, but this info is to general. We are looking for detailed configuration example to write user info to a sql table

Example : TESTSQLSERVER.windows.database.net (database : TESTDB

Our connection is created and working :

jdbc:sqlserver://TESTSQLSERVER.database.windows.net:1433;database=TESTDB;encrypt=false;trustServerCertificate=false

we have a table on database TESTDB:

SELECT 'USER_ID', 'USER_NAME', 'emp_number' FROM dbo.Table

Now we want to write all info from the USERS in Saviynt to that table with following mapping :

SYSTEMUSERNAME as 'USER_ID'

,DISPLAYNAME as 'USER_NAME'

, CUSTOMPROPERTY26 as 'emp_number'

Can you provide us the code on what needs to be set in which properties of the connection for this example? CREATEACCOUNTJSON UPDATEACCOUNTJSON GRANTACCESSJSON REVOKEACCESSJSON ENABLEACCOUNTJSON UPDATEUSERJSON, ACCOUNTSIMPORT ENTITLEMENTVALUEIMPORT…

The only thing we have now, is a working db connection, and that’s where our knowledge ends for the configuration, to write to a azure SQL database.

[axelB]

Hi adarshk, can you please reply on the last question?

[axelB]

in the meatime we were able to connect to a sql but we do not know the code to retrieve a attribute from the manager of a USER in saviynt. 

With an Active Directory connection we are able to do this : 

"manager": "${managerAccount?.accountID}"

But in the SQL DB connection : the following is not working : 

__99__eco_mgr_emp_number='${managerAccount?.customproperty26}'

while retrievel of the user info attributes just works fine : __99__eco_emp_number='${user.customproperty26}'

please help us on what code to use for this?

thanks

Can you please share what has to be used for SQL Connection and the jason/code to use?

[Dhruv_S]

Hi @axelB 

I appreciate you reaching out to the Saviynt forums. 

Before answering your questions mentioned above, I would like to know more about the business usecase you are trying to achieve here. 

From the description, I can see you have mentioned about exporting complete user data from Saviynt to Azure SQL DB.

1. Are you trying to use Saviynt as a source of truth for Azure DB here which will regularly update the data from Saviynt to Azure DB? Or it is a one-time activity to load the users to Azure?

2. Do you want to export all user's all attributes in Azure DB? Or specific set of attributes and specific set of users?

Please note DB connector cannot export users from Saviynt to DB directly. With the help of DB connector, you can do provisioning from Saviynt to DB which means an account in DB will be created for the corresponding users in Saviynt. Is your use case similar to this where you want to create an account in DB for corresponding users in Saviynt? Please confirm and provide the information. Accordingly, we can suggest you the next steps.

Regards,

Dhruv Sharma

[axelB]

I have opened a new forum request, please reply there : https://forums.saviynt.com/t5/data-access-governance/json-sql-code-for-a-sql-connection-in-saviynt/t...

1. we are using the create jason, and update jason of the SQL connector we have, and this is all working.

2. No, we are defining sepcific attributes of a USER in saviynt to be put in columns in the sql database, which is working except of what is mentioned above, and in the new post that i created.

this is working, except if we want to put an attribute of the manger of a USER in saviynt

[rushikeshvartak · Accepted solution]

I think this is already resolved 

__99__eco_mgr_emp_number='${usersManagerObj?.customproperty26}'