[midPoint] Get the generated email address back to database
Ivan Noris
ivan.noris at evolveum.com
Thu Nov 13 17:49:11 CET 2014
Hi Jason,
in MS SQL Server, you probably need to use DateTime type column instead
of Timestamp, and default values (for creation of row) and triggers (for
row update).
(Just googling, I'm not a MS SQL Server expert...)
Regards,
Ivan
On 11/13/2014 03:37 PM, Jason Everling wrote:
> Yes, this is the one that worked for me yesterday,
> <icfi:jdbcUrlTemplate>*jdbc:sqlserver://%h:%p;database=%d*</icfi:jdbcUrlTemplate>
>
> I wish I could do it directly, there wouldn't be a way to access the
> real system database directly, there is literally a table for each
> attribute with the student id or employee number as the unique key.
> For example, there are 3 address tables, 3 name tables, 3 status
> tables, etc.. Joins are needed to get the correct information and to
> match up the data.
>
> Is what I did,
>
> Instead of using a CSV file, I am actually sync'ing to a secondary sql
> database on the sql server. It is basically syncing what I had in the
> CSV directly to a database and I am using a sql server integration
> services package to keep it sync'd with one another.
>
> In midpoint, I removed the CSV connector and now only have the
> database table connector that connects to the secondary database on
> the sql server. Midpoint then generates the username, email address,
> password and such which then gets pushed back to the secondary
> database. The SSIS package on SQL Server runs every hour, picks up the
> changes, and updates the changes to the correct tables in the primary
> database. Also works the opposite, student updates address in the
> student system, gets sync'd to the secondary database, midpoints picks
> this up and changes it in midpoint which then gets sync'd to AD.
>
> The only thing I had an issue with was the change_timestamp column, if
> I create a column with the timestamp type in sql server midpoint
> errors out, I had to change it to nvarchar(50) for it to work in
> midpoint but it seems to not be getting used.
>
> JASON
>
> On Thu, Nov 13, 2014 at 2:17 AM, Ivan Noris <Ivan.Noris at evolveum.com
> <mailto:Ivan.Noris at evolveum.com>> wrote:
>
> Hi Jason,
>
> unfortunately I have no example for SQL Server and the
> synchronization configuration nor detailed knowledge of SQL Server.
>
> But, as a last resort, I believe that DB configuration with some
> kind of "datetime" column updated automatically when content of
> the row changes should be possible (as in other databases).
>
> Regards,
> Ivan
>
> ------------------------------------------------------------------------
>
> *From: *"Jason Everling" <jeverling at bshp.edu
> <mailto:jeverling at bshp.edu>>
> *To: *"midPoint General Discussion"
> <midpoint at lists.evolveum.com <mailto:midpoint at lists.evolveum.com>>
> *Sent: *Wednesday, November 12, 2014 8:59:33 PM
>
> *Subject: *Re: [midPoint] Get the generated email address back
> to database
>
> Awesome! I got it working, well i at least have the email
> address and password going back into the database with a
> little dirty work,
>
> So, the only issue I have so far is the change_timestamp
> column for sql server, how should this column be created for
> SQL Server?
>
> JASON
>
> On Wed, Nov 12, 2014 at 11:29 AM, Jason Everling
> <jeverling at bshp.edu <mailto:jeverling at bshp.edu>> wrote:
>
> What is the jdbcURLTemplate for SQL Server when using the
> database table sync
>
> <icscdbtable:jdbcUrlTemplate>jdbc:sqlserver://%h:%p;databaseName=%d;</icscdbtable:jdbcUrlTemplate>
>
> Where to put username/password because sql server has to
> be something like or is the below correct?
>
> jdbc:sqlserver://%h:%p;databaseName=%d;user=%user;password=%password
>
> JASON
>
> On Wed, Nov 12, 2014 at 10:41 AM, Jason Everling
> <jeverling at bshp.edu <mailto:jeverling at bshp.edu>> wrote:
>
> I am going about this the hard way, I was just
> thinking about this, I could create another database
> on the sql server and actually write a sql server
> integration services script to sync the information
> that I am putting in the CSV file and instead of
> syncing from the csv file I could sync from that
> database that would only have 1 table!
>
> This should be do-able!
>
> JASON
>
> On Wed, Nov 12, 2014 at 10:28 AM, Jason Everling
> <jeverling at bshp.edu <mailto:jeverling at bshp.edu>> wrote:
>
> Oh Ok, I don't really want to provision or update
> anything else in the database, that would require
> a lot of joins, we have a table basically for each
> attribute, name table, address table, email table,
> email2 table, address2 table, status table for
> more or less employeeType, department table and so
> on. Each table's unique key is soc_sec which is
> the student id and is the employeeNumber in midpoint.
>
> Would a correlation still be needed if the groovy
> sql statement is already matching employeeNumber
> using the update statement when using the simple
> method?
>
>
> JASON
>
> On Wed, Nov 12, 2014 at 9:45 AM, Ivan Noris
> <Ivan.Noris at evolveum.com
> <mailto:Ivan.Noris at evolveum.com>> wrote:
>
> Hi Jason,
>
> depending on direction(s) you wish to use, you
> may need the -sync version of the resource AND
> the sync Groovy script - if you wish to use
> the connector as bi-directional.
>
> If you want to do it only for provisioning
> (and keeping CSV for import/recon/livesync),
> you don't need the sync Groovy script.
>
> Be adwised that the -simple resource sample
> does NOT contain any correlation expression
> configuration. Which is mandatory if you wish
> to use it as proposed by me yesterday (the
> "dirty" part).
>
> Regards,
> Ivan
>
> ------------------------------------------------------------------------
>
> *From: *"Jason Everling"
> <jeverling at bshp.edu
> <mailto:jeverling at bshp.edu>>
> *To: *"midPoint General Discussion"
> <midpoint at lists.evolveum.com
> <mailto:midpoint at lists.evolveum.com>>
> *Sent: *Wednesday, November 12, 2014
> 3:46:04 PM
> *Subject: *Re: [midPoint] Get the
> generated email address back to database
>
>
> Yes, I cannot use the DBTable Connector
> because of how are database is, there are
> so many tables that require joins which is
> why I went with the CSV file, easier to
> dump then scp over to the midpoint host.
>
> I am leaning more toward the ScriptedSQL
> after reviewing it, the update sample
> script should be fine, I just need to
> remove everything and just leave the
> staement with email and edit the SQL
> statement.
>
> Would I use the Sync sample script or just
> the localhost simple? Not really syncing
> the info, just pushing the info back.
>
> JASON
>
>
>
> On Tue, Nov 11, 2014 at 1:13 PM, Ivan
> Noris <ivan.noris at evolveum.com
> <mailto:ivan.noris at evolveum.com>> wrote:
>
> Hi Jason,
>
>
> > Yes, the real database the CSV file
> is being dumped from, SQL Server
> > 2008 R2, I have already loaded the
> jdbc driver for SQL Server in the
> > rebuilt war so that is already taken
> care of,
>
> Well, there are several possibilities:
>
> 1.
> - use the CSV export from SQL DB
> - create user with email address in
> midPoint
> - provision to either specific SQL DB
> table OR LDAP/Active Directory
> attribute, from which your SQL DB
> application will fetch the attribute.
>
> This way you will have no direct
> connection to SQL DB and you'll keep
> most of your current settings. But the
> SQL DB must fetch the data from
> some intermediate table or LDAP/AD
> attribute.
>
> 2.
> - use the CSV export from SQL DB
> - create user with email address in
> midPoint
> - provision directly to your SQL DB.
>
> This is somehow "dirty" as you will
> provision to the same resource as
> you indirectly use as your
> authoritative resource. For midPoint,
> attempt
> to provision email attribute to the
> SQL DB will look like "add"
> operation, but connector will discover
> existing user record in SQL DB
> and modify it instead of adding. This
> requires 100% correct correlation
> expression to automagically convert
> the add to modify.
>
> 3.
> - use the SQL DB as both authoritative
> source for your users as well as
> target resource for email address
> provisioning
>
> This is the most clean solution, with
> no intermediate steps and no
> magic. You just set the proper
> resource mappings (outbounds, inbounds).
>
> The option "3" is recomended over the "2".
>
> As for your SQL DB: connecting (as
> either source or target resource)
> would require a connector. In
> midPoint, you have three possibilities:
>
> a) DB Table connector: this is
> suitable for ONE table (no joins). Which,
> I suppose is not your case...
> b) ScriptedSQL connector: universal
> scriptable (Groovy) connector. This
> can do anything, but you have to write
> your operations (add, modify,
> delete, search etc.). The connector is
> just a glue that puts the
> operation scripts to a form usable
> from midPoint.
> c) custom SQL connector (must be
> written in Java)
>
> Btw. you don't need to have JDBC
> drivers bundled in (custom build of)
> midpoint. It's completely OK just drop
> the driver JAR files to
> appropriate directory of your
> application server, in my case for
> example, customer's "/opt/tomcat/lib"
> contains MySQL and MS SQL drivers
> needed for the deployment.
>
> Regards,
> Ivan
>
> --
> Ing. Ivan Noris
> Senior Identity Management Engineer
> evolveum.com <http://evolveum.com>
>
> ___________________________________________
> "Idem per idem - semper
> idem Vix."
>
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com
> <mailto:midPoint at lists.evolveum.com>
> http://lists.evolveum.com/mailman/listinfo/midpoint
>
>
>
>
>
> CONFIDENTIALITY NOTICE:
> This e-mail together with any attachments
> is proprietary and confidential; intended
> for only the recipient(s) named above and
> may contain information that is
> privileged. You should not retain, copy or
> use this e-mail or any attachments for any
> purpose, or disclose all or any part of
> the contents to any person. Any views or
> opinions expressed in this e-mail are
> those of the author and do not represent
> those of the Baptist School of Health
> Professions. If you have received this
> e-mail in error, or are not the named
> recipient(s), you are hereby notified that
> any review, dissemination, distribution or
> copying of this communication is
> prohibited by the sender and to do so
> might constitute a violation of the
> Electronic Communications Privacy Act, 18
> U.S.C. section 2510-2521. Please
> immediately notify the sender and delete
> this e-mail and any attachments from your
> computer.
>
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com
> <mailto:midPoint at lists.evolveum.com>
> http://lists.evolveum.com/mailman/listinfo/midpoint
>
>
>
>
> --
> Ing. Ivan Noris
> Senior Identity Management Engineer
> evolveum.com <http://evolveum.com>
> ___________________________________________
> "Idem per idem - semper idem Vix."
>
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com
> <mailto:midPoint at lists.evolveum.com>
> http://lists.evolveum.com/mailman/listinfo/midpoint
>
>
>
>
>
>
>
>
> CONFIDENTIALITY NOTICE:
> This e-mail together with any attachments is proprietary and
> confidential; intended for only the recipient(s) named above
> and may contain information that is privileged. You should not
> retain, copy or use this e-mail or any attachments for any
> purpose, or disclose all or any part of the contents to any
> person. Any views or opinions expressed in this e-mail are
> those of the author and do not represent those of the Baptist
> School of Health Professions. If you have received this e-mail
> in error, or are not the named recipient(s), you are hereby
> notified that any review, dissemination, distribution or
> copying of this communication is prohibited by the sender and
> to do so might constitute a violation of the Electronic
> Communications Privacy Act, 18 U.S.C. section 2510-2521.
> Please immediately notify the sender and delete this e-mail
> and any attachments from your computer.
>
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com <mailto:midPoint at lists.evolveum.com>
> http://lists.evolveum.com/mailman/listinfo/midpoint
>
>
>
>
> --
> Ing. Ivan Noris
> Senior Identity Management Engineer
> evolveum.com <http://evolveum.com>
> ___________________________________________
> "Idem per idem - semper idem Vix."
>
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com <mailto:midPoint at lists.evolveum.com>
> http://lists.evolveum.com/mailman/listinfo/midpoint
>
>
>
>
>
> CONFIDENTIALITY NOTICE:
> This e-mail together with any attachments is proprietary and
> confidential; intended for only the recipient(s) named above and may
> contain information that is privileged. You should not retain, copy or
> use this e-mail or any attachments for any purpose, or disclose all or
> any part of the contents to any person. Any views or opinions
> expressed in this e-mail are those of the author and do not represent
> those of the Baptist School of Health Professions. If you have
> received this e-mail in error, or are not the named recipient(s), you
> are hereby notified that any review, dissemination, distribution or
> copying of this communication is prohibited by the sender and to do so
> might constitute a violation of the Electronic Communications Privacy
> Act, 18 U.S.C. section 2510-2521. Please immediately notify the sender
> and delete this e-mail and any attachments from your computer.
>
>
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com
> http://lists.evolveum.com/mailman/listinfo/midpoint
--
Ing. Ivan Noris
Senior Identity Management Engineer
evolveum.com
___________________________________________
"Idem per idem - semper idem Vix."
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.evolveum.com/pipermail/midpoint/attachments/20141113/6ea9ddbc/attachment.htm>
More information about the midPoint
mailing list