Allow only encrypted connection to Azure SQL Server

Jan Vávra 251 Reputation points
2020-12-18T16:01:25.837+00:00

Hello,
is there a setup on Azure Sql Server thath permits only enrypted connection ?
I was horrified to find out my SSMS is using an unecrypted connection from my laptop to azure because the checkbox value is remembered from previous connection to a local sql server.

Is it a good practice to connect to Azure Sql Server via the Internet with only source ips restricted ?
I've tried a ssh tunnels through a Linux VM in azure but it is not working.
Should I rather setup a VPN server on a VM machine ? Do you recommend any VPN server software on Linux that would be connected from Windows desktops ?

Is there a best practice documentation pointing this issue? Eg. Use only Virtual Private Network to connect to Azure SQL and VPN.

Jan.

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,756 questions
Accepted answer
  1. Alberto Morillo 32,891 Reputation points MVP
    2020-12-29T15:06:45.617+00:00

    All connections coming from SSMS to Azure SQL are encrypted even if the you don't set "Encrypt connection" setting on. Azuire SQL Database only allows encrypted connections.

    When a client first attempts a connection to SQL Azure, it sends an initial connection request. Consider this a "pre-pre-connection" request. At this point the client does not know if SSL/Encryption is required and waits an answer from SQL Server/SQL Azure to determine if SSL is indeed required throughout the session (not just the login sequence, the entire connection session). A bit is set on the response indicating so. Then the client library disconnects and reconnects armed with this information.

    When you set "Encrypt connection" setting on SSMS you avoid the "pre-pre-connection", you are preventing any proxy from turning off the encryption bit on the client side of the proxy, this way attacks like man-in-the-middle attack are avoided.

    When secure connections are needed, please enable "Encrypt connection" setting.

    You can run the following command to verify all connections to Azure SQL are encrypted: 

    select * from sys.dm_exec_connections.
    2 people found this answer helpful.

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Cris Zhan-MSFT 6,606 Reputation points
    2020-12-21T08:26:43.953+00:00

    Hi @Jan Vávra ,

    Also check the doc: An overview of Azure SQL Database and SQL Managed Instance security capabilities

    SQL Database, SQL Managed Instance, and Azure Synapse Analytics secure customer data by encrypting data in motion with Transport Layer Security (TLS).

    SQL Database, SQL Managed Instance, and Azure Synapse Analytics enforce encryption (SSL/TLS) at all times for all connections. This ensures all data is encrypted "in transit" between the client and server irrespective of the setting of Encrypt or TrustServerCertificate in the connection string.

    Note that some non-Microsoft drivers may not use TLS by default or rely on an older version of TLS (<1.2) in order to function. In this case the server still allows you to connect to your database. However, we recommend that you evaluate the security risks of allowing such drivers and application to connect to SQL Database, especially if you store sensitive data.

    3 people found this answer helpful.
  2. David Browne - msft 3,766 Reputation points
    2020-12-18T16:26:08.34+00:00

    SSMS is using an unecrypted connection from my laptop to azure

    SSMS (and all programs using Microsoft drivers) always connects using encryption, even if you don't explicitly ask for an encrypted connection.

    See:

    TLS considerations for database connectivity

    Transport Layer Security (TLS) is used by all drivers that Microsoft supplies or supports for connecting to databases in Azure SQL Database or Azure SQL Managed Instance. No special configuration is necessary.

    https://learn.microsoft.com/en-us/azure/azure-sql/database/connect-query-content-reference-guide#tls-considerations-for-database-connectivity

    2 people found this answer helpful.
    0 comments
  3. Alberto Morillo 32,891 Reputation points MVP
    2021-01-04T17:33:58.383+00:00

    @Jan Vávra Please refer to the this documentation to configure encrypted connections to SQL Server instances on-premises and on Azure IaaS VMs.

    Hope this helps.

    1 person found this answer helpful.
    0 comments
  4. Jan Vávra 251 Reputation points
    2021-01-05T08:17:44.233+00:00

    Yes it helped. The whole time I was searching for something that I've just found. A server option to force encryption in Computer Management:

    53535-obrazek.png

    Documented at paragraph Configure server
    https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15
    This way is probably also configured an Azure Database instance.

    In sys.dm_exec_connections there is shown that all connection are with encrypt_option = 'TRUE'

    Note. I had some Windows problem, so I couldn't change Force option to yes, there was only a small grey bar without anything clickable. So I've changed registry key:Počítač\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.VAVRADB\MSSQLServer\SuperSocketNetLib\ForceEncryption to 1.

    Thanks to all.

    1 person found this answer helpful.