One place for hosting & domains

      starting at only


      SQL Server Security Best Practices, Part 2

      This guide is the second in a series of articles that covers SQL Server security best practices.
      Part 1 of this series discussed a SQL Server installation’s physical security, operating system security, and application maintenance. Additionally, the previous guide outlined how to disable unnecessary features, enable encryption, and implement data masking.

      The second part of this series describes how and why you should:

      SQL Server Authentication

      Protection of data stored with SQL Server depends upon the ability to authenticate access to specific sets of data. SQL Server provides two options for database authentication in a Windows or Linux environment:

      You are prompted to select one of these SQL Server authentication modes during SQL Server setup.


      You can change the SQL Server authentication mode even after the initial installation decision has been made.

      Windows or Linux Authentication Mode

      In this mode, an installer logs into SQL Server using their Windows or Linux account. SQL Server validates the account name and password via the Windows or Linux operating system. SQL Server does not prompt for a password and does not perform the validation.

      Windows or Linux authentication uses Active Directory (AD) accounts. As a result, you can have centralized policy control for authentication. Policies can govern password strength and complexity, password expiration, account lockout, and active directory groups in the active directory.

      Windows or Linux-based authentication is the default authentication mode and is much more secure than
      SQL Server Authentication (discussed in the next section). Windows or Linux Authentication uses the Kerberos security protocol to support the above-mentioned security features. A connection made using Windows or Linux Authentication is sometimes called a trusted connection because SQL Server trusts the credentials provided by the underlying Windows or Linux operating system.

      SQL Server and Windows/Linux Authentication Mode (Mixed-Mode)

      When using SQL Server Authentication, logins are created in SQL Server and are not based on Windows or Linux user accounts. Both the username and the password are created
      by SQL Server and are stored within SQL Server. Users connecting using SQL Server Authentication must provide their credentials (username and password) every time that they connect to SQL Server.

      This mode does not use the Windows or Linux Kerberos security protocol, and it is considered to be inferior to
      Windows or Linux Authentication mode.

      System Administrator (SA) Account

      If you are using
      SQL Server (mixed-mode) authentication, SQL Server automatically creates a System Administrator (SA) user login with sysadmin privileges and permissions. To increase the security of your SQL Server, you should perform the following:

      1. Rename the SA login account to a different, more obscure, name.
      2. Disable the account entirely, if you do not plan on using it.
      3. For the SA (or renamed) account, select a complex password, consisting of lower/upper case letters, numbers, and punctuation symbols.
      4. Do not allow applications to use the SA (or equivalently renamed) account in any of the application connection strings.


      Any other user-based (lower-privileged) SQL Server accounts should also use complex, unique passwords.

      High-Privileged Operating System Accounts

      SQL Server uses a Windows or Linux account to run its services. Typically one should not assign high-privileged, built-in accounts (or equivalents) such as Network Service or Local System to the various SQL Server services. This can increase the risk of nefarious database/server activity, should someone be able to log into these types of accounts.

      Only assign the appropriate level of security-required accounts to SQL Server services. If not needed, any high-privileged operating system accounts on the server housing the SQL Server should be disabled as appropriate.

      Restrict SQL Traffic

      Database servers typically have one or more servers connecting to them. Access to these servers must be allowed only to and from designated IP addresses. Doing this can potentially prevent a nefarious user from accessing the server. In certain cases, a user of SQL Server may need to connect directly to the database. Restricting those SQL connections to the specific IP addresses (or at least IP class block or segment) that require it should be implemented.

      These IP restrictions can be managed with different solutions on different platforms:

      SQL Server Patches (Service Packs)

      Microsoft regularly releases SQL Server service packs and/or cumulative packs for fixing known issues, bugs, and security issues. It is highly advisable to apply SQL Server patching on production instances of SQL Server. However, before applying a security patch to production systems, it is advisable to apply these patches in a test environment. This is done to validate the changes in the patch and ensure that your database operates as expected under the patch.


      When dealing with production instances of SQL Server, it is important to regularly backup the server’s databases. A database backup creates a copy of the operational state, architecture, and stored data of a database. Backups help guard against potential database failures. These failures can happen because of corruption, disk array failure, power outages, disasters, and other scenarios.

      Backups can also assist with non-failure scenarios where a rollback of your database to a particular date may be necessary. Full database backups (on a regularly scheduled basis) and incremental backups (on a daily or running time basis) should be performed and maintained.

      Securing your backups is critical, and database professionals sometimes do not consider all of the requirements for securing database backups. This work includes:

      • Restriction of access to backup files. Do not provide all people in your organization the access rights (create, view, modify, and delete) to backup files.

      • Encrypting backup files properly.

      • Storing backups in an off-site facility. Depending on the organization and the critical nature of the database data, backups of a certain age should be preserved and archived.


      Auditing is another key component of SQL Server security. A designated database administrator or database security team should regularly review SQL Server auditing logs for failed logins.

      SQL Server provides a default login audit mechanism for reviewing all of the login accounts. These audit facilities record incoming requests by username and client IP address. Login failures can assist in discovering and eliminating suspicious database activity. The following types of activity can show up in the SQL Server audit logs:

      • Extended Events: Extended Events is a lightweight performance monitoring system that enables users to collect data needed to monitor and troubleshoot problems in SQL Server.

      • SQL Trace: SQL Trace is SQL Server’s built-in utility that monitors and records SQL Server database activity. This utility can display server activity, create filters that focus on the actions of users, applications, or workstations, and can filter at the SQL command level.

      • Change Data Capture: Change Data Capture (CDC) uses a SQL Server agent to record insert, update, and delete activity that applies to a specific table.

      • Triggers: Application-based SQL Server Triggers can be written specifically to populate a user-defined audit table to store changes to existing records in specific tables.

      • SQL Server-Level Audit Specifications: A Server Audit Specification defines which Audit Action Groups can be audited for the entire server (or instance). Some audit action groups consist of server-level actions such as the creation of a table or modification of a server role. These are only applicable to the server itself.

      Hardware and/or software firewall logs (that is, external to SQL Server) should be regularly examined to monitor and detect any nefarious attempts at server penetration.


      In part two of this article series, you reviewed additional methods of enhancing the security of SQL Server databases. These included choosing an
      authentication mode, restricting the
      System Administrator account, assignment of
      security-friendly accounts to SQL Server,
      restricting SQL traffic, application of
      patch updates,
      backup strategies, and use of
      auditing. To review earlier security recommendations, revisit
      Part 1: SQL Server Security Best Practices.

      Source link

      SQL Server Security Best Practices

      SQL Server security is perhaps one of the most overlooked facets of database server maintenance. Without taking the necessary precautions, an instance of SQL Server can be ripe for abuse and failure.

      SQL Database Security: User Management
      guide discussed the logical implementation of users, groups, roles, and permissions, to enhance, or limit database user security. The part one of the SQL Security Best Practices guide discusses a variety of important additional maintenance security topics.

      SQL Server Security: Infrastructure

      A very big part of SQL Server security is the physical security associated with the location of the SQL Server database. For SQL Server physical security, you consider things such as the safety and access of the data center, and other physical aspects associated with the server that the database resides on. For example, data center access can be controlled by things like human guards, keys, smart card access, face recognition software, and fingerprint readers.

      Data centers not only need to protect the servers where SQL Server resides, but other pieces of infrastructure. It may include things like modems, hubs, routers, storage arrays, and physical firewall devices. Physical security requires dealing with hardware devices, software (firewalls, operating systems, layered products), and network infrastructure, and keeping them at arms-length from humans, hackers, and any potential natural disasters (floods, hurricanes, power outages, etc).

      A person in charge of physical security must deal with things such as 24×7 security guards, climate control monitoring (extreme hot or extreme cold can affect equipment adversely), fire detection and suppression systems, water leakage detection mechanisms, ensuring that necessary equipment is plugged into Uninterruptible Power Supply (UPS), and the scheduling of both hardware and software preventative maintenance.

      One advantage to hosting a SQL Server in the cloud is that the cloud infrastructure provider is responsible for the physical security of the server hardware.

      SQL Server Security: Operating System and Applications

      Next on the list of security issues is the operating system that SQL Server resides on. SQL Server supports both Microsoft Windows and several flavors of Linux. There are many precautions that you must take to protect your operating system from hackers, viruses, and bugs. This could otherwise affect the functioning, access to, and integrity of SQL Server.

      First and foremost, operating system upgrades and (security) patches should always be applied whenever they become available. Before applying them to production-level machines, it may be prudent to apply them first to test or development environments, and allow them to run for a period of time. This ensures that the upgrades and patches are stable and are not problematic. Moreover, when an operating system goes end-of-life, it should always be replaced with a supported operating system version.

      It is a good practice to disable public internet access on your servers, to mitigate outside hacking interference. This can be followed by implementing robust firewalls on your operating system. By defining the appropriate firewall rules, you can restrict access to and from database servers that run on your server. You can also limit database access only to specific applications. Some popular firewall options for Linux servers are
      , and
      . You can also
      add the free Linode Cloud Firewalls service
      to the Linode Compute Instance that hosts SQL Server.

      Additionally, it is extremely good practice to remove unnecessary and unused applications from your server. This includes unwanted operating system features (for example, email or FTP) that could potentially lend itself to a security threat.

      Finally, you can make use of SQL Server’s
      Extended Protection for Authentication
      option to prevent an authentication relay attack that exploits service and channel binding.

      By default, SQL Server’s Extended Protection is turned off. You can enable it on a Windows-based client that is connected to the SQL Server by following the steps below:

      1. Select All Programs.
      2. Select Microsoft SQL Server 200X.
      3. Select Configuration Tools and select SQL Server Configuration Tools.
      4. Select SQL Server Configuration Manager.
      5. Click SQL Server Network Configuration and right-click on Protocols for MYSQLServer.
      6. Go to Advanced and from the Extended protection, select Allowed.

      By default, SQL Server’s Extended Protection is turned off. You can enable it on a …

      Securing Server Ports

      Another important security measure is to close all unnecessary server ports via your firewall, and open up select ports, as necessary. For example, by default, SQL Server runs on port 1433. Therefore, you can allow TCP port 1433 (and 3389 for remote server access) if no other application runs on the server. Similarly, the
      Microsoft Analysis Service
      uses default port 2383 as a standard port. You should also audit any ports that are used by your development stack and make sure that only the necessary ports are enabled.

      You may also consider changing SQL Server’s default listening port (1433) to another port number. By not changing it, this well-documented port number can be an invitation to hackers to infiltrate a SQL Server instance. For this reason, use a non-default port to solidify your SQL Server security. You can modify this very easily using the
      SQL Server Configuration Manager

      SQL Server Add-on Features

      SQL Server consists of database engine features that may not be needed by every installation. Some of these components can be a potential target used by hackers to gain access to a SQL Server instance. Therefore, it is good common practice to disable the add-on components and features in SQL Server that are not used. This limits the chances of any potential hacker attack. Some of the features you may consider disabling are the following:

      • OLE Automation Procedures: they enable SQL Server to leverage
        Object Linking and Embedding (OLE)
        to interact with other
        Component Object Model (COM)
        objects. From the data security standpoint, this area is more prone to attack.

      • Database Mail XPs: enables the Database Mail extended stored procedures in the MSDB database.

      • Scan for startup procs: an option to scan for automatic execution of stored procedures at Microsoft SQL Server startup time.

      • Common language runtime (CLR) integration feature: provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.

      • Windows (not Linux) process spawned by xp_cmdshell: Has the same security rights as the SQL Server service account, and spawns a Windows command shell and passes in a string for execution. Any output is returned as rows of text.

      • Cross-database Ownership Chaining (also known as cross-database chaining): A security feature of SQL Server that allows users of databases access to other databases besides the one they are currently using. Again, if you do not need a particular SQL Server feature, you can disable it.

      SQL Server Encryption

      A huge area of security for SQL Server is encryption. SQL Server supports several different encryption mechanisms to protect sensitive data in a database. The different encryption options available are as follows:

      • Always Encrypted Option – The Always Encrypted option helps to encrypt sensitive data inside client applications. The always encrypted-enabled driver automatically encrypts and decrypts sensitive data in the client applications. The encryption keys are never revealed to the SQL Server database engine. It does an excellent job of protecting confidential data.

      • Transparent Data Encryption (TDE) – TDE offers encryption at the file level. TDE solves the problem of protecting data at rest, and encrypting databases both on the hard drive and consequently on backup media. It does not protect data in transit or data in use. It helps to secure the data files, log files, and backup files.

      • Column-Level Encryption – Column-level encryption helps to encrypt specific column data; for example, credit card numbers, bank account numbers, and social security numbers.

      Data Masking

      Data masking is a technique used to create a version of data that looks structurally similar to the original, but hides (masks) sensitive information. The version with the masked information can then be used for a variety of purposes, such as offline reporting, user training, or software testing.

      Specifically, there are two types of data masking supported by SQL Server:

      • Static Data Masking – Static Data Masking is designed to help organizations create a sanitized copy of their databases where all sensitive information has been altered in a way that makes the copy shareable with non-production users. With Static Data Masking, the user configures how masking operates for each column selected inside the database. Static Data Masking then replaces data in the database copy with new, masked data generated according to that configuration. Original data cannot be unmasked from the masked copy. Static Data Masking performs an irreversible operation.

      • Dynamic Data Masking – Dynamic data masking helps to limit sensitive data exposure to non-privileged users. It can be used to greatly simplify the design and coding of security in your application. Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to specify how much sensitive data to reveal with minimal impact on the application layer.


      Database security is an extremely important part of database design, operations, and maintenance. It includes things such as physical security, operating system and application maintenance, disabling of superfluous features, port maintenance, encryption, and data masking. Collectively, and if addressed properly, these measures help keep a SQL Server database free from attack,operationally sound, and ensure database integrity.

      Source link

      Security On Autopilot: Best Practices for Securing Kubernetes Clusters


      About the Talk

      A high-level security checklist thats help you better secure your Kubernetes clusters throughout your container’s lifecycle. Get your 4Cs (Cloud, Cluster, Container, and Code) covered!

      What You’ll Learn

      • 3 common Kubernetes security mistakes
      • Kubernetes security best practices
      • Why it’s important to secure the container lifecycle

      This Talk Is Designed For

      Anyone looking to secure their Kubernetes clusters at all levels.



      About The Presenter

      Mamta Jha, Co-founder TechScalable
      Co-founder and enterprise architect with 17+ years of IT experience. Skilled cloud solutions architect with experience migrating, automating, and optimizing mission critical deployments in various cloud platforms, leveraging configuration management, CI/CD, and DevOps tools.

      Source link