Blog

DBFit and SQL Server and JDBC: Missing JDBC Driver.

Free copy of SQL Best Practices?

Get your copy of SQL Best Practices and be the database developer that has rocket fuel in his veins.
Even better? These Best Practices are written in an easy to understand format so your whole team will quickly want to adopt them.
Get SQL Best Practices

If you are using the java version of DBFit and you want to connect to SQL Server you may start to think it can't be done. Fear not! It is possible! I'll summarize the required steps as well as all the error messages I received along the way.

Running through these steps will enable you to connect to a sql server named instance using integrated security.

(If you've already got this under control then go ahead and Create Your First Test Using SQL Server and DBFit!)

Step 1: Download the JDBC SQL Server Driver

You need to download the JDBC SQL Server Driver. You can get it at Microsoft JDBC Drivers for SQL Server.

The DBFit FAQ includes the following info:

Deploy the JAR in the same folder as dbfit-XXX.jar. If you specify the full JDBC connection string explicitly, use Microsoft’s driver in JDBC URL. DbFit does not support 3rd party SQL Server drivers at the moment.

To be more specific this means that once you've downloaded the JDBC driver and unzipped them, find the file called sqljdbc4.jar and copy it into the DBFit directory called "lib" (..\DBFit\lib\)

If it's missing you'll see a mess of an error message like this:

dbfit.SqlServerTest

java.lang.Error: Cannot load SqlServer database driver com.microsoft.sqlserver.jdbc.SQLServerDriver. Is the JDBC driver on the classpath?
	at dbfit.api.DbEnvironmentFactory$EnvironmentDescriptor.checkDriver(DbEnvironmentFactory.java:45)
	at dbfit.api.DbEnvironmentFactory$EnvironmentDescriptor.createEnvironmentInstance(DbEnvironmentFactory.java:60)
	at dbfit.api.DbEnvironmentFactory.createEnvironmentInstance(DbEnvironmentFactory.java:102)
	at dbfit.api.DbEnvironmentFactory.newEnvironmentInstance(DbEnvironmentFactory.java:106)
	at dbfit.SqlServerTest.(SqlServerTest.java:5)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at java.lang.Class.newInstance(Unknown Source)
	at fit.FixtureClass.newInstance(FixtureClass.java:24)
	at fit.FixtureLoader.instantiateFixture(FixtureLoader.java:62)
	at fit.FixtureLoader.instantiateFirstValidFixtureClass(FixtureLoader.java:84)
	at fit.FixtureLoader.disgraceThenLoad(FixtureLoader.java:44)
	at fit.Fixture.loadFixture(Fixture.java:142)
	at fit.Fixture.getLinkedFixtureWithArgs(Fixture.java:134)
	at fit.Fixture.doTables(Fixture.java:79)
	at fit.FitServer.process(FitServer.java:81)
	at fit.FitServer.run(FitServer.java:56)
	at fit.FitServer.main(FitServer.java:41)
Caused by: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
	at java.net.URLClassLoader.findClass(Unknown Source)
	at java.lang.ClassLoader.loadClass(Unknown Source)
	at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
	at java.lang.ClassLoader.loadClass(Unknown Source)
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(Unknown Source)
	at dbfit.api.DbEnvironmentFactory$EnvironmentDescriptor.checkDriver(DbEnvironmentFactory.java:43)
	... 19 more

Step 2: Make sure SQL Server Browser is running

If you are connecting to a named instance of SQL Server then the next step is to make sure that SQL Server Browser service is running. You should already have it installed, just go to Services (press the Windows Button+R and type services.msc). Right click on SQL Server Browser  and select "Start".

If the SQL Server Browser Service is not running you'll see the following message:

Query

java.lang.IllegalArgumentException: No open connection to a database is available. Make sure your database is running and that you have connected before performing any queries.
	at dbfit.api.AbstractDbEnvironment.checkConnectionValid(AbstractDbEnvironment.java:237)
	at dbfit.api.AbstractDbEnvironment.getConnection(AbstractDbEnvironment.java:162)
	at dbfit.api.AbstractDbEnvironment.createStatementWithBoundFixtureSymbols(AbstractDbEnvironment.java:108)
	at dbfit.fixture.Query.getDataTable(Query.java:43)
	at dbfit.fixture.RowSetFixture.doRows(RowSetFixture.java:92)
	at fit.Fixture.doTable(Fixture.java:156)
	at fitlibrary.traverse.AlienTraverseHandler.doTable(AlienTraverseHandler.java:21)
	at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretWholeTable(DoTraverseInterpreter.java:104)
	at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretWholeTable(DoTraverseInterpreter.java:89)
	at fitlibrary.DoFixture.interpretWholeTable(DoFixture.java:73)
	at fitlibrary.suite.InFlowPageRunner.run(InFlowPageRunner.java:27)
	at fitlibrary.DoFixture.interpretTables(DoFixture.java:42)
	at dbfit.DatabaseTest.interpretTables(DatabaseTest.java:26)
	at fit.Fixture.doTables(Fixture.java:81)
	at fit.FitServer.process(FitServer.java:81)
	at fit.FitServer.run(FitServer.java:56)
	at fit.FitServer.main(FitServer.java:41)

Step 3: add the sqljdbc_auth.dll

If like most of us you're using integrated security with SQL Server you'll need to add the sqljdbc_auth.dll into your java directory. Go back to the  your sqljdbc_6.0 directory and track down sqljdbc_auth.dll. Mine was in the following location: ..\sqljdbc_6.0\enu\auth\x86 . Copy sqljdbc_auth.dll into your java bin directory. The path for mine was C:\Program Files (x86)\Java\jre1.8.0_91\bin.

If this file is missing you'll get the error message below. I managed to crack this one with the help of a google groups post by Yavor Nikolov where someone else was reporting the same issue.

Connect

java.lang.UnsatisfiedLinkError: no sqljdbc_auth in java.library.path
	at java.lang.ClassLoader.loadLibrary(Unknown Source)
	at java.lang.Runtime.loadLibrary0(Unknown Source)
	at java.lang.System.loadLibrary(Unknown Source)
	at com.microsoft.sqlserver.jdbc.AuthenticationJNI.(AuthenticationJNI.java:35)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2229)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2220)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1326)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at dbfit.api.AbstractDbEnvironment.connect(AbstractDbEnvironment.java:51)
	at dbfit.environment.SqlServerEnvironment.connect(SqlServerEnvironment.java:53)
	at dbfit.api.AbstractDbEnvironment.connect(AbstractDbEnvironment.java:57)
	at dbfit.DatabaseTest.connect(DatabaseTest.java:52)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at fitlibrary.closure.MethodClosure.invoke(MethodClosure.java:35)
	at fitlibrary.closure.MethodClosure.invokeTyped(MethodClosure.java:28)
	at fitlibrary.closure.CalledMethodTarget.invokeTyped(CalledMethodTarget.java:77)
	at fitlibrary.closure.CalledMethodTarget.invokeTyped(CalledMethodTarget.java:95)
	at fitlibrary.closure.CalledMethodTarget.invokeAndWrap(CalledMethodTarget.java:358)
	at fitlibrary.traverse.workflow.caller.ActionCaller.run(ActionCaller.java:37)
	at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretRow(DoTraverseInterpreter.java:176)
	at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretWholeTable(DoTraverseInterpreter.java:96)
	at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretWholeTable(DoTraverseInterpreter.java:89)
	at fitlibrary.DoFixture.interpretWholeTable(DoFixture.java:73)
	at fitlibrary.suite.InFlowPageRunner.run(InFlowPageRunner.java:27)
	at fitlibrary.DoFixture.interpretTables(DoFixture.java:42)
	at dbfit.DatabaseTest.interpretTables(DatabaseTest.java:26)
	at fit.Fixture.doTables(Fixture.java:81)
	at fit.FitServer.process(FitServer.java:81)
	at fit.FitServer.run(FitServer.java:56)
	at fit.FitServer.main(FitServer.java:41)


Test It!

Once these three steps were complete I was able to successfully connect to a named instance of SQL Server and run a quick test query. Hooray! (Need help with your first test? Check out this little DBFit tutorial on Creating Your First DBFit Test Using SQL Server)

DBFit SQLServer Java and JDBC

Other Errors: Java Runtime Environment (JRE) version 1.8 is not supported by this driver. Use the sqljdbc4.jar

Whoops. I had copied all the sqljdbc jar files into the lib directory. The only one that is needed is sqljdbc4.jar. You can delete sqljdbc.jar, sqljdbc41.jar, sqljdbc42.jar from DBFit\lib\ . Leave only sqljdbc4.jar.

java.lang.UnsupportedOperationException: Java Runtime Environment (JRE) version 1.8 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.(SQLServerConnection.java:304)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1011)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at dbfit.api.AbstractDbEnvironment.connect(AbstractDbEnvironment.java:51)
	at dbfit.environment.SqlServerEnvironment.connect(SqlServerEnvironment.java:53)
	at dbfit.api.AbstractDbEnvironment.connect(AbstractDbEnvironment.java:57)
	at dbfit.DatabaseTest.connect(DatabaseTest.java:52)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at fitlibrary.closure.MethodClosure.invoke(MethodClosure.java:35)
	at fitlibrary.closure.MethodClosure.invokeTyped(MethodClosure.java:28)
	at fitlibrary.closure.CalledMethodTarget.invokeTyped(CalledMethodTarget.java:77)
	at fitlibrary.closure.CalledMethodTarget.invokeTyped(CalledMethodTarget.java:95)
	at fitlibrary.closure.CalledMethodTarget.invokeAndWrap(CalledMethodTarget.java:358)
	at fitlibrary.traverse.workflow.caller.ActionCaller.run(ActionCaller.java:37)
	at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretRow(DoTraverseInterpreter.java:176)
	at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretWholeTable(DoTraverseInterpreter.java:96)
	at fitlibrary.traverse.workflow.DoTraverseInterpreter.interpretWholeTable(DoTraverseInterpreter.java:89)
	at fitlibrary.DoFixture.interpretWholeTable(DoFixture.java:73)
	at fitlibrary.suite.InFlowPageRunner.run(InFlowPageRunner.java:27)
	at fitlibrary.DoFixture.interpretTables(DoFixture.java:42)
	at dbfit.DatabaseTest.interpretTables(DatabaseTest.java:26)
	at fit.Fixture.doTables(Fixture.java:81)
	at fit.FitServer.process(FitServer.java:81)
	at fit.FitServer.run(FitServer.java:56)
	at fit.FitServer.main(FitServer.java:41)


Tags: , ,

No comments yet.

Leave a Reply