Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Nifi Connection to MSSQL server DB

Solved Go to solution

Nifi Connection to MSSQL server DB

Explorer

I am trying to build a DBCPConnectionPool that can connect to MSSQL server. I downloaded the jar file and gave the path in DBCPConnectionPool. Here is my configuration

Database Connection URLInfo--> jdbc:mysql://a5d3iwbrq1.cloudapp.net:3306/chakra Database Driver Class NameInfo--> /root/sqljdbc_4.0/enu/sqljdbc4.jar Database Driver Jar UrlInfo--> No value setDatabase UserInfo--> chakra PasswordInfo--> ******

however get an error when i enable this

2015-10-17 14:55:54,352 ERROR [pool-28-thread-5] o.a.n.c.s.StandardControllerServiceNode [DBCPConnectionPool[id=ee00cbf3-7dd3-4c32-93a6-9a06a8e5e6a7]] Failed to invoke @OnEnabled method due to {} org.apache.nifi.reporting.InitializationException: org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class '/root/sqljdbc_4.0/enu/sqljdbc4.jar

Please let me now how we can resolve this.

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Nifi Connection to MSSQL server DB

Explorer

Thanks guys for the response. I was able to modify the configuration for MS SQL server.

Database Connection URLInfo--> jdbc:sqlserver://a5d3iwbrq1.database.windows.net:1433;databaseName=chakra

Database Driver Class NameInfo--> com.microsoft.sqlserver.jdbc.SQLServerDriver

Database Driver Jar UrlInfo--> file:///usr/share/java/sqljdbc4.jar

setDatabase UserInfo--> chakra

PasswordInfo--> ******

Once you have the configuration set, you also need to use generateFlowFile or something to trigger the ExecuteSQL as Timer Driver schedule does not work on the version of Nifi that i was using.

Once this is done i ran into a bug where ExecuteSQL is not able to get the source table structure and gives a avro schema error

https://issues.apache.org/jira/browse/NIFI-1010

I am assuming that once the above bug is fixed we should be able to use ExecuteSQl for MS SQLServer DB.

6 REPLIES 6

Re: Nifi Connection to MSSQL server DB

Hey Chakra -

You need to provide the Java class name for "Database Driver Class NameInfo" and not the jar file path.

The jar file path goes in Database Driver Jar UrlInfo.

Highlighted

Re: Nifi Connection to MSSQL server DB

Mentor

i see your jdbc URL is for MySQL but driver is SQL server. Which do you need to access? Standard SQL server port is 1433.

Re: Nifi Connection to MSSQL server DB

Mentor

And url should not say jdbc:mysql it needs to be sql server specific, look that up, i dont remember exactly if its just jdbc:mssql

Re: Nifi Connection to MSSQL server DB

Explorer

Thanks guys for the response. I was able to modify the configuration for MS SQL server.

Database Connection URLInfo--> jdbc:sqlserver://a5d3iwbrq1.database.windows.net:1433;databaseName=chakra

Database Driver Class NameInfo--> com.microsoft.sqlserver.jdbc.SQLServerDriver

Database Driver Jar UrlInfo--> file:///usr/share/java/sqljdbc4.jar

setDatabase UserInfo--> chakra

PasswordInfo--> ******

Once you have the configuration set, you also need to use generateFlowFile or something to trigger the ExecuteSQL as Timer Driver schedule does not work on the version of Nifi that i was using.

Once this is done i ran into a bug where ExecuteSQL is not able to get the source table structure and gives a avro schema error

https://issues.apache.org/jira/browse/NIFI-1010

I am assuming that once the above bug is fixed we should be able to use ExecuteSQl for MS SQLServer DB.

Re: Nifi Connection to MSSQL server DB

New Contributor

This appears to work in the Nifi zipfile for windows available at https://nifi.apache.org/download.html.

However MSSQL does not work with the latest tarfile from Linux, nor with the latest sources due to the avro error above. To get it working in a Vagrant Linux Box I had to modify the JdbcCommon.java as per the suggestion in https://issues.apache.org/jira/browse/NIFI-1010 and recompile. It worked then.

Re: Nifi Connection to MSSQL server DB

New Contributor

Hi dears,

Today ı try to connect my nifi processor to SQL Server on a Windows Server. First I did not copy sqljdbc4.jar to lib folder, then configure processor like below:

97583-pastedimage.png

But, while enabling the connector, could not succeed.

After getting above error, i tried to copy jars to lib folder but, i can not achieve to bring online?

!!!Waiting your responses, asap!!!

Don't have an account?
Coming from Hortonworks? Activate your account here