[midPoint] Database table connector to MS SQL
Stefano Belluomini | EI
stefano at everythinginfo.cloud
Wed Oct 12 22:41:53 CEST 2022
Is the staffId column in your database configured to allow NULL? I use the same connector and the column I’m using as the key does not allow nulls.
________________________________
From: midPoint <midpoint-bounces at lists.evolveum.com> on behalf of dmitri--- via midPoint <midpoint at lists.evolveum.com>
Sent: Wednesday, October 12, 2022 12:54:45 AM
To: midpoint at lists.evolveum.com <midpoint at lists.evolveum.com>
Cc: dmitri at asgardsecurity.eu <dmitri at asgardsecurity.eu>
Subject: [midPoint] Database table connector to MS SQL
Hello!
I have a MS SQL with View, accessible with SQL Management Studio, all ok.
Now I’ve used next configuration to setup the resource in Midpoint:
<connectorRef oid="88cd0194-1ace-4d17-a613-14edd35dc292" relation="org:default" type="c:ConnectorType">
<!-- ConnId org.identityconnectors.databasetable.DatabaseTableConnector v1.4.9.1 -->
</connectorRef>
<connectorConfiguration xmlns:icfc=http://midpoint.evolveum.com/xml/ns/public/connector/icf-1/connector-schema-3<https://aus01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmidpoint.evolveum.com%2Fxml%2Fns%2Fpublic%2Fconnector%2Ficf-1%2Fconnector-schema-3&data=05%7C01%7Cstefano%40everythinginfo.cloud%7C4f8e68fcf141484f5bcf08daab9d3db5%7Ce066da37c938490cbf598264850113a8%7C1%7C0%7C638010989101198269%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=FY8S2syt%2FvZ%2Fha%2BDBkdOOtHatsyBAA3YsuKeiD7w71Q%3D&reserved=0>>
<icfc:configurationProperties>
<icscdbtable:host>XXX.com</icscdbtable:host>
<icscdbtable:port>1433</icscdbtable:port>
<icscdbtable:user>XXX </icscdbtable:user>
<icscdbtable:password>
<t:encryptedData>
<t:encryptionMethod>
<t:algorithm xmlns=http://prism.evolveum.com/xml/ns/public/types-3<https://aus01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fprism.evolveum.com%2Fxml%2Fns%2Fpublic%2Ftypes-3&data=05%7C01%7Cstefano%40everythinginfo.cloud%7C4f8e68fcf141484f5bcf08daab9d3db5%7Ce066da37c938490cbf598264850113a8%7C1%7C0%7C638010989101198269%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=gfP5efXj0oZCjYp5%2FMjZEYE2tjNBLx7Js1uqBmzBqCM%3D&reserved=0>>http://www.w3.org/2001/04/xmlenc#aes256-cbc</t:algorithm<https://aus01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.w3.org%2F2001%2F04%2Fxmlenc%23aes256-cbc%253C%2Ft%3Aalgorithm&data=05%7C01%7Cstefano%40everythinginfo.cloud%7C4f8e68fcf141484f5bcf08daab9d3db5%7Ce066da37c938490cbf598264850113a8%7C1%7C0%7C638010989101198269%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=LP8kJt8ynLwmsgybOJSHbURnlftxE%2F6E33bZEhyrXBQ%3D&reserved=0>>
</t:encryptionMethod>
<t:keyInfo>
<t:keyName>XXXXX</t:keyName>
</t:keyInfo>
<t:cipherData>
<t:cipherValue>XXX</t:cipherValue>
</t:cipherData>
</t:encryptedData>
</icscdbtable:password>
<icscdbtable:database>XXXX</icscdbtable:database>
<icscdbtable:table>Profiles</icscdbtable:table>
<icscdbtable:keyColumn>Staff Id</icscdbtable:keyColumn>
<icscdbtable:jdbcDriver>com.microsoft.sqlserver.jdbc.SQLServerDriver</icscdbtable:jdbcDriver>
<icscdbtable:quoting>Single</icscdbtable:quoting>
<icscdbtable:jdbcUrlTemplate>jdbc:sqlserver://%h:%p;encrypt=false;databaseName=%d;</icscdbtable:jdbcUrlTemplate>
</icfc:configurationProperties>
</connectorConfiguration>
Test Connection is ok, schema generation is ok – everything seems to be fine, now when I try to browse objects from resource or run import task I get exception:
Operation
org.identityconnectors.framework.api.ConnectorFacade.search
Message
Configuration error: SQL param name should be not null
Parameters
objectClass
[ObjectClass: __ACCOUNT__]
Error
Configuration error: SQL param name should be not null
show<https://aus01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fidm-play.tedo.ru%2Fmidpoint%2Fresult%3F87&data=05%7C01%7Cstefano%40everythinginfo.cloud%7C4f8e68fcf141484f5bcf08daab9d3db5%7Ce066da37c938490cbf598264850113a8%7C1%7C0%7C638010989101198269%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=KfcBTNjHJ4enZTwTZK2404MsnPd%2B%2B0ep0AljlAtI%2B%2BU%3D&reserved=0>
com.evolveum.midpoint.util.exception.ConfigurationException: Configuration error: SQL param name should be not null at com.evolveum.midpoint.provisioning.ucf.impl.connid.ConnIdUtil.processConnIdException(ConnIdUtil.java:169) at com.evolveum.midpoint.provisioning.ucf.impl.connid.ConnIdUtil.processConnIdException(ConnIdUtil.java:87) at com.evolveum.midpoint.provisioning.ucf.impl.connid.SearchExecutor.executeConnIdSearch(SearchExecutor.java:236) at com.evolveum.midpoint.provisioning.ucf.impl.connid.SearchExecutor.execute(SearchExecutor.java:110) at com.evolveum.midpoint.provisioning.ucf.impl.connid.ConnectorInstanceConnIdImpl.search(ConnectorInstanceConnIdImpl.java:1787) at com.evolveum.midpoint.provisioning.impl.resourceobjects.ResourceObjectConverter.searchResourceObjects(ResourceObjectConverter.java:1382)
Timeout wise seems that Midpoint is getting the data – query takes around 27 seconds to run on server, more-or-less same time I wait till I get this error in Midpoint interface.
Any suggestions would be much appreciated, thank you!
Cheers,
Dmitry
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.evolveum.com/pipermail/midpoint/attachments/20221012/b2b36d58/attachment.htm>
More information about the midPoint
mailing list