<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
</head>
<body>
<div>
<div>
<div dir="ltr">
<div>Hi Dmitry,</div>
<div dir="ltr"><br>
</div>
<div dir="ltr">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.</div>
<div dir="ltr"><br>
</div>
<div dir="ltr">Regards,</div>
<div dir="ltr">Stefano </div>
</div>
</div>
<div id="ms-outlook-mobile-signature">
<div></div>
</div>
</div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b> dmitri@asgardsecurity.eu <dmitri@asgardsecurity.eu><br>
<b>Sent:</b> Thursday, October 13, 2022 6:41:53 PM<br>
<b>To:</b> Stefano Belluomini | EI <stefano@everythinginfo.cloud>; 'midPoint General Discussion' <midpoint@lists.evolveum.com><br>
<b>Subject:</b> RE: [midPoint] Database table connector to MS SQL</font>
<div> </div>
</div>
<style>
<!--
@font-face
{font-family:"Cambria Math"}
@font-face
{font-family:Calibri}
@font-face
{font-family:"Source Sans Pro"}
p.x_MsoNormal, li.x_MsoNormal, div.x_MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif}
a:link, span.x_MsoHyperlink
{color:#0563C1;
text-decoration:underline}
p.x_xmsonormal, li.x_xmsonormal, div.x_xmsonormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif}
span.x_EmailStyle23
{font-family:"Calibri",sans-serif;
color:windowtext}
.x_MsoChpDefault
{font-size:10.0pt}
@page WordSection1
{margin:70.85pt 70.85pt 70.85pt 70.85pt}
div.x_WordSection1
{}
-->
</style>
<div lang="EN-US" link="#0563C1" vlink="purple" style="word-wrap:break-word">
<div class="x_WordSection1">
<p class="x_MsoNormal">‘Staff id’ – IS configured to allow NULL values as I see, could this be a problem?</p>
<p class="x_MsoNormal">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.</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">Cheers,</p>
<p class="x_MsoNormal">Dmitry</p>
<p class="x_MsoNormal"> </p>
<div>
<div style="border:none; border-top:solid #E1E1E1 1.0pt; padding:3.0pt 0cm 0cm 0cm">
<p class="x_MsoNormal"><b>From:</b> Stefano Belluomini | EI <stefano@everythinginfo.cloud>
<br>
<b>Sent:</b> Wednesday, October 12, 2022 11:42 PM<br>
<b>To:</b> midPoint General Discussion <midpoint@lists.evolveum.com><br>
<b>Cc:</b> dmitri@asgardsecurity.eu<br>
<b>Subject:</b> Re: [midPoint] Database table connector to MS SQL</p>
</div>
</div>
<p class="x_MsoNormal"> </p>
<div>
<div>
<div>
<div>
<p class="x_MsoNormal">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.</p>
</div>
</div>
</div>
<div class="x_MsoNormal" align="center" style="text-align:center">
<hr size="2" width="98%" align="center">
</div>
<div id="x_divRplyFwdMsg">
<p class="x_MsoNormal"><b><span style="color:black">From:</span></b><span style="color:black"> midPoint <<a href="mailto:midpoint-bounces@lists.evolveum.com">midpoint-bounces@lists.evolveum.com</a>> on behalf of dmitri--- via midPoint <<a href="mailto:midpoint@lists.evolveum.com">midpoint@lists.evolveum.com</a>><br>
<b>Sent:</b> Wednesday, October 12, 2022 12:54:45 AM<br>
<b>To:</b> <a href="mailto:midpoint@lists.evolveum.com">midpoint@lists.evolveum.com</a> <<a href="mailto:midpoint@lists.evolveum.com">midpoint@lists.evolveum.com</a>><br>
<b>Cc:</b> <a href="mailto:dmitri@asgardsecurity.eu">dmitri@asgardsecurity.eu</a> <<a href="mailto:dmitri@asgardsecurity.eu">dmitri@asgardsecurity.eu</a>><br>
<b>Subject:</b> [midPoint] Database table connector to MS SQL</span> </p>
<div>
<p class="x_MsoNormal"> </p>
</div>
</div>
<div>
<div>
<p class="x_xmsonormal">Hello!</p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal">I have a MS SQL with View, accessible with SQL Management Studio, all ok.</p>
<p class="x_xmsonormal">Now I’ve used next configuration to setup the resource in Midpoint:</p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal"><connectorRef oid="88cd0194-1ace-4d17-a613-14edd35dc292" relation="org:default" type="c:ConnectorType"></p>
<p class="x_xmsonormal"> <!-- ConnId org.identityconnectors.databasetable.DatabaseTableConnector v1.4.9.1 --></p>
<p class="x_xmsonormal"></connectorRef></p>
<p class="x_xmsonormal"><connectorConfiguration xmlns:icfc=<a href="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" originalsrc="http://midpoint.evolveum.com/xml/ns/public/connector/icf-1/connector-schema-3" shash="FoHZRlns2FibBfYmOGNhBD1t7TiQ+HQKzELQiiaSyH5ul5Vlqz5sZ0eXitAqRa2PB8YwYSm25cNkxvxra3ytdPmXiOBqWwzpofa1byexjtuXQLZlosxZxeb0a+vmSxL03/2EP04lBOBlaPLyEhZgwBVfTzu5Xl37KWJtrtZTEmc=">http://midpoint.evolveum.com/xml/ns/public/connector/icf-1/connector-schema-3</a>></p>
<p class="x_xmsonormal"> <icfc:configurationProperties></p>
<p class="x_xmsonormal"> <icscdbtable:host>XXX.com</icscdbtable:host></p>
<p class="x_xmsonormal"> <icscdbtable:port>1433</icscdbtable:port></p>
<p class="x_xmsonormal"> <icscdbtable:user>XXX </icscdbtable:user></p>
<p class="x_xmsonormal"> <icscdbtable:password></p>
<p class="x_xmsonormal"> <t:encryptedData></p>
<p class="x_xmsonormal"> <t:encryptionMethod></p>
<p class="x_xmsonormal"> <t:algorithm xmlns=<a href="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" originalsrc="http://prism.evolveum.com/xml/ns/public/types-3" shash="AjrGE+3p5ng4NpXhf22yL7AQvG5vinrbmsvgex2MSeukj1SD9bnyBEczmTA4VyIhtUki2KPQP6+A4+hXQlJhx1XQe3z89pAGY3sYyFxgLqnPVyZA3ml69umP7XSuwos2NjA47Xjc47Zscc3cZnndakTAxwnQsO5au1GEAAvVMm0=">http://prism.evolveum.com/xml/ns/public/types-3</a>><a href="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" originalsrc="http://www.w3.org/2001/04/xmlenc#aes256-cbc%3C/t:algorithm" shash="SGzUuTBG0QLaO7GWBmHZnYf/+6K6LsBhYJ8HhPkoUb+jGHHwmfiHs8k1o78DuCmNKKkZLfs3hGnYTNakL1T2g8Qz+hBe+cnA3JKDRQp2gDIw7C1DhDPUKut0eZnINk6sLSxOOg542xVOJblvd81ObGeLcA34N7KvXtVDbbe7lf4=">http://www.w3.org/2001/04/xmlenc#aes256-cbc</t:algorithm</a>></p>
<p class="x_xmsonormal"> </t:encryptionMethod></p>
<p class="x_xmsonormal"> <t:keyInfo></p>
<p class="x_xmsonormal"> <t:keyName>XXXXX</t:keyName></p>
<p class="x_xmsonormal"> </t:keyInfo></p>
<p class="x_xmsonormal"> <t:cipherData></p>
<p class="x_xmsonormal"> <t:cipherValue>XXX</t:cipherValue></p>
<p class="x_xmsonormal"> </t:cipherData></p>
<p class="x_xmsonormal"> </t:encryptedData></p>
<p class="x_xmsonormal"> </icscdbtable:password></p>
<p class="x_xmsonormal"> <icscdbtable:database>XXXX</icscdbtable:database></p>
<p class="x_xmsonormal"> <icscdbtable:table>Profiles</icscdbtable:table></p>
<p class="x_xmsonormal"> <icscdbtable:keyColumn>Staff Id</icscdbtable:keyColumn></p>
<p class="x_xmsonormal"> <icscdbtable:jdbcDriver>com.microsoft.sqlserver.jdbc.SQLServerDriver</icscdbtable:jdbcDriver></p>
<p class="x_xmsonormal"> <icscdbtable:quoting>Single</icscdbtable:quoting></p>
<p class="x_xmsonormal"> <icscdbtable:jdbcUrlTemplate>jdbc:sqlserver://%h:%p;encrypt=false;databaseName=%d;</icscdbtable:jdbcUrlTemplate></p>
<p class="x_xmsonormal"> </icfc:configurationProperties></p>
<p class="x_xmsonormal"></connectorConfiguration></p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal">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:
</p>
<p class="x_xmsonormal" align="right" style="text-align:right; background:white">
<b><span style="font-size:10.5pt; font-family:"Source Sans Pro",sans-serif; color:#333333">Operation</span></b></p>
<p class="x_xmsonormal" style="margin-left:36.0pt; background:white"><b id="x_x_id6b9c"><span style="font-size:10.5pt; font-family:"Source Sans Pro",sans-serif; color:#333333">org.identityconnectors.framework.api.ConnectorFacade.search</span></b></p>
<p class="x_xmsonormal" align="right" style="margin-left:97.5pt; text-align:right; background:white">
<b><span style="font-size:10.5pt; font-family:"Source Sans Pro",sans-serif; color:#333333">Message</span></b></p>
<p class="x_xmsonormal" style="margin-left:36.0pt; background:white"><span style="font-size:10.5pt; font-family:"Source Sans Pro",sans-serif; color:#333333">Configuration error: SQL param name should be not null</span></p>
<p class="x_xmsonormal" align="right" style="margin-left:195.0pt; text-align:right; background:white">
<b><span style="font-size:10.5pt; font-family:"Source Sans Pro",sans-serif; color:#333333">Parameters</span></b></p>
<table class="x_MsoNormalTable" border="0" cellspacing="0" cellpadding="0" style="margin-left:36.0pt; border-collapse:collapse; box-sizing:border-box; border-spacing:0px">
<tbody>
<tr style="box-sizing:border-box">
<td valign="top" style="padding:0cm 0cm 0cm 0cm">
<p class="x_xmsonormal"><b>objectClass</b></p>
</td>
<td style="padding:0cm 15.0pt 0cm 3.75pt; box-sizing:border-box; white-space:pre-wrap">
<p class="x_xmsonormal" style="word-break:break-all">[ObjectClass: __ACCOUNT__]</p>
</td>
</tr>
</tbody>
</table>
<p class="x_xmsonormal" style="margin-left:36.0pt; background:white"><span style="font-size:10.5pt; font-family:"Source Sans Pro",sans-serif; color:#333333"> </span></p>
<table class="x_MsoNormalTable" border="0" cellspacing="0" cellpadding="0" style="margin-left:36.0pt; border-collapse:collapse; box-sizing:border-box; border-spacing:0px">
<tbody>
<tr>
<td style="padding:.75pt .75pt .75pt .75pt"></td>
</tr>
</tbody>
</table>
<p class="x_xmsonormal" align="right" style="margin-left:390.0pt; text-align:right; background:white">
<b><span style="font-size:10.5pt; font-family:"Source Sans Pro",sans-serif; color:#333333">Error</span></b></p>
<p class="x_xmsonormal" style="margin-left:36.0pt; background:white"><span style="font-size:10.5pt; font-family:"Source Sans Pro",sans-serif; color:#333333">Configuration error: SQL param name should be not null</span></p>
<p class="x_xmsonormal" style="margin-left:36.0pt; background:white"><span style="font-size:10.5pt; font-family:"Source Sans Pro",sans-serif; color:#333333"><a href="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" originalsrc="https://idm-play.tedo.ru/midpoint/result?87" shash="kTeydlytnPsbsQjaH5xgNa24ix5bhOQeGQTs5uNSDNY8A7loy4T2EewKsqAPX6MCUd8tYAj6VQM2Nm8G8cdwBbavEnHfpouYBq9jpfDmCRTIaz+4xjcNY/U1RxbY0aoLLgOansF1G2qYg6lOTBkm3PqICWBFw3/phtfeH73D94E="><span style="color:#3C8DBC; text-decoration:none">show</span></a></span></p>
<p class="x_xmsonormal" style="margin-left:36.0pt; background:white"><span style="font-size:8.5pt; font-family:"Courier New"; color:#333333">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)</span></p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal">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.</p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal">Any suggestions would be much appreciated, thank you!</p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal">Cheers,</p>
<p class="x_xmsonormal">Dmitry</p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal"> </p>
<p class="x_xmsonormal"> </p>
</div>
</div>
</div>
</div>
</div>
</body>
</html>