Plan du site  
pixel
pixel

Articles - Étudiants SUPINFO

SQL Server and the security of SQL databases

Par Rafik BELMEHDI Publié le 08/02/2019 à 07:35:56 Noter cet article:
(0 votes)
Avis favorable du comité de lecture

Introduction

Information stored in databases in SQL Server is no longer enabled, neither in enterprises nor in security viewpoints, this information is only reported by a large number of attacks and threats; This article aims to better control risks and threats as well as databases, what are good practices to follow to reduce risks and secure SQL Server databases?

SQL Server

Definition

SQL Server (Structured Query Language Server), is a Database Management System (DBMS), which gives users the means to manipulate, organize and extract information (raw data) into databases.

SQL Server is developed by Microsoft, and one of the leaders in the database market; It is based on the SQL programming language, which is used for database management.

Versions of SQL Server

SQL Server evolves over the years, where each time it improves its versions and offers new features to users, below the versions of SQL Server:

  • SQL Server 2005.

  • SQL Server 2008.

  • SQL Server 2008 R2.

  • SQL Server 2012.

  • SQL Server 2014.

  • SQL Server 2016.

  • SQL Server 2017: is the latest version, it offers several new features and features compared to older versions namely: improvements in memory, improvements and optimization of query processing, security improvements, ... etc).

SQL Server Editions

SQL Server offers several edits depending on the needs of users, below the editions of SQL Server:

  • Enterprise: This edition is the Premium offer, which provides advanced and comprehensive features such as (unlimited virtualization, Business Intelligence databases, support of development tools on the Cloud ... etc).

  • Standard: This release provides standard features such as basic data management, Business Intelligence databases, and support for cloud development tools.

  • Web: This edition is intended for Web hosts.

  • Developer: This edition is for developers, enabling them to create and test SQL Server-based applications.

  • Express: This edition is basic, it offers a free database, and allows users and amateur developer, to create small applications.

Threats and risks on SQL databases

The following are the most critical threats to databases:

  • Abuse of Privileges

    When users of a company, have access privileges to a database exceeding the limits of their function, this poses a serious threat to the databases, where a user can easily compromise the data, if it benefits permissions such as: SELECT, INSERT, UPDATE, DELETE, (either these privileges were assigned to them by mistake or intentionally, ... etc).

    The consequences of this threat such as losses, reliability and data corruption, and additional expenses for the company. The discovery of such incidents may take months or even years.

  • SQL Injection Attack

    The injection attack is to pass invisible and unauthorized SQL code in the vulnerable source SQL code, then this code will be sent to the database where it will be executed; therefore with this attack one can get unlimited access to a database.

  • Weakness of the native audit

    Auditing consists of automatically logging transactions and information about connections (successful or failed) to databases. Do not enable auditing in SQL Server, presents a serious risk to the system, namely non-detection of threats and vulnerabilities, which can make access to the system rather easy.

  • Attack by Denial of Service

    Denial of Service (DOS) is an attack that involves remotely planting servers, sending multiple repeated requests, overloading server resources, and making databases inaccessible. This attack represents a serious threat, which is complex to block, because it is difficult to differentiate a real request from a request from DDos.

  • Dictionary attack

    If a malicious user has access to the files of a database, and in order to find a password stored in this database, he can use the dictionary attack, the latter is based on the fact that users use common passwords namely: surname, first name, date of birth, ... etc; the Malicious user tests a series of passwords that are in his dictionary until he finds the correct password; This attack does not work all the time, but it remains a threat on the databases. But if the user manages to recover one or more passwords, he will use it to harm the system.

How to secure SQL databases

To reduce the risks and to better secure SQL databases, below are the best practices to follow:

  • Access control to databases and queries

    Assign the rights that users need to restrict their access to SQL databases. Limit the number of attempts to connect to databases, so a user will be blocked if they do not enter the correct password after a few connection attempts. Giving users permissions to SQL queries, depending on the needs of their functions, for example the query below, returns one or more rows if the user has an authorization such as "SELECT" on the table. Student".

    SELECT Id_Student, Student_Name, Student_FirstName, Student_Note

    FROM Student

    GO

    If the user does not have display permission on the "Student" table, the query returns an empty result set. Regular checking of role changes and user behavior analysis, can detect malicious users, and take necessary action before any security breaches.

  • Encrypt database backups

    To reduce the risk of data loss and integrity, database backups are always important and essential for users, where the databases must be regularly backed up in order to have recovery points.

    The backup files must be encrypted, otherwise they will be easy to restore on other SQL Server installations; To prevent access to its files, the administrator can create encrypted backups with the built-in function "MEDIAPASSWORD", and reduce the spaces of backups with the function "COMPRESSION". The following script is an example of backing up an encrypted and compressed database in SQL Server:

    BACKUP DATABASE TEST

    TO DISK='C:\BDDBack\TEST.BAK'

    WITH

    COMPRESSION

    MEDIAPASSWORD='P@SsW0rD!sEcuRe'

    GO

  • Restrict access to the database backup folder

    Access to the database backup folder must be restricted, and only granted to users who really need to access it. A user who has access to the folder while not authorized, may copy the files from the backup for harmful purposes, or may accidentally delete the backup files.

  • SQL server authentication

    • Windows authentication mode

      SQL Server approves a user's login through the Windows account, and does not need to log in to SQL Server. This mode is more secure and is a good security practice.

    • Mixed Authentication Mode (SQL Server and Windows)

      During the installation of SQL Server, the "SA" account (System Administrator) is created automatically. With Mixed mode the user uses both "SA" and Windows accounts at the same time to authenticate. But it is not recommended to use this mode, because SQL Server does not block users after several incorrect authentication attempts, for example an intruder can use tools and methods to break the password of the "SA" account, and if he discovers this password he will have all the privileges on the database.

  • Complexify the password of the account "SA"

    With Mixed Authentication Mode in SQL Server, you need to set a complex password for the SA account because a weak password is easy to crack, which will allow an intruder to have all the privileges in SQL Server. To have a secure password, it must contain at least 8 characters, letters, numbers, capital letters, and symbols, for example: ("?", "9", "@", " / "," $ "," μ ", etc.).

    A good practice is to change the passwords of SA accounts frequently.

  • Audit connections and increase log size

    Regular monitoring of logs helps identify risks and threats that can harm databases. If, for example, a malicious user (intruder) is able to outperform other defense systems, audits can identify violations after an attack, and also logs and audits can be used to repair the system (with system updates) and go back to the identity of the author of the attack.

Conclusion

In this article, we have introduced SQL Server, the SQL databases and their importance, then we saw what are the most critical risks and attacks that threaten these databases, and finally we concluded with some good practices, so to reduce the risks and attacks, and to secure the best SQL databases.

References

https://docs.microsoft.com/fr-fr/sql/sql-server/sql-server-technical-documentation?view=sql-server-2017

https://docs.microsoft.com/fr-fr/dotnet/framework/data/adonet/sql/sql-server-security

https://docs.microsoft.com/fr-fr/sql/sql-server/what-s-new-in-sql-server-2017?view=sql-server-2017

https://support.office.com/fr-fr/article/cr%C3%A9er-un-lien-vers-une-base-de-donn%C3%A9es-azure-sql-server-database-ou-importer-des-donn%C3%A9es-%C3%A0-partir-de-celle-ci-88c0cc2c-21dd-46f5-b74a-0db3613f5166

A propos de SUPINFO | Contacts & adresses | Enseigner à SUPINFO | Presse | Conditions d'utilisation & Copyright | Respect de la vie privée | Investir
Logo de la société Cisco, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société IBM, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Sun-Oracle, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Apple, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Sybase, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Novell, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Intel, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Accenture, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société SAP, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Prometric, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Toeic, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo du IT Academy Program par Microsoft, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management

SUPINFO International University
Ecole d'Informatique - IT School
École Supérieure d'Informatique de Paris, leader en France
La Grande Ecole de l'informatique, du numérique et du management
Fondée en 1965, reconnue par l'État. Titre Bac+5 certifié au niveau I.
SUPINFO International University is globally operated by EDUCINVEST Belgium - Avenue Louise, 534 - 1050 Brussels