0

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?

Improve this question
4
  • 1
    Did you read the docs ? learn.microsoft.com/en-us/sql/database-engine/configure-windows/…
    – Stephen Morris - Mo64
    Commented Jan 15, 2021 at 18:29
  • 1
    windows 7 is unsecure sql server 2008 is very old and no longer supported. Upgrade your system urgently, don't develop anything with that old infrastructure
    – nbk
    Commented Jan 15, 2021 at 18:57
  • No way this setup is old.... (sarcasme). The factory is connected with this as well and updating this infrastructure cost a lot off PLC/SIEMENS licenses. Don't tell stuff I don't know yet, just help me look in the right direction to get this stuff work. Don't get why SSL was hard back in 2008.
    – Stijn Leenknegt
    Commented Jan 15, 2021 at 22:39
  • @StijnLeenknegt Sorry this tidbit isn't helpful to your question, but I can completely appreciate being forced to work with outdated software versions unfortunately. A factory I worked for in a past life was still running some systems on Windows 95 and some of our biggest suppliers were running their systems in DOS. ;)
    – J.D.
    Commented Jan 15, 2021 at 23:09

1 Answer 1

Reset to default
2

Plase try to follow this steps:

  • Launch the Microsoft Management Console. Select File > Add/Remove snap-in:

enter image description here

  • Add a Certificates snap-in:

enter image description here

  • Select “Computer account”:

enter image description here

  • Use the Local Computer:

enter image description here

  • With the snap-in now configured, right-click on the “Personal” certificate store folder, select All Tasks > Import:

enter image description here

  • Use the certificate import wizard to select the .PFX file to import. In either case, follow the import wizard instructions. Enter the password set when the PFX file was created. Ensure that “Include all extended properties” is selected:

enter image description here

  • Select “Place all certificates in the following store” and choose the “Personal” store:

enter image description here

Once the certificate has been imported, SQL Server must be configured to use it. The SQL Server configuration manager for SQL Server 2008/R2 has an option to set the certificate associated with an instance, by opening the “SQL Server Networking Configuration,” right-clicking on “Protocols for ,” and opening the Certificate tab. However, this method cannot be used to select any certificate other than a machine certificate for servers that belong to a domain.

  • Find the “Thumbprint” of the certificate, and copy it to the clipboard. In the certificate management console, it can be found by opening the certificate, selecting the “Details” tab, scrolling to the bottom of the field list, and selecting “Thumbprint”:

enter image description here

  • After locating the thumbprint, copy it out of the certificate viewer, paste it into notepad, remove all of the spaces, and copy the modified thumbprint back to the clipboard. Be careful – it’s easy to make a mistake.

    • Open regedit
    • Navigate to: HKLM\Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib
    • Locate the “Certificate” entry
    • Assign its value to the thumbprint copied out of the certificate, e.g.:

enter image description here

The instance must be restarted for the changes to take effect.

Improve this answer
1
  • The final step works on the development machine. But not on the client's machine. Perhaps it's something with windows 7/sql 2008. Thanks for your reply.
    – Stijn Leenknegt
    Commented Jan 17, 2021 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.