Data is one of the most critical and valuable components for any industry and it’s protection is every individual’s responsibility. Data lives within Microsoft SQL Server, and therefore, it is the duty of DBAs to ensure that their SQL Server is safe from unwanted detection and intrusion.
Today, I will demonstrate a method by which you can isolate your SQL Server instance from unwanted detection. I believe that this is a best practice and must be followed for all production systems.
SQL Server Configuration Manager
Per Books-On-Line,
- SQL Server Configuration Manager is a tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to manage the network connectivity configuration from SQL Server client computers
- It is basically a Microsoft Management Console (MMC) snap-in and for SQL 2008 utilizes the SQLServerManager10.msc snap-in component
- System administrators: please note that the configuration manager uses WMI to monitor and change SQL Server settings
Managing Protocol access
After all, client connectivity to access, manipulate and store data is all the SQL Server is about. SQL Server Configuration Manager allows you to configure server and client network protocols, and connectivity options.
The SQL Server configuration manager allows you to control how applications on the network access your database – what protocol is to be used, whether remote connections are allowed or not and also control the IP addresses & ports that SQL server would listen on for incoming requests. However, there is one key aspect that can be controlled when configuring protocols – the discovery of an SQL Server instance on the network.
How applications “discover” SQL Server?
Applications on a network discover a SQL Server instance by sending out something called as an “enumeration request”. If a SQL Server is visible on the network, the SQL Server Browser service would respond to this enumeration request and allow the SQL Server instances on the server to be listed amongst the list of available instances to connect to.
Hiding a SQL Server instance
In a production environment, you may want to hide the production instances of SQL Server to avoid detection. Essentially, all that needs to be done is to instruct the SQL Server Browser not to respond to an enumeration request for that particular SQL Server instance. Here are the brief steps on how you can go about achieving this objective:
- Launch the SQL Server Configuration Manager
- Expand the node for “SQL Server Network Connection”
- Browse out to “Protocols for <<SQL Server instance name>>”
- Right-click and go to “Properties”
- In the “Protocols for <<SQL Server instance name>>”, choose “Yes” from the drop-down to hide this instance from detection over the network
As you can see that in just 5 steps (2 are images, so they don’t count) you can hide your SQL Server. Users and applications can still connect to this instance normally, but anyone attempting to enumerate through the network for a list of available SQL Servers would not find this instance on their list.
Other “How-to” topics
You can do a lot with the SQL Server Configuration Manager. You can find a list of common operations, and how to go about them on the Books-On-Line page here: http://msdn.microsoft.com/en-us/library/ms188707.aspx
By the way…
I have often seen developers using their trusted Windows Services applet to restart the SQL Server service. This was acceptable till SQL 2008 came about. Starting SQL 2008, this practice should not be followed.
This is because SQL Server uses a service master key for all it’s operations and this key needs to be regenerated upon service restart. These keys are not re-generated if the services are restarted using the Windows Services applet. The SQL Server Configuration manager is the right place to go to if you need a SQL Server service restart.
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 