Applies to
All ApexSQL tools
Summary
This article explains how to encrypt the connection between SQL Server instance and ApexSQL tools.
To prepare and configure SQL Server instance to utilize an encrypted connection, see How to set and use encrypted SQL Server connections article.
In this article, we’ll use ApexSQL Monitor, and ApexSQL Defrag as example clients, running under Windows user account using a trusted SSL certificate.
The ‘Encrypt connection’ option
The ApexSQL connection encryption option is available in the process of connecting to a desired SQL Server instance or particular database from that instance or adding an instance in the process of monitoring, e.g.
For illustrative purpose, we will use ApexSQL Monitor (Web interface) and ApexSQL Defrag (standard Windows application) to show how to enable this option, but basically, controls are the same for all ApexSQL applications.
In ApexSQL Monitor: go to the Add server subsystem, input necessary information related to the SQL Server instance you want to add for monitoring and click the Connection options link:
Check Encrypt connection option, save, and add the instance for monitoring.
In ApexSQL Defrag: in the Home tab, Servers panel, click Add button. In the Connection dialog, click Connection options button, marked in the picture below:
Check Encrypt connection option in the Connection options dialog, confirm, and establish the connection:
In order for this encryption options to work, it is necessary to previously set up or check the environment.
FAQs
Q: Is there a way to check whether connection is already encrypted for a certain SQL Server instance?
A: Yes, run the following customized PowerShell script:
# 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 + ":")
The following dialog will appear:
Input a desired SQL server instance name in the corresponding form, like shown above.
|
Quick tip: Use (local) or local/domain host name for a default SQL Server instance, and for the named instance use domain\server_name format (DBA2\ProductionEnvironment, e.g.) |
After input, click Check button, and result in next dialog will show true or false value:
In this case, a default SQL Server instance with disabled connection encryption was used.
Q: Do ApexSQL tools automatically update the connection to a certain SQL Server instance if encryption is enabled for that instance in the meantime?
A: No, if SQL Server instance was added before encryption is applied, that instance should be added again, in order to utilize encrypted connection.
Q: The encrypted connection takes some time to establish using my ApexSQL tools, and it sometimes fails. What can I do about this?
A: The connection timeout can be increased in the connection options dialog.
Q: I want to use SQL Server Management Studio’s encrypt connection option while connecting to the specific SQL Server instance and then use some of the integrated ApexSQL tools (like ApexSQL Source Control, e.g.). How it will affect them?
A: As long as the requirements to utilize encrypted connection are fulfilled, ApexSQL tools integrated in SSMS will work as expected, even if they do not explicitly use encrypt connection option.
Q: Which users are allowed to enable and use encrypted connections within ApexSQL Monitor?
A: Only Administrator and Power user roles are able to set the encrypted connection.
