Always enable Encryption/Verification of server certificate explicitly on the connection string in late configured scenario #1080
Assignees
Labels
mssql pgsql mysql
an issue that applies to all relational databases, same as labeling with `mssql` `mysql` and `pgsql`
Milestone
Comments
Aniruddh25
added
the
mssql pgsql mysql
Merged
Aniruddh25
changed the title
1 task
Aniruddh25
added a commit
that referenced
this issue
Jan 30, 2023
… configured (#1134) ## Why make this change? - Close #1080 ## What is this change? - Add a new property `IsLateConfigured` to `RuntimeConfigProvider` to identify late configuration. If configuration is NOT provided at startup, we mandate connections to SQL databases are encrypted and that the client verifies the server certificate. - This translates to `Encrypt=true, TrustServerCertificate=false` attributes of the connection string. Server name in the connection string should match the certificate name exactly. - However, if configuration is provided at startup, we will rely on the connection string to provide the correct settings for encrypted connections/server certificate validations in order to suit local development preferences. - Modify `SqlQueryExecutor` to store a `ConnectionStringBuilder` property instead of `ConnectionString` to easily set different attributes of the connection string. - Even though the integration tests provide configuration at startup, we set `IsLateConfigured = true` so the tests run with encrypted connections always since that is the most secure way. - To enable encryption for SQL Server on Linux, we added a modified `start-mssql-server.bash` script. Referred [this doc.](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-docker-container-security?view=sql-server-ver16#encrypt-connections-to-sql-server-linux-containers) ## How was this tested? - [X] Integration Tests All test serve good regression since connections fail if the connection string properties aren't honored. ## Sample Request(s) ``` # Example Connection string when self signed cert name = mssqlserver.cert.name "Server=mssqlserver.cert.name,1433;Database=master;MultipleActiveResultSets=False;User=sa;Password=XXX;Encrypt=true;TrustServerCertificate=false" ``` ## Note For testing on SQL Server on windows, we use SQL LocalDB which doesn't support encryption.  The SqlClient library would override and disable encryption for local db as per this change: dotnet/SqlClient#1312 Since encryption is disabled for SQLLocalDB on Windows, this change is only tested for SQL on Linux. ## References: 1. [TLS Considerations]( https://learn.microsoft.com/en-us/azure/azure-sql/database/connect-query-content-reference-guide?view=azuresql#tls-considerations-for-database-connectivity) 2. [Client Initiated Encryption](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-encrypted-connections?view=sql-server-ver16#client-initiated-encryption)
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
To ensure we always use encrypted connections and verify the server’s certificate, DAB engine can explicitly append these recommended values for the respective properties in the connection strings, irrespective of what they are provided in the connection string from a SWA customer.
Note again DAB will not specify the root CA path in the connection strings for any of the database types, but will rely on the container instance to have the root CA installed in the OS store (because even if there are ways to specify that path for PgSql/MySQL, there is no SSLCA path parameter for an Azure SQL connection string.)
The text was updated successfully, but these errors were encountered: