SQL Server Security

You can improve the protection of your database against malicious users or unauthorised access with just a few simple steps. This document will cover the following items

• Setting up server-level firewall rules for your server in the Azure Portal
• Connect to your database using a secure connection string
• Manage user access
• Protect your data with encryption
• Enable SQL database auditing
• Enable SQL database threat detection

Create Server-level Firewall in Azure Portal

SQL databases are protected by a firewall in Azure. By default, any connections to the server and the databases contained inside the server are rejected, except for any Azure service connections. The most secure configuration is to set the option “Allow access to Azure Services’ to OFF. If you do, then need to connect to the database from an Azure VM or cloud service you should create a reserved IP address and allow that IP address access.

Follow the below steps to allow connections from a specific IP address.

1. From the left-hand menu, select the SQL databases option, and then in the new window select the database you would like to configure the firewall rule for.
2. In the overview window click Set server firewall on the toolbar to open the Firewall settings page.
   

3. If you are connected using the machine you want access from then you can use the Add client IP button in the toolbar, and then press save. If you want to add a different IP address you can fill in the rule manually and then press save.

4. When you press save it will pop up a window to state that the rule is being applied and once complete will show a message confirming the rule has been applied successfully.

Connecting Application to your Database using a Secure Connection

To ensure a secure and encrypted connection between your application and the SQL Database, the connection string must be configured to request an encrypted connection and not trust the server certificate. Azure provides the correctly configured connection strings for your database. The strings available are:

• ADO.NET
• JDBC
• ODBC
• PHP
  

To view these connection strings, please follow the below steps:

1. From the SQL Database overview screen, select the Show database connection strings located on the right hand side of the overview screen.

2. When the connection strings window opens, you will see a tabbed window which, depending on the tab selected, will show you the relevant connection strings.

Creating Database Users

To create a database user, you need to connect to your Sever using SSMS with the admin username and password you created at launch. When logging into your SQL Server, you will need to select SQL Server authentication as the authentication method.

Once logged in, follow the below steps to create a new user.

1. In the Object Explorer, right click the database you wish to add a new user to and click on New Query.
2. In the query window, enter the following query:
     CREATE USER ApplicationUser WITH PASSWORD = ‘YourStrongPassword1’
3. On the toolbar, click Execute to create the user.
4. The user will automatically receive permissions to connect to the database but won’t have permissions to read or write the data. To grant these permissions, you can use the following two queries:
     ALTER ROLE db_datareader ADD MEMBER ApplicationUser
     ALTER ROLE db_datareader ADD MEMBER ApplicationUser
   

Protect your Data with Encryption

Azure SQL Databases use Transparent Data Encryption (TDE) to encrypt your data and doesn’t require any changes to the application that is accessing the data. By default, encryption is turned on. You can validate that encryption is turned on or, if you want to, turn it off by following the steps below:

1. From the left-hand menu, select the SQL databases option, and then in the new window select the database you would like to check the encryption settings for.
2. In the sub menu on the left-hand side of the database overview, in the security section select Transparent data encryption. You will then be taken to the TDE page which will show if Data encryption is turned on or not.

Enabling SQL Database Auditing

Database auditing tracks database events and writes them to a log window in your Azure Storage account. Auditing a database can help you maintain regulatory compliance, understand any activity that has occurred on the database and gain insight into discrepancies and anomalies that could indicate potential security violations.

In order to turn on auditing, you will need to have a storage account set up on azure or create a new one at the time of switching on auditing.

The following steps will show you how to enable auditing.

1. From the left-hand menu, select the SQL Database option, then in the new window select the database that you wish to turn auditing on for.
2. In the sub-menu on the left-hand side of the database overview window, select Auditing which can be found in the Security section. This will open the auditing window.
3. You can use the switch, highlighted in the below screenshot, to turn auditing on or off. When auditing is turned on, you will then be able to select the storage option for the auditing log.

SQL Database Threat Detection

Azure offers an additional layer of security on a SQL Server. When turned, it is activated so all databases located on that server and costs $15 per server per month. That regardless of the server having one database or twenty. Threat detection allows customers to detect and respond to potential threats as they occur, by providing alerts on anomalous activities.

Follow the below steps to turn on threat detection:

1. From the let-hand menu, select the SQL Database option, then in the new window select the database that you wish to turn threat detection on for.
2. In the sub-menu on the left-hand side of the database overview window, select Advanced Threat Protection, we can be found in the security section. This will open the threat protection window.
3. To enable threat protection, press the button labelled Enable Advanced Threat Protection on the server. This is free for the first 60 days but will then be billed at $15 dollars a month per server.