This topic explains how to configure LabKey Server to retrieve and display data from Microsoft SQL Server as an external data source.

Microsoft SQL Server can also be used as LabKey Server's primary data source.

The Microsoft SQL Server JDBC Driver

We strongly recommend using Microsoft's JDBC driver, which is included with LabKey Server Premium Editions as part of the BigIron module. Support for the jTDS driver was removed in 23.3.0.

Configure the MS SQL Server Data Source

Add a <Resource> element to your installations labkey.xml configuration file. Use the template below as a general starting point, replacing the words in capitals with their appropriate values.

If you will be connecting to more than one data source, be sure that all have unique name parameters, i.e. mssqlDataSource1, mssqlDataSource2, or similar. This value will be used whenever you connect an external schema to this datasource so it must be unambiguous.

<Resource name="jdbc/mssqlDataSource" 
  auth="Container"
  type="javax.sql.DataSource"
  username="USERNAME"
  password="PASSWORD"
  driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://SERVER_NAME:1433;databaseName=DATABASE_NAME;trustServerCertificate=true;applicationName=LabKey Server;"
  maxTotal="20"
  maxIdle="10" 
  maxWaitMillis="120000" 
  accessToUnderlyingConnectionAllowed="true"
  validationQuery="SELECT 1"
/>

Notes:

• In the url parameter, "trustServerCertificate=true" is needed if SQL Server is using a self-signed cert that Java hasn't been configured to trust. The applicationName isn't required but identifies the client to SQL Server to show in connection usage and other reports.
• If the port is not 1433 (the default), then remember to edit this part of the url parameter as well.
• The connection pool size (maxTotal) of 20 is a baseline recommendation. Learn more about pool sizes in this topic: Troubleshoot Server Installation and Configuration
• The maxWaitMillis parameter is provided to prevent server deadlocks. Waiting threads will time out when no connections are available rather than hang the server indefinitely.

Define a New Schema

Now define a new schema from the SQL Server data source. For details see Set Up an External Schema.

Related Topics

• External Schemas and Data Sources
• Supported Technologies