Which firewall ports to open to allow browsing of instance names and connections to SQL Server 2008 R2

By default Windows Server 2008 will block incoming connections to the SQL Server browser service, and to the SQL server default instance. If you are trying to connect to a SQL server instance, but are unable to browse to it under ‘network servers’ you need to open UDP port 1434. Firstly make sure the SQL Server Browser Service is started on the SQL server that you wish to advertise the instance for. Next fire up the ‘Windows Firewall with Advanced Security’ mmc snapin and create a new inbound rule. Create the rule for a port and specify the port as UDP port 1434 as shown below:

Allow incoming connections to named instances by opening UDP port 1434

To allow clients to connect the default instance and to access databases attached to that instance you will also need to open TCP port 1433. This can be done as above, by creating a new incoming rule for TCP port 1433 as shown below:

Open TCP port 1433 on SQL server 2008 R2 to allow connnections to the default instance

If desired you may wish to lock down these rules to certain profiles e.g. Domain, or even to certain subnets or hosts to help increase security. Also bear in mind that it is not best practice to have the SQL Browser Service enabled. Additional relevant SQL ports which you may wish to open can be found on the Microsoft site here:

Configure the Windows Firewall to allow SQL Server Access

These include:

TCP 1434 “SQL Admin Connection”
TCP 4022 “SQL Service Broker”
TCP 135 “SQL Debugger/RPC”
TCP 2383 “Analysis Services”
TCP 2382 “SQL Browser”

If using a named instance using dynamic ports you may wish to create a program rule with an exception for sqlservr.exe, normally found in

%ProgramFiles%\Microsoft SQL Server\MSSQL_YOUR_VERSION_NUMBER.YOUR_INSTANCE_NAME\MSSQL\Binn\

You may also wish to consider opening remote administrion ports on the windows firewall Domain Profile either through ‘Windows Firewall’ or ‘Windows Firewall with Advanced Security’ to allow access to start and stop the SQL server through SQL Management Studio:

Enable Remote Administration Port Rules in ‘Windows Firewall With Advanced Security’

 

Enable Remote Administration in the ‘Windows Firewall’

 

Always take extreme care when opening firewall ports, only opening those which are absolutely necessary in order to reduce the attack surface of your servers.

Force reboot of a remote server that has hung shutting down

I had an issue last night when a remote server that I was applying windows updates to, hung while it was shutting down. I will still able to ping the server, and access its file shares, but was unable to get RDP access. I was cursing at this point, with the prospect of a long journey the following day to investigate and bring the server back online. I then considered what other steps I could take to try and force the server to reboot remotely. I used the PSTools command psexec to see if I could still get command line access to the remote server, and fortunately I could. Next I tried to force a reboot of the remote server using psexec and the shutdown command as follows, where REMOTE_SERVER_NAME is the name of the remote server that I was trying to reboot:

psexec \\REMOTE_SERVER_NAME shutdown /r /t 01

alternatively you could use:

shutdown /m \\REMOTE_SERVERNAME /r /t 01

This returned the following error:

1115 A system shutdown is in progress

This basically meant that a system shutdown was already in progress,  and therefore the command was unable to force a reboot. In the end I used the pskill command to stop the winlogon service on the remote server to try and release whichever process wass causing the server to hang on shutdown. I should stress that this was a last resort, and not something that I would recommend doing unless essential:

pskill \\REMOTE_SERVER_NAME  winlogon

Anyway, after another few minutes the remote server did finally restart, although there are a few other things that I should mention that happened in the process. The operating system on this machine was Windows Server 2008 R2. After the server came back up (verified by ping -t REMOTE_SERVER_NAME) I tried to RDP the box again. I was able to enter my credentials and the logon process appeared to start, but after a few seconds the following message appeared on the screen:

Please wait for the Windows Modules Installer

The machine sat like that for quite some time, and then started ‘Configuring Updates’. My RDP session then abruptly ended and the server restarted itself again. Again, when it was back up I tried to RDP the server again and received the ‘Please wait for the windows modules installer message’ for a second time. Thankfully, after a few minutes and another ‘configuring updates’ message, logon continued and ther server was back up and running. On checking the event log and windows update log I was able to verify that all the updates had installed OK, and there were no other errors worthy of note. So in summary, if you want to save yourself a long trip, to most likely press a power or reset switch, you may want to try the above first.    

 

Generate a new SID on Windows Server 2008 and Windows 7

With the NewSID tool no longer supported by Microsoft for more recent versions of Windows, you may find yourself in a situation where you need to generate a new SID on a Windows Server 2008 or Windows 7 computer, and wonder which tool you need to use. I myself have run across an issue in the past in our development environment where duplicate SIDs caused a problem. Care needs to be taken when cloning Windows virtual machines, particularly if they will later be used as domain controllers.

In order to avoid any issues like this, the new preferred method to set a new SID on a Windows machine is to use Sysprep. Before running Sysprep, you may wish to verify the current SID on the sytem that you wish to modify. This can easily be done by running the psgetsid utility, which is part of the excellent Pstools developed by Mark Russinovich. The output of the psgetsid command can be seen below, showing the current machine SID:

Output of psgetsid before running Sysprep on Windows Server 2008 R2

 Next you can sysprep by running the following command:

C:\Windows\System32\Sysprep\sysprep.exe

Running Sysprep
 
On the Sysprep, screen make sure that you tick the ‘Generalize’ tick box as shown below:
 

Choose settings for the System Preparation Tool

 The Sysprep process will take a few minutes to run, and will automatically reboot the system if you chose to do so. On reboot, the following screen will be displayed. Click ‘Next’ to continue:

 

Click next to continue the Sysprep process

 After the sysprep process is complete, you can run psgetsid again to verify that a new SID has been generated for this computer:

Output of psgetsid after running Sysprep on Windows Server 2008 R2