Manual in PDF format
1. SQL Server configuration options
- Depending on how NET Genium accesses the database, we distinguish three different types of database server configuration:
- 1. Local access – The database server is installed on the same computer as the application server.
- 2. Access via technology account from Active Directory – The database server is installed on a different computer than the application server, and NET Genium accesses the database under a technology account from Active Directory.
- 3. Access via local SQL Server account – The database server is installed on a different computer than the application server, and NET Genium accesses the database under the local SQL Server account.
2. Installing SQL Server 2016
2.1. Installation preparation
- Mount the installation media in “iso” format, or open the “O:\Install\_WIN\SQLServer2016Media” directory on the shared “\\office” server if the installation is located within the NetGenium internal computer network.
2.2. SQL Server 2016 installation via “Setup.exe”
- Run the “SETUP.exe” program.
- In the “Installation” section, select the “New SQL Server stand-alone installation or add features to an existing installation” option.
- Agree to the license terms and click “Next”.
- Check “Use Microsoft Update to check for updates (recommended)” and click “Next”.
- If you require full-text indexing of the columns in the database, check the “Full-Text and Semantic Extractions for Search” option in the “Feature Selection” section and in the “Instance Features” category, and click the “Next” button.
- In the “Server Configuration” section, on the “Service Accounts” tab, check the “Grant Perform Volume Maintenance Task Privilege to SQL Server Database Engine Service” option and click the “Next” button.
- In the “Database Engine Configuration” section on the “Server Configuration” tab, click the “Add Current User” button and then click the “Next” button.
- In the “Reporting Services Configuration” section, select the “Install and configure” option and click the “Next” button.
- In the “Consent to install Microsoft R Open” section, click the “Accept” button and click “Next” to complete the installation.
- You must restart your computer after the installation is complete.
3. Install SQL Server Management Studio
3.1. Installation preparation
- Mount the installation media in “iso” format, or if the installation is located within the NetGenium internal computer network, open the “O:\Install\_WIN” directory on the “\\office” shared server.
3.2. Installing SQL Server Management Studio via “Setup.exe”
- Start the program “SQLManagementStudio 17.1 SSMS-Setup-ENU.exe”.
- Click the “Install” button.
- After installation, restart your computer with the “Restart” button.
4. Creating the “ConnectionString.txt” file
- “ConnectionString.txt” is a file in the “Config” directory that contains the database connection string definition.
- The procedure for creating the “ConnectionString.txt” file differs depending on the configuration type of the database server.
- From the variants below, select the type of database server configuration you want to use and create and set up the “ConnectionString.txt” file accordingly.
- 1. Local access – The database server is installed on the same computer as the application server.
- 2. Access via technology account from Active Directory – The database server is installed on a different computer than the application server, and NET Genium accesses the database under a technology account from Active Directory.
- 3. Access via local SQL Server account – The database server is installed on a different computer than the application server, and NET Genium accesses the database under the local SQL Server account.
4.1. Connection string for local access to the database
- When installing NET Genium via the “Setup.exe” program (see the “NET Genium installation” manual), after checking the “Create database” option, the “ConnectionString.txt” file is created in the NET Genium directory in the “Config” folder. This file contains the definition of the connection string to the database.
- For example, the contents of the “ConnectionString.txt” file contain the following connection string to the database: “server=(local)\SQLEXPRESS;Trusted_Connection=true;database=netgenium”.
4.2. Connection string for accessing the database using a technology account from Active Directory
- Create a “ConnectionString.txt” file in the “Config” directory of your NET Genium and paste the following information into this file:
- IP address or name of the database server
- Database name
- Save the data according to the following pattern: “server=123.456.789.123;Trusted_Connection=true;database=netgenium”.
4.3. Connection string for accessing the database using a local SQL Server account
- Create a “ConnectionString.txt” file in the “Config” directory of your NET Genium and paste the following information into this file:
- IP address or name of the database server
- Database name
- The name of the local SQL Server account
- Password in this account
- Save the data according to the following pattern:
“server=123.456.789.123;database=netgenium;User Id=svcngadmin;Password=1234”.
5. Network penetration settings
- Use this setting only if the database server is installed on a different computer than the application server and NET Genium accesses the database under the technology account from Active Directory or under the local SQL Server account.
- From the “Start” menu, locate and run “SQL Server 2016 Configuration Manager”.
- Open the “SQL Server Network Configuration” node, select “Protocols for SQLEXPRESS”. Right-click on “TCP/IP” and select “Properties”.
- On the “Protocol” tab, set “Enabled” to “Yes”.
- On the “IP Addresses” tab, click on the “IP1” node and set “Active” and “Enabled” to “Yes”.
- On the same tab, click on the “IPALL” node and set the “TCP Port” to “1433”.
- In the last step, you need to restart SQL Server for the changes to take effect.
- Highlight “SQL Server Services”, right-click on “SQL Server (SQLEXPRESS)” and select “Restart”.
6. Firewall settings
- Use this setting only if the database server is installed on a different computer than the application server and NET Genium accesses the database under a technology account from Active Directory, or under local SQL Server account.
- From the “Start” menu, locate and launch “Windows Firewall with Advanced Security”.
- Click on “Inbound Rules” and select “New Rule…” in the “Actions” section.
- In the “Rule Type” section, select the “Port” type and click the “Next” button.
- In the “Protocol and Ports” section, select “TCP” and enter the port number “1433”. Then click the “Next” button.
- In the “Action” section, select the “Allow the connection” option and click the “Next” button.
- In the “Profile” section, check all three options: “Domain”, “Private”, “Public” and click “Next”.
- In the “Name” section, fill in the name of the “SQL Server” role and complete the setup with the “Finish” button.
7. Setting permissions for access to the database
- The procedure for setting permissions differs depending on the type of database server configuration.
- From the variants below, select the type of database server configuration you want to use and set the permissions using Management Studio accordingly.
- 1. Local access – The database server is installed on the same computer as the application server.
- 2. Access via technology account from Active Directory – The database server is installed on a different computer than the application server, and NET Genium accesses the database under a technology account from Active Directory.
- 3. Access via local SQL Server account – The database server is installed on a different computer than the application server, and NET Genium accesses the database under the local SQL Server account.
7.1. Setting permissions for local access
- There are 2 ways to set permissions for local access:
- Automatic authorization settings via the “Setup.exe” NET Genium program.
- Manually set permissions via “Microsoft SQL Server Management Studio”.
7.1.1. Automatic permission setting via the “Setup.exe” NET Genium program
- When installing NET Genium via “Setup.exe” and checking the “Authorize Network Service account to access the MSSQL database” option, the program automatically sets the permissions to access the database (see the “NET Genium Installation” manual).
7.1.2. Manually set permissions through “Microsoft SQL Server Management Studio”
- The settings for accessing the database to the predefined “NetworkService” account, which is selected in the application pool identity, are described below.
- From the “Start” menu, locate and run “Microsoft SQL Server Management Studio”.
- Open the “Security” node, right-click on “Logins” and select “New Login…”.
- On the “General” tab, check “Windows authentication” and click on the “Search…” button.
- In the newly opened dialog, click on the “Advanced” button.
- Click on the “Find” button, search for the “NETWORK SERVICE” account and confirm your choice with the “OK” button.
- On the “Server Roles” tab, check the “sysadmin” option and confirm with the “OK” button.
- Right-click on the main node of your SQL Server and select the “Properties” option.
- On the “Security” tab, make sure that the default option “Windows Authentication mode” is checked.
7.2. Setting permissions for access through the technology account from Active Directory
- The following describes how to set permissions for accessing the database to a technology account that is selected in the application pool identity – for example, “AD\svcngadmin”.
- From the “Start” menu, locate and run “Microsoft SQL Server Management Studio”.
- Open the “Security” node, right-click on “Logins” and select “New Login…”.
- On the “General” tab, check “Windows authentication” and enter the name of the technology account from Active Directory, including the domain name, for example “AD\svcngadmin” in the “Login name:” field.
- On the “Server Roles” tab, check the “sysadmin” option and confirm with the “OK” button.
- Right-click on the main node of your SQL Server and select the “Properties” option.
- On the “Security” tab, make sure that the default option “Windows Authentication mode” is checked.
7.3. Setting permissions for access through the local SQL Server account
- The following describes how to set permissions to access the database on a local SQL Server account that is different from the technology account set in the application pool identity.
- From the “Start” menu, locate and run “Microsoft SQL Server Management Studio”.
- Open the “Security” node, right-click on “Logins” and select “New Login…”.
- On the “General” tab page, fill in:
- Login name: for example “svcngadmin”
- Check “SQL Server authentication”
- Password: choose a very strong password (this password will be specified in the “ConnectionString.txt” file)
- Check “Enforce password policy”
- Uncheck “Enforce password expiration”
- On the “Server Roles” tab, check the “sysadmin” option and confirm with the “OK” button.
- Right-click on the main node of your SQL Server and select the “Properties” option.
- On the “Security” tab, check the “SQL Server and Windows Authentication mode” option and confirm with the “OK” button.
