This topic explains how to configure LabKey Server to retrieve and display data from Microsoft SQL Server as an
external data source. This topic assumes you have
reviewed the general guidance here and provides specific parameters and details for this database type.
Use Microsoft SQL Server JDBC Driver
LabKey only supports 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 early 2023.
Configure the Microsoft SQL Server Data Source
The new external data source must have a
unique JNDI name that you will use in naming the properties you will define. In the example on this page, we use "externalMSSQLDataSource", which will appear to users defining external schemas as "externalMSSQL". If you have more than one external data source, give each a unique name with the DataSource suffix ("firstExternalMSSQLDataSource", "secondExternalMSSQLDataSource", etc.). Learn more
here.
In the <LABKEY_HOME>/config/application.properties file, add a new section with the name of the datasource and the parameters you want to define. Provide your own <SERVER_NAME>, <DB_NAME>, <DB_USERNAME>, and <DB_PASSWORD> where indicated:
context.resources.jdbc.externalMSSQLDataSource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
context.resources.jdbc.externalMSSQLDataSource.url=jdbc:sqlserver://<SERVER_NAME>:1433;databaseName=<DB_NAME>;trustServerCertificate=true;applicationName=LabKey Server;
context.resources.jdbc.externalMSSQLDataSource.username=<DB_USERNAME>
context.resources.jdbc.externalMSSQLDataSource.password=<DB_PASSWORD>
There are additional properties you can set, as shown in the
template for the main "labkeyDataSource" in the application.properties file.
driverClassName
Use this as the
driverClassName:
com.microsoft.sqlserver.jdbc.SQLServerDriver
url
The
url property for SQL Server takes this form, substituting the name of your server and database:
jdbc:sqlserver://<SERVER_NAME>:1433;databaseName=<DB_NAME>;trustServerCertificate=true;applicationName=LabKey Server;
:
- If the port is not 1433 (the default), then remember to edit this part of the url parameter as well.
- In the url property, "trustServerCertificate=true" is needed if SQL Server is using a self-signed cert that Java hasn't been configured to trust.
- The "applicationName=LabKey Server" element isn't required but identifies the client to SQL Server to show in connection usage and other reports.
See the
Microsoft JDBC driver documentation for all supported URL properties.
validationQuery
Use "SELECT 1" as the validation query for Microsoft SQL Server. This is the default so does not need to be provided separately for this type of external data source:
Define a New External Schema
To define a new schema from this data source see
Set Up an External Schema.
Support for SQL Server Synonyms
SQL synonyms provide a way to connect to a database with alternate names/aliases for database objects such as tables, views, procedures, etc. Learn more here:
Related Topics