[midPoint] Database table connector to MS SQL

Thu Oct 13 10:43:53 CEST 2022

Hi Dmitry,

The connector will complain if a reference column allows nulls, even if none of the cells have nulls. I’m confident that’s your problem.

Regards,
Stefano
________________________________
From: dmitri at asgardsecurity.eu <dmitri at asgardsecurity.eu>
Sent: Thursday, October 13, 2022 6:41:53 PM
To: Stefano Belluomini | EI <stefano at everythinginfo.cloud>; 'midPoint General Discussion' <midpoint at lists.evolveum.com>
Subject: RE: [midPoint] Database table connector to MS SQL

‘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%7C08471687c902499b40a908daacf6ccb3%7Ce066da37c938490cbf598264850113a8%7C1%7C0%7C638012473266378166%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=ldNI0Nv7DesLEiArwpb3TbEUqxKan67wkOxtw%2Fjz76o%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%7C08471687c902499b40a908daacf6ccb3%7Ce066da37c938490cbf598264850113a8%7C1%7C0%7C638012473266378166%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=kmshc6sXiF6kYdooDTVuzrGvckyQS7DxFFFOzTZ2WGw%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%7C08471687c902499b40a908daacf6ccb3%7Ce066da37c938490cbf598264850113a8%7C1%7C0%7C638012473266378166%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=F7HUW7WdFdiWqOahKRyfhMnSv97lu2DEeWEULVOsQvI%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%7C08471687c902499b40a908daacf6ccb3%7Ce066da37c938490cbf598264850113a8%7C1%7C0%7C638012473266378166%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=zG1%2BqMwmrABgs46eBQILICbXjpLxHJd%2FH6zqoMKIl8c%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/20221013/519b52dd/attachment-0001.htm>