Firewalls, MDT and SQL

In this post I am going to cover off configuring the Windows Server 2008 firewall to support accessing the MDT database via named pipes. I started to write this post because I spent a very frustrating half hour or so trying to work out why my client would not talk to my brand new MDT SQL database the other week on my brand new Windows Server 2008 system. Finally the penny dropped and I realised it was the firewall getting in the way. Rather than just turning it off I thought I would try to work out which ports I needed to open for SQL access.

SQL access gets a little complicated because there are two ways to access a SQL database - named pipes - default MDT method or by TCP/IP - the more normal way to access SQL. To further complicate matters we can have single and multi-instance SQL configurations - either default or named.

So the configuration I was working with was a Windows Server 2008 system running MDT and SQL as well. So I already had some ports open - namely file sharing.

I hunted the web to find configuration information for accessing SQL over a firewall. I could find plenty of information on doing this via TCP/IP. For example https://technet.microsoft.com/en-us/library/cc646023.aspx.

So I set about looking for named pipes information. Well after a bit of searching I found that I needed port 445 open for named pipes. Those of you quick off the mark will notice this does not help me much. Port 445 was already open - it is opened to support file shares. So my port was open but I still could not connect.

Now the other thing about my configuration is that the install was not a default  one. That is there was no default named instance of SQL - I had installed a named instance. So this means I needed the SQL browser to be running and that also meant accessible. Once I opened the port for the SQL browser (UDP 1434 by default ) I found my client connected.

If you want to enable the UDP port you can do this one of two ways.

1. via the Windows Firewall Settings dialogue
   or
2. via the command line.

My favourite is via the command line of course; and the one I will cover here. This is done via the netsh interface. To do this carry out the following steps;

1. Open a command prompt as Administrator on your Windows Server 2008 server.
2. Type the following line:
           netsh firewall add portopening UDP 1434 "SQL Browser" Enable All
   this opens UDP port 1434 for all profiles and gives it the name "SQL Browser". If you now view the Windows Firewall settings via the dialogue, see below, you will see this rule listed as SQL Browser. Have look at the following link for more information on using netsh to configure the Windows Firewall: https://technet.microsoft.com/en-us/library/cc737845.aspx

Summary

If your SQL install is not a default one and you do not have file sharing enabled on the server you need to open TCP 445 and UDP 1434 if using named pipes.

If using TCP/IP to access SQL check out the first link I gave above on how to configure the firewall for that. You might also need to tweak your SQL configuration if using a named instance of SQL.

This post was contributed by Richard Trusson, a Senior Consultant with Microsoft Services, UK.