Can VBA connect to SQL server via SSL certificate?

Question

I'm using an Excel application with a SQL server database. I'd like to use SQL Server in a cloud and need to use a secure connection.

Does anyone know how this can be done?

Answer 1

You need to remember there are a few different ways to setup encryption. One is server side or client side or both (which is not recommended). Assuming you have SSL enabled on the SQL Server side and have Enabled Force Encrypted Connection then all connections to the SQL Server would be forced to use SSL certificate. You can find more information on how to set this up in SQL Server here https://support.microsoft.com/en-au/kb/316898

Once you have the server ready to accept SSL you need to have the client machine running excel set up to trust the certificate's root authority. Once this is done your connection to the SQL Server would be SSL enabled. The following article goes through how to import the root certificate https://technet.microsoft.com/en-us/library/cc754841.aspx

Hopefully this gives you a better understanding of how it works and what you need to do. You can find a lot of information if you google "setting up SSL on the SQL Server" and "setting up trust for certificates root authority".