Can NuGenesis 9 LMS connect to Microsoft SQL Server databases to query data in SmartBuilder? - WKB84652

ENVIRONMENT

• NuGenesis 9 LMS

ANSWER

Yes, NuGenesis LMS and SmartBuilder can connect to SQL Server databases, but they need JDBC drivers from Microsoft. Download the driver from Microsoft's website:　

• Download - JDBC Driver for SQL Server | Microsoft Docs
• The JDBC driver package now includes multiple copies of the driver, with each one being compiled for a different Java runtime version. Use the driver that's compiled for JRE 8. Any driver named with "jre18" refers to Java Runtime version 18, NOT to Java 1.8 (also known as Java 8).

1. Unzip its contents and copy the sqljdbc JAR into a directory on the LMS server.
   • NOTE: SQL Server 2014 requires a different version of the JDBC driver than previous versions. Always download the sqljdbc JAR file that is appropriate for the version of SQL Server.
2. Open a command prompt with admin rights and set the JAVA_HOME variable to the Java installation in the LMS server:
   • set JAVA_HOME=Drive:\WatersLMSServer\java\jre
3. Browse to the WildFly\bin folder:
   • cd Drive:\WatersLMSServer\Wildfly-11.0.0.Final\bin
4. Run the batch file "jboss-cli.bat".
5. Enter the following commands, in this order:
   1. NuGenesis versions 9.0-9.2: connect
   2. NuGenesis versions 9.3+: connect remote+https://localhost:9993
   3. When prompted to accept the server's certificate, enter T to accept it for one session, or P to permanenty accept it
   4. module add --name=com.microsoft.sqlserver --resources=C:\sqljdbc4.jar --dependencies=javax.api,javax.transaction.api
      • Note: the path used for the "--resources" parameter must be the path and file name used for the sqljdbc JAR file in step 1.
6. Close the command prompt.
7. Edit the file Drive:\WatersLMSServer\Wildfly-11.0.0.Final\standalone\configuration\nugenesis-lms.xml.
8. Add the following block of text to nugenesis-lms.xml, below the "<datasources>" tag:
   • <datasource enabled="true" jndi-name="java:/SQLServer" pool-name="SQLServer">
     <connection-url>jdbc:sqlserver://server:1434;DatabaseName=SQLDBName</connection-url>
     <driver>mssql</driver>
     <security>
     <user-name>elntest</user-name>
     <password>elntest</password>
     </security>
     </datasource>
   • NOTE: Some versions of SQL Server use port 1433 for the listener port. Consult with the SQL Server administrator for the correct port number.
9. Add the following block of text to nugenesis-lms.xml, below the "<drivers>" tag:
   • <driver module="com.microsoft.sqlserver" name="mssql">
     <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
     </driver>
10. Save and close nugenesis-lms.xml.
11. Edit the file Drive:\WatersLMSServer\WildFly-11.0.0.Final\modules\com\microsoft\sqlserver\main\modules.xml.
12. Add the following line to the <dependencies> block in modules.xml:
    • <module name="javax.xml.bind.api"/>
13. Save and close modules.xml.
14. Log in to LMS, and edit the QueryDataSources list on the LMS Administration page > Lists.
15. Add a new value to this list, where the value is the value of the jndi-name tag in mssql-ds.xml.
16. Save the list. The datasource should now be available in SmartBuilder.

Rollback instructions:

1. Remove the <datasource> and <driver> information from nugenesis-lms.xml (as added per the installation instructions).
2. Delete the file Drive:\WatersLMSServer\WildFly-11.0.0.Final\modules\com\microsoft\sqlserver\main\modules.xml.
3. Restart the NuGenesis LMS Server service.

ADDITIONAL INFORMATION

The username must be a valid Windows or SQL Server account with permission to log in to the database specified in the JDBC connect string. The user must have at least the "db_datareader" role for the specified database.

If the user credentials in nugenesis-lms.xml are for a SQL server account, the server must be configured to "SQL Server and Windows Authentication mode":

1. Log in to the SQL Server Management Studio.
2. Select the server entry in the Object Explorer list (top-most entry in the list), right-click, and select Properties.
3. Click the Security page.
4. Set the server authentication mode.
5. Click OK.
6. Restart the SQL Server service.