Azure SQL Server security first steps

This post is a summary of the Tutorial: Secure a single or pooled database.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-security-tutorial

1. Reset Azure SQL SA Password
2. Create Firewall Rules
3. Configure Azure Active Directory Administrator
4. Create Users in SQL Server Management Studio
5. Implement Advanced Security

Reset Azure SQL SA Password

When you first provision an Azure SQL server instance, you must designate a SQL Server admin login. SQL server creates that account as a login in the master database. This account connects using SQL Server authentication (user name and password). Only one these System Administrator (SA) accounts can exist.

To reset the password for the server admin, go to the Azure portal, click SQL Servers, select the server from the list, and then click Reset Password.

Create Server Firewall Rules

The second most crucial step is to create firewall rules. These allow client applications to connect to the SQL database.

The START IP and END IP can be the same (192.168.0.0 – 192.168.0.0).

If you get the following message:

The server you specified (eax360-dataexportservice.database.windows.net) does not exist in any subscription in user.user@eax360.com. 
Either you have signed in with an incorrect account or your server was removed from all subscriptions in this account. 
Please check your account and try again. (ConnectionDlg)

It means that your IP supplied as a Firewall rule is invalid. Check that an organisation Firewall is not preventing your access.

You can also specify a database-level firewall. Server-level firewalls trickle-down into all the databases, but if you would like apply a firewall to individual databases, then issue the following T-SQL statement:

EXECUTE sp_set_database_firewall_rule N'Example DB Rule','0.0.0.4','0.0.0.4';

Configure Azure Active Directory Administrator

From the settings area of the SQL Server configuration blade, you have the option to set the Active Directory Admin:

This is really important when you need to authenticate using Active Directory. Database users (excluding administrators) cannot be created using the Azure portal. Azure RBAC roles do not propagate to SQL servers, databases, or data warehouses. They are only used to manage Azure resources and do not apply to database permissions.

For example, the SQL Server Contributor role does not grant access to connect to a database or data warehouse. This permission must be granted within the database using T-SQL statements.

Create Users in SQL Server Management Studio

To create SQL Users, you will need to login to SQL Server Management Studio (SSMS) with the Azure Active Directory Admin created in the previous step.

Once logged in, issue the following Query:

CREATE USER [syed.hussain@eax360.com] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [syed.hussain@eax360.com];

This will create a new User and set that as a database owner.

If you receive the error:

Cannot connect to eax360.database.windows.net.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

You need to specify the database that you are connecting to:

Once this is complete, you should be able to logon correctly.

Implement Advanced Security

The final steps are optional but highly recommended. Azure SQL security is constantly evolving but I recommend putting the following in place:

1. Advanced Data Security
2. Auditing
3. Dynamic data masking especially if the database is used for reporting and there are GDPR concerns.
4. Transparent data encryption

For more information on the above visit the official Microsoft documentation: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-security-tutorial