Is my Azure SQL Database connection secure?

Security is a very important consideration when working in a cloud environment. Of course, each cloud service announces that the client who is storing data within that environment can expect high levels of secure encryption. But what about the different pathways between the cloud environment and your PC?

This post gives you some details about security questions when transferring your data from Azure SQL Database via an internet connection.

Secure data in transit

Let’s take a look at a scenario where you are using Azure Database located in the cloud environment, and you are working with local SQL Server Management Studio connected to the database via Internet.

When you imagine such a configuration, one of the concerns is data security on the pathway between the database and your laptop.

Is that connection encrypted?

Let’s check by running a test!

It is prerequisite to be running an Azure SQL Database. You can create one by using this manual.

When you have created the database, open SQL Server Management Studio and provide the server name, login and password:

Click connect to login.

Now, you are connected, so let’s check if that connection is secure. Write this code in the query window:

 SELECT *
 FROM sys.dm_exec_connections
 WHERE session_id = @@SPID 

Execute it and check the results tab:

The encrypt_option = true means that all data transferred is encrypted for your connection. This is positive, but lets check out a few more options.

A second test

Close your SQL Server Management Studio, open the login window again and click on the options button:

There are two specific options here: Encrypt connection and Trust server certificate.

Switch off Encrypt connection option, connect and write the provided code in the query window:

 SELECT *
 FROM sys.dm_exec_connections
 WHERE session_id = @@SPID 

Execute it and check the results:

Encrypt option is still true, which is surprising!

We had switched off Encrypt option, so surely one could expect to get a FALSE reading here? It appears that our entry has been overwritten.

Azure SQL Database enforces encryption (SSL/TLS) at all times for all connections

This is quite different behaviour than in SQL Server.

When you connect to the on-premise SQL with Encrypt connection option disabled and run the code provided earlier, the results are different. I will connect to SQL Server installed on my local laptop to show the results:

As you can see , the Encrypt connection option is switched off. Now I can use this SQL statement to check if  the data in securely encrypted:

 SELECT *
 FROM sys.dm_exec_connections
 WHERE session_id = @@SPID 

This time the encrypt_option is false, which means that disabling the Encrypt connection option in SQL Server Management Studio works as expected with my local SQL Server instance and the connection is not secure.

What about Trust server certificate option?

Azure SQL Databases and on-premise SQL Servers always encrypts network packets associated with logging in. However, there is a slight difference between them.

In case of SQL Server logging in network packets can be encrypted using self-signed certificate, which is not 100% secure, or a third-party certificate requested from an Certificate Authority. Third-party certificate is the best option but requesting and configuring it on your local servers can be time consuming. If you are interested in the details, take a look at this instruction: https://support.microsoft.com/en-us/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-mi

If you are running Azure Database, you don’t need to worry as Azure SQL Database is already configured with necessary certification!

Summary

Security is a very wide and important consideration especially if you are involved in designing hybrid environments that uses cloud and local resources.

I hope you enjoyed this post about maintaining security when transferring data from Azure SQL Database over an internet connection. It’s good to know that regardless of the client’s settings, it is always encrypted.

If you have some additional questions, do feel free to ask or make a comment, maybe it will be a good starting point or greater discussion!