- Interact with Azure Cosmos DB with PowerShell - Tue, Sep 14 2021
- Azure health services: Track Microsoft cloud outages and maintenance - Wed, Sep 8 2021
- Powerline: Customize your PowerShell console - Tue, Aug 31 2021
As you and I both know, Microsoft SharePoint Server 2013 is a multi-faceted beast. Specifically, SharePoint is an n-tier ASP.NET Web application platform that uses SQL Server for its data tier. Securing your SharePoint farm therefore involves focusing on each part in isolation in addition to evaluating the security of the entire environment.
Today I’d like to provide one simple way to improve the security of your SQL Server layer; this tip applies not only to SharePoint Server, but any line-of-business (LOB) application that uses SQL Server for storage.
That tip is simply this:
Change the SQL Server listening ports off their default, and block the standard SQL Server communications ports on your network.
SharePoint best practice states that you should configure SQL Server this way prior to rolling out your SharePoint farm, and point your SharePoint Web front end (WFE) and application servers to a client alias instead of the server’s actual host name.
However, you can also make the change after the farm has been deployed; by the conclusion of this article you’ll understand how to make this happen both on your SQL Server as well as on your SharePoint servers. Let’s get to work.
Why Change SQL Server Listening Ports? ^
Because SharePoint stores over 90 percent of portal data within SQL Server, it makes sense that a malicious individual interested in stealing corporate data would target your SQL Server instances.
The first (default) instance of SQL Server listens for connections on ports TCP 1433 and UDP 1434. If you don’t change these ports, then your SQL Server is vulnerable to attack because any attacker worth his or her salt will attempt connection on those port values.
By contrast, second and subsequent (named) SQL Server instances running on the same box get dynamically selected ports. Nonetheless, as conscientious administrators, we need to take control of our SQL Server listening ports and write firewall rules to block the traditional ones.
Changing the default ports isn’t the only action we should take to secure SQL Server, but it is a good starting point.
The problem we’ll have in our SharePoint environment if we change the SQL Server port identities is that we’ll lose connectivity between SharePoint and SQL Server—that’s obviously not a good thing.
Thus, SQL Server client aliases come in to save the day. We’ll learn how to create aliases later on in this blog post. For now, let’s start with SQL Server port configuration.
Configuring SQL Server 2012 ^
As with all Windows technologies, we can use a number of tools and methods to accomplish any task. For this article, I’ll go the way of SQL Server Configuration Manager, which is one of the admin tools present on your SQL Server boxes.
In SQL Server Configuration Manager, navigate to SQL Server Network Configuration > Protocols, and then double-click the TCP/IP entry.
As shown in the screenshot, change the IPAll parameter to your new port assignment. Don’t select an intuitive port number. For instance, if your company was founded in 1990, choosing that as your SQL Server port number is not a smart move.
Changing the SQL Server listener port address
Make sure to remove all references to the old port number, again as shown above. That completes the server-side configuration. Now let’s turn our attention to client-side setup.
Creating the SQL Server Client Alias ^
It’s important to note that a SQL client alias serves the same function as a DNS host or alias record, but we do not (repeat: do not) need the latter to use the former. All we have to do to reference a SQL Server by another name on our network is to configure the client device with a new alias definition.
We can do this a couple of different ways. On workstation or server computers that already have the SQL Server administrative tools installed, we can open SQL Server Configuration Manager, browse to Aliases, right-click that element, and select New Alias from the shortcut menu.
As shown below, you can fill in the appropriate fields:
- Alias Name: This is the “friendly” name by which you’d like to reference your SQL Server instance
- Port No: This is your new, custom TCP port number
- Server: This is the actual DNS host name of the SQL Server computer
Creating a client alias from SQL Server Configuration Manager
More likely, though, you need to configure the alias on a non-SQL Server machine (such as a SharePoint WFE or app server). How can you do that? Sure, you can mount the SQL Server DVD or ISO and load up the administrative tools, but that is a royal pain and ultimately unnecessary.
As it happens, recent versions of Windows client and server operating systems have the SQL Server Client Network Utility built-in.
We can create a SQL alias from any Windows client or server machine.
Pop open a Run prompt and type cliconfg; this opens the utility, which I show you in the screenshot above. Enable the TCP/IP protocol on the General tab, and then switch to the Alias tab. Fill in the protocol, alias name, server name, and port number as I showed you before. That’s all there is to it!
Now whenever you need to connect to your target SQL Server machine that is configured to listen on an alternate port number, you can use the alias name.
A second benefit of using SQL client aliases is that you’ll have a much easier time if the time comes for you to migrate a SQL Server instance to another server. In this case, you’ll simply need to update your alias mapping and you’re all set.