[midPoint] Database table connector to MS SQL

[dmitri at asgardsecurity.eu]

‘Staff id’ – IS configured to allow NULL values as I see, could this be a problem?

I can ask the IT to change this behavior in SQL View query, although I don’t see any NULL value there in the data.

 �

Cheers,

Dmitry

 �

From: Stefano Belluomini | EI <stefano at everythinginfo.cloud> 
Sent: Wednesday, October 12, 2022 11:42 PM
To: midPoint General Discussion <midpoint at lists.evolveum.com>
Cc: dmitri at asgardsecurity.eu
Subject: Re: [midPoint] Database table connector to MS SQL

 �

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 <mailto:midpoint-bounces at lists.evolveum.com> > on behalf of dmitri--- via midPoint <midpoint at lists.evolveum.com <mailto:midpoint at lists.evolveum.com> >
Sent: Wednesday, October 12, 2022 12:54:45 AM
To: midpoint at lists.evolveum.com <mailto:midpoint at lists.evolveum.com>  <midpoint at lists.evolveum.com <mailto:midpoint at lists.evolveum.com> >
Cc: dmitri at asgardsecurity.eu <mailto:dmitri at asgardsecurity.eu>  <dmitri at asgardsecurity.eu <mailto: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 <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:algorithm>

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

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

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/20221013/459aefec/attachment-0001.htm>