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 ...
7 comments
Read more

Gradle: Configuring assets folder of Android application

  In many projects I've worked I've come to appreciate the advantages that using a continuous integration tool like TeamCity. During the development of  last Android application, available on Google Play at OCL Lab Results , I saw the need to include or exclude certain files in the assets of the same.    I decided to go into the benefits offered by a build automation system like Gradle . Let me make one thing clear before starting the details, this article is not a tutorial on Gradle, so I hope you're familiar with it before reading, although I confess that it is not very difficult to understand its main components. All code shown below is designed with Gradle 2.1 and Android Studio.   First you need to ensure the existence of the folder of assets to be included in the apk. So we will create the next task to verify the existence of that folder: def assetsProjectFolderPath= '/src/main/assets'   task createAssetsFolder <<{ def folder = file...
Post a Comment
Read more