VBA ADO connection string with Encryption

Question

I have a remote database (SQL Server) that I connect to via the Internet.

I use this connection string (works perfectly):

ConnectionString = "Driver={SQL Server Native Client 11.0};" _
                            & "Server=xxx.xxx.x.xx;" _
                            & "Database=MyDbName;" _
                            & "Uid=MyId;" _
                            & "Pwd={MyPassword};" _
                            & "Connection Timeout=30;"

I was asked to add encryption to this connection string, but I do not know what I am required to add and where...

I tried to add this Encrypt=yes; commend to the connection string but I get an error (“The certificate chain was issued by an authority that is not trusted”).

Server OS - Windows server 2016 standard.

Users OS - windows 7 - 10 with Excel 2007 - 2019 or 365

What error do you get? What is your client OS? The Server's OS? — David Browne - Microsoft
When SQL Server gets installed a self-signed X.509 certificate is used by default, so won't be trusted by any CA-related certificates in your Trusted Roots store. When adding Encrypt=yes to your connection string you probably also need to include TrustServerCertificate=yes as well. — AlwaysLearning
@AlwaysLearning - I add "Encrypt=yes;TrustServerCertificate=yes;" to the connection string and it works, is this mean that now my connection string is encrypted and more secure ? — xl0911

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2021-05-18T21:46:00

If the server doesn't have a certificate that the client trusts, then you can't create an encrypted connection without explicitly approving the untrusted certificate with the TrustServerCertificate connection string keyword.

See: Using Encryption Without Validation in SQL Server Native Client

VBA ADO connection string with Encryption

1 Answers