Difference between revisions of "SQL Server Configuration in QPR UI"

From Mea Wiki
Jump to navigation Jump to search
 
(31 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
This page contains instructions for SQL Server configuration. Note that these instructions assume that there is an SQL Server 2016 instance with name '''SQLSERVER2016'''. Your SQL Server may be other version or have other instance name.
+
This page contains instructions how to configure SQL Server to be used for QPR UI database. Note that these instructions assume that there is an SQL Server 2017 instance with name '''SQLSERVER2017'''. Your SQL Server may be other version or have other instance name.
  
−
When making a new QPR MobileDashboard installation, check all the configuration items in this page. When updating to the latest version of QPR MobileDashboard, there is no need to change these settings.
+
When making a new QPR UI installation, check all the instructions in this page. When updating to the latest version of QPR UI, there is no need to change these settings.
  
−
==Enabling SQL Server TCP/IP port==
+
QPR UI supports TLS 1.2, so older TLS versions can be disabled in the SQL Server.
−
If named instance are used in the SQL Server, a TCP port needs to be assigned to the instance as follows:
 
  
−
# Open '''Sql Server 2016 Configuration Manager''' in Windows start menu.
+
==Creating new database for QPR UI==
−
# Click '''SQL Server Network Configuration''' and '''Protocols for SQLSERVER2016'''.
+
When making a new installation for QPR UI, a new SQL Server database needs to be created as described below. If you have an existing QPR UI database, don't create a new database.
−
# Double click '''TCP/IP'''.
 
−
# In '''Protocol''' tab, select '''Enabled''' is '''Yes'''.
 
−
# In '''IP Addresses''' tab, in section '''IPAll''' select '''TCP Port''' is '''1433'''. (To be confirmed: Is this actually enough?)
 
−
# Open '''SQL Server services''' and click '''Restart''' for '''SQL Server (SQLSERVER2016)'''.
 
  
−
More information: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port
+
# Open '''Microsoft SQL Server Management Studio''' and login to a proper instance.
 +
# In '''Object Explorer''' right click '''Databases''' ans from the popup menu select '''New database...'''.
 +
# Define a name for the database and change other settings for the database if needed (default settings will work for QPR UI).
 +
# In '''Object Explorer''' open click '''Security''' and then open '''Logins'''. You can create a new login by right clicking Logins and from the popup menu selecting '''New Login...''' or you may want to use an existing login.
 +
# Open '''Login Properties''' window by double clicking the login. In the '''General''' tab, check that authentication related settings are as desired.
 +
# Open the '''User Mapping''' tab and select the '''Map''' checkbox for the QPR UI database. Click also '''db_owner''' in the bottom side of the tab. Click '''OK'''.
  
 
==Checking SQL Server Browser is running==
 
==Checking SQL Server Browser is running==
Line 19: Line 19:
 
# Open '''Services''' in Windows Control Panel.
 
# Open '''Services''' in Windows Control Panel.
 
# Find service '''SQL Server Browser''' and check that it has status '''Running'''. If the service is missing, SQL Server Browser is not installed.
 
# Find service '''SQL Server Browser''' and check that it has status '''Running'''. If the service is missing, SQL Server Browser is not installed.
 +
 +
More information: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-browser-service-database-engine-and-ssas
 +
 +
If the SQL Server Browser refuses to start, check that the SQL Server Browser Window service '''Startup type''' is not '''Disabled'''.
 +
 +
== Enabling SQL Server TCP/IP protocol==
 +
The SQL Server must have TCP/IP protocol enabled, for QPR UI to be able connect to the database though Payara. To assign a TCP port:
 +
# Open '''Sql Server 2017 Configuration Manager''' in Windows start menu.
 +
# Click '''SQL Server Network Configuration''' and '''Protocols for SQLSERVER2017'''.
 +
# Double click '''TCP/IP'''.
 +
# In '''Protocol''' tab, check that '''Enabled''' is '''Yes'''.
 +
# In '''IP Addresses''' tab, check that '''Active''' is '''Yes''' and '''Enabled''' is '''Yes''' for the IP addresses that are to be used.
 +
## If you use dynamic TCP ports, set '''TCP Dynamic Ports''' to '''0''' and '''TCP Port''' to empty for all the IP addresses.
 +
## If you use static TCP port, set '''TCP Dynamic Ports''' to empty and '''TCP Port''' the used TCP port (usually 1433) for all the IP addresses.
 +
# Open '''SQL Server services''' in the left side hierarchy and click '''Restart''' for '''SQL Server (SQLSERVER2017)'''.
 +
 +
More information: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port
 +
 +
If you are unsure whether the TCP port is accessible in the QPR UI server, it can be tested as follows:
 +
# In the server where QPR UI will be installed, open Windows '''Command Prompt'''.
 +
# Type '''telnet [DATABASE_SERVER_HOSTNAME] [PORT]''', for example '''telnet localhost 1433'''. (If the Telnet program is not installed, you can install it in '''Turn Windows features on or off''' screen in Window Control Panel.)
 +
#* If the TCP port is accessible, a connection is opened (the command prompt screen is emptied).
 +
#* If the TCP port is not accessible, the connection fails with message '''... Could not open connection to the host ...'''
 +
# You can close the command prompt window.
 +
 +
Note that there may be a firewall blocking TCP connections.
  
 
==Enabling SQL Server Authentication==
 
==Enabling SQL Server Authentication==
−
If you are planning to use '''SQL Server Authentication''' in QPR MobileDashboard, check that SQL Server Authentication has been enabled as follows:
+
If you are planning to use '''SQL Server Authentication''' in QPR UI, check that SQL Server Authentication has been enabled as described below:
  
 
# Open '''Microsoft SQL Server Management Studio''' and login to a proper instance.
 
# Open '''Microsoft SQL Server Management Studio''' and login to a proper instance.
Line 27: Line 53:
 
# Click '''Security''' page and select '''SQL Server and Windows Authentication mode'''. Click '''OK'''.
 
# Click '''Security''' page and select '''SQL Server and Windows Authentication mode'''. Click '''OK'''.
 
# Open '''Services''' in Windows Control Panel.
 
# Open '''Services''' in Windows Control Panel.
−
# Find '''SQL Server (SQLSERVER2016)''' and for it click '''Restart''' from the right click popup menu.
+
# Find '''SQL Server (SQLSERVER2017)''' and for it click '''Restart''' from the right click popup menu.
 +
 
 +
If you are using '''Windows Authentication''', it's better to use '''Windows Authentication mode''' for better security.
  
 
More information: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode
 
More information: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode
  
−
==Creating new database for QPR MobileDashboard==
+
[[Category: QPR UI]]
−
When making a new installation for QPR MobileDashboard, a new SQL Server database needs to be created as follows:
 
−
 
 
−
# Open '''Microsoft SQL Server Management Studio''' and login to a proper instance.
 
−
# In '''Object Explorer''' right click '''Databases''' ans from the popup menu select '''New database...'''.
 
−
# Define a name for the database and change other settings for the database if needed (default settings will work for QPR MobileDashboard).
 
−
# In '''Object Explorer''' open click '''Security''' and then open '''Logins'''. You can create a new login by right clicking Logins and from the popup menu selecting '''New Login...''' or you may want to use an existing login.
 
−
# Open '''Login Properties''' window by double clicking the login. In the '''General''' tab, check that authentication related settings are as desired.
 
−
# Open the '''User Mapping''' tab and select the '''Map''' checkbox for the QPR MobileDashboard database. Click also '''db_owner''' in the bottom side of the tab. Click '''OK'''.
 
−
[[Category: QPR MobileDashboard]]
 

Latest revision as of 14:48, 15 January 2020

This page contains instructions how to configure SQL Server to be used for QPR UI database. Note that these instructions assume that there is an SQL Server 2017 instance with name SQLSERVER2017. Your SQL Server may be other version or have other instance name.

When making a new QPR UI installation, check all the instructions in this page. When updating to the latest version of QPR UI, there is no need to change these settings.

QPR UI supports TLS 1.2, so older TLS versions can be disabled in the SQL Server.

Creating new database for QPR UI

When making a new installation for QPR UI, a new SQL Server database needs to be created as described below. If you have an existing QPR UI database, don't create a new database.

  1. Open Microsoft SQL Server Management Studio and login to a proper instance.
  2. In Object Explorer right click Databases ans from the popup menu select New database....
  3. Define a name for the database and change other settings for the database if needed (default settings will work for QPR UI).
  4. In Object Explorer open click Security and then open Logins. You can create a new login by right clicking Logins and from the popup menu selecting New Login... or you may want to use an existing login.
  5. Open Login Properties window by double clicking the login. In the General tab, check that authentication related settings are as desired.
  6. Open the User Mapping tab and select the Map checkbox for the QPR UI database. Click also db_owner in the bottom side of the tab. Click OK.

Checking SQL Server Browser is running

If named instance are used in the SQL Server, the SQL Server Browser service needs to be running. Check it as follows:

  1. Open Services in Windows Control Panel.
  2. Find service SQL Server Browser and check that it has status Running. If the service is missing, SQL Server Browser is not installed.

More information: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-browser-service-database-engine-and-ssas

If the SQL Server Browser refuses to start, check that the SQL Server Browser Window service Startup type is not Disabled.

Enabling SQL Server TCP/IP protocol

The SQL Server must have TCP/IP protocol enabled, for QPR UI to be able connect to the database though Payara. To assign a TCP port:

  1. Open Sql Server 2017 Configuration Manager in Windows start menu.
  2. Click SQL Server Network Configuration and Protocols for SQLSERVER2017.
  3. Double click TCP/IP.
  4. In Protocol tab, check that Enabled is Yes.
  5. In IP Addresses tab, check that Active is Yes and Enabled is Yes for the IP addresses that are to be used.
    1. If you use dynamic TCP ports, set TCP Dynamic Ports to 0 and TCP Port to empty for all the IP addresses.
    2. If you use static TCP port, set TCP Dynamic Ports to empty and TCP Port the used TCP port (usually 1433) for all the IP addresses.
  6. Open SQL Server services in the left side hierarchy and click Restart for SQL Server (SQLSERVER2017).

More information: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port

If you are unsure whether the TCP port is accessible in the QPR UI server, it can be tested as follows:

  1. In the server where QPR UI will be installed, open Windows Command Prompt.
  2. Type telnet [DATABASE_SERVER_HOSTNAME] [PORT], for example telnet localhost 1433. (If the Telnet program is not installed, you can install it in Turn Windows features on or off screen in Window Control Panel.)
    • If the TCP port is accessible, a connection is opened (the command prompt screen is emptied).
    • If the TCP port is not accessible, the connection fails with message ... Could not open connection to the host ...
  3. You can close the command prompt window.

Note that there may be a firewall blocking TCP connections.

Enabling SQL Server Authentication

If you are planning to use SQL Server Authentication in QPR UI, check that SQL Server Authentication has been enabled as described below:

  1. Open Microsoft SQL Server Management Studio and login to a proper instance.
  2. In Object Explorer right click the topmost item and select Properties.
  3. Click Security page and select SQL Server and Windows Authentication mode. Click OK.
  4. Open Services in Windows Control Panel.
  5. Find SQL Server (SQLSERVER2017) and for it click Restart from the right click popup menu.

If you are using Windows Authentication, it's better to use Windows Authentication mode for better security.

More information: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode

Retrieved from "https://wiki.onqpr.com/mea/index.php?title=SQL_Server_Configuration_in_QPR_UI&oldid=15112"