How to set and use encrypted SQL Server connections
How to set and use encrypted SQL Server connections
Execute these snap-ins from command line or PowerShell with administrative privileges (right-click → Run as administrator), in order to confirm root certificate authority. Open certmgr.msc snap-in, and seek for the Trusted Root Certification Authorities/Certificates folder. In this case, we will use the certificate marked above (name of this certificate represents the machine name). Let’s look at the properties of chosen certificate (double-click it to open), in order to the requirements which must be met in order to embed appropriate certificate to the SQL Server configuration. The strong requirement for using certificates is that they are placed in one of the mentioned certificate stores, because of validity and trust. In Details tab, information like version, signature algorithm, enhanced key usage and many other are present. The first thing that should be confirmed is CN, which stands for Common Name, or in other words, issuer – it must be fully qualified domain name of the machine (in this case, SLAV). In needed moment, the certificate must be available for use (Valid from and Valid to). Enhanced Key Usage field should have value displayed in the picture below, in order to fulfill the main purpose of this certificate – authentication of the SQL Server instance. Next, click on Edit properties… button. Choose Enable only the following purposes option, and, optionally, set the friendly name (Administrator, e.g.). Confirm the changes.
To input the correct server name: use (local) or local/domain host name for a default SQL Server instance, and for the named instance use domain\server_name format (DB1\TestEnvironment, e.g.) In this case, we will query a default SQL Server instance, with enabled connection encryption. After input, click Check button, and result in next dialog will show TRUE or FALSE value: Q: Is installation of system root certificates in Windows required?
A: No, Windows has built-in certificates. However, users are allowed to add as many certificates as they need and use them only, as long as they are trusted, issued by CAs. Q: Is it possible to use self-signed certificates for this purpose? A: Yes, it is, but self-signed certificates have significantly lower level of security, and that is the reason they are recommended only for testing purposes. Q: Is it possible to install trusted certificate from current user certificate store in order to encrypt connection with remote SQL Server? A: Yes, if that certificate is imported to the Trusted Root Certification Authorities/Certificates folder on the remote machine, SQL Server instance protocols configuration has enabled ForceEncryption option, along with embedded certificate and remote access to that SQL Server instance is granted. Q: Can I manage my certificates with other methods than described here? A: Yes, all certificates can be managed within Internet Explorer (ran with administrator privileges). Go to Internet options -> Content tab -> Certificate button. Author Recent Posts Daniel TikvickiDaniel is a librarian who ran into a vortex of IT world, where he is levitating and learning constantly. He likes books, merely all forms of artistic expression (being a musician/poet himself), and he is underground publisher (fanzines and experimental music). Also, the points of interest include theology, mythology and pseudoscience.
Daniel is currently working for ApexSQL LLC as Software sales engineer where he specializes for the BI environment. Latest posts by Daniel Tikvicki (see all) How to monitor the SQL Server tempdb database - July 6, 2017 How to monitor total SQL Server indexes size - June 12, 2017 How to set and use encrypted SQL Server connections - May 8, 2017
SQLShack
SQL Server training EspañolHow to set and use encrypted SQL Server connections
May 8, 2017 by Daniel Tikvicki As the standard for securing the host-server interaction, Secure Sockets Layer or SSL is implemented in a Web environment. However, the SSL can provide the encrypted connection and data transfer between a particular SQL Server instance and a client application. A trusted SSL certificate validates the SQL Server instance when the client application requests encrypted connection (or vice versa), while the SQL Server must be configured to follow the certificate authority (CA). This means that a certificate must be “signed” by a trusted source. Generally, trusted sources are specialized companies (digital certificates vendors). However, self-signed certificates can be sufficient in SQL Server environment, as long as this kind of certificate follows the specified requirements (which are out of the scope of this article). The main reason why a connection between SQL Server instance and any client application should be encrypted is authorized communication between them. This security layer can prevent unwanted sensitive data leak, or exclude the possibility for any SQL injection attack, e.g.Prerequisites and necessary steps
These are the steps to utilize the SSL encryption, which will be thoroughly explained separately: Check the prerequisites for certificate management and usage Review existing trusted SSL certificate from certification stores (local machine or current user) Configure SQL Server protocols for a desired SQL Server instance and enable encryption forcing optionPrerequisites
In order to ensure that certificate management and encrypted connection configuration will be successful, as prerequisites, several options in Local Group Policy Editor (within current user) need to be enabled/disabled. To open Local Group Policy Editor, click + R (Run) and type gpedit.msc. Alternatively, seek for it in Windows 10 search, under the full title. Expand the User Configuration item and the Administrative Templates, and click the Windows components folder: Click on the folder Microsoft Management Console and right-click the setting as shown below (Restrict the user from entering author mode), and choose Edit option: The dialog for enabling/disabling the particular setting will appear. Disable this setting and confirm: When disabled, this setting allows the current user to configure the Management Console. Next step is to choose Restricted/Permitted snap-ins: Enable settings marked above (Certification authority, Certificates and Computer management), to ensure the current user’s authority.Review the existing certificate from the current user store
To check the existing certificates within local computer, run the following pre-defined Microsoft Management Console snap-ins: certlm.msc – certificates from the local machine store certmgr.msc – certificates related to a current user (which will be used in this article) NoteExecute these snap-ins from command line or PowerShell with administrative privileges (right-click → Run as administrator), in order to confirm root certificate authority. Open certmgr.msc snap-in, and seek for the Trusted Root Certification Authorities/Certificates folder. In this case, we will use the certificate marked above (name of this certificate represents the machine name). Let’s look at the properties of chosen certificate (double-click it to open), in order to the requirements which must be met in order to embed appropriate certificate to the SQL Server configuration. The strong requirement for using certificates is that they are placed in one of the mentioned certificate stores, because of validity and trust. In Details tab, information like version, signature algorithm, enhanced key usage and many other are present. The first thing that should be confirmed is CN, which stands for Common Name, or in other words, issuer – it must be fully qualified domain name of the machine (in this case, SLAV). In needed moment, the certificate must be available for use (Valid from and Valid to). Enhanced Key Usage field should have value displayed in the picture below, in order to fulfill the main purpose of this certificate – authentication of the SQL Server instance. Next, click on Edit properties… button. Choose Enable only the following purposes option, and, optionally, set the friendly name (Administrator, e.g.). Confirm the changes.
Configure SQL Server to use encrypted connection
Open SQL Server Configuration Manager, expand SQL Server Network configuration, choose Protocols properties for a desired SQL Server instance (in this case, it is a default instance). Enable ForceEncryption option in Flags tab, and choose the certificate from drop-down menu: Confirm the settings and restart the SQL Server service to apply changes.FAQs
Q: Can I check whether connection is already encrypted for a certain SQL Server instance? A: Yes, with this customized PowerShell script: 123456789101112131415161718192021222324252627282930313233343536 # First part of the script which creates dialog and forms, and hold input in the textbox#[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") [void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") $dialog = New-Object System.Windows.Forms.Form $dialog.Text = "Enter SQL Server instance name:"$dialog.Size = New-Object System.Drawing.Size(400,100) $dialog.StartPosition = "CenterScreen" $check = New-Object System.Windows.Forms.Button$check.Location = New-Object System.Drawing.Size(250,20)$check.Size = New-Object System.Drawing.Size(75,23)$check.Text = "Check"$check.Add_Click({$x=$input.Text;$dialog.Close()})$dialog.Controls.Add($check) $input = New-Object System.Windows.Forms.TextBox $input.Location = New-Object System.Drawing.Size(40,20) $input.Size = New-Object System.Drawing.Size(200,20) $dialog.Controls.Add($input) $dialog.Add_Shown({$dialog.Activate()})[void] $dialog.ShowDialog() $x #Second part of the script, which executes specific SQL statement and passes result in pop-up dialog#$script = Invoke-Sqlcmd -Query "SELECT DISTINCT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID" -ServerInstance $input.Text$wshell = New-Object -ComObject Wscript.Shell$wshell.Popup($script.ItemArray,0,"Connection encryption enabled for instance " + $input.Text + ":") After executing, the following dialog will appear: To get valid information, input a desired SQL server instance name in the corresponding form, like shown above. NoteTo input the correct server name: use (local) or local/domain host name for a default SQL Server instance, and for the named instance use domain\server_name format (DB1\TestEnvironment, e.g.) In this case, we will query a default SQL Server instance, with enabled connection encryption. After input, click Check button, and result in next dialog will show TRUE or FALSE value: Q: Is installation of system root certificates in Windows required?
A: No, Windows has built-in certificates. However, users are allowed to add as many certificates as they need and use them only, as long as they are trusted, issued by CAs. Q: Is it possible to use self-signed certificates for this purpose? A: Yes, it is, but self-signed certificates have significantly lower level of security, and that is the reason they are recommended only for testing purposes. Q: Is it possible to install trusted certificate from current user certificate store in order to encrypt connection with remote SQL Server? A: Yes, if that certificate is imported to the Trusted Root Certification Authorities/Certificates folder on the remote machine, SQL Server instance protocols configuration has enabled ForceEncryption option, along with embedded certificate and remote access to that SQL Server instance is granted. Q: Can I manage my certificates with other methods than described here? A: Yes, all certificates can be managed within Internet Explorer (ran with administrator privileges). Go to Internet options -> Content tab -> Certificate button. Author Recent Posts Daniel TikvickiDaniel is a librarian who ran into a vortex of IT world, where he is levitating and learning constantly. He likes books, merely all forms of artistic expression (being a musician/poet himself), and he is underground publisher (fanzines and experimental music). Also, the points of interest include theology, mythology and pseudoscience.
Daniel is currently working for ApexSQL LLC as Software sales engineer where he specializes for the BI environment. Latest posts by Daniel Tikvicki (see all) How to monitor the SQL Server tempdb database - July 6, 2017 How to monitor total SQL Server indexes size - June 12, 2017 How to set and use encrypted SQL Server connections - May 8, 2017