View source for SQL Server Configuration in QPR UI

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.

When making a new QPR UI installation, check all the configuration items 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==
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.
# Click '''SQL Server Network Configuration''' and '''Protocols for SQLSERVER2016'''.
# 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

==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:
# 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.

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

==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 follows:

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

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

==Creating new database for QPR UI==
When making a new installation for QPR UI, 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 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'''.

[[Category: QPR UI]]