PROVISIO DevBlog

How to Encrypt SiteRemote Server to SQL Server Connections

If you want to encrypt the connection between your SiteRemote Server and the Microsoft SQL Server hosting the database for SiteRemote, especially when using a remote Microsoft SQL Server, you can just enable forced encryption in the settings of your Microsoft SQL Server. This enables basic encryption between the two servers. To learn how you can enable encryption for your Microsoft SQL Server please refer to the documentation of the SQL Server provided by Microsoft.

The above enables encryption but does not protect from man-in-the-middle attacks on the connection. If you need this additional layer of security, configure your Microsoft SQL Server to use a certificate and make sure the machine running the SiteRemote Server trusts the certificate. Please refer to the SQL Server documentation provided by Microsoft for the SQL Server version you are using to configure this setup. Once this is done, you need to change the connection string used by SiteRemote to connect to the SQL Server. Note that this change can only be done after SiteRemote has been installed.

Open the file ..\PROVISIO\SiteRemote\Config\SiteRemoteServer.config from your SiteRemote installation folder with an editor and look for lines similar to this example:

<ConnectionStrings>
	<Add Name="default" Encrypted="true">
		<ConnectionString>Data Source=sql.server;Initial Catalog=SiteRemoteBackEndServer;User ID=sa;Password=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx</ConnectionString>
	</Add>
</ConnectionStrings>

You need to add encrypt=true and trustServerCertificate=false (true would not validate the certificate) to the connection string. The above example would look like this with the additional settings added:

<ConnectionStrings>
	<Add Name="default" Encrypted="true">
		<ConnectionString>Data Source=sql.server;encrypt=true;trustServerCertificate=false;Initial Catalog=SiteRemoteBackEndServer;User ID=sa;Password=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx</ConnectionString>
	</Add>
</ConnectionStrings>