NML

Production Azure SQL Database best practices

Posted on 03 September 2019
Charl Marais

Maintenance

Auditing

All production Azure SQL Servers and Azure SQL Database must have auditing enabled. This is of the highest importance, and failure to implement this will almost certainly come at a high cost.

To configure audit logs for an Azure SQL Server:

  1. In the Azure AD portal, browse to the Azure SQL Server
  2. Open the Auditing blade
  3. Toggle Auditing to ON
  4. Select and configure at least one appropriate destination
    1. Storage is the cheapest, but slowest to interrogate. It is recommended to use blob storage for most scenarios.
    2. Log Analytics could be very expensive if a database has high operation volumes.
    3. Event Hub is only useful if you connect some processing system to the streamed logs
  5. Click Save

To configure audit logs for an Azure SQL Database:

  1. In the Azure AD portal, browse to the Azure SQL Database
  2. Open the Auditing blade
  3. Toggle Auditing to ON
  4. Select and configure at least one appropriate destination
    1. Storage is the cheapest, but slowest to interrogate. It is recommended to use blob storage for most scenarios.
    2. Log Analytics could be very expensive if a database has high operation volumes.
    3. Event Hub is only useful if you connect some processing system to the streamed logs
  5. Click Save

To view the audit logs of an Azure SQL Databases:

  1. In the Azure AD portal, browse to the Azure SQL Database
  2. Open the Auditing blade
  3. Click View audit logs
  4. Select the Audit source for server vs database

Backups

All production Azure SQL Databases must have backups configured. Azure makes this very easy.

You do not generally need to set other long term retention options, unless you have tables with data that gets cleared as part of operational and may need to be able to restore a backup to get to historic data that no longer exists on the current database, for example audit log tables.

To configure backups for a database:

  1. In the Azure AD portal, browse to the Azure SQL Server
  2. Open the Manage Backups blade
  3. By default, you will have at least Point In Time Recovery (PITR) backups already configured on all databases deployed to the Azure SQL server
  4. Check the database(s) you want to configure additional backups for
  5. Click Configure retention
  6. In the panel, configure the appropriate settings
    1. NB: For production database, set PITR to 35 days if the database pricing tier allows it
  7. Click Apply
  8. Click Yes on the confirmation box

Restoring backups

To restore a backup of an Azure SQL Database to an Azure SQL Server:

  1. In the Azure AD portal, browse to the Azure SQL Database
  2. Click Restore on the Overview blade
  3. Select the backup source
    1. Generally you will want to restore a Point-in-time backup, as it allows you to roll back the database to a very specific time (up to the second). When you know when something went wrong, say via audit logs, this is a life saving feature.
  4. Leave the provided name, unless you have a good reason to change it
  5. Based on the backups source, set or choose the backup details to restore
  6. Choose the appropriate pricing tier. By default is should be set to the same tier as the current database.
  7. Click Ok

The restore process can take quite a bit of time, depending on the size of the database, and selected backup source. Once the backup is complete, you can either update your application connection strings to refer to the restored database, or rename the database with an AD Admin User in SQL Management Studio.

NB: It is important that you delete databases that are no longer in use, as Azure will bill for the database even if it is not in use.

Security

Use managed identities

Always use managed identities for connecting Azure resources to the Azure SQL Database whenever possible. To do that you need an Azure AD security user group, to which you must add the managed identities to of the resources that need database access. Always use a different security user group for you production databases, and if possible, multiple user groups for different databases (if you have more that one).

NB: You will need to keep and Active Directory Admin set on the Azure SQL Service in order for the managed identities to access the database.

To configure managed identity access to an Azure SQL Database:

  1. Create an Azure AD security user group in the form of <DBNAME>-database-managed-identities.
  2. Add the resource managed identities to the user group. This can be done via the Azure Portal UI, or with the following Azure CLI command: az ad group member add --group <DBNAME>-database-managed-identities --member-id <SERVICE_PRINCIPAL_OBJECTID>
  3. Login to the Azure SQL Database with a Active Directory Admin user (needs to be setup in Azure Portal) and run the following SQL commands on the database (NOT master!):

CREATE USER [<MANAGED_IDENTITY_SECURITY_GROUP>] FROM EXTERNAL PROVIDER;

ALTER ROLE db_datareader ADD MEMBER [<MANAGED_IDENTITY_SECURITY_GROUP>];

ALTER ROLE db_datawriter ADD MEMBER [<MANAGED_IDENTITY_SECURITY_GROUP>];

GO

Use Azure Active Directory user accounts

Users should have explicit access to databases where they require, for auditing purposes. They should only have db_datawriter access if they need to modify data. Prefer db_datareader only, or none.

CREATE USER [<USERNAME>@nml.co.za] FROM EXTERNAL PROVIDER;

ALTER ROLE db_datareader ADD MEMBER [<USERNAME>@nml.co.za];

ALTER ROLE db_datawriter ADD MEMBER [<USERNAME>@nml.co.za];

GO

Use a read only user for support

AZURE NOW ALLOWS ADD USER ACCOUNTS. THE FOLLOWING IS NOT LONGER RECOMMENDED

Production or restricted databases should have a read only database user for support investigations. Data should never ever be manually updated in production database, but only as part of the standard dev-ops release cycle. As a very last resort, with the right approvals and checks, an Azure Active Directory Admin SQL user can resolve issues.

To create a SQL server read only user:

  1. Login to the Azure SQL Database with an Active Directory Admin user (must be setup in Azure Portal) and run the following SQL command on the master database: create login [<LOGIN_NAME>] WITH PASSWORD = '<PASSWORD>'. Ensure that the password adheres best practice for passwords (min 32 character length, randomized alpha & numeric characters, etc)
  2. Switch to the target database and run the following SQL command: CREATE USER [<USER_NAME>] FROM LOGIN [<LOGIN_NAME>];
  3. Set read only access by running the following SQL command: EXEC sp_addrolemember 'db_datareader', '<USER_NAME>'
  4. Store the password in the production or restricted Azure Key Vault associated with the Azure SQL Database

Active Directory admin

If you do not use managed identities to Azure services to access you Azure SQL Database, Active Directory Admins must be removed.

  1. Login to to Azure Portal
  2. Browse to the SQL Server where the Active Directory Admin has been added
  3. Open the Active Directory Admin blade
  4. Click Remove Admin
  5. Click Save
  6. Click Yes to confirm

Firewalls and virtual networks

One of the most important considerations in ensuring a secure Azure SQL Server, is to know what connnections are allowed and from where. In Azure you can configure a virtual firewall that blocks connections for unknow sources. Except in very, very rare cases, all production Azure SQL Server deployments must have firewall settings configured.

To configure a firewall for Azure SQL Server:

  1. In the Azure AD portal, browse to the Azure SQL Server
  2. Open the Firewalls and virtual networks blade
  3. Set Allow access to Azure Services to OFF. This setting allows any Azure Service on any subnet globally to request connections to you Azure SQL Server. For production database, you can use https://resources.azure.com/ to find IP address used by your Azure services, and configured the manually in step 4 below.
  4. Specifiy any non Azure IP ranges that should have access. Clearly name each to anybody looking at the configuration understands who/what the rule is for. For example, to allow connection from the NML offices, add a rule like NML | 41.71.68.114 | 41.71.68.114.
    1. Generally you should be more specific and set the Start IP and the End IP to the same value. Ranges should only be allowed on exception.
  5. If you have Azure App Service and Azure Virtual Machines that participate in an Azure Virtual Network, you can add the virtual network that are allowed to connect to that Azure SQL Server
    1. Do not add any development or testing virtual networks to production Azure SQL Server deployments

Advanced Data Security

It is highly recommended that all production Azure SQL Server and Azure SQL Database deployments must have Advanced Data Security enable. It monitor, detect and report any malicious and suspicious activity.

To enable Advanced Data Security on Azure SQL Server:

  1. In the Azure AD portal, browse to the Azure SQL Server
  2. Open the Advanced Data Security blade
  3. Toggle to ON
  4. Configure settings for Vulnerability Assessment Settings
  5. Set Periodic recurring scans to ON and configure an email address (generally a team distribution list)
  6. Click Save

Adhoc Scripts best practice

This is the process that must be followed when executing scripts on all databases. This applies to all environments

NEVER EVER run a DELETE script against any database.

BEGIN TRY
    BEGIN TRAN
        -- Get the name of the user running this script

        DECLARE @username VARCHAR(MAX) 
        SET @username = (SELECT ORIGINAL_LOGIN())

	      -- YOUR UPDATE statement GOES HERE
        
    COMMIT TRAN
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

	DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
        DECLARE @ErrorState INT = ERROR_STATE()

    -- Use RAISERROR inside the CATCH block to return error  
    -- information about the original error that caused  
    -- execution to jump to the CATCH block.  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
An error has occurred. This application may no longer respond until reloaded. Reload