Premium Feature — Available with all Premium Editions of LabKey Server. Learn more or contact LabKey.

As part of installing required components, you need to install a database server. You can use either PostgreSQL or Microsoft SQL Server as the primary database with a Premium Edition of LabKey Server. 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 labkey.xml or in your mssql.properties file later.
  • Complete the wizard.

If you've already installed SQL Server without enabling SQL Server Authentication then see How to: Change Server Authentication Mode in the Microsoft SQL Server documentation.

Using SQL Server with a Local Development Machine

Follow the general steps to set up a development machine, with a few exceptions noted below:

1. You can ignore the instructions here around the config file "labkey.xml", which do not apply to setting up a development server.

2. Instead of configuring your pg.properties file, use the one for use with Microsoft SQL Server (mssql.properties), specifying JDBC settings, including URL, port, username, password, etc.

3. Instead of running "gradlew pickPg" to pick up the properties file, run:

gradlew pickMSSQL

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 Configuration File

Edit the LabKey Server configuration file (usually named labkey.xml or ROOT.xml) to configure the jTDS JDBC driver for Microsoft SQL Server, available with Premium Editions of LabKey Server.

Comment out the Resource tag that specifies the PostgreSQL configuration. This Resource tag can be identified by the driverClassName "org.postgresql.Driver". After commenting out this resource tag, it should look similar to the following:

Comment out PostgreSQL Resource Tag

<!--
<Resource name="jdbc/labkeyDataSource" auth="Container"
type="javax.sql.DataSource"
username="postgres"
password="sa"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://localhost:5433/labkey162"
maxTotal="20"
maxIdle="10"
maxWaitMillis="120000"
accessToUnderlyingConnectionAllowed="true"
validationQuery="SELECT 1"
/>
-->

Add MS SQL Server Resource Tag

Use the following template for configuring a MS SQL Server data source. Replace USERNAME, PASSWORD, and DATABASE_NAME to fit the particulars of your target data source.

<Resource name="jdbc/labkeyDataSource" auth="Container"
type="javax.sql.DataSource"
username="USERNAME"
password="PASSWORD"
driverClassName="net.sourceforge.jtds.jdbc.Driver"
url="jdbc:jtds:sqlserver://localhost:1433/DATABASE_NAME"
maxTotal="20"
maxIdle="10"
maxWaitMillis="120000"
accessToUnderlyingConnectionAllowed="true"
validationQuery="SELECT 1"/>

Documentation for this driver is available on SourceForge. Other JDBC drivers for Microsoft SQL Server have not been tested and have known issues.

Note: 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.

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. These alternative names function like shortcuts or symlinks, allowing you to "mount" tables and views which actually exist in another schema or database. For more information, see SQL Synonyms.

Installation on Other Platforms

VirtualBox Deployment

On all operating systems, you can set up a VirtualBox VM running Windows and install SQL Server there. Follow these Mac-specific instructions, along with the documentation above for this deployment.

Linux Deployment

Microsoft distributes a native Linux version. Obtain it and follow the documentation available here:

Docker Deployment

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:

docker run hello-world

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:

docker image ls

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:

docker ps -a

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 useNTLMv2 parameter to the URL string in your data source tag:

url="jdbc:jtds:sqlserver://SERVER_NAME:1433/DATABASE_NAME;useNTLMv2=true"

In addition, you need to install the "ntlmauth.dll" driver file within the driver package; it is not included by default. You can download the full driver from https://sourceforge.net/projects/jtds/. Find the ntlmauth.dll in the x64 > SSO subfolder and put it into the Windows/System directory.

If this dll is not present, you'll see an error message like:

SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

Related Topics

Discussion

Was this content helpful?

Log in or register an account to provide feedback


previousnext
 
expand all collapse all