- Azure PowerShell vs. Azure CLI - Wed, Mar 15 2023
- Use Azure Storage for backup - Thu, Feb 23 2023
- Azure Data Studio vs. SQL Server Management (SSMS) - Wed, Feb 8 2023
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.
Hello
This is such a nice article and thank you for covering this in detail.
I have one SQL Server named instance and 3 Analysis Services instances (PowerPivot, MD and Tabular). I need to configure kerberos to the 3 Analysis Services instances and was wondering if I need to change the Port number for these instances too. How would I do that and have you got an article on configuring kerberos for Analysis Services?
Thanks
Yoshi
Hi Yoshi. Have you taken a look at the free Kerberos Configuration Manager for SQL Server (http://cbt.gg/1gUPVyi)? You might find it helpful in identifying and resolving any Kerberos-SSAS issues that you might have. Thanks, Tim
This article appears to foucs on Security thru obscurity, which is for the most part a fallacy creating a warm sense of security which is in reality wafer thin. In a real attack were the network has already been breached, an attacker can just port scan and in a matter of seconds will find out what ports on what IPs in a given range are running SQL server, if the attacker has instead breach the SP server but not got out onto the network as a whole, they’d be able to follow the aliases to get to the backend data just as effectively as they could follow the machine name, once access to the SQL via SP has been established, they can just ask the SQL server what it’s hostname in, and again in a matter of seconds the actions above are bypassed.
Far import important than the above steps are appropriate firewall rules, effective patching and strong credential management.
Hi Simon–thanks for sharing your thoughts. I agree with your points regarding multi-layer security; you won’t get any argument from me. However, I do want to point out that this article does not set out to cover defense in depth. Rather, my goal was to zero in on a specific SQL Server feature–the alias–and the main uses of that feature from the perspective of the DBA or SharePoint admin. Thanks, Tim
Simon, I replied to your comment in a blog post.
Hi Timothy
Thanks for sharing your thoughts on this topic. I just wanted to know if it was best to set up the Alias on the SQL Server and then make the changes via CLICONFIG on the WFE and App servers after SharePoint is installed and configured or do you do this before starting the SharePoint installation process? If this needs to be done before the installation process do you then use the Alias name in the SharePoint installation steps instead of the SQL Server name?
Can the steps above be done after SharePoint is installed and configured with the SQL Server name?
Besides creating a UDL connection and testing the alias on the WFE and App Servers is there any other way to check if SharePoint is using the Alias or the original SQL Server name when connecting to the SharePoint databases?
Thanks for your assistance.
Yoshi
Hey Yoshi. Yeah, you can establish the SQL alias after you deploy the farm. You’d just create the alias with the actual name of the SQL Server. For instance, SharePoint is registered with SQL1, and you create a client alias that maps SQL1 to, well, SQL1. But then if the database server becomes SQL2, all you have to do is update the client alias and you’re good to go.
You can use PowerShell or Central Administration to determine the SQL Server that the farm is registered to.
Hope this helps, Tim
Nice Post..
You can also get the detail information for same configuration on
http://codecreature.wordpress.com/2014/08/11/create-and-configure-sql-server-instance-and-alias-for-sharepoint-installation/
Thanks for the compliment and the resource link, Hemant! -Tim