Changing IP Settings on an SQL Cluster

This simple five minute job during an implementation  really threw me.

To paint a picture there is a three node SQL cluster with two instances (2 Active nodes, one Passive) these are isolated from clients behind a firewall.

To facilitate a hardened firewall policy to permit only TCP 1433 to the instance resource group IP addresses, as well as  ensuring only the instance resource group IP address listens on that port (opposed to the default ALL IP’s setting) some changes are required to the network settings in SQL configuration manager.

On a standalone SQL server, it’s simply a matter of changing the settings using the Configuration Manager GUI, restarting the SQL service and the change takes effect. However when in a cluster the changes revert back to the previous ones immediately after clicking ok.

After venturing into this issue a bit more I discovered what I was trying to do wasn’t really documented any ware, but some other articles pointed me in the general direction of the joys of quorum in clustering. In a nutshell I was making a change on one box but as the registry settings being changed are managed by the cluster service the the other two nodes in the cluster won quorum and overwrote the settings.

To change these settings the cluster the reservation checkpoint for the registry path needs to be removed, the changes made in the registry and then the cluster reservation checkpoint added again.

The first step is to get the checkpoint name of the instance you are going to modify, run the following command:

Cluster res /checkpoints

Once you have the instance name, take the SQL server offline in failover cluster manager and run the following command:

cluster res "SQL Server (INSTANCENAME)" /removecheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.INSTANCENAME\MSSQLSERVER"

You should now edit the registry or use SQL configuration manager to make the changes you wish to make.
Personally I prefer to edit the registry as this enables you to delete the unused IP addresses and just leave the cluster IP in place, which is much tidyer.
The path to edit the registry settings is

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.INSTANCENAME\MSSQLServer\SuperSocketNetLib\Tcp\

Delete any of the IPx keys you don’t need but leave IPAll
To specify the port for the IP address to listen on, simply modify the TCPPort value, and remove the value from the TcpDynamicPorts
Once you are happy with the changes, run the following command to add the checkpoit back into clustering

cluster res "SQL Server (INSTANCENAME)" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.INSTANCENAME\MSSQLSERVER"

Bring the SQL Server resource back online and check SQL configuration manager, the changes should have taken affect.

As a result of this change, your firewall rules will be more secure as the massive dynamic port range doesn’t need to be permitted and if need be both SQL instances can be failed over to the one server without ports comflicting. There is also the added bonus that the IP configuration in SQL configuration manager looks a whole lot tidyer.

Configuring default FTP logon domain

If you’re still stuck in the dark insecure age of the internet and using FTP, you may want users to login to your FTP site using their domain credentials.

By default, the FTP service will use the local user database on the server itself (unless you enter your username in the domain\username format), you can however configure IIS to use a domain by default.

Take caution in doing this though, if you’ve ever put an FTP server on the internet, take a look at the event logs, it will have a ton of brute force attacks on it within minutes.
By default FTP will be trying to authenticate locally, which is a much smaller attack surface (fewer users) as soon as you point it at your domain, it’s going to have a much larger attack surface (more users)

You need to make sure you don’t have any accounts such as “test” or users like “mary” with passwords of “password” or any dictionary word at all. You should also tie the FTP site down to the specific users that need access, so if an account does get compromised it can’t be used to put data in the FTP directory.
With the above in mind, use an elevated command prompt to run the following on the FTP server

adsutil set msftpsvc/DefaultLogonDomain "YourDomainName"

This will set the default logon domain for all FTP sites.

Using a PAC file to set proxy settings

There are many ways to configure proxy settings, via a GPO, via a build, or an application.

Proxy settings can cause issues for mobile users if they use their device away from the corporate LAN as the proxy server will not be reachable, this will render the internet browser unusable (unless of course Direct Access has been implemented)

There are many solutions to this problem, some common ones are:
1. Teach users to enable and disable proxy settings, This is not the most elegant solution, is likely to cause a fair amount of support calls, and also means proxy settings cannot be enforced.
2. Run a 3rd party app that users can click on and select proxy on or proxy off. Im not a fan of these types of applications that sit there and use up resources for no real reason.

3. Run a login script that sets the proxy setting if you are connected to the corporate LAN, and doesn’t if you are not. This is a long winded way of doing it, and is not 100% effective.

In my opinion, the most effective and efficient way of configuring proxy settings is to use a proxy auto config file (PAC)
A PAC file contains a JavaScript function "FindProxyForURL(url, host)". This function returns a string with one or more access method specifications. These specifications cause the user agent to use a particular proxy server or to connect directly
.
You configure your browser (works in all popular browsers) to use a script to configure proxy settings, this setting remains in place permantly. If the PAC file is placed on a web server accessible only within the corporate LAN, if the user is away from the LAN, the config file is not found, so therefore a proxy is not used.

When the user is within the LAN, the file is found, and proxy settings configured.
Some say that a login script can achieve this too, however the login script requires you to login to take effect.

Take a scenario where a user is in the office, closes the lid on his or her laptop, gets on the train then opens the lid, and connects via 3G.
If proxy settings were configured with a login script, the office proxy settings would still be present unless the user logged off and on again.
With a PAC method in place, the browser looks for the settings each time a page is requested, therefore it would fail to find the config file and connect directly.
Below is an example PAC file which can be modified to suit your needs. This could be further extended to look at the current IP of the client, and return a different proxy depending on where the client is. Eg if the client is within an IP range which is associated with the Paris office, the Paris proxy would be returned, or if the client is on a New York IP range, the New York proxy would be returned.

function FindProxyForURL(url, host)
 {
        
        // Direct connections to Hosts
         if (isPlainHostName(host) ||
         (host == "127.0.0.1") ||
         (host == "www.a-whole-domain.com") ||
         (shExpMatch(host, "*.a-entire-domain.com")) ||
         (shExpMatch(host, "10.20.30.*"))) {
           return "DIRECT"
         } else {
           return "PROXY proxy-server.domain.com:8080"
         }
 }

Within this file, access to the IP range 10.20.30.0 - 10.20.30.255 would be accessed directly (bypassing the proxy) aswell as the domain www.a-whole-domain.com. anything under the domain a-entire.domain.com would also bypass the proxy. everything else will be directed at the proxy server "proxy-server.domain.com" on port 8080.
Add additional sites to the proxy bypass list by copying an existing line and pasting it below.


Although a WPAD file could also offer similar functionality, in my experience a PAC file is much more flexible and will enable changes to take effect instantly.

SAN Certificates – a great way to get more for your money

When it comes to SSL certificates, you have two choices, go for a standard SSL certificate for a single domain, or get a wildcard cert for *.yourdomain.com

I’ve always been a fan of wildcard certificates; I believe in the long run these are cheaper as a single certificate will cater for all of your SSL needs; however wildcard certificates come at a price.

When using IIS or ISA/TMG you have the ability to host multiple domains on a single IP address or Web listener using host headers, however this only applies to HTTP traffic. When using SSL only one SSL certificate can be applied to an IP address.

This causes a problem, do you apply lots of different IP addresses to your web server or ISA/TMG server and use a certificate for each domain, or do you buy a wildcard certificate.

In the environment I work in, we are able to get certificates for pennies, but this doesn’t cover wildcard certificates. This means its difficult to justify a wildcard certificate.

However, there is a way! You can create what is known as “Subject Alternative Name” certificates. This is just like a normal certificate, but it is valid for any other domain you specify.

For example, I could request a SAN cert for:

Webmail.yourdomain.com
Portal.yourdomain.com
Crm.yourdomain.com
Anythingelseyouwant.yourdomain.com

To request a SAN cert, open an mmc and add the certificates snap in to it (ensure you select local computer)

Expand Certificates | Personal | Certificates

Right click on certificates and select All Tasks | Advanced Operations | Create custom request

Click next on the first two prompts, then select the web server template and click next.

Click the details button to expand the web server certificate template, and then click properties.

Add the normal subject names such as Organisation and country. Then add as many common names (domains) as you like!

Follow the rest of the wizard until completion; you will then have a CSR to upload to your certificate provider. This certificate will be valid for all of the domains you specified. If you think far enough in the future and specify some domains you think you may need in the future, it will save even more money!