- Set the SQL Server Browser service's startup type to "Automatic"
- Start the SQL Server Browser service
- Use SQL Server Configuration Manager to enable the TCP/IP protocol for the named instance
- Download the Microsoft JDBC Driver for SQL Server
- Extract the downloaded sqljdbc4.jar to %DATA_LOADER_HOME%\Java\lib\ext
- Create an org.apache.commons.dbcp.BasicDataSource bean in your \conf\database-conf.xml file by adapting the sample database-conf.xml bean for SQL Server 2012 Express
- Create a simple extract process to create records in SQL Server to test the configuration. Refer to this working set of database-conf.xml, process-conf.xml and an SDL file as a guide.
Additional Info
Along the way you may encounter numerous errors. Below are some of the ones I encountered and how they were resolved.
FATAL [main] process.ProcessRunner topLevelError (ProcessRunner.java:238) - Unable to run process sqlServerApplicationExtractjava.lang.RuntimeException: com.salesforce.dataloader.exception.DataAccessObjectInitializationException: Database error encountered during connecting for database configuration: insertApplication. Sql error: Cannot load JDBC driver class 'com.microsoft.sqlserver.jdbc.SQLServerDriver'.
- Solution: Extract the downloaded sqljdbc4.jar to %DATA_LOADER_HOME%\Java\lib\ext
Caused by: java.lang.UnsupportedOperationException: Java Runtime Environment (JRE) version 1.6 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.
- Solution: Delete sqljdbc.jar from %DATA_LOADER_HOME%\Java\lib\ext
- In my case, I had extracted both sqljdbc.jar and sqljdbc4.jar to the \Java\lib\ext folder
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
- Solution: Edit the connection string to specify a named instance, "localhost\SQLEXPRESS" instead of just specifying the server "localhost"
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the host IGNEOUS, named instance sqlexpress failed. Error: "java.net.SocketTimeoutException: Receive timed out". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434. For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host.
- Solution: Start the SQL Server Browser service
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (The server sqlexpress is not configured to listen with TCP/IP.)
- Solution: Use SQL Server Configuration Manager to enable the TCP/IP protocol for the named instance
