FREQUENTLY ASK QUESTIONS

Instalation MSSSQL

MSSQL installation always varies slightly depending on the server where it is installed and the version that is installed. Below you will find a short sample video that guides you through the installation of MSSQL2014.
Ukázka instalace Microsoft SQL Server 2014

 

It is always necessary to observe the basic parameters:

1. SQL server must be installed in mixed authentication mode (can be switched after installation)
   Default encoding of the language Czech_CS_AS (can be changed when creating each new database)
2. In case of Express edition installation, enable TCP/IP and Named Pipes communication for the instance after installation (done in SQL Server Configuration Manager)
3. In the case of installation as an instance, ensure that the "SQL Server Browser" is started automatically (done in SQL Server Configuration Manager)
4. In a corporate environment, provide firewall penetration. (it is advisable to enable communication to the sqlserver and sqlbrowser services)
5. During the installation process, make sure to configure the correct user(s) that will be authorized to access the server. You can use the user "sa" and set a strong password or use a domain group or user. If this information is lost, the server will be unmanageable and Palstat may not be able to retrieve data from the server for further work.

  Possible firewall access settings for MSSQL

netsh advfirewall firewall add rule name="MsSqlEngine" dir=in program="c:Program FilesMicrosoft SQL Server[CHANGE_IT]MSSQLBinnsqlservr.exe" action=allow

netsh advfirewall firewall add rule name="MsSqlBrowser" dir=in program="c:Program Files (x86)Microsoft SQL Server90Sharedsqlbrowser.exe" action=allow

 

Instalation Microsoft SQL Server Management Studio

The tool is used to view and manage the entire SQL Server and is required for further configuration. Always install the latest version, which can be found on the Microsoft website

In the app, you can:

1. Create user "pal".
2. Creating system databases.
3. Other specific settings needed for specific running for each customer.
   •     Limiting or extending memory requirements.
   •     Setting default directories for data storage (Data, Log, Backup).
   •     Authentication level

 

SQL Server Configuration and Settings

After successful installation of SQL Server and Management Studio, you will be able to log in to SQL Server administration. Launch Management Studio from the Start menu or search for the application. Once the program is started, you will be prompted to enter your password. Enter the user "sa" and the password that was entered during the SQL Server installation or log in as a Windows Authentication user who is authorized to administer the server.

The following steps can also be executed with our pre-made script at the support address. Use the manual procedure if you want to go through the whole process manually and control everything better. When using the script, you must modify the input parameters described here.

Creating user

Create a user on the SQL server that the application will use to connect to the server. Connecting using the "sa" administrator account is not out of the question, but is not recommended as a security risk.

Click the tree on the left side of the program and select "New Login" with the right mouse button, see. Picture.

 

 

 

---

In the displayed window, enter the user name "pal" and define a password after switching to "SQL Server authentication". Next, disable "Enforce password policy", if you leave this option on, the password will expire after some time and Palstat will stop working. Under the "server roles" tab, set the necessary permissions. Suitable DBCREATOR, which can be removed after the databases are created and leave only PUBLIC. see picture below.

 

After creating a user, disconnect and then connect to the database using the icons . Connect already using the user "pal". By switching to the new user, you ensure that the user will be the owner of the newly created databases and it will no longer be necessary to modify the databases in this respect.

 

Creating databases

The following databases must be established into which the system divides the data according to their type:

• pal3g_data - main database
• pal3g_archiv - exclusively for the Archive module, it does not serve as a backup of deleted data
• pal3g_attach - attachments from all parts of the system
• pal3g_qsd - exclusively for the QSD module "Controlled Documentation"
• pal3g_demodata - optionally if you want a test database

Now the user "pal" is already created on the SQL server and you are logged in as this user. Now you need to create a database whose owner must be the user "pal". The creation could have been done under the user "sa", but when creating the DB you must define the owner otherwise it will be set to the currently logged in user.

Click the tree on the left side see. Figure and right click on "New Database"

 

In the opened window, first enter the database name in the "Database name" field and define the owner "pal" in the "Owner" field (it does not have to be done if the user "pal" is logged in). On this page you can further specify where the data file of the database to be created should be stored. It is often advisable to choose a different disk than the system itself for better performance and security. Change the storage location by dragging the slide and setting the path in the "Path" column. The settings must be made for both the Data file and the Log file. (Doesn't have to be done, use the global SQL server settings)

Under the "Options" tab, select the character set "Czech_CS_AS" in the "Collation" field (Does not need to be done if the server has already been installed with this Collation.)

You can set the recovery model to "Simple" to reduce the size of the Log file, if you choose another model it will not affect the operation of PALSTAT CAQ. It is recommended to keep the default value "Full"

No other settings are necessary.

---

After creating each database, it will be displayed in the left part of the program in the tree under the "Databases" option. After creation, you can make further database settings by right-clicking on the "Properties" option.

Creating databases using, SQL statement

Databases can be created using our SQL command, which can be used to create the necessary databases very quickly and automatically.

The script must be executed by a user with permission to create users and databases.

The SQL script for creating databases can be found in the SUPPORT/SQL section of our website.

Configuring MSSQL and enabling TCP/IP

Start the "SQL Server Configuration Manager" application. Next, under the Native Client Configuration option, enable the appropriate protocols on which SQL should communicate, i.e. TCP/IP and Named Pipes. For each option you can also set on which port you want the protocol to communicate.

If you don't change it will communicate on standard ports:

TCP/1433 SQL server

TCP/1434 SQL Browser

Server-side SSL encrypted connection

If you require encrypted SQL communication between the application and the SQL server, it is necessary to enable this feature on the SQL server in the first place. The Palstat application itself will detect the encrypted connection and use it. This feature is available in Palstat as of version 2017.03.00 and will display an icon in the bottom bar of the application if the SSL connection is activated.

The server-side encryption settings can be done in different ways and may vary depending on the SQL Server version and its configuration.
The instructions are therefore very general and based on the MSSQL2012 R2 version. Official Microsoft instructions.

SSL encrypted communication settings