AZURE SQL database connection enable TLS trustservercertificate=true , Encrypt=yes from client

Question

Hi we have system internet facing application which connected to Azure SQL database . we are try to connect from application web facing , having issue with TLS setting enable with Encrypt=yes. (ensure that secure connection) . our idea is to enable secure connection and when connect , final out come should be select Encript_option, auth_scheme select sys.dm_exec_connections value should be Encript_option=TRUE . but sure auth_scheme= SQL or FEDERATED

Could you please guide me to configure this in client side and , Azure SQL database side or create any EKM certificate and apply (not sure how to do this)

thanks in advance

Answer 1

 final out come should be select Encript_option, auth_scheme select sys.dm_exec_connections value should be Encript_option=TRUE . but sure auth_scheme= SQL or FEDERATED    

The auth_scheme value of sys.dm_exec_connections is not related to encrypted connections. As long as the encrypt_option shows TRUE, the connection is securely encrypted. honoring the Encrypt=Yes in the connection string.

Answer 2

This from MS
"since custom SSL certificates are not available."
"you can either set Encrypt=True and TrustServerCertificate=True or Encrypt=False, once Encrypt=False, TLS will still be established, but it will skip checking server-side certificate"

Answer 3

Hi @Ashwan ,

In order to enable secure connection and when connect as a best practice, recommend that in the connection string used by the application, you specify an encrypted connection and not trust the server certificate. This forces your application to verify the server certificate and thus prevents your application from being vulnerable to man in the middle type attacks.

transparent-data-encryption-tde-overview

Note: TDE cannot be used to encrypt system databases, such as the master database, in Azure SQL Database and Azure SQL Managed Instance. The master database contains objects that are needed to perform the TDE operations on the user databases. It is recommended to not store any sensitive data in the system databases. Infrastructure encryption is now being rolled out which encrypts the system databases including master.

Please refer to TLS section (Encryption in transit & Encryption in motion) of this documentation

Hope this information will help. Please let us know in case of further queries. Thanks.

Answer 4

Hi I am still facing an issue with my linked service as connection failure , How can I solve this issue?