SQL Server Encryption (TLS) - Internal CA (Trusted Root)
I have configured an SQL 2022 Std instance with a certificate issued via an Internal CA.
As per the above documentation, I was expecting that the certificate would need to be
added to the trusted root certificate store on all client computers and devices
However after both CA & SQL certificates are imported to Trusted Root Certification Authorities successfully on the client, and testing with client connections set to encrypt I receive the error:
The certificate chain was issued by an authority that is not trusted
I'm aware that I can set trustservercertificate to true on the connections and have confirmed as documented in SQL Server Verify Network Encryption the connections are encrypted when this setting is true.
Does the error indicate a misconfiguration?
Below shows the SQL Server binding.
And an extract from sqlcheck as per the MS documentation: Certificate Requirements for SQL Server
I also note from that article:
Starting with SQL Server 2019 (15.x), SQL Server Configuration Manager automatically validates all certificate requirements during the configuration phase itself. If SQL Server successfully starts after you configure a certificate, it's a good indication that SQL Server can use that certificate
Server starts fine and shows connections are encrypted when the client is forced to trust the server certificate.
FYI I'm testing with SSMS v19 as the client.
The documentation here SQL Server - Client Encryption Summary seems a little ambiguous as both internal ca scenarios (highlighted) refer to a company's certificate server, but only 1 imports the cert on the client and the other (which appears to be me) must set trustservercertificate?
Any advice appreciated.
The error has disappeared and I am now able to establish encrypted connections from the client without specifying trustservercertificate=true
I have not changed any configuration settings to resolve this however the VM hosting the sql server instance had been shut down and restarted.
I had previously restarted the SQL Server Service multiple times after configuring the certificate.
- Which of definitions of database schema is correct?
- How to get isolation level in EF for MSSQL?
- Generate SQL Create Scripts for existing tables with query
- Insert multiple rows WITHOUT repeating the "INSERT INTO ..." part of the statement?
- Index for two columns that will be used in an operation on where clause?
- How do you update a datetime field in a table every time another field in the same table gets updated?
- Pros & Cons of TRUNCATE vs DELETE FROM
- comparing and contrasting Sybase and SQL Server
- How does sql server choose values in an update statement where there are multiple options?
- How to disconnect all users in sql server except sa?
- Select Consecutive Numbers in SQL
- SQL Find consecutive numbers in groups
- Spring Quartz configuration for connecting MS SQL server
- The most elegant way to generate permutations in SQL server
- Check column has 'letter' or '-' then bring value from another column
- Reading child XML node from column
- Combine JSON objects from multiple rows into a single object
- Find all stored procedures that reference a specific column in some table
- Laravel 10 stopped working with SQL Server Database
- Using TransactionScope around a stored procedure with transaction in SQL Server 2014
- Entity storing it's status as a FK to a status table. Can I update the FK once the status changes?
- How can I convert a Sql Server 2008 DateTimeOffset to a DateTime
- I need to replace old path's images to new path with the same image
- Format date as yyyy-mm-dd hh:mm:ss.000
- Does a foreign key automatically create an index?
- How do I select only the latest entry in a table for each group so each 'latest' is a single row?
- "The certificate chain was issued by an authority that is not trusted" when connecting DB in VM Role from Azure website
- Kafka Connect SqlServer Configuration (SSL)
- SQL-Server: Is there a SQL script that I can use to determine the progress of a SQL Server backup or restore process?
- TSQL Email Validation (without regex)