Common SQL Server TCP and UDP Ports

TCP 80

Default port for HTTP connections.  Typically used for report server access.

TCP 135

Used by Transact-SQL debugger, but is also used to control SQL Server Integration Services.

TCP 443

Default port for HTTPS connections.  Typically used for report server access using secure socket layer (SSL) connections.

TCP 445

SMB port for use with named pipes protocol.   The SQL Server is configured to listen for incoming client connections by using named pipes over a NetBIOS session, SQL Server communicates over TCP port 445, just like file and printer sharing.

TCP 1433

Default port for SQL Server clientconnections.  The SQL Server can be reconfigured to listen on other ports, but 1433 is the most common implementation.

TCP 1434

Default port for Dedicated Admin Connections.

UDP 1434

The SQL Server Browser listens on this port for incoming connection requests, and responds on the TCP port of the named instance.  

TCP 2382

Default port for the SQL Server Browser to listen for connection requests to a named instance of Analysis Services.

TCP 2383

Default port for SQL Server Analysis Services.

TCP 4022

Default port for SQL Server ServiceBroker.

The commands below can be used with Command Prompt to open the firewall ports required by SQL. 

 

@echo ========= SQL Server Ports ===================
@echo Enabling SQLServer default instance port 1433
netsh advfirewall firewall add rule name = "SQL Server" dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN
@echo Enabling Dedicated Admin Connection port 1434
netsh advfirewall firewall add rule name = "SQL Admin Connection" dir = in protocol = tcp action = allow localport = 1434 remoteip = localsubnet profile = DOMAIN
@echo Enabling SQL Server Service Broker port 4022
netsh advfirewall firewall add rule name = "SQL Service Broker" dir = in protocol = tcp action = allow localport = 4022 remoteip = localsubnet profile = DOMAIN
@echo Enabling Transact-SQL Debugger/RPC port 135
netsh advfirewall firewall add rule name = "SQL Debugger/RPC" dir = in protocol = tcp action = allow localport = 135 remoteip = localsubnet profile = DOMAIN
@echo Enabling Named Pipes Access port 445
netsh advfirewall firewall add rule name = "SQL Named Pipes Access" dir = in protocol = tcp action = allow localport = 445 remoteip = localsubnet profile = DOMAIN
@echo ========= Analysis Services Ports ==============
@echo Enabling SSAS Default Instance port 2383
netsh advfirewall firewall add rule name = "Analysis Services" dir = in protocol = tcp action = allow localport = 2383 remoteip = localsubnet profile = DOMAIN
@echo Enabling SQL Server Browser Service port 2382
netsh advfirewall firewall add rule name = "SQL Browser" dir = in protocol = tcp action = allow localport = 2382 remoteip = localsubnet profile = DOMAIN
@echo ========= Misc Applications ==============
@echo Enabling HTTP port 80
netsh advfirewall firewall add rule name = "HTTP" dir = in protocol = tcp action = allow localport = 80 remoteip = localsubnet profile = DOMAIN
@echo Enabling SSL port 443
netsh advfirewall firewall add rule name = "HTTPS" dir = in protocol = tcp action = allow localport = 443 remoteip = localsubnet profile = DOMAIN
@echo Enabling port for SQL Server Browser Service
netsh advfirewall firewall add rule name = "SQL Browser UDP" dir = in protocol = udp action = allow localport = 1434 remoteip = localsubnet profile = DOMAIN
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh advfirewall set currentprofile settings unicastresponsetomulticast enable

Anti-Ransomware File System Resource Manager Lists

FSRM is a server role that can be used to protect network shares from crypto-variant viruses from encrypting files to your server.

Once an infection has been identified by FSRM, a notification email can be sent for further investigation.

The filename patterns used are obtained from – https://fsrm.experiant.ca

This website already provides a link for a PowerShell script that installs the FSRM role, and downloads the required filename patterns.

I decided to not use the provided PowerShell script, and I’m using the simplified script below instead.

After manually installing the FSRM role I ran the following PowerShell script to create the required file screening group.

new-FsrmFileGroup -name "Anti-Ransomware File Groups" -IncludePattern @((Invoke-WebRequest -Uri "https://fsrm.experiant.ca/api/v1/combined").content | convertfrom-json | % {$_.filters})

Once the file group “Anti-Ransomware File Groups” has been imported, a file screen can be manually created for a server drive or individual folders.

To update the file group change new-FsrmFileGroup to set-FsrmFileGroup in the PowerShell script.

Disable Proofing Tools in Office 2010

The executable ToggleProof1.exe can be used to edit user profiles to disable proofing in Office 2010.

Open command prompt as Administrator before navigating to the folder containing the ToggleProof1.exe executable. See usage and the example below for the correct command line argument.

Usage: ToggleProof.exe “path\to\ntuser.dat\file”

Example: ToggleProof1.exe F:\Users\Students\Profiles\Student001.V2\NTUSER.DAT

After running a successfully command line argument a confirmation will be displayed.

Rename the user DAT extension to the MAN extension after disabling proofing.

ToggleProof1 download

Topping Up Microsoft Azure Credit

Log into Microsoft Volume Licensing Service Center portal – VLSC, and open the Relationship Summary page.

Select ‘Add Open License’ and enter the License & Authorization numbers that you would have received from your Microsoft licensing reseller. You’ll need to wait for the licensing request to be processed before proceeding.

After processing you should receive a product key for the purchased Azure credits. Go to the following url to add the credits – https://account.azure.com/Subscriptions

Click on ‘Click here to add credits’ and enter the product key.

If the product key is accepted confirmation will be displayed.

Windows 10 Remote Desktop – No Connection

Our Remote Desktop connection settings are applied with Group Policies, and have previously worked with Vista, Windows 7 & 8 clients. On the rollout of Windows 10 we were unable to access these clients.

On checking Inboard Rules of a Windows 10 client ‘Remote Desktop (TCP-In)’ had been renamed to ‘Remote Desktop – User Mode (TCP-In)’

Changes were also noticed to the ‘Programs and Services’ tab.

I created a new group policy for testing where a changed ‘Programs and Services’ tab > Programs > ‘This program’ from ‘System’ to ‘%SystemRoot%\system32\svchost.exe’. I also added termservice to ‘Programs and Services’ tab > Services > ‘Apply to service with this short name’

Create bootable ISO for High Sierra Installer

I needed to create a High Sierra ISO for use with VirtualBox – which worked successfully.

The following method worked successfully on both Sierra and High Sierra systems.

The High Sierra installation files can be downloaded from the App Store when using a Mac or Hackintosh. I usually download a fresh copy of High Sierra after each update release from Apple.

Open Terminal with Spotlight, or by navigating to the Utilities | Applications folder and click the Terminal.app

Enter the following lines into Terminal to create the ISO for High Sierra.

hdiutil create -o /tmp/HighSierra.cdr -size 5300m -layout SPUD -fs HFS+J

hdiutil attach /tmp/HighSierra.cdr.dmg -noverify -mountpoint /Volumes/install_build

sudo /Applications/Install\ macOS\ High\ Sierra.app/Contents/Resources/createinstallmedia --volume /Volumes/install_build

mv /tmp/HighSierra.cdr.dmg ~/Desktop/InstallSystem.dmg

hdiutil detach /Volumes/Install\ macOS\ High\ Sierra

hdiutil convert ~/Desktop/InstallSystem.dmg -format UDTO -o ~/Desktop/HighSierra.iso

mv ~/Desktop/HighSierra.iso.cdr ~/Desktop/HighSierra.iso

A usable ISO should be created if no errors are encountered.

To use these commands in a script file, I would suggest using the && operator to chain the commands together. When the && operator is used the next command is run if and only if the preceding command exited without errors.

hdiutil create -o /tmp/HighSierra.cdr -size 5300m -layout SPUD -fs HFS+J && hdiutil attach /tmp/HighSierra.cdr.dmg -noverify -mountpoint /Volumes/install_build && sudo /Applications/Install\ macOS\ High\ Sierra.app/Contents/Resources/createinstallmedia --volume /Volumes/install_build && mv /tmp/HighSierra.cdr.dmg ~/Desktop/InstallSystem.dmg && hdiutil detach /Volumes/Install\ macOS\ High\ Sierra && hdiutil convert ~/Desktop/InstallSystem.dmg -format UDTO -o ~/Desktop/HighSierra.iso && mv ~/Desktop/HighSierra.iso.cdr ~/Desktop/HighSierra.iso

Create executable script files with Terminal.app

Open Terminal with Spotlight, or by navigating to the Utilities | Applications folder and clicking Terminal.app

Once Terminal is open, navigate to the location where the file is to be created. If you want to know your present location type the pwd command.

cd Desktop/

Create file with the touch command.

touch script.command

If you try to run the created script it will fail due inappropriate access privilages.

Use the chmod command to apply the appropriate access privilages.

chmod +x script.command

Use TextEdit to add the required commands to the file.

NOTE: If you need to run a command that requires root access, add the sudo prefix your command. When the script runs you’ll be requested to enter your admin password.

Deny or allow client logons with Group Policy

As part of our GDPR implementation we have decided to deny staff from logging onto student clients, and vice versa.

The required policies can be found in – Computer Configuration > Policies > Security Settings > Local Policies > User Rights Assignment.

The “Deny log on locally” specifies the users or groups that are not allowed to log into the local computer.

The Group Policy has been tested on Windows 7 and Windows 10 clients successfully.