As part of
installing LabKey, you need to install a database server. With a Premium Edition of LabKey Server, you can use either PostgreSQL or Microsoft SQL Server as the primary database. This topic covers the process of setting up a Windows machine to use Microsoft SQL Server as the primary database. Information about other environments is provided
below.
Install and Configure Microsoft SQL Server
For current supported versions of Microsoft SQL Server, see
Supported Technologies.
Basic Installation Topics:
Additional Topics:
Install Microsoft SQL Server
If you already have a licensed version of Microsoft SQL Server in a
supported version, follow the installation instructions noting the requirements outlined for installing an express edition below.
If you don't have a licensed version of Microsoft SQL Server, you can download a free Express Edition. Note that the Express Edition has database size limitations that generally make it inappropriate for production deployments. You can also use the Developer Edition; details may differ slightly from instructions in this topic.
Configure During Installation
During installation, configure Microsoft SQL Server to accept both
Windows Authentication and SQL Server Authentication, ("Mixed Mode"), and specify a user name and password for the administrative account.
- Select the Custom installation option, choose the download location, then click Install to begin.
- Once the SQL Server Installation Center wizard begins, choose New SQL Server installation.
- Accept the license terms and click Next.
- In the SQL Server 2019 Setup wizard, proceed through the steps accepting defaults until the Database Engine Configuration step:
- Choose Mixed Mode (SQL Server authentication and Windows authentication).
- Keep track of the user name and password; LabKey Server uses it to authenticate to SQL Server. It must be provided in plaintext in the application.properties (or on a development machine, in your mssql.properties file).
- Complete the wizard.
Configure TCP/IP
After you've installed SQL Server, you'll need to configure it to use TCP/IP. Follow these steps:
- Launch the SQL Server Configuration Manager.
- Under the SQL Server Network Configuration node, select Protocols for <servername>.
- In the right pane, right-click on TCP/IP and choose Enable.
- Right-click on TCP/IP and choose Properties.
- Switch to the IP Addresses tab.
- Scroll down to the IPAll section, clear the value next to TCP Dynamic Ports and set the value for TCP Port to 1433 and click OK. By default, SQL Server will choose a random port number each time it starts, but the JDBC driver expects SQL Server to be listening on port 1433.
- Click OK
- Restart the service by selecting the SQL Server Services node in the left pane, selecting SQL Server <edition name> in the right pane, and choosing Restart from the Action menu (or use the Restart button on the toolbar).
SQL Server Management Studio
Download the SQL Server Management Studio graphical database management tool.
- Navigate to: Download SQL Server Management Studio
- Click the download link to obtain the latest general availability (GA) version of SQL Server Management Studio
- Run the downloaded .exe file.
- Use Windows Update to install the latest service packs.
Set Up a Login
You may want to set up a new login (in addition to the "sa" system administrator) for LabKey Server to use to connect to SQL Server:
- Run SQL Server Management Studio.
- Connect to the database.
- Under Security > Logins, add a new login, using SQL Server authentication.
- Enter the user name and password.
- Use this password to configure the data source below.
LabKey Application Properties
Edit the
<LABKEY_HOME>/config/application.properties file to configure the "labkeyDataSource" values appropriate for Microsoft SQL Server, available with Premium Editions of LabKey Server.
Update the lines in the default template to provide the Microsoft SQL Server values for these lines:
context.resources.jdbc.labkeyDataSource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
context.resources.jdbc.labkeyDataSource.url=jdbc:sqlserver://SERVER_NAME:1433;databaseName=DATABASE_NAME;trustServerCertificate=true;applicationName=LabKey Server;
On other lines in this primary data source section, you still need to provide the username and password and set any other necessary properties appropriately.
driverClassName
Use "com.microsoft.sqlserver.jdbc.SQLServerDriver".
See below if you have been using a jTDS driver (i.e. net.sourceforge.jtds.jdbc.Driver) in a past configuration.
url
Substitute the SERVER_NAME and DATABASE_NAME in the default value shown above:
jdbc:sqlserver://SERVER_NAME:1433;databaseName=DATABASE_NAME;trustServerCertificate=true;applicationName=LabKey Server;
Notes:
- If your SQL Server is not using port 1433 (the default), edit that part of the url property value.
- 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.
For production environments, adding the certificate to a trust store enables validation. See the
driver documentation for details on these configuration options.
Additional Topics
Install CLR Functions (GROUP_CONCAT)
When setting up SQL Server, you may need to separately install the GROUP_CONCAT
Common Language Runtime (CLR) function. For details see
GROUP_CONCAT Install.
You may also need to install the PremiumStats CLR functions separately. For details see
PremiumStats Install.
EHR users may also need to install LDKNaturalize, following similar methods.
SQL Server Synonyms
LabKey Server supports the use of
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. The alternate names form a layer of abstraction between LabKey Server and the underlying database, providing the following benefits:
- Easier integration with external databases. Naming differences between the client (LabKey Server) and the resource (the database) are no longer a barrier to connection.
- Insulation from changes in the underlying database. If the names of database resources change, you can maintain the connection without changing core client code.
- Hides the underlying database. You can interact with the database without knowing its exact underlying structure.
Installation on Other Platforms
Linux Deployment
Microsoft distributes a native Linux version. Obtain it and follow the documentation available here:
Docker Deployment on Mac/OSX
1. Install
Docker.
2. Load the SQL Server docker image.
Learn more about this here.
- Note that M1/arm64 MacBooks aren't compatible with Docker-based SQL Server but can use Azure SQL Edge.
docker pull mcr.microsoft.com/mssql/server:2019-latest
3. Run the SQL Server docker image:
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=Strong!Passw0rd' -p 1401:1433 --name mssql -d mcr.microsoft.com/mssql/server:2019-latest
- Use a strong password (not the password shown above, or something simple like 'sa').
- Specify whatever available port you want (1401 here)
- Give it whatever name you want. This will be how you start/stop the Docker container (mssql here)
4. Update mssql.properties with the port and password specified above, then run:
5. Start LabKey.
Docker Deployment on Windows
The Linux edition of SQL Server is also available within a Docker container:
Note that in order to run a Docker container for SQL Server on Windows, you must enable Hyper-V, and when you do so, you cannot also run VirtualBox.
Steps to deploy SQL Server in Docker on a Windows machine:
1. Install a stable version of
Docker for Windows, accepting the default settings in the installer dialog.
2.
Restart your PC, if it does not automatically restart.
- Note that you may see a warning about Hyper-V not being enabled yet. If so, click cancel. We will enable it manually.
3.
Remove the Docker environment variables:
DOCKER_CERT_PATH=C:
DOCKER_HOST=tcp://192..xx.xx.xxx:2376
DOCKER_MACHINE_NAME=default
DOCKER_TLS_VERIFY=1
4.
Enable Hyper-V by going to
Turn Windows features on or off and checking all Hyper-V boxes. Click
OK.
5. Make sure Virtualization is enabled by checking your Windows Task Manager. Find it on the Performance tab under the grid for CPU usage. If it is not enabled, check the
troubleshooting documentation available from Docker.
6.
Restart your PC. Docker should start during this reboot. Follow status from the icon on the taskbar.
7. Use
Windows Powershell to test the installation:
8. If the test is unsuccessful, install
Docker Toolbox for Windows and restart, then try again.
9. Once the test is successful, you can
pull the SQL Server image for Linux from the Docker registry. Find the specific
featured tag to pull on this page. For example:
docker pull mcr.microsoft.com/mssql/server:2019-latest
10. Once completed, run:
11. You should now see microsoft/mssql-server-linux listed in the Docker image cache.
12.
Run SQL Server:
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<your strong password>' -p 1401:1433 --name mssql -d microsoft/mssql-server-linux:2019-latest
13.
View your SQL Server container up and running:
14.
Connect to SQL Server via SQL Server Management Studio using:
- Server name: your Server name followed by a comma and the port number
- Authentication: Login "sa" with your new strong password
15. You'll now be able to connect via LabKey. Modify mssql.properties with the port number and password.
16. If desired, you can use disable the automatic start of Docker by editing the settings.
For troubleshooting assistance with this process, review the
Docker documentation.
Using Windows/Domain Authentication
In some organizations, you may want or need to use Windows/Domain Authentication instead of SQL Server Authentication. This option should be considered carefully, as access to LabKey Server will depend on a successful login in the domain/Active Directory. If a problem occurs, like an expired profile or if Active Directory is either down or having issues, this will prevent the Windows-based authentication to SQL Server.
In order to use Windows/Domain Authentication you need to add the integratedSecurity parameter to the URL string in your labkeyDataSource application.properties section:
context.resources.jdbc.labkeyDataSource.url=jdbc:sqlserver://SERVER_NAME:1433;databaseName=DATABASE_NAME;trustServerCertificate=true;applicationName=LabKey Server;integratedSecurity=true;
In addition, you need to install the MSSQL JDBC Auth DLL file within the driver package; it is not included by default. You can download the matching version of the full driver from
Microsoft directly. The error message you see will guide you to the expected version and specific file name. Find the DLL in the auth > x64 subfolder, download it, and put it into the Windows/System32 directory.
If this DLL is not present, you'll see an error message similar to:
Message: This driver is not configured for integrated authentication. ClientConnectionId:##### SQLState: 08S01 ErrorCode: 0
com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:#####
...
java.lang.UnsatisfiedLinkError: Unable to load authentication DLL mssql-jdbc_auth-10.2.1.x64
Switch from jTDS to Microsoft JDBC Driver
Support for the jTDS driver (net.sourceforge.jtds.jdbc.Driver) was removed in 23.3 (March 2023).
If you are currently using the jTDS driver with a
development machine, the next time you pull from develop and run 'gradlew pickMSSQL' you'll start using the new driver. If you need to switch back for some reason, you can use the target 'gradlew pickJtds' to return to using the jTDS driver.
For other instances, you will need to update the labkeyDataSource driverClassName and URL
in the application.properties file. We recommend that you first upgrade your
staging (and/or test) instance(s), and upgrade production after testing is completed.
Note that if you are using
Windows domain authentication, you will need to obtain and install a different DLL than you needed for the jTDS driver.
Related Topics