Interesting little problem came up today when migrating a database from Azure SQL database to local server. The connections sting in the web app had the following property set
The combination of the two keywords ensures that data transfer from Website to Database server is encrypted using a certificate generated by the user. Essentially this makes sure that the data cannot be read during data transfer using the Azure network. However when we migrated to the local SQL Server database we didn’t have a certificate created so using the connection string as it was ( when the app was using Azure SQL database results in error “the-certificate-chain-was-issued-by-an-authority-that-is-not-trusted-when-conn”
The solution here is to create a user defined certificate and use that when connection to SQL Server but in this case we were working on a Dev environment so didn’t need a certificate issued by a trusted authority. To rectify the issue you simply need to change TrustServerCertificatete = True which will then allow the connection to use self-signed certificates instead. Through this approach we still implement security (instead of Setting Encyprt=False”) but are not constrained by the need to purchase a trusted certificate.