By: Eric Blinn | Updated: 2022-06-03 | Comments | Related: 1 | 2 | More > Database Administration
Problem
It is generally considered a bad practice to make remote desktop connections to Windows machines running SQL Server. These connections open up security vulnerabilities and use resources that would be better used by the SQL Server service. Check out this screenshot from a real SQL Server. This server should be focusing on the SQL Server service but is instead working on 2 RDP sessions.
In fact, as the architecture landscape continues to move away from full versions of Windows and towards low or no UI options such as Windows Core, Linux, or containers, RDP connections may not even be available.
Solution
This tip is actually the second in a series showing the best ways to avoid making remote desktop connections to a SQL Server. Check out the first tip here. That prior tip showed a series of common scenarios that often lead people to make remote desktop connections to SQL Servers and showed alternatives that did not require RDP. This tip will continue with even more tasks that are commonly completed with RDP connections, but can and should be done another way.
Using Server Manager
Modern Server editions of Windows come with a feature called Server Manager. This program usually comes up immediately upon making a remote desktop connection to a server. Far too often this author sees that window get closed by a user as quickly as it appeared. In fact, one time an administrator with whom this author was working said, "I don't know why that comes up. You just have to close it." This section of the tip shows why that window should be given a much closer look.
The use of Server Manager -- without also logging directly into the SQL Server -- will require the use of a jump box running a Server edition of Windows as it will not come up on a desktop version.
By default, only the local server will be listed in the server section on the left side of the application window. The first step to using Server Manager is to add the desired servers to the application. Right click on "All Servers" and choose the "Add Servers" option. This will bring up a window that allows a user to search for an add servers to the list.
Once added, there will be a list of servers available under the "All Servers" selection. This screenshot shows 4 obscured servers. For each server, a right-click context menu is available that shows a whole series of management options that can all be completed remotely from this jump box without making a remote desktop connection. Restart the server, add the WSFC role to later enable AGs or FCIs, or use remote computer management to manage local groups or disks. Just don't use that "Remote Desktop Connection" option!
Setting up a Windows shared folder
It is a very common scenario to need a Windows file share to move files to or from a SQL Server. While an administrator can use an admin share to copy things like installation media, other scheduled tasks may not work well with that type of share. Traditionally, this process of making a regular share starts by making a remote desktop connection to the server, right clicking a folder in Windows Explorer, and, finally, by using context menu options to create the file share.
This process can take place without ever making that remote desktop connection. And it isn't that hard! Follow the steps from the previous section of this tip to open Computer Management on the remote server. In the tree will be an option to show Shared Folders. Right click on "Shares" to find a context menu that offers the option "New Share…". This will open a wizard to walk through the process of creating the share on the remote machine which will be very familiar to anyone that has made a file share before.
Modifying the service
SQL Configuration Manager is a management tool that help manage certain features about the family of SQL Server services. Some of its more common uses are to modify startup parameters, change a service account, or modify network protocols. Most often this tool is reached by making a remote desktop connection to the server and opening the app directly as "SQL Server 2019 Configuration Manager".
The application looks something like this.
All of those features and options can be accessed without that application at all! Using remote computer management (see prior segments of the tip for more details) to connect a SQL Server will add all of those options at the bottom of the tree under the "Services and Applications" section. The below screenshot shows all those same options as above –except remotely.
Using Enter-PSSession to run PowerShell commands remotely
Enter-PSSession is PowerShell cmdlet that allows a user to open a remote connection to another server. After running this cmdlet, future cmdlets will be run as if the user was local to the server. This will remain true until the Exit-PSSession cmdlet is executed and the remote connection is closed. This method of making a remote PowerShell session is only useful for single commands being run interactively. To run an entire PowerShell script remotely, skip to the next section of this tip.
In the screenshot below several commands have been entered into the PowerShell command line from a laptop computer that does not have any SQL Server applications installed.
The first command creates a remote session on the server "MyServer1". The subsequent commands have a different path and a prefix of the remote server name. These clues indicate to the user that the command will be run against the remote server.
The second command reads the status of the SSRS service and returns that the service is running. The final command exits the remote session and returns control to the local machine. Notice that the server prefix is gone and the path has changed back to its original value. While this session only included a single remote command, many commands could be run – one at a time – before exiting the remote session.
Using Invoke-Command to run a PowerShell script remotely
Sometimes a few interactive PowerShell commands aren't enough to complete a task. Maybe an entire script needs to be executed remotely. Enter the Invoke-Command cmdlet. Invoke-Command allows a subsequent PowerShell script to be run against a target server. This script can be written directly into the statement or read from a file.
Consider this simple script called "InputScript.ps1". It displays information about the any SQL Server processes executing and then displays the version of Windows. On this author's laptop it found no SQL Server services executing then displayed the number 10 for Windows 10.
Putting the exact same text of this script into an Invoke-Command cmdlet allows it to be run against a remote server as defined by the ComputerName parameter. The following script and screenshot show just that. The same 2 commands are inside the curly braces of the "ScriptBlock" parameter. This time a SQL Server service is found and the server is running version 6 of Windows.
Invoke-Command -ComputerName MyServer -ScriptBlock {
Get-Process | Where-Object ProcessName -eq "sqlservr"
[System.Environment]::OSVersion.Version.Major
}
The command will work the same way if the script is stored in a separate file. This version of the script and accompanying screenshot show the same command except that the ScriptBlock is replaced with a FilePath parameter. The path listed in that parameter is the same file name from the first execution. The output of this script is identical to the previous execution.
Invoke-Command -ComputerName MyServer -FilePath "C:\Scripts\InputScript.ps1"
Modifying the Windows Firewall
Many system administrators still use the Windows Defender Firewall UI to manage firewall rules for SQL Server. Continuing the theme of the tip, this process can be easily completed with a PowerShell command. The PowerShell command needs to be run on the target server, but that is very easy to do by following one of the PowerShell remoting options outlined earlier in this tip.
The following command will create a rule to allow ports 1433 (SQL Server default TCP port) and 1434 (SQL Server dedicated admin connection) as inbound connections to a Windows server. There are also options to create the rule by application if that is preferred. These are all outlined on Microsoft's documentation website.
New-NetFirewallRule -DisplayName "Allow TCP 1433/1434 for SQL Server" -Direction Inbound -Action Allow -Protocol TCP -LocalPort 1433,1434
Removing Internet Access Entirely
Now that administrators are no longer logging into the SQL Server using a Remote Desktop Connection to perform common tasks like downloading patches or searching for solutions to issues, why should the SQL Server even have access to the internet? Hopefully this series of tips removed most of the legitimate reasons for a SQL Server to need to talk to the internet – leaving only nefarious reasons like a piece of malware uploading stolen data or downloading even more malicious code.
Carefully consider the possibility of blocking internet access entirely on SQL Servers. If the server still needs to talk to the internet, perhaps to move data to or from an SFTP site or connect to a cloud-based email provider, then this may not be an option.
Final Thoughts
Hopefully, this tip provided some more methods to better manage SQL Servers remotely.
Next Steps
About the author
Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-06-03
