<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
</head>
<body>
<div dir="ltr">
<div></div>
<div>
<div>
<div dir="ltr">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.</div>
</div>
<div id="ms-outlook-mobile-signature">
<div></div>
</div>
</div>
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size: 11pt;"><b>From:</b> midPoint <midpoint-bounces@lists.evolveum.com> on behalf of dmitri--- via midPoint <midpoint@lists.evolveum.com><br>
<b>Sent:</b> Wednesday, October 12, 2022 12:54:45 AM<br>
<b>To:</b> midpoint@lists.evolveum.com <midpoint@lists.evolveum.com><br>
<b>Cc:</b> dmitri@asgardsecurity.eu <dmitri@asgardsecurity.eu><br>
<b>Subject:</b> [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}
span.x_EmailStyle17
{font-family:"Calibri",sans-serif;
color:windowtext}
.x_MsoChpDefault
{font-family:"Calibri",sans-serif}
@page WordSection1
{margin:70.85pt 70.85pt 70.85pt 70.85pt}
-->
</style>
<div lang="EN-US" style="word-wrap:break-word">
<div class="x_WordSection1">
<p class="x_MsoNormal">Hello!</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">I have a MS SQL with View, accessible with SQL Management Studio, all ok.</p>
<p class="x_MsoNormal">Now I’ve used next configuration to setup the resource in Midpoint:</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal"><connectorRef oid="88cd0194-1ace-4d17-a613-14edd35dc292" relation="org:default" type="c:ConnectorType"></p>
<p class="x_MsoNormal"> <!-- ConnId org.identityconnectors.databasetable.DatabaseTableConnector v1.4.9.1 --></p>
<p class="x_MsoNormal"></connectorRef></p>
<p class="x_MsoNormal"><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%7C4f8e68fcf141484f5bcf08daab9d3db5%7Ce066da37c938490cbf598264850113a8%7C1%7C0%7C638010989101198269%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=FY8S2syt%2FvZ%2Fha%2BDBkdOOtHatsyBAA3YsuKeiD7w71Q%3D&reserved=0" originalsrc="http://midpoint.evolveum.com/xml/ns/public/connector/icf-1/connector-schema-3" shash="YPgMze26qLPKYikAEEh4ie9N8DC5eDGjqhweUkkZT38FGn5GzF/aoNtFIkGFh77cHhYN7ZzqeuY5bWWDbkFhXYjeDVtfI5zMLSef5XfBv1/UIVxz00sd6zGXWusNVqIOKAoy6JXBpPgMXGTES+BS+K/5MqZV+9JA5yD/l/T3H9I=">http://midpoint.evolveum.com/xml/ns/public/connector/icf-1/connector-schema-3</a>></p>
<p class="x_MsoNormal"> <icfc:configurationProperties></p>
<p class="x_MsoNormal"> <icscdbtable:host>XXX.com</icscdbtable:host></p>
<p class="x_MsoNormal"> <icscdbtable:port>1433</icscdbtable:port></p>
<p class="x_MsoNormal"> <icscdbtable:user>XXX </icscdbtable:user></p>
<p class="x_MsoNormal"> <icscdbtable:password></p>
<p class="x_MsoNormal"> <t:encryptedData></p>
<p class="x_MsoNormal"> <t:encryptionMethod></p>
<p class="x_MsoNormal"> <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%7C4f8e68fcf141484f5bcf08daab9d3db5%7Ce066da37c938490cbf598264850113a8%7C1%7C0%7C638010989101198269%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=gfP5efXj0oZCjYp5%2FMjZEYE2tjNBLx7Js1uqBmzBqCM%3D&reserved=0" originalsrc="http://prism.evolveum.com/xml/ns/public/types-3" shash="DHRRuhMlxxMnza/kDNbSDyaBVKVMQFej88T0qW9LikHRcqJsR9AWWGhbLe5WR3BE8QDseBXWD4hVl/+a/AUkrQiuDeH3TcMVujfIgNUkrEXHPFqtpXK1rNiPf6qaBJ+cGrDY6Jtsj2eIsorf1oH6DEiQeXpWrxuyxoG0a2AYLGI=">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%7C4f8e68fcf141484f5bcf08daab9d3db5%7Ce066da37c938490cbf598264850113a8%7C1%7C0%7C638010989101198269%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=LP8kJt8ynLwmsgybOJSHbURnlftxE%2F6E33bZEhyrXBQ%3D&reserved=0" originalsrc="http://www.w3.org/2001/04/xmlenc#aes256-cbc%3C/t:algorithm" shash="VeMjKAg+pborxTEd9EtY86tfFGnO0A21HnX+quTfA5cypnykY4Yz9ruuzXGci4o3w9xAtKfwfWyKSIGtJ69nsjuiKZssBNRLF3pGPOmh0L9c8C55kRGEFuNj6I9Pbl9gf6xqZ0EL2hfvqIHCw6xEiElZhuqc5/ZbA8skuTH4tkY=">http://www.w3.org/2001/04/xmlenc#aes256-cbc</t:algorithm</a>></p>
<p class="x_MsoNormal"> </t:encryptionMethod></p>
<p class="x_MsoNormal"> <t:keyInfo></p>
<p class="x_MsoNormal"> <t:keyName>XXXXX</t:keyName></p>
<p class="x_MsoNormal"> </t:keyInfo></p>
<p class="x_MsoNormal"> <t:cipherData></p>
<p class="x_MsoNormal"> <t:cipherValue>XXX</t:cipherValue></p>
<p class="x_MsoNormal"> </t:cipherData></p>
<p class="x_MsoNormal"> </t:encryptedData></p>
<p class="x_MsoNormal"> </icscdbtable:password></p>
<p class="x_MsoNormal"> <icscdbtable:database>XXXX</icscdbtable:database></p>
<p class="x_MsoNormal"> <icscdbtable:table>Profiles</icscdbtable:table></p>
<p class="x_MsoNormal"> <icscdbtable:keyColumn>Staff Id</icscdbtable:keyColumn></p>
<p class="x_MsoNormal"> <icscdbtable:jdbcDriver>com.microsoft.sqlserver.jdbc.SQLServerDriver</icscdbtable:jdbcDriver></p>
<p class="x_MsoNormal"> <icscdbtable:quoting>Single</icscdbtable:quoting></p>
<p class="x_MsoNormal"> <icscdbtable:jdbcUrlTemplate>jdbc:sqlserver://%h:%p;encrypt=false;databaseName=%d;</icscdbtable:jdbcUrlTemplate></p>
<p class="x_MsoNormal"> </icfc:configurationProperties></p>
<p class="x_MsoNormal"></connectorConfiguration></p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">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_MsoNormal" align="right" style="text-align: right; background-color: white;">
<b><span style="font-size: 10.5pt; font-family: "Source Sans Pro", sans-serif; color: rgb(51, 51, 51);">Operation</span></b></p>
<p class="x_MsoNormal" style="margin-left: 36pt; background-color: white;"><b id="x_id6b9c"><span style="font-size: 10.5pt; font-family: "Source Sans Pro", sans-serif; color: rgb(51, 51, 51);">org.identityconnectors.framework.api.ConnectorFacade.search</span></b><span style="font-size: 10.5pt; font-family: "Source Sans Pro", sans-serif; color: rgb(51, 51, 51);"></span></p>
<p class="x_MsoNormal" align="right" style="margin-left: 97.5pt; text-align: right; background-color: white;">
<b><span style="font-size: 10.5pt; font-family: "Source Sans Pro", sans-serif; color: rgb(51, 51, 51);">Message</span></b></p>
<p class="x_MsoNormal" style="margin-left: 36pt; background-color: white;"><span style="font-size: 10.5pt; font-family: "Source Sans Pro", sans-serif; color: rgb(51, 51, 51);">Configuration error: SQL param name should be not null</span></p>
<p class="x_MsoNormal" align="right" style="margin-left: 195pt; text-align: right; background-color: white;">
<b><span style="font-size: 10.5pt; font-family: "Source Sans Pro", sans-serif; color: rgb(51, 51, 51);">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_MsoNormal"><b>objectClass</b><b></b></p>
</td>
<td style="padding:0cm 15.0pt 0cm 3.75pt; box-sizing:border-box; white-space:pre-wrap">
<p class="x_MsoNormal" style="word-break:break-all">[ObjectClass: __ACCOUNT__]</p>
</td>
</tr>
</tbody>
</table>
<p class="x_MsoNormal" style="margin-left: 36pt; background-color: white;"><span style="font-size: 10.5pt; font-family: "Source Sans Pro", sans-serif; display: none; color: rgb(51, 51, 51);"> </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_MsoNormal" align="right" style="margin-left: 390pt; text-align: right; background-color: white;">
<b><span style="font-size: 10.5pt; font-family: "Source Sans Pro", sans-serif; color: rgb(51, 51, 51);">Error</span></b><b><span style="font-size: 10.5pt; font-family: "Source Sans Pro", sans-serif; color: rgb(51, 51, 51);"></span></b></p>
<p class="x_MsoNormal" style="margin-left: 36pt; background-color: white;"><span style="font-size: 10.5pt; font-family: "Source Sans Pro", sans-serif; color: rgb(51, 51, 51);">Configuration error: SQL param name should be not null</span></p>
<p class="x_MsoNormal" style="margin-left: 36pt; background-color: white;"><span style="font-size: 10.5pt; font-family: "Source Sans Pro", sans-serif; color: rgb(51, 51, 51);"><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%7C4f8e68fcf141484f5bcf08daab9d3db5%7Ce066da37c938490cbf598264850113a8%7C1%7C0%7C638010989101198269%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=KfcBTNjHJ4enZTwTZK2404MsnPd%2B%2B0ep0AljlAtI%2B%2BU%3D&reserved=0" originalsrc="https://idm-play.tedo.ru/midpoint/result?87" shash="iWdzJda7JrOSJ+jehFbBbkUmyxkdN+wC+zn6ktam1nFO8k3+iVuWCq7dh7gs68YiMVZeBbCudpKZI8HE+pNFWPSL3EAUrh1EQ9xJ0fTDD+SDuCZjrT7q7dFKjOtd25k6cTqvtKqp2n3ua3N8A9ZqUQoJTn3Ef+E7ph8N6qf7VNc="><span style="text-decoration: none; color: rgb(60, 141, 188);">show</span></a></span></p>
<p class="x_MsoNormal" style="margin-left: 36pt; background-color: white;"><span style="font-size: 8.5pt; font-family: "Courier New"; color: rgb(51, 51, 51);">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_MsoNormal"> </p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">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_MsoNormal"> </p>
<p class="x_MsoNormal">Any suggestions would be much appreciated, thank you!</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">Cheers,</p>
<p class="x_MsoNormal">Dmitry</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal"> </p>
</div>
</div>
</div>
</body>
</html>