SQL 2014 Unattended Install

**Note – I’m not a SQL guy, I just have to do the basics. Any post install configuration is specific to the environment and requirements of the system and organisation**

SQL 2014 unattended install is reasonably straightforward once you understand the switches.

There are two parts of the install, firstly the command line switches to run the install and the second is the configuration file referenced during install.

To create the Configuration File run the GUI install selecting all the necessary options required. At the final point before clicking Install you can see where the automatically created configurationfile.ini is located:

SQL 2014

This configuration file now needs to be updated with the necessary information for the environment. For us its setting the GUI to progress only, service accounts, DB and logs locations etc. A sample configuration is shown below:

;SQL Server 2014 Configuration File
[OPTIONS]
; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.
ACTION=”Install”
; Use the /ENU parameter to install the English version of SQL Server on your localized Windows operating system.
ENU=”True”
; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.
; UIMODE=”Normal”
; Setup will not display any user interface.
QUIET=”False”
; Setup will display progress only, without any user interaction.
QUIETSIMPLE=”True”
; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found.
UpdateEnabled=”True”
; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature.
ERRORREPORTING=”False”
; If this parameter is provided, then this computer will use Microsoft Update to check for updates.
USEMICROSOFTUPDATE=”False”
; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server Data Tools, and other shared components.
FEATURES=SQLENGINE
; Specify the location where SQL Server Setup will obtain product updates. The valid values are “MU” to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services.
UpdateSource=”E:\SQL\Updates”
; Displays the command line parameters usage
HELP=”False”
; Specifies that the detailed Setup log should be piped to the console.
INDICATEPROGRESS=”True”
; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.
X86=”False”
; Specify the root installation directory for shared components.  This directory remains unchanged after shared components are already installed.
INSTALLSHAREDDIR=”C:\Program Files\Microsoft SQL Server”
; Specify the root installation directory for the WOW64 shared components.  This directory remains unchanged after WOW64 shared components are already installed.
INSTALLSHAREDWOWDIR=”C:\Program Files (x86)\Microsoft SQL Server”
; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS).
INSTANCENAME=”MSSQLSERVER”
; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature.
SQMREPORTING=”False”
; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance.
INSTANCEID=”MSSQLSERVER”
; Specify the installation directory.
INSTANCEDIR=”C:\Program Files\Microsoft SQL Server”
; Agent account name
AGTSVCACCOUNT=”domain\sqlagent-service-account”
; Auto-start service after installation.
AGTSVCSTARTUPTYPE=”Automatic”
; CM brick TCP communication port
COMMFABRICPORT=”0″
; How matrix will use private networks
COMMFABRICNETWORKLEVEL=”0″
; How inter brick communication will be protected
COMMFABRICENCRYPTION=”0″
; TCP port used by the CM brick
MATRIXCMBRICKCOMMPORT=”0″
; Startup type for the SQL Server service.
SQLSVCSTARTUPTYPE=”Automatic”
; Level to enable FILESTREAM feature at (0, 1, 2 or 3).
FILESTREAMLEVEL=”0″
; Set to “1” to enable RANU for SQL Server Express.
ENABLERANU=”False”
; Specifies a Windows collation or an SQL collation to use for the Database Engine.
SQLCOLLATION=”Latin1_General_CI_AS”
; Account for SQL Server service: Domain\User or system account.
SQLSVCACCOUNT=”domain\sql-service-account”
; Windows account(s) to provision as SQL Server system administrators.
SQLSYSADMINACCOUNTS=”domain\username” “domain\username”
; Default directory for the Database Engine backup files.
SQLBACKUPDIR=”E:\SQL\Backup”
; Default directory for the Database Engine user databases.
SQLUSERDBDIR=”E:\SQL\Data”
; Default directory for the Database Engine user database logs.
SQLUSERDBLOGDIR=”E:\SQL\Logs”
; Directory for Database Engine TempDB files.
SQLTEMPDBDIR=”E:\SQL\TempDB”
; Directory for the Database Engine TempDB log files.
SQLTEMPDBLOGDIR=”E:\SQL\TempDBLogs”
; Provision current user as a Database Engine system administrator for %SQL_PRODUCT_SHORT_NAME% Express.
ADDCURRENTUSERASSQLADMIN=”False”
; Specify 0 to disable or 1 to enable the TCP/IP protocol.
TCPENABLED=”1″
; Specify 0 to disable or 1 to enable the Named Pipes protocol.
NPENABLED=”0″

Note the UpdateSource option. For Service Packs or Cumulative Updates simply drop the .exe into this folder and it will be slipstreamed at install.

I now have a batch file to call setup.exe and specify the configuration file above. I put the configuration file into the same folder as the setup.exe but it doesn’t really matter. As I have added specific service accounts I also need to add the password switches.

## Create necessary Folders for DB/Log/Backup Files ##

mkdir E:\SQL\Logs
mkdir E:\SQL\Data
mkdir E:\SQL\Backup

"\\SQL-Files-Location\Setup.exe" /ConfigurationFile="\\SQL-Files-Location\SQL-ConfigurationFile.ini" /IacceptSQLServerLicenseTerms=True /AGTSVCPASSWORD=password /SQLSVCPASSWORD=password

Before anyone shouts yes I know the passwords are in clear in the setup batch file but my deployment folder is locked down tightly and this works while I work out a way to run this more securely.

 

Advertisements

Remove unused NICs from BGInfo

Like many organisations we use BGInfo on all Windows systems to provide a simple way to view the machine name, IP address etc.

One thing we have noticed is on laptops which have standard wireless, Bluetooth, SSL VPN etc there are a number of devices that do not have IP addresses assigned. This isn’t a technical issue but on some screens it does look a bit of a mess (see below)

BGInfo-ALL-IP

We wanted to show only ‘live’ IP addresses and remove the blanks to neaten things up slightly. Natively I couldn’t find a simple option so ended up on google and found a number of threads showing similar results. Most of the results however also showed the IPV6 address which we don’t currently need and confuses the end user when the support team ask for their IP address.

So, show active IPs, IPv4 only. I also wanted to show the Windows 10 build as this is starting to become more and more important with the six monthly release cycle and the necessary changes required.

Active IPs

I ended up creating three .vbs scripts (IP, DNS Servers, Gateway) and linking each one to a custom field within BGInfo.

IP Address VBS:

strMsg = ""
strComputer = "."

Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set IPConfigSet = objWMIService.ExecQuery("Select IPAddress from Win32_NetworkAdapterConfiguration WHERE IPEnabled = 'True'")

For Each IPConfig in IPConfigSet
  If Not IsNull(IPConfig.IPAddress) Then
    For i = LBound(IPConfig.IPAddress) to UBound(IPConfig.IPAddress)
     If Not Instr(IPConfig.IPAddress(i),":") > 0 Then
        strMsg = strMsg & IPConfig.IPAddress(i) & vbcrlf & vbtab
      End If
    Next
  End If
Next

Echo strMsg

 

DNS Server VBS:

strMsg = ""
strComputer = "."

Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set IPConfigSet = objWMIService.ExecQuery("Select DNSServerSearchOrder from Win32_NetworkAdapterConfiguration WHERE IPEnabled = 'True'")

For Each IPConfig in IPConfigSet
  If Not IsNull(IPConfig.DNSServerSearchOrder) Then
    For i = LBound(IPConfig.DNSServerSearchOrder) to UBound(IPConfig.DNSServerSearchOrder)
      If i = 0 Then
        strMsg = strMsg & IPConfig.DNSServerSearchOrder(i)
      ElseIf i > 0 Then
        strMsg = strMsg & vbcrlf & vbtab & IPConfig.DNSServerSearchOrder(i)
      End If
    Next
  End If
Next

Echo strMsg

 

Default Gateway VBS:

strMsg = ""
strComputer = "."

Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set IPConfigSet = objWMIService.ExecQuery("Select DefaultIPGateway from Win32_NetworkAdapterConfiguration WHERE IPEnabled = 'True'")

For Each IPConfig in IPConfigSet
  If Not IsNull(IPConfig.DefaultIPGateway) Then
    For i = LBound(IPConfig.DefaultIPGateway) to UBound(IPConfig.DefaultIPGateway)
     If Not Instr(IPConfig.DefaultIPGateway(i),":") > 0 Then
        strMsg = strMsg & IPConfig.DefaultIPGateway(i) & vbcrlf
      End If
    Next
  End If
Next

Echo strMsg

 

We now need to add these vbs scripts into BGInfo as Custom Fields:

BGInfo-New Field

Now add to the BGInfo information as a standard field and the result removes all gaps!

BGinfo-Min-IP

Windows 10 Build

Finally we need to add the Windows 10 build number. This is much easier than above as the information is held within the registry so the BGInfo custom field can simply point to it:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ReleaseID

 

 

Clone a Domain Controller for testing

Often I find the need to clone a DC to carry out some testing (lovely and simple in a virtualised environment). With Server 2008 R2 and earlier this wasn’t much fun with DNS and Time changes causing various issues along with removing all the other DCs and seizing roles etc.

With Server 2012 R2 Domain Controllers its suddenly far easier. The updated Microsoft instructions are here but the steps are essentially:

  1. Active Directory Users and Computers – Navigate to Domain Controllers, right click and delete the unwanted DC. Select ‘Delete this Domain Controller anyway’.
  2. Active Directory Sites and Services – Delete unwanted sites and any unwanted DCs in the site you are retaining.

Thats it! One thing that is now built in is seizing roles. When you delete the DC that has one of the FMSO roles you get a pop-up informing you of this and the option to move roles to the DC you are working on, much easier!

 

LAPS – Local Admin Password Solution

As part of my role I took over a reasonable sized Windows Server infrastructure. Many servers have now been replaced but some of the originals remain. I am currently running security tasks across all areas and one was to change the local admin password and account name for all servers.

There used to be generally two ways to acheive this:

  1. Manually reset each password and save into a spreadsheet.
  2. Use Group Policy Preferences to set a standard password.

Creating a spreadsheet of passwords fills me with dread and sods law says you won’t be able to open it when you need it. Using GPP is a reasonable idea but the implementation stores passwords in clear text within the Sysvol folder (read-only to anyone on the network). Microsoft have realised this is an issue and have actually prevented the GPP solution from working as at May 2014.

The new, approved solution is LAPS – Local Admin Password Solution. This is a combination of dll and GPO configurations which regularly, automatically reset the local administrator password and store the information in Active Directory. Its a free tool available here complete with downloadable instructions. The instructions are very clear and easy to follow so I won’t detail them here but give a quick overview.

  1. Install the management agent onto a management server.
  2. Extend the schema and make some small permission changes to the AD OU/s you wish to manage the servers in.
  3. Create a GPO with password complexity and refresh time you require.
  4. Install agent onto server to be managed.

Once complete (the above took 30 minutes not including Change Control and testing). You can retrieve the password in 3 ways:

  1. Using the LAPS tool

LAPS

2. Inspecting the attribute editor in Active Directory

AD

3. Viewing in PowerShell

PS

I’m Back!

Its been an awful long time since I have updated this blog so apologies first of all.

Whilst I was working for Steria I was involved in multiple classified systems so posting technical information online wasn’t the best of career moves!

I currently work for Hall & Woodhouse based in Dorset, UK as the IT Technical Manager.

wuauclt

switches

/DetectNow
/ReportNow
/RunHandlerComServer
/RunStoreAsComServer
/ShowSettingsDialog
/ResetAuthorization
/ResetEulas
/ShowWU
/ShowWindowsUpdate
/SelfUpdateManaged
/SelfUpdateUnmanaged
/UpdateNow
/ShowWUAutoScan
/ShowFeaturedUpdates
/ShowOptions
/ShowFeaturedOptInDialog
/DemoUI

Configuring vSphere 5.0 Update Manager Download Service

The UMDS (Update Manager Download Service) is different from the VMware Update Manager and is used to download patches for non-internet connected vmware environments (similar to an offline WSUS). The installer is within the vSphere 5 DVD under the “umds” folder.

1. Install UMDS as a normal VMware application selecting local/remote databases etc. You cannot install UMDS on a system that already has Update Manager installed. The important selection during setup is to set the path to where you actually want the patches to be downloaded to (rather than a folder within C:\Program Files\Vmware …….).

2. To configure UMDS you need to run the vmware-umds executable from an elevated command prompt. Note: switches are case-sensitive!

Disable update for Virtual Appliance & enable ESXi host downloads

vmware-umds -S –enable-host –disable-va

Show current download platforms

vmware-umds -G

Removes all platforms other than 5.0

vmware-umds -S -d embeddedEsx-3.5.0-INTL esx-3.5.0-INTL embeddedEsx-4.0.0 esx-4.0.0 embeddedESX-4.1.0 esx-4.1.0

Download updates using the current configuration

vmware-umds -D

Export repository

vmware-umds -E –export-store <path to export>

Full configuration and switches on the vmware site http://pubs.vmware.com/vsphere-51/index.jsp?topic=%2Fcom.vmware.vsphere.update_manager_utility.doc%2FGUID-3B44E90F-2589-4FFF-B39F-CA407AFE0619.html