title: Custom processor + DBCPConnectionPool for SQL Server : driver jar not loaded
body: <p>I have created a controller service to connect to a test db.<a href="https://i.stack.imgur.com/tHFE2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/tHFE2.png" alt="enter image description here"></a></p>
  
  <p>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 ... ?</p>
  
  <p>I am unsure The pom for processor and the nar projects are as follows :</p>
  
  <p>processor pom.xml</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;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"&gt;
  &lt;modelVersion&gt;4.0.0&lt;/modelVersion&gt;
  &lt;parent&gt;
  &lt;groupId&gt;com.datalake&lt;/groupId&gt;
  &lt;artifactId&gt;CDCNiFi&lt;/artifactId&gt;
  &lt;version&gt;1.0-SNAPSHOT&lt;/version&gt;
  &lt;/parent&gt;
  &lt;artifactId&gt;nifi-NiFiCDCPoC-processors&lt;/artifactId&gt;
  &lt;packaging&gt;jar&lt;/packaging&gt;
  &lt;dependencies&gt;
  &lt;dependency&gt;
  &lt;groupId&gt;org.apache.nifi&lt;/groupId&gt;
  &lt;artifactId&gt;nifi-api&lt;/artifactId&gt;
  &lt;/dependency&gt;
  &lt;dependency&gt;
  &lt;groupId&gt;org.apache.nifi&lt;/groupId&gt;
  &lt;artifactId&gt;nifi-utils&lt;/artifactId&gt;
  &lt;/dependency&gt;
  &lt;dependency&gt;
  &lt;groupId&gt;org.apache.nifi&lt;/groupId&gt;
  &lt;artifactId&gt;nifi-dbcp-service-api&lt;/artifactId&gt;
  &lt;/dependency&gt;
  &lt;dependency&gt;
  &lt;groupId&gt;org.apache.nifi&lt;/groupId&gt;
  &lt;artifactId&gt;nifi-processor-utils&lt;/artifactId&gt;
  &lt;/dependency&gt;
  &lt;!-- Third-party --&gt;
  &lt;dependency&gt;
  &lt;groupId&gt;com.microsoft.sqlserver&lt;/groupId&gt;
  &lt;artifactId&gt;mssql-jdbc&lt;/artifactId&gt;
  &lt;version&gt;6.1.0.jre8&lt;/version&gt;
  &lt;/dependency&gt;
  &lt;dependency&gt;
  &lt;groupId&gt;org.codehaus.jackson&lt;/groupId&gt;
  &lt;artifactId&gt;jackson-mapper-asl&lt;/artifactId&gt;
  &lt;/dependency&gt;
  &lt;dependency&gt;
  &lt;groupId&gt;com.fasterxml.jackson.core&lt;/groupId&gt;
  &lt;artifactId&gt;jackson-databind&lt;/artifactId&gt;
  &lt;version&gt;2.8.7&lt;/version&gt;
  &lt;/dependency&gt;
  &lt;!-- Testing &amp; Cross-cutting concerns --&gt;
  &lt;dependency&gt;
  &lt;groupId&gt;junit&lt;/groupId&gt;
  &lt;artifactId&gt;junit&lt;/artifactId&gt;
  &lt;scope&gt;test&lt;/scope&gt;
  &lt;/dependency&gt;
  &lt;dependency&gt;
  &lt;groupId&gt;org.apache.nifi&lt;/groupId&gt;
  &lt;artifactId&gt;nifi-mock&lt;/artifactId&gt;
  &lt;scope&gt;test&lt;/scope&gt;
  &lt;/dependency&gt;
  &lt;dependency&gt;
  &lt;groupId&gt;org.slf4j&lt;/groupId&gt;
  &lt;artifactId&gt;slf4j-simple&lt;/artifactId&gt;
  &lt;scope&gt;test&lt;/scope&gt;
  &lt;/dependency&gt;
  &lt;/dependencies&gt;
  &lt;/project&gt;
  </code></pre>
  
  <p>nar pom.xml</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;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"&gt;
  &lt;modelVersion&gt;4.0.0&lt;/modelVersion&gt;
  &lt;parent&gt;
  &lt;groupId&gt;com.datalake&lt;/groupId&gt;
  &lt;artifactId&gt;CDCNiFi&lt;/artifactId&gt;
  &lt;version&gt;1.0-SNAPSHOT&lt;/version&gt;
  &lt;/parent&gt;
  &lt;artifactId&gt;nifi-NiFiCDCPoC-nar&lt;/artifactId&gt;
  &lt;version&gt;1.0-SNAPSHOT&lt;/version&gt;
  &lt;packaging&gt;nar&lt;/packaging&gt;
  &lt;properties&gt;
  &lt;maven.javadoc.skip&gt;true&lt;/maven.javadoc.skip&gt;
  &lt;source.skip&gt;true&lt;/source.skip&gt;
  &lt;/properties&gt;
  &lt;dependencies&gt;
  &lt;dependency&gt;
  &lt;groupId&gt;com.datalake&lt;/groupId&gt;
  &lt;artifactId&gt;nifi-NiFiCDCPoC-processors&lt;/artifactId&gt;
  &lt;version&gt;1.0-SNAPSHOT&lt;/version&gt;
  &lt;/dependency&gt;
  &lt;dependency&gt;
              &lt;groupId&gt;org.apache.nifi&lt;/groupId&gt;
              &lt;artifactId&gt;nifi-standard-services-api-nar&lt;/artifactId&gt;
              &lt;type&gt;nar&lt;/type&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
  &lt;groupId&gt;com.microsoft.sqlserver&lt;/groupId&gt;
  &lt;artifactId&gt;mssql-jdbc&lt;/artifactId&gt;
  &lt;version&gt;6.1.0.jre8&lt;/version&gt;
  &lt;scope&gt;runtime&lt;/scope&gt;
  &lt;/dependency&gt;
  &lt;/dependencies&gt;
  &lt;/project&gt;
  </code></pre>
  
  <p>The issues :</p>
  
  <p>The SQL Server JDBC jar is , probably, not loaded at runtime, the app.log has the following error :</p>
  
  <pre><code>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 &lt;configure logger template to pick the code location&gt;
  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
  </code></pre>
  
  <ul>
  <li><p>In the controller service, I do not see my custom processor in
  'Referencing Components'(it says 'no referencing components')</p></li>
  <li><p>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?)</p></li>
  </ul>
  
  <p>*****Edit-1*****
  As suggested by Mahendra, I did the following :</p>
  
  <ul>
  <li><p>Without any changes to the existing pom.xml files, added the dependency (sqljdbc4, 4.0) to both, the build and deployment succeeded but the jdbc driver error persisted</p></li>
  <li><p>The 'Database Driver Location(s)' kept as 'file:///'. Same error
  <a href="https://i.stack.imgur.com/x1YEn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/x1YEn.png" alt="enter image description here"></a></p></li>
  <li><p>Added the sqljdbc4.jar in NiFi/lib and the driver error vanished(getting other errors but those will be taken in a different thread). <strong>Note</strong> that the controller service still doesn't show the processor as 'referenced component'.</p></li>
  <li><p>The java version</p>
  
  <p>java -version
  java version "1.8.0_121"
  Java(TM) SE Runtime Environment (build 1.8.0_121-b13)
  Java HotSpot(TM) 64-Bit Server VM (build 25.121-b13, mixed mode)</p></li>
  </ul>
  
  <p>Isn't there a way to avoid adding the sqljdbc4.jar in lib, I mean third-party dependencies like fasterxml etc. are behaving properly, is this an issue with my configuration or some problem with the sql server maven repos. ?</p>