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.
- Searching for strings with double quotes in SQL Server
- Check if table exists in SQL Server
- The database 'xxx' cannot be opened because it is version 904
- Insert Update trigger how to determine if insert or update
- SQL query to determine whether it is day or evening
- Invalid value specified for connection string attribute
- Reset identity seed after deleting records in SQL Server
- Call a stored procedure with parameter in c#
- Join 3 tables without table 2 and 3 overlapping
- How to run SQL Server Agent Powershell script with output and exit code
- Refer to original value within CASE statement
- alternative to xp_cmdshell bcp?
- Querying data from SQL Server table with Powershell
- How to export data as CSV format from SQL Server using sqlcmd?
- ExecuteScalar() not returning full contents of cell
- SQL Query to select certain number of records with WHERE clause
- How to select 10 rows for a each column value in the 'IN' statement (T-SQL)
- How to execute a .sql file using PowerShell
- "The certificate chain was issued by an authority that is not trusted" when connecting DB in VM Role from Azure website
- Invoke-Sqlcmd doesn't allow TrustServerCertificate
- Can you insert into a replicated SQL Server DB?
- How to set up a Windows Docker Container with SQL Server?
- Remove trailing spaces from fixed length in output file generated by sqlcmd
- How to suppress hyphens in SQLCMD
- Error when adding replication logreader agent with login
- Differences Between Drivers for ODBC Drivers
- Calculate value based on sum of previous calculated values
- Get all the table names that are available in all the <s>schemas</s> databases in SQL Server
- Detect character encoding of file in SQL Server
- What performs better: Bunch of UNION queries vs separate queries?