{"id":10167,"date":"2017-01-31T15:01:52","date_gmt":"2017-01-31T21:01:52","guid":{"rendered":"http:\/\/www.mrc-productivity.com\/techblog\/?p=10167"},"modified":"2025-06-06T13:18:03","modified_gmt":"2025-06-06T19:18:03","slug":"mssql-installation-guide","status":"publish","type":"ht_kb","link":"https:\/\/www.mrc-productivity.com\/techblog\/?ht_kb=mssql-installation-guide","title":{"rendered":"SQL Server (MSSQL) Installation Guide"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Overview<\/h2>\n\n\n\n<p>Microsoft SQL Server customers will need to perform a few additional steps after installing m-Power in order to connect to their SQL Server database. This post will walk you through the needed steps to finish the installation. <\/p>\n\n\n\n<p>First, you will need a JDBC driver file to connect to the SQL Server instance. Additionally, a few SQL Server configuration settings need to be enabled in order for m-Power to communicate with the database. <\/p>\n\n\n\n<p>Also note, m-Power will connect to the database via SQL Server Authentication. The setup\/configuration of m-Power requires a <strong>SQL Server Admin User<\/strong> 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. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Configuration<\/h2>\n\n\n\n<p>There are a few required configurations which must be done in order to connect SQL Server to m-Power. Only once the required configurations are done can the optional configurations be pursued. <\/p>\n\n\n\n<p><strong>Required Configuration<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><a href=\"#step-1-installing-the-jdbc-driver-required\" data-type=\"internal\" data-id=\"#step-1-installing-the-jdbc-driver-required\">Installing the JDBC Driver<\/a><\/li>\n\n\n\n<li><a href=\"##step-2-configure-m-power-sql-server-properties-required\">Configure m-Power SQL Server Properties<\/a><\/li>\n\n\n\n<li><a href=\"#step-3-enabling-tcp-ip\">Enabling TCP\/IP<\/a><\/li>\n<\/ol>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<p><strong>Optional Configuration: <\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Active Directory Runtime Setup<\/li>\n\n\n\n<li>Active Directory Interface Signin<\/li>\n\n\n\n<li>Integrated Security<\/li>\n<\/ul>\n<\/div><\/div>\n<\/div><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"jdbc_driver\">Step 1: Installing the JDBC Driver <em>(required)<\/em><\/h3>\n\n\n\n<p>Follow these instructions to download and install the JDBC driver that enables m-Power to communicate with SQL Server.<\/p>\n\n\n\n<div class=\"wp-block-ht-blocks-list heroic-styled-list\" id=\"heroic-styled-list-\"><ol class=\"fa-ul\"><li>Open the driver download page for Microsoft <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/connect\/jdbc\/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16\" target=\"_blank\" rel=\"noopener\">here<\/a>.<\/li><li>Click &#8220;Download Microsoft JDBC Driver 11.X for SQL Server (zip).<\/li><li>Unzip the folder to the desktop (or anywhere of your choice).<\/li><li>Open the new sqljdbc_11.X.X.X_enu folder > sqljdbc_11.X > enu <\/li><li>Move the<code> mssql-jdbc-11.2.1.jre11.jar <\/code>file into \/m-power\/tomcat\/lib. <\/li><li>Restart Tomcat.<\/li><\/ol><style>#heroic-styled-list- li:before{\n\t\t\t\t\t\t   background:#000000;\n\t\t\t\t\t\t   border-radius: 0;\n\t\t\t\t\t\t   }<\/style><\/div>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-info\"><strong>Note:<\/strong> You will need to move this file into development and production environments.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"configure\">Step 2: Configure m-Power SQL Server Properties<em> (required)<\/em><\/h3>\n\n\n\n<p>Various m-Power files will need to be setup correctly to ensure a connection can be made.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">File: <code>ms_sqlserver_driver.properties<\/code><\/h4>\n\n\n\n<p>Location: &#8230;\/m-power\/proddata\/conf\/<\/p>\n\n\n\n<p>Edit this file in a text editor (mrc recommends Notepad++) and verify that the <code>connection_properties<\/code> line is formatted as such:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>connection_properties=SelectMethod\\=Cursor<strong>;encrypt\\=true;trustServerCertificate\\=true;<\/strong><\/code><\/pre>\n\n\n\n<p>New installations of m-Power will already have this present, however older customers will need to add this manually.<\/p>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-info\"><strong>Note <\/strong>&#8211; the port number the SQL Server instance will be run on is also listed in this file.<strong> It is assumed to be the default port 1433.<\/strong> If your SQL Server instance will utilize a different port, please change the <code>port<\/code> property value in this file.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">File: <code>mrc-spring-context.xml<\/code><\/h4>\n\n\n\n<p>Location:  \/m-power\/mrcjava\/WEB-INF\/classes\/<\/p>\n\n\n\n<p>Edit this file in a editor and only if not already present, append the following to the end of any ms_sqlserver bean&#8217;s JDBC url:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>;encrypt=true;trustServerCertificate=true; <\/code><\/pre>\n\n\n\n<p>Repeat this process for the other mrc-spring-context.xml file in \/m-power\/mrcwebgui\/WEB-INF\/classes\/. <\/p>\n\n\n\n<p>Again, new installations of m-Power will already have this present, however older customers will need to add this manually.<\/p>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-danger\">Do not edit the<strong><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-orange-color\">mrcdb<\/mark>_ms_msqlserver1<\/strong> connection (or any connection with <em>mrcdb<\/em> in the id value) in the \/mrcwebgui mrc-spring-context.xml . This bean is not a connection to SQL Server, but rather m-Power&#8217;s embedded Derby database.<\/p>\n\n\n\n<p>Ensure you restart Tomcat after editing the mrc-spring-context.xml files.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"TCPIP\">Step 3: Enabling TCP\/IP<em> (required)<\/em><\/h3>\n\n\n\n<p>Once the driver has been installed and Tomcat restarted, you will need to confirm that the TCP\/IP ports are open and enabled.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/www.mrc-productivity.com\/docs\/images\/enableTCPIP.jpg\" alt=\"TCPIP Properties\"\/><\/figure>\n\n\n\n<p>Under the Protocol tab, be sure that Enabled is set to Yes.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/www.mrc-productivity.com\/docs\/images\/Protocol.jpg\" alt=\"TCPIP Enabled\"\/><\/figure>\n\n\n\n<p>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.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/www.mrc-productivity.com\/docs\/images\/IP1.jpg\" alt=\"IP1\"\/><\/figure>\n\n\n\n<p>Scroll to the bottom and set the IPAll TCP Port to 1433.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/www.mrc-productivity.com\/docs\/images\/IPAll.jpg\" alt=\"IPAll\"\/><\/figure>\n\n\n\n<p>Restart the MS SQL Service and Tomcat.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/www.mrc-productivity.com\/docs\/images\/restartSQLserver.jpg\" alt=\"Restart SQL Server\"\/><\/figure>\n\n\n\n<p>Once both services have been restarted, you may move onto the configuration of your m-Power installation using an admin SQL Server user.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"auth\">Active Directory Interface Signin (optional)<\/h3>\n\n\n\n<p>As mentioned before, the m-Power interface login validates via the SQL 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:<\/p>\n\n\n\n<p>After configuring m-Power with the admin database user, sign in with this database user. <\/p>\n\n\n\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-9d6595d7 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\">\n<p>Create your Active Directory users as m-Power developers. This is done within <strong>Admin<\/strong>(Menu) -&gt; <strong>Developers<\/strong> -&gt;<strong>Create New<\/strong>. When specifying the user profile, do not include the domain name. Instead, only enter the Login name. Ensure the username is entered in <strong>ALL CAPS<\/strong>.<\/p>\n<\/div>\n\n\n\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\">\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"\/docs\/vue-images\/developer.png\" alt=\"\"\/><\/figure>\n<\/div>\n<\/div>\n\n\n\n<p>Next, directly on the m-Power server edit the \/m-power\/proddata\/conf\/mrctool.properties file in Notepad++. Add the following two properties to the bottom of the file:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>active_directory=true\nactive_directory_url=ldap:\/\/XX.XX.XXX.XXX:389;domain=mrc.test;search_base=DC=mrc,DC=test<\/code><\/pre>\n\n\n\n<p>The first property <code>active_directory<\/code> tells m-Power to validate by an Active Directory account rather than the traditional Database user account. <\/p>\n\n\n\n<p>The second property <code>active_directory_url<\/code> tells m-Power the LDAP URL to your Active Directory server. Please make the following configurations to your URL string (<code>ldap:\/\/XX.XX.XXX.XXX:389;domain=mrc.test;search_base=DC=mrc,DC=test<\/code>) as necessary:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Replace <code>XX.XX.XXX.XXX<\/code> with your Active Directory server address.<\/li>\n\n\n\n<li>Replace <code>MYDOMAIN.com<\/code> our Active Directory domain.<\/li>\n\n\n\n<li>Add the necessary domain content values (DC=) to the <code>search_base<\/code> parameter. You may add as many domain content values as is necessary for your system (separated by commas).<br>Save your changes when done and restart Tomcat.<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/www.mrc-productivity.com\/docs\/vue-images\/mrctools_adsetup.png\" alt=\"mrctools.properties file with Active Directory configuration\"\/><\/figure>\n\n\n\n<p>Once set up, save your changes and restart Tomcat.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>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 <a href=\"\/docs\/knowledge-base\/implementing-security#validate-by-active-directory\" target=\"_blank\" rel=\"noopener\">here<\/a> to configure.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"intSec\">Integrated Security (optional)<\/h3>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>First, be sure you have completed the above steps to enable <a href=\"#auth\">Active Directory Interface Login<\/a>.<\/p>\n\n\n\n<p>Download the Integrated Security authentication file <a href=\"https:\/\/mrc-productivity.com\/downloads\/sqljdbc_auth.dll\">here<\/a> and install on the server&#8217;s C:\\Program Files\\java\\jdk[version]\\bin folder (Use the directory where the java folder is set up on your server).<\/p>\n\n\n\n<p>Next, we will remove the MSSQL database user reference from the \/m-power\/mrcwebgui\/WEB-INF\/classes\/mrc-spring-context.xml file:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Find your existing ms_sqlserver1 connection.<\/li>\n\n\n\n<li>Within this bean, find and remove the elements for:<br><span class=\"padleft\"><code>your_username<\/code><\/span><br><span class=\"padleft\"><code>your_password<\/code><\/span><\/li>\n\n\n\n<li>Find the jdbc url and append <code>;integratedSecurity=true<\/code> to the end of the value.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/www.mrc-productivity.com\/docs\/images\/springcontext.png\" alt=\"spring context\"\/><\/figure>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Open your Windows service panel and find the necessary Tomcat entry. Right click and select Properties:<br><img decoding=\"async\" class=\"padleft\" src=\"https:\/\/www.mrc-productivity.com\/docs\/images\/tomcat.png\" alt=\"developer\" width=\"85%\"><\/li>\n\n\n\n<li>Switch to the &#8220;Log On&#8221; tab. Select the &#8220;This Account&#8221; radio. Browse to find the correct domain user and enter\/confirm the password entry. Press Apply.<br><img decoding=\"async\" class=\"padleft\" src=\"https:\/\/www.mrc-productivity.com\/docs\/images\/tomcatLogOn.png\" alt=\"developer\" width=\"70%\"><\/li>\n\n\n\n<li>Restart Tomcat.<\/li>\n<\/ul>\n\n\n\n<p>Now all database connections will use the user credentials set here to communicate with m-Power.<\/p>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-alert\"><strong>Note:<\/strong> If insufficient privileges are found, the user in the above screenshot will need their privileges to be elevated.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Overview Microsoft SQL Server customers will need to perform a few additional steps after installing m-Power in order to connect to their SQL Server 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&#8230;<\/p>\n","protected":false},"author":10,"comment_status":"closed","ping_status":"closed","template":"","format":"standard","meta":{"footnotes":""},"ht-kb-category":[152],"ht-kb-tag":[],"class_list":["post-10167","ht_kb","type-ht_kb","status-publish","format-standard","hentry","ht_kb_category-administration"],"_links":{"self":[{"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/ht-kb\/10167","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/ht-kb"}],"about":[{"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/types\/ht_kb"}],"author":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10167"}],"version-history":[{"count":55,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/ht-kb\/10167\/revisions"}],"predecessor-version":[{"id":12513,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/ht-kb\/10167\/revisions\/12513"}],"wp:attachment":[{"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10167"}],"wp:term":[{"taxonomy":"ht_kb_category","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=%2Fwp%2Fv2%2Fht-kb-category&post=10167"},{"taxonomy":"ht_kb_tag","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=%2Fwp%2Fv2%2Fht-kb-tag&post=10167"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}