question

JanVavra avatar image
1 Vote"
JanVavra asked DavidBrowne-msft commented

Allow only encrypted connection to Azure SQL Server

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.

sql-server-generalazure-sql-database
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@JanVavra, please let us know if the answers helped you or you need more information on the same. if they helped you can mark them as 'Accept Answer'.

0 Votes 0 ·
AlbertoMorillo avatar image
1 Vote"
AlbertoMorillo answered DavidBrowne-msft commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

How come I have these then?

I'm not sure where these <name-pipes> connections come from in thie Azure SQL server setup. All connections comming from an IP adress is encrypted.
Maybe from app services using private link on "local" subnets?

The column with TRUE/FALSE is the encrypt_option

138024-image.png


0 Votes 0 ·
image.png (39.5 KiB)
DavidBrowne-msft avatar image DavidBrowne-msft PoulChristensen-5014 ·

I see them too. NTLM connections over Named Pipes connected to Master. The original_login_name for them is NT AUTHORITY\SYSTEM or DB31\WF-WOWTAjpVw9Z2HnB Azure SQL Database does not support client connections using either of those options, so these connections are originating on the server hosting the SQL Server instance.

So probably Azure-specific monitoring and management agents.

0 Votes 0 ·
DavidBrowne-msft avatar image
2 Votes"
DavidBrowne-msft answered

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://docs.microsoft.com/en-us/azure/azure-sql/database/connect-query-content-reference-guide#tls-considerations-for-database-connectivity

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
2 Votes"
Criszhan-msft answered Criszhan-msft edited

Hi @JanVavra,

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.







5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JanVavra avatar image
0 Votes"
JanVavra answered

@AlbertoMorillo
Thanks for a reply. Is there an explicit setup on Sql Server to enforce encryption? We also run on premise instances.
Also I've confirmed by Microsoft Network Monitor that connection is encrypted.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
1 Vote"
AlbertoMorillo answered

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

Hope this helps.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JanVavra avatar image
1 Vote"
JanVavra answered AlbertoMorillo commented

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://docs.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.


obrazek.png (30.8 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You are very welcome!

0 Votes 0 ·