LWS/Solr: Configuring the Database Connector for SQL Server 2008 R2


So how do you configure the Database connector to connect and crawl a SQL Server 2008 R2 database? I’m glad you asked. We can do this the easy way or the hard way. I always opt for the easy way even if it is harder.

Parts List:

  • Windows Server 2008 R2
  • SQL Server 2008 R2 w/Management Studio
  • LucidWorks Search 2.7 installed on Windows Server 2008 R2 or any other support version of Windows
  • JDBC driver: SQL Server 2008R2 (download here. If the link is broken then search on Google, or Bing or Duck Duck Go, or some other search engine. Better yet: ask the NSA. They’ll know where to find it). The driver you must use is sqljdbc4.jar.

I am going to assume you are running this on Windows as there is no other way to run SQL Server. And, just because I knew you were wondering, these instructions will work on a Solr Cloud install as well. You will be copying the JDBC driver on the node that has the Collection Server running, but we are getting ahead of ourselves.

I do have a recommendation before we start: run a test program that proves you can connect to your SQL Server instance and can execute the SQL you want to execute. Don’t want to do that first? Even though I have the code for that program at the bottom of this post? And even though it uses a properties file so you can isolate what you need?

Your call.

The Short Version

  • Create a collection
  • Import the SQL Server JDBC Driver
  • Create a Database Connector
  • Start the crawl

The Long Version

Create a collection

This would be rather important since you can’t import a driver except at a collection level. Create one. Call it now-on-tap. C’mon. You’re using LWS. You know what to do.

Import the SQL Server JDBC Driver

This is where things get a little complicated. I don’t want you to do this. At least not the conventional way. Do this: copy the JDBC driver (%FOLDER_WHERE_YOU_UNZIPPED_THE_MSFT_JDBC_DRIVER%\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\sqljdbc4.jar) to %LWS_HOME%\data\lucid.jdbc\jdbc\now-on-tap. The now-on-tap directory might not exist. Please create it if it doesn’t exist, but in any case copy the JDBC driver into it. If you named your collection something else then use that name when you look for/create the directory. Refresh the admin JDBC Drivers UI page. The jar file should magically appear in the listing. Screen Shot 2014-03-28 at 3.05.34 PM While the driver can go in one of two places I have only told you about one of them (if you really want to know you can find the second location in our documentation). 3/28/14: The JDBC driver Import functionality is…shall we say…volatile for now. Do a manual copy like I asked you to. I cannot be held responsible for singed eyebrows.

Create a Database Connector

Click on the Status menu item on the menu bar at the top of the admin page. Press the large green button labeled New Data Source and select Database. Enter the following with the URL, Driver, Username, and Password matching your special set-up:

  • Name: nowontap
  • URL: jdbc:sqlserver://localhost;databasename=master
  • Driver [select from the drop down]: com.microsoft.sqlserver.jdbc.SQLServerDriver
  • Username: nowontap
  • Password: nowontap
  • SQL SELECT Statement: SELECT name as id, * from dbo.spt_values

Press Create. Screen Shot 2014-03-28 at 3.22.21 PM Before you start the crawl there are two things to point out:

  1. The dropdown might not list the driver class name for some other JDBC jar files you decide to use . That can happen. In the event where that occurs you have to find out what the class name of the driver is (for example, for SQLite the class name is org.sqlite.JDBC), select Other from the dropdown list above and manually enter the class name.
  2. Your SQL statement must have one field identified as the field for the index id field. The usual syntax for SQL would be to list the column name desired and use the AS syntax. For the SQL above I am using the name field as my id. Cheesy, but it works.

About the SQL above: I did not want to make anyone load up an example data set just for this so I decided to use one of the tables from the master database in SQL Server.

Start the crawl

Press Start Crawl. Notice the Crawl History at the bottom says the crawl is running. It is not lying. When the crawl completes click on Tools, run an empty search and revel in your handiwork. Screen Shot 2014-03-28 at 3.52.55 PM Screen Shot 2014-03-28 at 4.40.51 PM Screen Shot 2014-03-28 at 4.43.08 PM What? It didn’t work?

This Might Not Have Worked For You

How is that possible? Are there no cruelty laws?

The issue might be with your SQL Server set-up. Remember that test program I mentioned at the beginning of the post? The one I said would save your life (well, okay, maybe I didn’t say that)? The test program might have pointed out that you had an authentication problem.

Say what?

SQL Server 2008 R2 defaults to using Windows Authentication which the Microsoft JDBC driver does not support natively (what? The driver is supposed to support a special kind of authentication natively? Well, no, one wouldn’t thinks so, but that is how the Microsoft JDBC driver works). Can it support Windows Authentication? Of course. Simply include the path to the DLL supplied with the driver in your %PATH% variable either in the command line where you are running things or in the Environment Variables for your system (the DLL is located at %FOLDER_WHERE_YOU_UNZIPPED_THE_MSFT_JDBC_DRIVER%\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\auth\x64 or %FOLDER_WHERE_YOU_UNZIPPED_THE_MSFT_JDBC_DRIVER%\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\auth\x86).

The login I used in this example was a SQL Server Authentication login as I created a new user in the database instance I wanted to crawl. I had to tell SQL Server, for this particular database, to please use both Windows Authentication and SQL Server Authentication. How do you do that? In Microsoft SQL Server Management Studio right click on the database instance. Select Properties. When the Server Properties window opens select Security. Under Server Authentication select SQL Server and Windows Authentication mode.

Screen Shot 2014-03-28 at 4.14.13 PM

Press OK and you should be okay if you are using a login using SQL Server Authentication. If you insist on using Windows Authentication then make sure the DLL is in your %PATH% and you update the JDBC URL.

Update the URL? Yes. Add integratedSecurity=true to enable Windows Authentication. Your URL should look something like: jdbc:sqlserver://localhost;databasename=master;integratedSecurity=true

Now wait a minute! Are you saying that I can’t connect to SQL Server from a non-Windows install of LWS?

Oh, the horror. What you can do is connect to SQL Server from a non-Windows platform as long as you use SQL Server Authentication. As soon as you decide to use Windows Authentication you are restricted to running LWS on Windows as you need the ability to use the sqljdbc_auth.dll and you can only do that from Windows (let that be a lesson to you).

Things to Watch Out For

A million things can be misconfigured in SQL Server that could cause problems. I am just going to list some of the ones that I trip over on a regular basis. Transitive advice:

Don’t use Windows Authentication when you mean SQL Server Authentication.

By which I also mean Don’t use SQL Server Authentication when you really want Windows Authentication. You can set both in the Server properties, but that might be a security hole so I promise not to hunt you down if you do this in development, but don’t go to sleep if you’ve done this in production (yes, that’s my reflection in the mirror).

Test the JDBC URL with a sample program just to be sure

I know: that’s so 80’s. No school like old school. Here’s one now: jdbctest.properties

#
# jdbctest.properties
#
# The jdbctest program looks for this file by default.
#

jdbctest.jdbc.driver.classname=com.microsoft.sqlserver.jdbc.SQLServerDriver
#
# This is an example URL for Microsoft SQL Server using Windows Authentication
#
# jdbc:sqlserver://localhost;integratedSecurity=true;databasename=master
jdbctest.jdbc.url=jdbc:sqlserver://localhost;databasename=master
jdbctest.db.user=carlos
jdbctest.db.password=SQLServer123
jdbctest.sql.query=SELECT top 10 * from dbo.spt_values

JDBCTest.java

import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCTest {
	private static final String PROPERTIES_JDBC_DRIVER_CLASS_NAME = "jdbctest.jdbc.driver.classname";
	private static final String PROPERTIES_JDBC_URL = "jdbctest.jdbc.url";
	private static final String PROPERTIES_DB_USER_NAME = "jdbctest.db.user";
	private static final String PROPERTIES_PASSWORD = "jdbctest.db.password";
	private static final String PROPERTIES_SQL_QUERY = "jdbctest.sql.query";

	private String _driverClassName;
	private String _jdbcURL;
	private String _dbUser;
	private String _dbPassword;
	private String _sqlQuery;

	public static void main(String[] args) throws IOException {
		JDBCTest jdbcTest = new JDBCTest();

		System.out.println("Welcome to the Now On Tap JDBC test! Thank you for your support.");

		jdbcTest.readProperties();
		jdbcTest.connectAndQuery();

		System.out.println("Goodbye!");
	}

	private void connectAndQuery() {
		Connection conn = null;
		Statement stmt = null;
		try {
			System.out.println("Loading driver: " + _driverClassName);
			Class.forName(_driverClassName);

			System.out.println("Connecting to database...");
			conn = DriverManager.getConnection(_jdbcURL, _dbUser, _dbPassword);

			System.out.println("Creating statement...");
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(_sqlQuery);
			int fieldCount = rs.getMetaData().getColumnCount();
			String[] fieldName = new String[fieldCount];
			for (int i = 0; i < fieldName.length; i++) {
				fieldName[i] = rs.getMetaData().getColumnName(i + 1);
			}

			while (rs.next()) {
				for (int i = 0, j = 1; i < fieldCount; i++, j++) {
					String val = rs.getString(j);
					System.out.print(fieldName[i] + ": " + val + ", ");
				}
				System.out.println();
			}

			rs.close();
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// finally block used to close resources
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException se) {
				se.printStackTrace();
			}

			try {
				if (conn != null)
					conn.close();
			} catch (SQLException se) {
				se.printStackTrace();
			}// end finally try
		}// end try
	}

	private void readProperties() throws IOException {
		Properties properties = new Properties();

		FileReader reader = new FileReader("jdbctest.properties");
		properties.load(reader);

		_driverClassName = properties
				.getProperty(PROPERTIES_JDBC_DRIVER_CLASS_NAME);
		_jdbcURL = properties.getProperty(PROPERTIES_JDBC_URL);
		_dbUser = properties.getProperty(PROPERTIES_DB_USER_NAME);
		_dbPassword = properties.getProperty(PROPERTIES_PASSWORD);
		_sqlQuery = properties.getProperty(PROPERTIES_SQL_QUERY);
	}

}

Compile this and run it from the command line. The program looks for the properties file in the same directory in which it is running.

References

http://docs.lucidworks.com/display/help/Add+a+JDBC+Driver

Disclosures

Carlos Valcarcel is a full time employee of LucidWorks, but lives in New York as he prefers hurricanes to earthquakes. Having worked at IBM, Microsoft, and Fast Search and Transfer the only thing he is sure of is that the font editor he wrote on his Atari 800 was the coolest program he has ever written. While questions can be a drag he admits that answers will be harder to give without them. The cat isn’t real, but then neither are you. Enjoy your search responsibly.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s