We have a MS SQL 2008 R2 service running on a Windows 7 computer. I've developed a webapplication that runs on a Digital Ocean droplet. This droplet makes a connection with this computer through a subdomain "sql.client-domain.ext" (fictive domain name).
There is a firewall which only allows connections from the droplet to the computer where SQL service is running.
I want an encrypted connection between the droplet and the computer. I've purchased a certificate.
On my local test machine I've installed the PFX certificate in the personal folder (in MMC). I've added the 'NETWORK SERVICE' user with READ permissions for the certificate key.
In the SQL management console, I can't see the certificate anywhere. I tried with service restarts, nothing.
I've also tried to change the hostname to the subdomain and added the subdomain in the hosts file under the Windows directory.
EXEC sp_addserver 'sql.client-domain.ext', local; Host file: 127.0.0.1 sql.client-domain.ext
I can connect in the SQL client program to sql.client-domain.ext but I can't pick the certificate.
I only need the certificate for TCP connections, not local connections.
Any hints or experienced advice?