MSSQL Installation Guide

Microsoft SQL Server customers will need to perform a few additional steps after installing m-Power in order to connect to your SQL database. This post will walk you through the needed steps to finish the installation. First, you will need a JDBC driver file to connect to the database. Additionally, a few SQL Server configuration settings need to be enabled in order to communicate with the database. Also note, m-Power will connect to the database via SQL Server Authentication. The setup/configuration of m-Power requires an MSSQL Server Admin User in order to generate the necessary metadata on the database. Subsequently, all developers will need to be set up as database users in order to sign into the interface.

  • Active Directory Interface Signin
  • Active Directory Runtime Setup
  • Integrated Security
  • Installing the JDBC Driver

    Follow these instructions to download and install the driver.

    1. Open the driver download page for Microsoft here.
    2. Click the “Download” button.
    3. Select the “sqljdbc_X.X.XXXX_enu.exe” and click “Next” to begin the download.
    4. Run the downloaded exe on the server.
    5. Unzip the folder to the desktop (or anywhere of your choice).
    6. Open the new Microsoft JDBC Driver for SQL Server folder > enu > jre8.
    7. Move the sqljdbc4X.jar file into /m-power/tomcat/lib. (Note: You will need to move this file into development and production.)
    8. Restart Tomcat.

    Enabling TCP/IP

    Once the driver has been installed and Tomcat restarted, you will need to confirm that the TCP/IP ports are open and enabled.

    Open the Microsoft SQL Server Configuration Tools and then the SQL Server Configuration Manager. Under the SQL Server Network Configuration, select Protocols and SQLEXPRESS. Right click on TCP/IP and select Properties.

    TCPIP Properties

    Under the Protocol tab, be sure that Enabled is set to Yes.

    TCPIP Enabled

    While still in the Properties window, click the IP Addresses tab and under IP1, change Active and Enabled both to Yes. Be sure the current IP address is listed under the IP Address.

    IP1

    Scroll to the bottom and set the IPAll TCP Port to 1433.

    IPAll

    Restart the MS SQL Service and Tomcat.

    Restart SQL Server

    Once both services have been restarted, you may move onto the configuration of your m-Power installation using an admin SQL Server user.

    Active Directory Interface Signin

    As mentioned before, the m-Power interface login validates via the MSSQL Server authentication. This means all m-Power developers need to have a valid user setup on the database in order to sign in. If you wish to have only one admin database user and have your developers sign into the m-Power interface via their Windows Authentication/Active Directory, please follow these instructions:

    After configuring m-Power with the admin database user, sign in with this database user. Create your Active Directory users as m-Power developers. This is done within Admin Menu -> System Management -> Manage m-Power Developers. When specifying the user profile, do not include the domain name. Instead, only enter the Login name. Ensure the username is entered in ALL CAPS.

    developer

    Next, directly on the m-Power server edit the /m-power/proddata/conf/mrctool.properties file in Notepad. Add the following line at the bottom of the file: ‘active_directory=true’ (omit the quotes). This setting tells m-Power to validate by an Active Directory account rather than the traditional Database user account. Be sure to save your change.

    Finally, to point m-Power to your Active Directory server, you will need to add an Active Directory bean to your m-power/mrcwebgui/WEB-INF/classes/mrc-spring-context.xml file. More information about configuring Active Directory can be found here.

    developer

    Once set up, save your changes and restart Tomcat.

    You should now be able to log into the m-Power interface using a valid Active Directory user that has been also been set up as an m-Power developer. Be sure you are signing into the interface with only the username. Do not include the domain.

    Note, the above steps are only for developers logging into the development interface. If wanting to have your end user/run-time security validation please use the instructions here to configure.

    Also note, whether in the development environment or at run-time, any communication needed between m-Power and your MSSQL server will use the admin database user that was used to configure m-Power. If wishing to not use a database user at all, you can configure Integrated Security to have all development and run-time connections use an Active Directory user. Please proceed to the next section.

    Integrated Security

    Configuring Integrated Security will remove the need to specify an MSSQL database user/password in configuration files. Instead, you will point Tomcat to use an Active Directory account as the pooled connection user.

    First, be sure you have completed the above steps to enable Active Directory Interface Login.

    Download the Integrated Security authentication file here and install on the server’s C:\Program Files\java\jdk[version]\bin folder (Use the directory where the java folder is set up on your server).

    Next, we will remove the MSSQL database user reference from the /m-power/mrcwebgui/WEB-INF/classes/mrc-spring-context.xml file:

    • Find your existing ms_sqlserver1 connection.
    • Within this bean, find and remove the elements for:
      <property name="username"><value>your_username</value></property>
      <property name="password" <value>your_password</value></property>
    • Find the jdbc url and append “;integratedSecurity=true” to the end of the value.
    • For the m-Power interface to authenticate to your Active Directory server, you will need to be sure you have added an Active Directory bean. Instructions for this are in the previous section. Save your changes.

    spring context

    Once finished editing the mrc-spring-context.xml file inside of /m-power/mrcwebgui, it is time to edit your runtime file located in /m-power/mrcjava/WEB-INF/classes/mrc-spring-context.xml. You will need to repeat the steps referenced above.

    The final step is that, since we are using Integrated Security and no user/password is defined in any spring context file, we need to configure a valid Active Directory to run Tomcat. This user and their credentials, will be used to facilitate all communication to the database.

    • Open your Windows service panel and find the necessary Tomcat entry. Right click and select Properties:
      developer
    • Switch to the “Log On” tab. Select the “This Account” radio. Browse to find the correct domain user and enter/confirm the password entry. Press Apply.
      developer
    • Restart Tomcat.

    Now all database connections will use the user credentials set here to communicate with m-Power.

    Note: If insufficient privileges are found, the user in the above screenshot will need their privileges to be elevated.

    Related Posts:

    Leave a Reply

    Your email address will not be published. Required fields are marked *