Skip to main content

Configure SSL on MS SQL Server with OpenSSL


I configured successfully SSL on Microsoft SQL Server 2012 Express Edition for the purpose of encrypting external network connections to the database that are made through Internet. For performance reasons for internal clients on the network I do not want to force the use of SSL and leave to the clients the option of use it or not. I set Force Encryption to No with the following steps:

  • Sql Server Configuration Manager
  • Sql Server Network Configuration
  • Protocols for (MYSQLSERVERNAME)
  • Right click: Properties
  • Flags tab.

When I try to establish an encrypted connection with Microsoft Sql Server Management Studio checking Encrypt connection option on Options > Connection Properties I get the following error.

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.) (Microsoft SQL Server, Error: -2146893022)

What is striking is that if I select Force Encryption as Yes on Sql Server Configuration Manager and I not select Encrypt connection on Microsoft Sql Server Management Studio I can connect to the database. If I execute the query:

select * from sys.dm_exec_connections

In fact the column encrypt_option is TRUE.

The certificate was generated with Openssl and this is the information:

 Certificate:  
   Data:  
     Version: 3 (0x2)  
     Serial Number: 2 (0x2)  
   Signature Algorithm: sha256WithRSAEncryption  
     Validity  
       Not Before: Jun 9 15:53:18 2016 GMT  
       Not After : Jun 9 15:53:18 2018 GMT  
     Subject: C=US, ST=State, L=Location, O=Testing, OU=Development, CN=JOSEPH-ASUS  
     Subject Public Key Info:  
       Public Key Algorithm: rsaEncryption  
         Public-Key: (2048 bit)  
         ...  
         Exponent: 65537 (0x10001)  
     X509v3 extensions:  
       X509v3 Subject Key Identifier:   
         DB:7F:58:DC:F7:D9:90:2A:DF:0E:31:84:5C:49:68:E7:61:97:D8:41  
       X509v3 Authority Key Identifier:   
         keyid:C9:5C:79:34:E0:83:B2:C7:26:21:90:17:6A:86:88:84:95:19:88:EA  
   
       X509v3 Basic Constraints:   
         CA:FALSE  
       X509v3 Key Usage:   
         Key Encipherment, Data Encipherment  
       X509v3 Extended Key Usage:   
         TLS Web Server Authentication  
       Netscape Comment:   
         OpenSSL Generated Certificate  
       X509v3 Subject Alternative Name:   
         DNS:alternatename1, DNS:alternatename2, IP Address:192.168.1.100, IP Address:192.191.1.101, IP Address:192.168.1.103  
   Signature Algorithm: sha256WithRSAEncryption  

But I receive this error: The target principal name is incorrect.

The certificate generated with OpenSSL work properly. In my case the problem was rights of the account under which runs MSSQL over the certificate, I solved this issue with the follow steps:

  • Open SQL Server Configuration Manager.
  • Locate the account which is used to run MSSQL instance (Log On tab on MSSQL instance Properties).
  • Open MMC Console and add Certificates (Local Machine) snap-in.
  • Search the certificate store, right click on certificate and select All Tasks -> Manage Private Keys....
  • Set the Permissions to the same account under which MSSSQL runs.
Labels:

Comments

Post a Comment

Popular posts from this blog

Generate self signed certificate with OpenSSL for IIS

Recently I wanted to enable SSL to a project hosted on IIS 8. Finally the tool I used was   OpenSSL , after many days fighting with   makecert   commands.The certificate is generated in Debian, but I could import it seamlessly into IIS 7 and 8. Download the  OpenSSL  compatible with your OS and setup the configuration file. Set the configuration file as default configuration of OpenSSL. # OpenSSL configuration file. # # Establish working directory. dir = . [ ca ] default_ca = CA_default [ CA_default ] serial = $dir/serial database = $dir/certindex.txt new_certs_dir = $dir/certs certificate = $dir/cacert.pem private_key = $dir/private/cakey.pem default_days = 365 default_md = md5 preserve = no email_in_dn = no nameopt = default_ca certopt = default_ca policy = policy_match [ policy_match ] countryName = match stateOrProvinceName
7 comments
Read more

Docker multi-stage build for Spring Boot application

  The post of this blog is to show the advantages of using multi-stage build on Docker for image size optimization. In case you've gotten here and never heard of the word "multi-stage", don't worry. Basically it is to build your image in different stages, and copy the artifacts from one stage to another, this allows you to keep the readability and maintenance of your Dockerfile, as well as reduce the size of the resulting image. For practice, let's use this Spring Boot learning project here , in which we use Maven to compile our project. Then let's not wait any longer, and let's get to work. Variant 1 This was the first variant that crossed my mind, basically it was, let me use a JDK base image to be able to compile the project, in this case openjdk:8, install Maven, compile the source code, and declare the entrypoint to the artifact binary .jar file. FROM openjdk:8 RUN apt-get update && apt-get install -y maven COPY . /usr/app WORKDIR
Post a Comment
Read more