3.16.3

Microsoft SQL Server on Windows

1. Download Microsoft SQL Server 2022 Express

2. Install Microsoft SQL Server 2022 Express

  • You can select the Basic installation type during installation.

  • Also for database management, we need SQL Server Management Studio (SSMS). You can download it here.

  • Also, as an alternative to Server Management Studio, you can use sqlcmd. The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt.

3. Enable TCP/IP connections

HES uses TCP/IP to connect to the SQL Server database, but SQL Server Express does not enable TCP support by default. To enable TCP/IP:

3.1. In SQL Server Configuration Manager, expand the SQL Server Network Configuration -> Protocols for SQLEXPRESS node.

3.2. Right-click the TCP/IP item on the right, then select Properties.

3.3. On the General tab, change Enabled to Yes.

3.4. On the IP Addresses tab, under the IPAll node, clear the TCP Dynamic Ports box.

3.5. In TCP Port, enter the port to listen on 1433. This port is to be used in the HES connection string.

3.6. Click OK.

3.7. Restart the Microsoft SQL Server Express service using either the standard service control panel or the SQL Express tools.

4. Change authentication mode

4.1. Start SQL Server Management Studio and connect to the Server.

4.2. In Object Explorer, right-click the instance of SQL Express, then select Properties.

4.3. Select the Security section on the left.

4.5. Change the Server Authentication to SQL Server and Windows Authentication mode.

4.6. Restart the Microsoft SQL Server Express service using either the standard service control panel or the SQL Express tools.

5. Creating SQL Server User and Database

Create a user in Server Management Studio:

5.1. Start SQL Server Management Studio and connect to the Server.

5.2. In Object Explorer go to Security -> Logins, right-click the Logins node and select New Login.

5.3. In the "login new" window:

  • Enter the username: hesuser.

  • Change the Windows authentication to SQL Server аuthentication.

  • Enter the user's password.

  • Disable Enforce password expiration box.

  • Click OK.

Create a database in the Server Management Studio:

5.4. In Object Explorer go to Databases, right-click the Databases, then select New Database.

  • Enter the database name: hesdb

  • select the owner (an user from the previous step).

  • Click OK.

An alternative way to create a database and user is to use the sqlcmd utility.

5.1. Start the SQLCMD type "sqlcmd" in Windows search.

The following lines create a database hesdb, the user hesuser with the password <user_password>. Сhange <user_password> with your real password:

> CREATE LOGIN [hesuser]  WITH PASSWORD = 'user_password';
> GO
> CREATE DATABASE hesdb;
> GO
> USE hesdb; 
> GO
> CREATE USER [hesuser] from login [hesuser];
> GO
> GRANT CONTROL ON DATABASE::hesdb  TO [hesuser];
> GO