Security features of SQL Server on Linux

If you are a Linux user who is new to SQL Server, the following tasks walk you through some of the security tasks. These are not unique or specific to Linux, but it helps to give you an idea of areas to investigate further. In each example, a link is provided to the in-depth documentation for that area.

Create a login and a database user

Grant others access to SQL Server by creating a login in the master database using the CREATE LOGIN statement. For example:

CREATE LOGIN Larry WITH PASSWORD = ‘************’; 

Logins can connect to SQL Server and have access (with limited permissions) to the master database. To connect to a user-database, a login needs a corresponding identity at the database level, called a database user. Users are specific to each database and must be separately created in each database to grant them access. The following example moves you into the AdventureWorks2014 database, and then uses the CREATE USER statement to create a user named Larry that is associated with the login named Larry. Though the login and the user are related (mapped to each other), they are different objects. The login is a server-level principle. The user is a database-level principal.

USE AdventureWorks2014;

GO

CREATE USER Larry;

GO

  • A SQL Server administrator account can connect to any database and can create more logins and users in any database.
  • When someone creates a database they become the database owner, which can connect to that database. Database owners can create more users.

Later you can authorize other logins to create a more logins by granting them the ALTER ANY LOGIN permission. Inside a database, you can authorize other users to create more users by granting them the ALTER ANY USER permission. For example:

GRANT ALTER ANY LOGIN TO Larry;  

GO  

USE AdventureWorks2014;  

GO  

GRANT ALTER ANY USER TO Jerry;   

GO  

Now the login Larry can create more logins, and the user Jerry can create more users.

Granting access with least privileges

The first people to connect to a user-database will be the administrator and database owner accounts. However these users have all the permissions available on the database. This is more permission than most users should have.

When you are just getting started, you can assign some general categories of permissions by using the built-in fixed database roles. For example, the db_datareader fixed database role can read all tables in the database, but make no changes. Grant membership in a fixed database role by using the ALTER ROLE statement. The following example add the user Jerry to the db_datareader fixed database role.

USE AdventureWorks2014;  

GO  

ALTER ROLE db_datareader ADD MEMBER Jerry;  

For a list of the fixed database roles, see Database-Level Roles.

Later, when you are ready to configure more precise access to your data (highly recommended), create your own user-defined database roles using CREATE ROLE statement. Then assign specific granular permissions to you custom roles.

For example, the following statements create a database role named Sales, grants the Sales group the ability to see, update, and delete rows from the Orders table, and then adds the user Jerry to the Sales role.

CREATE ROLE Sales;  

GRANT SELECT ON Object::Sales TO Orders;  

GRANT UPDATE ON Object::Sales TO Orders;  

GRANT DELETE ON Object::Sales TO Orders;  

ALTER ROLE Sales ADD MEMBER Jerry;  

Configure row-level security

Row-Level Security enables you to restrict access to rows in a database based on the user executing a query. This feature is useful for scenarios like ensuring that customers can only access their own data or that workers can only access data that is pertinent to their department.

The following steps walk through setting up two Users with different row-level access to the Sales.SalesOrderHeader table.

Create two user accounts to test the row level security:   

USE AdventureWorks2014;  

GO  

CREATE USER Manager WITHOUT LOGIN;    

CREATE USER SalesPerson280 WITHOUT LOGIN;   

Grant read access on the Sales.SalesOrderHeader table to both users:   

GRANT SELECT ON Sales.SalesOrderHeader TO Manager;     

GRANT SELECT ON Sales.SalesOrderHeader TO SalesPerson280;

Create a new schema and inline table-valued function. The function returns 1 when a row in the SalesPersonID column matches the ID of a SalesPerson login or if the user executing the query is the Manager user.

CREATE SCHEMA Security;  

GO  

CREATE FUNCTION Security.fn_securitypredicate(@SalesPersonID AS int)    

    RETURNS TABLE  

WITH SCHEMABINDING  

AS    

   RETURN SELECT 1 AS fn_securitypredicate_result   

WHERE (‘SalesPerson’ + CAST(@SalesPersonId as VARCHAR(16)) = USER_NAME())    

    OR (USER_NAME() = ‘Manager’);   

Create a security policy adding the function as both a filter and a block predicate on the table:

CREATE SECURITY POLICY SalesFilter  

ADD FILTER PREDICATE Security.fn_securitypredicate(SalesPersonID)   

  ON Sales.SalesOrderHeader,  

ADD BLOCK PREDICATE Security.fn_securitypredicate(SalesPersonID)   

  ON Sales.SalesOrderHeader  

WITH (STATE = ON);  

Execute the following to query the SalesOrderHeader table as each user. Verify that SalesPerson280 only sees the 95 rows from their own sales and that the Manager can see all the rows in the table.

EXECUTE AS USER = ‘SalesPerson280’;  

SELECT * FROM Sales.SalesOrderHeader;   

REVERT;

EXECUTE AS USER = ‘Manager’;  

SELECT * FROM Sales.SalesOrderHeader;  

REVERT;  

Alter the security policy to disable the policy. Now both users can access all rows.

ALTER SECURITY POLICY SalesFilter  

WITH (STATE = OFF);   

Enable dynamic data masking

Dynamic Data Masking enables you to limit the exposure of sensitive data to users of an application by fully or partially masking certain columns.

Use an ALTER TABLE statement to add a masking function to the EmailAddress column in the Person.EmailAddress table:

USE AdventureWorks2014;

GO

ALTER TABLE Person.EmailAddress    

ALTER COLUMN EmailAddress   

ADD MASKED WITH (FUNCTION = ’email()’);

Create a new user TestUser with SELECT permission on the table, then execute a query as TestUser to view the masked data:

CREATE USER TestUser WITHOUT LOGIN;  

GRANT SELECT ON Person.EmailAddress TO TestUser;   

EXECUTE AS USER = ‘TestUser’;  

SELECT EmailAddressID, EmailAddress FROM Person.EmailAddress;      

REVERT;   

Enable Transparent Data Encryption

One threat to your database is the risk that someone will steal the database files off of your hard-drive. This could happen with an intrusion that gets elevated access to your system, through the actions of a problem employee, or by theft of the computer containing the files (such as a laptop).

Transparent Data Encryption (TDE) encrypts the data files as they are stored on the hard drive. The master database of the SQL Server database engine has the encryption key, so that the database engine can manipulate the data. The database files cannot be read without access to the key. High-level administrators can manage, backup, and recreate the key, so the database can be moved, but only by selected people. When TDE is configured, the tempdb database is also automatically encrypted.

Since the Database Engine can read the data, Transparent Data Encryption does not protect against unauthorized access by administrators of the computer who can directly read memory, or access SQL Server through an administrator account.

Configure TDE

  • Create a master key
  • Create or obtain a certificate protected by the master key
  • Create a database encryption key and protect it by the certificate
  • Set the database to use encryption

Configuring TDE requires CONTROL permission on the master database and CONTROL permission on the user database. Typically an administrator configures TDE.

The following example illustrates encrypting and decrypting the AdventureWorks2014 database using a certificate installed on the server named MyServerCert.

USE master; 

GO 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘**********’; 

GO 

CREATE CERTIFICATE MyServerCert WITH SUBJECT = ‘My Database Encryption Key Certificate’; 

GO 

USE AdventureWorks2014;  

GO

CREATE DATABASE ENCRYPTION KEY 

WITH ALGORITHM = AES_256 

ENCRYPTION BY SERVER CERTIFICATE MyServerCert; 

GO

ALTER DATABASE AdventureWorks2014 

SET ENCRYPTION ON;  

To remove TDE, execute ALTER DATABASE AdventureWorks2014 SET ENCRYPTION OFF;

The encryption and decryption operations are scheduled on background threads by SQL Server. You can view the status of these operations using the catalog views and dynamic management views in the list that appears later in this topic.

Configure backup encryption

SQL Server has the ability to encrypt the data while creating a backup. By specifying the encryption algorithm and the encryptor (a certificate or asymmetric key) when creating a backup, you can create an encrypted backup file.    

The following example creates a certificate, and then creates a backup protected by the certificate.

USE master;  

GO  

CREATE CERTIFICATE BackupEncryptCert  

   WITH SUBJECT = ‘Database backups’;  

GO

BACKUP DATABASE [AdventureWorks2014]  

TO DISK = N’/var/opt/mssql/backups/AdventureWorks2014.bak’ 

WITH 

  COMPRESSION, 

  ENCRYPTION  

   ( 

   ALGORITHM = AES_256,  

   SERVER CERTIFICATE = BackupEncryptCert 

   ), 

  STATS = 10 

GO 

Active Directory authentication for SQL Server on Linux

This provides an overview of Active Directory (AD) authentication for SQL Server on Linux. AD authentication is also known as Integrated authentication in SQL Server.

AD authentication overview

AD authentication enables domain-joined clients on either Windows or Linux to authenticate to SQL Server using their domain credentials and the Kerberos protocol.

AD Authentication has the following advantages over SQL Server Authentication:

  • Users authenticate via single sign-on, without being prompted for a password.
  • By creating logins for AD groups, you can manage access and permissions in SQL Server using AD group memberships.
  • Each user has a single identity across your organization, so you don’t have to keep track of which SQL Server logins correspond to which people.
  • AD enables you to enforce a centralized password policy across your organization.

Configuration steps

In order to use Active Directory authentication, you must have an AD Domain Controller (Windows) on your network.

Known issues

  • At this time, the only authentication method supported for database mirroring endpoint is CERTIFICATE. WINDOWS authentication method will be enabled in a future release.
  • SQL Server on Linux does not support NTLM protocol for remote connections. Local connection may work using NTLM.

Join SQL Server on a Linux host to an Active Directory domain

This provides general guidance on how to join a SQL Server Linux host machine to an Active Directory (AD) domain. There are two methods: use a built-in SSSD package or use third-party Active Directory providers. Examples of third-party domain join products are PowerBroker Identity Services (PBIS), One Identity, and Centrify. This guide includes steps to check your Active Directory configuration. However, it is not intended to provide instructions on how to join a machine to a domain when using third-party utilities.

Before you configure Active Directory authentication, you need to set up an Active Directory domain controller, Windows, on your network. Then join your SQL Server on Linux host to an Active Directory domain.

Check the connection to a domain controller

Check that you can contact the domain controller with both the short and fully qualified names of the domain:

Bash

ping contoso

ping contoso.com

If either of these name checks fail, update your domain search list. The following sections provide instructions for Ubuntu, Red Hat Enterprise Linux (RHEL), and SUSE Linux Enterprise Server (SLES) respectively.

Ubuntu 16.04

  1. Edit the /etc/network/interfaces file, so that your Active Directory domain is in the domain search list:

/etc/network/interfacesCopy

# The primary network interface

auto eth0

iface eth0 inet dhcp

dns-nameservers **<AD domain controller IP address>**

dns-search **<AD domain name>**

  • After editing this file, restart the network service:

Bash

sudo ifdown eth0 && sudo ifup eth0

  • Next, check that your /etc/resolv.conf file contains a line like the following example:

/etc/resolv.conf

search contoso.com com 

nameserver **<AD domain controller IP address>**

RHEL 7.x

  1. Edit the /etc/sysconfig/network-scripts/ifcfg-eth0 file, so that your Active Directory domain is in the domain search list. Or edit another interface config file as appropriate:

/etc/sysconfig/network-scripts/ifcfg-eth0

PEERDNS=no

DNS1=**<AD domain controller IP address>**

DOMAIN=”contoso.com com”

  • After editing this file, restart the network service:

Bash

sudo systemctl restart network

  • Now check that your /etc/resolv.conf file contains a line like the following example:

/etc/resolv.conf

search contoso.com com 

nameserver **<AD domain controller IP address>**

  • If you still cannot ping the domain controller, find the fully qualified domain name and IP address of the domain controller. An example domain name is DC1.CONTOSO.COM. Add the following entry to /etc/hosts:

/etc/hosts

**<IP address>** DC1.CONTOSO.COM CONTOSO.COM CONTOSO

SLES 12

  1. Edit the /etc/sysconfig/network/config file, so that your Active Directory domain controller IP is used for DNS queries and your Active Directory domain is in the domain search list:

/etc/sysconfig/network/config

NETCONFIG_DNS_STATIC_SEARCHLIST=””

NETCONFIG_DNS_STATIC_SERVERS=”**<AD domain controller IP address>**”

  • After editing this file, restart the network service:

Bash

sudo systemctl restart network

  • Next, check that your /etc/resolv.conf file contains a line like the following example:

/etc/resolv.conf

search contoso.com com

nameserver **<AD domain controller IP address>**

Join to the AD domain

After the basic configuration and connectivity with domain controller is verified, there are two options for joining a SQL Server Linux host machine with Active Directory domain controller:

Option 1: Use SSSD package to join AD domain

This method joins the SQL Server host to an AD domain using realmd and sssd packages.

Use the following steps to join a SQL Server host to an Active Directory domain:

  1. Use realmd to join your host machine to your AD Domain. You must first install both the realmd and Kerberos client packages on the SQL Server host machine using your Linux distribution’s package manager:

RHEL:

base

sudo yum install realmd krb5-workstation

SUSE:

Bash

sudo zypper install realmd krb5-client

Ubuntu:

Bash

sudo apt-get install realmd krb5-user software-properties-common python-software-properties packagekit

  • If the Kerberos client package installation prompts you for a realm name, enter your domain name in uppercase.
  • After you confirm that your DNS is configured properly, join the domain by running the following command. You must authenticate using an AD account that has sufficient privileges in AD to join a new machine to the domain. This command creates a new computer account in AD, creates the /etc/krb5.keytab host keytab file, configures the domain in /etc/sssd/sssd.conf, and updates /etc/krb5.conf.

Bash

sudo realm join contoso.com -U ‘[email protected]’ -v

You should see the message, Successfully enrolled machine in realm.

The following table lists some error messages that you could receive and suggestions on resolving them:

Error messageRecommendation
Necessary packages are not installedInstall those packages using your Linux distribution’s package manager before running the realm join command again.
Insufficient permissions to join the domainCheck with a domain administrator that you have sufficient permissions to join Linux machines to your domain.
KDC reply did not match expectationsYou may not have specified the correct realm name for the user. Realm names are case-sensitive, usually uppercase, and can be identified with the command realm discover contoso.com.

SQL Server uses SSSD and NSS for mapping user accounts and groups to security identifiers (SIDs). SSSD must be configured and running for SQL Server to create AD logins successfully. realmd usually does this automatically as part of joining the domain, but in some cases, you must do this separately.

  • Verify that you can now gather information about a user from the domain, and that you can acquire a Kerberos ticket as that user. The following example uses id, kinit, and klist commands for this.

Bash

id [email protected]

uid=1348601103([email protected]) gid=1348600513(domain [email protected]) groups=1348600513(domain [email protected])

kinit [email protected]

Password for [email protected]:

klist

Ticket cache: FILE:/tmp/krb5cc_1000

Default principal: [email protected]

Option 2: Use third-party openldap provider utilities

You can use third-party utilities such as PBIS, VAS, or Centrify. This article does not cover steps for each individual utility. You must first use one of these utilities to join the Linux host for SQL Server to the domain before continuing forward.

SQL Server does not use third-party integrator’s code or library for any AD-related queries. SQL Server always queries AD using openldap library calls directly in this setup. The third-party integrators are only used to join the Linux host to AD domain, and SQL Server does not have any direct communication with these utilities.

Verify that your /etc/krb5.conf is configured correctly. For most third-party Active Directory providers, this configuration is done automatically. However, check /etc/krb5.conf for the following values to prevent any future issues:

/etc/krb5.conf

[libdefaults]

default_realm = CONTOSO.COM

[realms]

CONTOSO.COM = {

}

[domain_realm]

contoso.com = CONTOSO.COM

.contoso.com = CONTOSO.COM

Check that the reverse DNS is properly configured

The following command should return the fully qualified domain name (FQDN) of the host that runs SQL Server. An example is SqlHost.contoso.com.

Bash

host **<IP address of SQL Server host>**

The output of this command should be similar to **<reversed IP address>**.in-addr.arpa domain name pointer SqlHost.contoso.com. If this command does not return your host’s FQDN, or if the FQDN is incorrect, add a reverse DNS entry for your SQL Server on Linux host to your DNS server.

Encrypting Connections to SQL Server on Linux

SQL Server on Linux can use Transport Layer Security (TLS) to encrypt data that is transmitted across a network between a client application and an instance of SQL Server. SQL Server supports the same TLS protocols on both Windows and Linux: TLS 1.2, 1.1, and 1.0. However, the steps to configure TLS are specific to the operating system on which SQL Server is running.

Requirements for Certificates

Before getting started, you need to make sure your certificates follow these requirements:

  • The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
  • The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
  • The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate’s key usage property (KEY_USAGE) also includes key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).
  • The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. Note: Wild Card Certificates are supported.

Configuring the OpenSSL Libraries for Use (Optional)

You can create symbolic links in the /opt/mssql/lib/ directory that reference which libcrypto.so and libssl.so libraries should be used for encryption. This is useful if you want to force SQL Server to use a specific version of OpenSSL other than the default provided by the system. If these symbolic links are not present, SQL Server will load the default configured OpenSSL libraries on the system.

These symbolic links should be named libcrypto.so and libssl.so and placed in the /opt/mssql/lib/ directory.

Overview

TLS is used to encrypt connections from a client application to SQL Server. When configured correctly, TLS provides both privacy and data integrity for communications between the client and the server. TLS connections can either be client initiated or server initiated.

Client Initiated Encryption

  • Generate certificate (/CN should match your SQL Server host fully qualified domain name)

Bash

openssl req -x509 -nodes -newkey rsa:2048 -subj ‘/CN=mssql.contoso.com’ -keyout mssql.key -out mssql.pem -days 365

sudo chown mssql:mssql mssql.pem mssql.key

sudo chmod 600 mssql.pem mssql.key  

sudo mv mssql.pem /etc/ssl/certs/

sudo mv mssql.key /etc/ssl/private/

  • Configure SQL Server

Bash

systemctl stop mssql-server

cat /var/opt/mssql/mssql.conf

sudo /opt/mssql/bin/mssql-conf set network.tlscert /etc/ssl/certs/mssql.pem

sudo /opt/mssql/bin/mssql-conf set network.tlskey /etc/ssl/private/mssql.key

sudo /opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2

sudo /opt/mssql/bin/mssql-conf set network.forceencryption 0

  • Register the certificate on your client machine (Windows, Linux, or macOS)
    • If you are using CA signed certificate, you have to copy the Certificate Authority (CA) certificate instead of the user certificate to the client machine.
    • If you are using the self-signed certificate, just copy the .pem file to the following folders respective to distribution and execute the commands to enable them
      • Ubuntu: Copy cert to /usr/share/ca-certificates/, rename its extension to .crt, and use dpkg-reconfigure ca-certificates to enable it as system CA certificate.
      • RHEL: Copy cert to /etc/pki/ca-trust/source/anchors/ and use update-ca-trust to enable it as system CA certificate.
      • SUSE: Copy cert to /usr/share/pki/trust/anchors/ and use update-ca-certificates to enable it as system CA certificate.
      • Windows: Import the .pem file as a certificate under current user -> trusted root certification authorities -> certificates
      • macOS:
        • Copy the cert to /usr/local/etc/openssl/certs
        • Run the following command to get the hash value: /usr/local/Cellar/openssl/1.0.2l/openssl x509 -hash -in mssql.pem -noout
        • Rename the cert to value. For example: mv mssql.pem dc2dd900.0. Make sure dc2dd900.0 is in /usr/local/etc/openssl/certs
  • Example connection strings
    • SQL Server Management Studio
  • SQLCMD

sqlcmd -S <sqlhostname> -N -U sa -P ‘<YourPassword>’

  • ADO.NET

“Encrypt=True; TrustServerCertificate=False;”

  • ODBC

“Encrypt=Yes; TrustServerCertificate=no;”

  • JDBC

“encrypt=true; trustServerCertificate=false;”

Server Initiated Encryption

  • Generate certificate (/CN should match your SQL Server host fully-qualified domain name)

Bash

openssl req -x509 -nodes -newkey rsa:2048 -subj ‘/CN=mssql.contoso.com’ -keyout mssql.key -out mssql.pem -days 365

sudo chown mssql:mssql mssql.pem mssql.key

sudo chmod 600 mssql.pem mssql.key  

sudo mv mssql.pem /etc/ssl/certs/

sudo mv mssql.key /etc/ssl/private/

  • Configure SQL Server

Bash

systemctl stop mssql-server

cat /var/opt/mssql/mssql.conf

sudo /opt/mssql/bin/mssql-conf set network.tlscert /etc/ssl/certs/mssql.pem

sudo /opt/mssql/bin/mssql-conf set network.tlskey /etc/ssl/private/mssql.key

sudo /opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2

sudo /opt/mssql/bin/mssql-conf set network.forceencryption 1

  • Example connection strings
    • SQLCMD

sqlcmd -S <sqlhostname> -U sa -P ‘<YourPassword>’

  • ADO.NET

“Encrypt=False; TrustServerCertificate=False;”

  • ODBC

“Encrypt=no; TrustServerCertificate=no;”

  • JDBC

“encrypt=false; trustServerCertificate=false;”

Common connection errors

COMMON CONNECTION ERRORS
Error messageFix
The certificate chain was issued by an authority that is not trusted.This error occurs when clients are unable to verify the signature on the certificate presented by SQL Server during the TLS handshake. Make sure the client trusts either the SQL Server certificate directly, or the CA which signed the SQL Server certificate.
The target principal name is incorrect.Make sure that Common Name field on SQL Server’s certificate matches the server name specified in the client’s connection string.
An existing connection was forcibly closed by the remote host.This error can occur when the client doesn’t support the TLS protocol version required by SQL Server. For example, if SQL Server is configured to require TLS 1.2, make sure your clients also support the TLS 1.2 protocol.