Quick Windows Server 2008 R2 – SQL Server Firewall setup

Setting up SQL Server 2012 I received the same warning I have always received when setting up a SQL instance on a new Windows server:

SQL Setup Support Rules – Firewall Warning

So I executed my handy Windows Server Firewall batch file for SQL to set up the default ports on the Firewall, which executed successfully, but I noticed the following warning during execution:

Warning – netsh firewall is deprecated

After a quick check I updated the file with the new correct script options (I have left the original script lines in, just commented them out for reference:

@echo =========  SSRS Ports  ===================
@echo Enabling SQLServer default instance port 1433
REM Deprecated: netsh firewall set portopening TCP 1433 "SQLServer" 
netsh advfirewall firewall add rule name="SQLServer" dir=in action=allow protocol=TCP localport=1433

@echo Enabling Dedicated Admin Connection port 1434
REM Deprecated: netsh firewall set portopening TCP 1434 "SQL Admin Connection" 
netsh advfirewall firewall add rule name="SQL Admin Connection" dir=in action=allow protocol=TCP localport=1434

@echo Enabling conventional SQL Server Service Broker port 4022  
REM Deprecated: netsh firewall set portopening TCP 4022 "SQL Service Broker" 
netsh advfirewall firewall add rule name="SQL Service Broker" dir=in action=allow protocol=TCP localport=4022

@echo Enabling Transact-SQL Debugger/RPC port 135 
REM Deprecated: netsh firewall set portopening TCP 135 "SQL Debugger/RPC" 
netsh advfirewall firewall add rule name="SQL Debugger/RPC" dir=in action=allow protocol=TCP localport=135

@echo =========  SSAS Ports  ==============
@echo Enabling SSAS Default Instance port 2383
REM Deprecated: netsh firewall set portopening TCP 2383 "Analysis Services" 
netsh advfirewall firewall add rule name="Analysis Services" dir=in action=allow protocol=TCP localport=2383

@echo Enabling SQL Server Browser Service port 2382
REM Deprecated: netsh firewall set portopening TCP 2382 "SQL Browser" 
netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=TCP localport=2382

@echo =========  Misc Applications  ==============
@echo Enabling HTTP port 80 
REM Deprecated: netsh firewall set portopening TCP 80 "HTTP" 
netsh advfirewall firewall add rule name="HTTP" dir=in action=allow protocol=TCP localport=80

@echo Enabling SSL port 443
REM Deprecated: netsh firewall set portopening TCP 443 "SSL" 
netsh advfirewall firewall add rule name="SSL" dir=in action=allow protocol=TCP localport=443

@echo Enabling port for SQL Server Browser Service 'Browse' Button
REM Deprecated: netsh firewall set portopening UDP 1434 "SQL Browser" 
netsh advfirewall firewall add rule name="SQL Browse" dir=in action=allow protocol=TCP localport=1434

@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
REM Deprecated:netsh firewall set multicastbroadcastresponse ENABLE
netsh advfirewall set currentprofile settings unicastresponsetomulticast enable

@echo Complete, Check your results.
pause

Hope it make your future installs easier.

Tested on:

  • Windows Server 2008 R2 Sp1 while installing SQL Server 2012
  • Windows Server 2008 R2 while installing SQL Server 2008 R2

Sources:

http://technet.microsoft.com/en-us/library/cc771046(v=ws.10).aspx

http://support.microsoft.com/kb/947709

http://support.microsoft.com/kb/949543

Note: This script is provided “as is” without any representations or warranties, express or implied – use of this script is at your own risk.

Three Level XML to Javascript Drop Down List

Following a post on an Internet forum, here is a small expansion on my Country / State dropdown selection script to enable a third level drop down. This additional level would allow for a country / state / city drop down selection on an XML file as apposed to just a country / state drop down selection.

I have broken the code in this post into its three sections; the HTML, the XML and an external JavaScript file.

A section of the HTML code:

...

Country slection – with State/Province select


The XML code:




 
     New York
     New Jersey    
 
 
     Sanfransico
     Hollywood    
 


 
     Johannesburg
     Pretoria    
 
 
     Durban
     Pietermaritsburg    
 

A section of the Javascript code:

...
function fillStateList()
{
 var stateList = document.getElementById("cboState")
 
 for (var x = stateList.options.length-1; x >-1; x--)
 {
     stateList.options[x] = null;
 }
 
 var countryListSelected = 
document.getElementById("cboCountry").selectedIndex;
 var numberStates = 
xmlDoc.getElementsByTagName("country")[countryListSelected]
.getElementsByTagName("state").length;
 
 for (var i=0; i<=numberStates-1; i++)
 {
      var currentState =  
xmlDoc.getElementsByTagName("country")
[countryListSelected].getElementsByTagName
("state").getAttribute("name");
     fillList(stateList,currentState,currentState);
 }
 
}

function fillCityList()
{
 var CityList = document.getElementById("cboCity")
 
     for (var x = CityList.options.length-1; x >-1; x--)
 {
     CityList.options[x] = null;
 }
 var countryListSelected = document.getElementById
("cboCountry").selectedIndex;
 var StateListSelected = document.getElementById
("cboState").selectedIndex;
 var numberCities = xmlDoc.getElementsByTagName("country")
[countryListSelected].getElementsByTagName("state")
[StateListSelected].getElementsByTagName("city").length;
 
     for (var i=0; i<=numberCities-1; i++)
 {
       var currentCity =  xmlDoc.getElementsByTagName("country")
[countryListSelected].getElementsByTagName("state")
[StateListSelected].getElementsByTagName("city").firstChild.nodeValue;
     fillList(CityList,currentCity,currentCity);
 }
}

In a real world situation a full world wide city level dissection of data should not be stored in an single XML file as the amount of entries would place too much load on the XML file -More than likely the XML file would not load. To store all the city names across the world the use of a database to hold the data, and call the required data as needed, would better suit the needs.

This script can be used for any XML file in a similar format to handle multiple levels of data and placing the data into drop down lists.

The complete code for this example can be downloaded here: [download#2]

Have fun and happy coding.

JavaScript / XML based Country, State Selection Script

JavaScript Country Selection list[UPDATE] The download is no longer supported or updated and has been removed

UPDATE 26 January 2009: Fixed the XML file load Bug that was affecting Chrome and Safari 

Making use of my XML based Country and State/Province file I have put together a small script to populate two drop down lists with the complete Country and the selected countries Province or State list.

The code is ALL client-side based, so it will suffer from browsers without JavaScript enabled, but it is a very fast and light weight script to use if you want to quickly add a Country and State selection box to your registration forms. This script does not require any database to function.

Code Explanation:
The initial XML Parser script:

if (window.ActiveXObject) 
{
    xmlDoc = new ActiveXObject("Microsoft.XMLDOM");
    xmlDoc.async=false;
    xmlDoc.load("country_state.xml");
}
else if (document.implementation && 
document.implementation.createDocument) 
{
    var xmlhttp = new window.XMLHttpRequest();
    xmlhttp.open("GET","country_state.xml",false);
    xmlhttp.send(null);
    xmlDoc = xmlhttp.responseXML.documentElement;
}

xmlDoc.async=false;

xmlDoc.load("country_state.xml");

Internet Explorer has had a built in XML parser since version 5, but this parser opens XML files differently to other browsers (Firefox, Opera, etc), so to load an XML file you need to first check which browser is running on the client, For IE:

window.ActiveXObject

and a double check for other browsers:

document.implementation && 
document.implementation.createDocument

If IE exists, create an empty Microsoft XML document object:

xmlDoc = new ActiveXObject("Microsoft.XMLDOM");
xmlDoc.async=false;
    xmlDoc.load("country_state.xml");

Or for Other browsers:

    var xmlhttp = new window.XMLHttpRequest();
    xmlhttp.open("GET","country_state.xml",false);
    xmlhttp.send(null);
    xmlDoc = xmlhttp.responseXML.documentElement;

Once the browser check is done it loads the XML document, from here on the code works the same in IE as in other browsers.

On a side note, there is an easy way to test that your XML file has been loaded correctly during development. Just add the document.write or alert function to return the number of nodes in the XML file (in my country_state XML example I would return the number of <country> nodes – with the expected result being 252):

document.write(xmlDoc.getElementsByTagName("country").length);

Once the XML file has loaded correctly, you can perform functions using the file. The next part of my code calls the fillCountryList() function that populates the first select box. I call the fillCountryList() function in my onload() event handler:
HTML:


JavaScript:

function fillCountryList ()
{
    var countryList = document.getElementById("cboCountry");

    for (var x = countryList.options.length-1; x >-1; x--)
    {
        countryList.options[x] = null;
    }
    var countryNames = xmlDoc.getElementsByTagName("country");
    var numberOfCountries = countryNames.length;

    for (var i=0; i<=numberOfCountries-1; i++)
    {
        var currentCountry =  countryNames[i].getAttribute("name");
        fillList(countryList,currentCountry,currentCountry);
    }
}

The fillCountryList() function identifies the first selection box with the specified ID (in my example cboCountry), the function then clears all current entries in the selection box. Clearing entries is good way to prevent duplicate information from being in your selection list prior to processing. On another side note, having VALID data in your selection box is an excellent way to still have your web page form display correctly if JavaScript is disabled on the client; this data can act as an alternative to the JavaScript data; an example of alternative data for the country selection box is listed.
Alternative HTML example:


The above code example is NOT a requirement, and my current example does not have any alternative data, but it can help your website to degrade well under different client environments.

The next part of the fillCountryList() function is getting the country names from loaded XML file:

var countryNames = xmlDoc.getElementsByTagName("country");

This will return an object collection for all nodes with the <country> tag to an array in the countryNames variable. A loop then processes the array and adds each country to the cboCountry select box using the fillList() function.

The state select box is only populated once a new country has been selected using the onChange event handler. Note: you should also be able to use the onClick event handler rather than the onChange event handler.

The fillStateList() function populates the States in the same way as the fillCountryList() function does, but first gets the selected countries array ID out of the country array before looping through THAT countries State/Province array therby filling the cboState select box with valid states.

Hopefully this code and explanation will assist beginners and experts alike. Download and enjoy.

This script has been tested under Firefox, Opera, and Internet Explorer 6 & Internet Explorer 7, Google Chrome and Safari 3.
Currently NOT working in Safari. Safari processes the “document. getElementsByTagName” function differently to other browsers, I will find a fix and release an updated version of the script soon.
UPDATE 26 January 2009: Fixed XML file load for Chrome and Safari :- Please drop me a line if you find any bugs that I may have missed.

Questions and comments are welcome.

License: Creative Commons Attribution 2.5 South Africa License.

Country Selection list with State and Province selections

[UPDATE] Comments are closed on this topic – The download is no longer supported or updated and has been removed

Country State XML exampleI have been looking all over the Internet for an AJAX or similar dynamic drop down tool for Country and Province/State selection (You select a country from a drop down list and the country/state drop down selection automatically populates with that countries list of provinces or states)

The good news is I found various versions, from ASP.NET controls, to simple JavaScript files, but the catch was that although I had the tool, the tool, didn’t have all the counties of the world, never mind their respective states or provinces.

So after much trawling, and searching, I have a complete world country list (including Antarctica) with their respective states and I have placed them all together into an XML file for ease of development use:

– [download#4] - [29.3 KB ZIP file]

Please leave a comment should any errors exist in the file, from broken XML, to incorrect spelling, to having a province not listed, I will fix it if it checks out.

Hope it helps.

[EDIT1 11/03/2008] – Removed Yugoslavia. Changed “Macedonia, Former Yugoslav Republic of” to “Macedonia”. Added Serbia and Montenagro, with states. Thank you omBRE for pointing out my error on the NoBox Media blog.

[EDIT2 24/04/2008] – Corrected spelling of Antarctica (Thanks Ryan) and updated the “Regions” in New Zealand (Thanks David for pointing that out over on the NoBox Media blog).

[EDIT2 08/05/2008] – Corrected spelling of Czech Republic, Thanks Tim.

License and Copyright:
The country and province names in the file are public domain.
XML file in its current format is licensed to Michael John Grove under the Creative Commons Attribution 2.5 South Africa License.