question Custom processor + DBCPConnectionPool for SQL Server : driver jar not loaded in Support Questions

question Custom processor + DBCPConnectionPool for SQL Server : driver jar not loaded in Support Questions https://community.cloudera.com/t5/Support-Questions/Custom-processor-DBCPConnectionPool-for-SQL-Server-driver/m-p/149489#M112007 I have created a controller service to connect to a test db.<img src="https://community.cloudera.com/t5/image/serverpage/image-id/21267iB159F850840EFBA1/image-size/medium?v=v2&px=400" role="button" title="13450-test-dbcp.png" alt="13450-test-dbcp.png" />I have a custom processor that reads data from SQL Server, the mock tests, the build and the deployment to NiFi succeed. The processor runs into error, maybe the nar dependency scope is at fault or ... ?I am unsure The pom for processor and the nar projects are as follows :<PRE><?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>com.datalake</groupId> <artifactId>CDCNiFi</artifactId> <version>1.0-SNAPSHOT</version> </parent> <artifactId>nifi-NiFiCDCPoC-processors</artifactId> <packaging>jar</packaging> <dependencies> <dependency> <groupId>org.apache.nifi</groupId> <artifactId>nifi-api</artifactId> </dependency> <dependency> <groupId>org.apache.nifi</groupId> <artifactId>nifi-utils</artifactId> </dependency> <dependency> <groupId>org.apache.nifi</groupId> <artifactId>nifi-dbcp-service-api</artifactId> </dependency> <dependency> <groupId>org.apache.nifi</groupId> <artifactId>nifi-processor-utils</artifactId> </dependency>  <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>mssql-jdbc</artifactId> <version>6.1.0.jre8</version> </dependency> <dependency> <groupId>org.codehaus.jackson</groupId> <artifactId>jackson-mapper-asl</artifactId> </dependency> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-databind</artifactId> <version>2.8.7</version> </dependency>  <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.apache.nifi</groupId> <artifactId>nifi-mock</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-simple</artifactId> <scope>test</scope> </dependency> </dependencies> </project></PRE><PRE><?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>com.datalake</groupId> <artifactId>CDCNiFi</artifactId> <version>1.0-SNAPSHOT</version> </parent> <artifactId>nifi-NiFiCDCPoC-nar</artifactId> <version>1.0-SNAPSHOT</version> <packaging>nar</packaging> <properties> <maven.javadoc.skip>true</maven.javadoc.skip> <source.skip>true</source.skip> </properties> <dependencies> <dependency> <groupId>com.datalake</groupId> <artifactId>nifi-NiFiCDCPoC-processors</artifactId> <version>1.0-SNAPSHOT</version> </dependency> <dependency> <groupId>org.apache.nifi</groupId> <artifactId>nifi-standard-services-api-nar</artifactId> <type>nar</type> </dependency> <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>mssql-jdbc</artifactId> <version>6.1.0.jre8</version> <scope>runtime</scope> </dependency> </dependencies> </project></PRE>The issues :<UL><LI>The SQL Server JDBC jar is , probably, not loaded at runtime, the app.log has the following error :</LI></UL><PRE>2017-03-09 15:04:06,074 ERROR [Timer-Driven Process Thread-1] c.s.d.processors.SQLServerCDCProcessor SQLServerCDCProcessor[id=ad9de403-015a-1000-2b40-7efbfdb049b1] Process or SQL exception in <configure logger template to pick the code location> 2017-03-09 15:04:06,080 ERROR [Timer-Driven Process Thread-1] c.s.d.processors.SQLServerCDCProcessor org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class 'com.microsoft.sqlserver.jdbc.SQLServerDriver' at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:252) ~[na:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_71] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_71] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_71] at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_71] at org.apache.nifi.controller.service.StandardControllerServiceProvider$1.invoke(StandardControllerServiceProvider.java:177) ~[na:na] at com.sun.proxy.$Proxy89.getConnection(Unknown Source) ~[na:na] at com.datalake.processors.SQLServerCDCProcessor.onTrigger(SQLServerCDCProcessor.java:244) ~[nifi-NiFiCDCPoC-processors-1.0-SNAPSHOT.jar:1.0-SNAPSHOT] at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) [nifi-api-1.1.1.jar:1.1.1] at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1099) [nifi-framework-core-1.1.1.jar:1.1.1] at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-1.1.1.jar:1.1.1] at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-1.1.1.jar:1.1.1] at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132) [nifi-framework-core-1.1.1.jar:1.1.1] at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_71] at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [na:1.8.0_71] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_71] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [na:1.8.0_71] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_71] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_71] at java.lang.Thread.run(Thread.java:745) [na:1.8.0_71] Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class 'com.microsoft.sqlserver.jdbc.SQLServerDriver' at org.apache.commons.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1429) ~[na:na] at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371) ~[na:na] at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044) ~[na:na] at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:249) ~[na:na] ... 19 common frames omitted Caused by: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver at java.net.URLClassLoader.findClass(URLClassLoader.java:381) ~[na:1.8.0_71] at org.apache.nifi.nar.InstanceClassLoader.findClass(InstanceClassLoader.java:117) ~[nifi-nar-utils-1.1.1.jar:1.1.1] at java.lang.ClassLoader.loadClass(ClassLoader.java:424) ~[na:1.8.0_71] at org.apache.nifi.nar.InstanceClassLoader.loadClass(InstanceClassLoader.java:98) ~[nifi-nar-utils-1.1.1.jar:1.1.1] at org.apache.nifi.nar.InstanceClassLoader.loadClass(InstanceClassLoader.java:82) ~[nifi-nar-utils-1.1.1.jar:1.1.1] at org.apache.commons.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1420) ~[na:na] ... 22 common frames omitted</PRE><UL><LI>In the controller service, I do not see my custom processor in 'Referencing Components'(it says 'no referencing components')</LI><LI>Right now, I have hard-coded the 'Database Driver Location(s)' but in production, I would like the controller to pick up that from the classpath(somehow, is it possible?)</LI></UL> Sun, 18 Aug 2019 13:05:58 GMT kaliyugantagoni 2019-08-18T13:05:58Z