Microsoft SQL Database

A database Administrator should create the database and provide the System Administrator (the individual who will be installing IWC), with credentials that will provide database owner rights to the newly created empty database.

The System Administrator can then provide these credentials when selecting the available database in the Datastore Wizard of the IWC Console.

The database will then be created without the need for the System Administrator to have rights on the SQL Server.

There are two possible authentication modes in SQL Server: Windows Authentication mode and Mixed mode.

  • As an SQL Server Security best practice, Ivanti recommends using Windows Authentication to connect to the SQL Server. This authentication mode in SQL Server can leverage the organization-wide Active Directory accounts, groups and password policies, thereby making access more secure. It is not recommended to use the SQL System Administrator (sa) account. This account has irrevocable administrative credentials on the whole server.

    • Using Windows Authentication allows a user to connect through a Microsoft Windows account. Database permission will be based on a membership of a specified Active Directory group.

  • The Mixed mode allows the users to connect to an instance of SQL Server using either Windows Authentication or SQL Server Authentication.

When creating the database, Ivanti recommends selecting the option Use FIPS Compliant Security Algorithms, which will encrypt sensitive information in the database with the AES256 algorithm. This will prevent sensitive data from being exposed to an attacker that has access to the database or local cache.

Microsoft SQL Server can use Transport Layer security (TLS) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. TLS uses certificates to validate the server and the client should verify the certificate using the chain of trust where the trust anchor is the root certificate authority. This requires that the client computer should trust the root authority of the certificate used by your SQL Server. Thus, Ivanti recommends the use of Certificate Authority (CA) signed certificates.

The Force Protocol Encryption option for the Database Engine must be enabled so that all client/server communication is encrypted. The clients that cannot support encryption are denied access. This option requires the SQL Server Native Client to be installed on the Windows Top Level Relay Servers and machines which will be running the IWC Console.

This outline describes how to enable encrypted connections for an instance of the SQL Server Database Engine by specifying a certificate for it using SQL Server Configuration Manager.

  • Provision (install) a certificate on the server

  • Configure the server to accept only encrypted connections

  • Enable Force Encryption flag on SQL Server Network Configuration option

  • Ensure the client computer trusts the server certificate

  • Configure the client to use only encrypted connections

  • Enable Force encryption flag on SQL Server Native Client Configuration option

  • Ensure that Trust Certificate Server flag is set to No – this is the default

When configured properly, connection will occur only if there is a verifiable server certificate. If not, the connection attempts will fail.

Force protocol Encryption client setting

Trust Server Certificate client setting

Connection string/connection attribute Encrypt/Use Encryption for Data

Connection string/connection attribute Trust server Certificate

Result

Yes

No

Ignored

Ignored

Connection occurs only if there is a verifiable server certificate, otherwise the connection attempt fails.

Table information source: Microsoft Learn website, Documentation section

  • The Force Protocol Encryption option from the IWC Datastore Wizard is ignored when the settings above are implemented.

  • For a FIPS compliant database, SSLv2, SSLv3, and TLS 1.0 are not allowed. TLS 1.1 is not recommended. It is allowed solely in support of legacy systems.

  • Ivanti recommends configuring Servers to support TLS 1.2 or higher.

  • SQL Server must be restarted after you change the Force Encryption setting(s).

  • For further information on enabling encrypted connections to the database engine (SQL Server Configuration Manager), please refer to MS SQL documentation.

When creating the database, Ivanti recommends selecting the option Use FIPS compliant Security Algorithms. Although sensitive data is always encrypted, selecting this option, will ensure IWC uses FIPS compliant security algorithms. This will prevent data from being exposed to an attacker that has access to the database or local cache.

For more details, see Create a new database.

Ivanti recommends utilizing TDE for an additional layer of encryption to protect data at rest.

Transparent Data Encryption (TDE) is the primary SQL Server encryption option. TDE performs real-time I/O encryption and decryption of the data and log files. In other words, encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE enables you to encrypt an entire database. This will prevent sensitive data from exposure if an attacker obtains physical access to the database hard disk.

Benefits at a glance:

  • TDE protects the data at rest, which means that the database’s data is encrypted using the AES or 3DES encryption algorithms. Ivanti recommends AES for FIPS compliance.

  • Without the original encryption certificate and master key, the data cannot be read when the drive is accessed, or the physical media is stolen.

  • Backups for databases that use TDE are also encrypted.

  • TDE is completely transparent to the application and requires no coding changes to implement.

For further information, please refer to MS SQL documentation.