<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Source Sans Pro";}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
p.xmsonormal, li.xmsonormal, div.xmsonormal
{mso-style-name:x_msonormal;
margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.EmailStyle23
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link="#0563C1" vlink=purple style='word-wrap:break-word'><div class=WordSection1><p class=MsoNormal>‘Staff id’ – IS configured to allow NULL values as I see, could this be a problem?<o:p></o:p></p><p class=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.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Cheers,<o:p></o:p></p><p class=MsoNormal>Dmitry<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=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<o:p></o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><div><div><p class=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.<o:p></o:p></p></div></div></div><div class=MsoNormal align=center style='text-align:center'><hr size=2 width="98%" align=center></div><div id=divRplyFwdMsg><p class=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> <o:p></o:p></p><div><p class=MsoNormal> <o:p></o:p></p></div></div><div><div><p class=xmsonormal>Hello!<o:p></o:p></p><p class=xmsonormal> <o:p></o:p></p><p class=xmsonormal>I have a MS SQL with View, accessible with SQL Management Studio, all ok.<o:p></o:p></p><p class=xmsonormal>Now I’ve used next configuration to setup the resource in Midpoint:<o:p></o:p></p><p class=xmsonormal> <o:p></o:p></p><p class=xmsonormal><connectorRef oid="88cd0194-1ace-4d17-a613-14edd35dc292" relation="org:default" type="c:ConnectorType"><o:p></o:p></p><p class=xmsonormal> <!-- ConnId org.identityconnectors.databasetable.DatabaseTableConnector v1.4.9.1 --><o:p></o:p></p><p class=xmsonormal></connectorRef><o:p></o:p></p><p class=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%7C4f8e68fcf141484f5bcf08daab9d3db5%7Ce066da37c938490cbf598264850113a8%7C1%7C0%7C638010989101198269%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=FY8S2syt%2FvZ%2Fha%2BDBkdOOtHatsyBAA3YsuKeiD7w71Q%3D&reserved=0">http://midpoint.evolveum.com/xml/ns/public/connector/icf-1/connector-schema-3</a>><o:p></o:p></p><p class=xmsonormal> <icfc:configurationProperties><o:p></o:p></p><p class=xmsonormal> <icscdbtable:host>XXX.com</icscdbtable:host><o:p></o:p></p><p class=xmsonormal> <icscdbtable:port>1433</icscdbtable:port><o:p></o:p></p><p class=xmsonormal> <icscdbtable:user>XXX </icscdbtable:user><o:p></o:p></p><p class=xmsonormal> <icscdbtable:password><o:p></o:p></p><p class=xmsonormal> <t:encryptedData><o:p></o:p></p><p class=xmsonormal> <t:encryptionMethod><o:p></o:p></p><p class=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%7C4f8e68fcf141484f5bcf08daab9d3db5%7Ce066da37c938490cbf598264850113a8%7C1%7C0%7C638010989101198269%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=gfP5efXj0oZCjYp5%2FMjZEYE2tjNBLx7Js1uqBmzBqCM%3D&reserved=0">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">http://www.w3.org/2001/04/xmlenc#aes256-cbc</t:algorithm</a>><o:p></o:p></p><p class=xmsonormal> </t:encryptionMethod><o:p></o:p></p><p class=xmsonormal> <t:keyInfo><o:p></o:p></p><p class=xmsonormal> <t:keyName>XXXXX</t:keyName><o:p></o:p></p><p class=xmsonormal> </t:keyInfo><o:p></o:p></p><p class=xmsonormal> <t:cipherData><o:p></o:p></p><p class=xmsonormal> <t:cipherValue>XXX</t:cipherValue><o:p></o:p></p><p class=xmsonormal> </t:cipherData><o:p></o:p></p><p class=xmsonormal> </t:encryptedData><o:p></o:p></p><p class=xmsonormal> </icscdbtable:password><o:p></o:p></p><p class=xmsonormal> <icscdbtable:database>XXXX</icscdbtable:database><o:p></o:p></p><p class=xmsonormal> <icscdbtable:table>Profiles</icscdbtable:table><o:p></o:p></p><p class=xmsonormal> <icscdbtable:keyColumn>Staff Id</icscdbtable:keyColumn><o:p></o:p></p><p class=xmsonormal> <icscdbtable:jdbcDriver>com.microsoft.sqlserver.jdbc.SQLServerDriver</icscdbtable:jdbcDriver><o:p></o:p></p><p class=xmsonormal> <icscdbtable:quoting>Single</icscdbtable:quoting><o:p></o:p></p><p class=xmsonormal> <icscdbtable:jdbcUrlTemplate>jdbc:sqlserver://%h:%p;encrypt=false;databaseName=%d;</icscdbtable:jdbcUrlTemplate><o:p></o:p></p><p class=xmsonormal> </icfc:configurationProperties><o:p></o:p></p><p class=xmsonormal></connectorConfiguration><o:p></o:p></p><p class=xmsonormal> <o:p></o:p></p><p class=xmsonormal> <o:p></o:p></p><p class=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: <o:p></o:p></p><p class=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><o:p></o:p></p><p class=xmsonormal style='margin-left:36.0pt;background:white'><b id="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><o:p></o:p></p><p class=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><o:p></o:p></p><p class=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><o:p></o:p></p><p class=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><o:p></o:p></p><table class=MsoNormalTable border=0 cellspacing=0 cellpadding=0 style='margin-left:36.0pt;border-collapse:collapse;box-sizing:border-box;border-spacing:0px'><tr style='box-sizing:border-box'><td valign=top style='padding:0cm 0cm 0cm 0cm'><p class=xmsonormal><b>objectClass</b><o:p></o:p></p></td><td style='padding:0cm 15.0pt 0cm 3.75pt;box-sizing:border-box;white-space:pre-wrap'><p class=xmsonormal style='word-break:break-all'>[ObjectClass: __ACCOUNT__]<o:p></o:p></p></td></tr></table><p class=xmsonormal style='margin-left:36.0pt;background:white'><span style='font-size:10.5pt;font-family:"Source Sans Pro",sans-serif;color:#333333'> </span><o:p></o:p></p><table class=MsoNormalTable border=0 cellspacing=0 cellpadding=0 style='margin-left:36.0pt;border-collapse:collapse;box-sizing:border-box;border-spacing:0px'><tr><td style='padding:.75pt .75pt .75pt .75pt'></td></tr></table><p class=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><o:p></o:p></p><p class=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><o:p></o:p></p><p class=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%7C4f8e68fcf141484f5bcf08daab9d3db5%7Ce066da37c938490cbf598264850113a8%7C1%7C0%7C638010989101198269%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=KfcBTNjHJ4enZTwTZK2404MsnPd%2B%2B0ep0AljlAtI%2B%2BU%3D&reserved=0"><span style='color:#3C8DBC;text-decoration:none'>show</span></a></span><o:p></o:p></p><p class=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><o:p></o:p></p><p class=xmsonormal> <o:p></o:p></p><p class=xmsonormal> <o:p></o:p></p><p class=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.<o:p></o:p></p><p class=xmsonormal> <o:p></o:p></p><p class=xmsonormal>Any suggestions would be much appreciated, thank you!<o:p></o:p></p><p class=xmsonormal> <o:p></o:p></p><p class=xmsonormal>Cheers,<o:p></o:p></p><p class=xmsonormal>Dmitry<o:p></o:p></p><p class=xmsonormal> <o:p></o:p></p><p class=xmsonormal> <o:p></o:p></p><p class=xmsonormal> <o:p></o:p></p></div></div></div></div></body></html>