Reset sa password in SQL server

By putting SQL Server into single-user mode, you can log in using any Windows administrator account with sysadmin permissions, even if an equivalent SQL login does not exist. This is very useful when you’ve lost sysadmin access (not-entirely-hypothetical example: someone set up a database, removed all sysadmin accounts except sa, and then couldn’t remember the sa password a few weeks down the line), since you can reset the sa password (or the passwords for any other account, for that matter). This post has more details, but essentially:

  1. Open the SQL Server Configuration Manager
  2. Click on the SQL Server 200{5,8} Services leaf, and stop the SQL Server instance you want to put into single-user mode
  3. Open the properties of the instance, go to the Advanced tab, and in the Startup Parameters option, add “;–m” (exactly, without quotes) to the end of the existing value
  4. Click OK, and restart the SQL Server instance
  5. You can now use sqlcmd with a Windows administrator login to execute SQL commands against the instance
  6. Once you’ve finished, don’t forget to remove the -m parameter and restart the SQL instance again to leave single-user mode đŸ˜‰

Nested elements in custom NAnt tasks

There’s a great post here detailing how to add nested elements within a custom NAnt task. I was writing a task to create IIS websites, and wanted to be able to specify multiple bindings, for example:

<IIS.Website.Create name="MySite" physicalPath="path">
    <bindings>
        <binding type="http" ip="1.2.3.4" port="8080" host="domain.com" />
        <binding type="net.msmq" host="localhost" />
    </bindings>
</IIS.Website.Create>

The important NAnt attributes you’ll need to know about are:

  • BuildElement – denotes a property as representing a nested element within a task, e.g. the <bindings> element above
  • BuildElementArray – denotes a property as representing an array of nested elements, e.g. the set of <binding> elements above
  • ElementName – used to denote a class as representing a build XML element

and the relevant NAnt classes are:

  • DataTypeBase – provides the abstract base class for NAnt types
  • Element – represents a build XML element

In simplified summary, to allow the XML snippet shown at the beginning of this post to be used, you will need to:

  • Create a class to represent the <binding> element, which derives from Element and is attributed with ElementName:
    [ElementName("binding")]
    public class IisBinding : Element
    {
        [TaskAttribute("type")]
        public string Type { get; set; }
    
        [TaskAttribute("ip")]
        public string IpAddress { get; set; }
    
        [TaskAttribute("port")]
        public int Port { get; set; }
    
        [TaskAttribute("host")]
        public string Host { get; set; }
    }
    
  • Create a data type that will represent a set of those <binding> elements. The crucial part is the BindingElements array. You’ll note that this class creates a representation of the IIS bindings (IisBindingDescription) from the elements in the array – you might just use the elements directly, depending on the scenario.
    [ElementName("bindings")]
    public class IisBindingSet : DataTypeBase
    {
        private readonly IList<IisBindingDescription> _bindings = new List<IisBindingDescription>();
    
        public IList<IisBindingDescription> Bindings
        {
            get { return _bindings; }
        }
    
        [BuildElementArray("binding")]
        public IisBinding[] BindingElements
        {
            set
            {
                foreach (var binding in value)
                {
                    ParseBinding(binding);
                }
            }
        }
    
        private void ParseBinding(IisBinding binding)
        {
            ...
    
            _bindings.Add(new IisBindingDescription(type, bindingInformation));
        }
    }
    
  • N.B. for completeness’ sake, this is the IisBindingDescription class – it’s just a DTO.
    public class IisBindingDescription
    {
        public string Protocol { get; private set; }
        public string Information { get; private set; }
    
        public IisBindingDescription(string protocol, string information)
        {
            Protocol = protocol;
            Information = information;
        }
    }
    
  • Add a property to your task with a BuildElement attribute, which unfortunately must also specify the name for the data type element – this must match the name used in the ElementName attribute used when creating the data type.
    [BuildElement("bindings")]
    public IisBindingSet Bindings { get; set; }
    

That’s all there is to it! Now, if you used the XML snippet from the top of this post in your build file, you would end up with two IisBindingDescription objects available in the Bindings property (and two elements in the BindingElements property, if you wanted to use them directly) of the IisBindingSet, which can be used where required.

It should be possible to use the BuildElementArray directly in your Task class, if you didn’t want a surrounding type. Also, there is a BuildElementCollection attribute, which I imagine acts very similarly to BuildElementArray but allows you to use a collection type instead.