Wednesday, December 28, 2011

Clients Not Showing Up In WSUS

We’re using VMWare templates for deploying servers. Despite using sysprep to remove all unique machine identifiers we get an issue where there is a duplicate SUSClientID. In this case the cloned machines have the same SUSClientID and cannot be uniquely identified by WSUS. The solution to this is to create and run the following batch file:
Rem - Batch script to delete duplicate SusClientIDs 
Rem - Implement this script as a "Startup" or "Logon"  script 
Rem - Script creates an output file called %Systemdrive%\SUSClientID.log 
Rem - If the %Systemdrive%\SUSClientID.log is already present, then the script simply exits 

@Echo off 
if exist %systemdrive%\SUSClientID.log goto end 
net stop wuauserv 
net stop bits 
reg delete "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate" /v PingID /f  > %systemdrive%\SUSClientID.log 2>&1 
reg delete "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate" /v AccountDomainSid /f  >> %systemdrive%\SUSClientID.log 2>&1 
reg delete "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate" /v SusClientId /f  >> %systemdrive%\SUSClientID.log 2>&1 
net start wuauserv 
wuauclt.exe /resetauthorization /detectnow          
:end 
exit

Using Windows Command Line To Manage User Accounts

I’ve been doing some work on a Windows Core box and needed to modify some user accounts. As there’s no GUI the command line is the only way to go.

First up, I wanted to find which users exist in the domain. The dsquery command is used to query the Active Directory objects. Putting user at the end of it filters the object type so only user objects are returned:

dsquery user



To filter it down by username we can add the –name directive to filter the results



dsquery user -name Bob*



And finally I wanted to change the password on a user account. The dsmod command is used to modify Active Directory objects. After using the second command above to find the full user DN I was looking for I ran the following command:



dsmod user "CN=username,OU=Users,OU=Company,DC=Domain,DC=local" -pwd <new Password>

How To Delete hiberfil.sys On Windows Server 2008

Hiberfil.sys is a Windows system file used by the hibernation feature. Whenever you hibernate your computer it’s memory contents are written to this file allowing you to quickly resume your computer.

Typically on a server this feature isn’t useful. Although it’s not enabled by default the hiberfil.sys file exists by default and uses several GB of disk space (depending on how much RAM you have installed). As it’s a system file it’s protected and you can’t just delete it. Instead you have to use the following command to remove it:

 

powercfg -h off

Thursday, December 22, 2011

Recommended MSDTC settings for using Distributed Transactions in SQL Server

MSDTC needs to be configured correctly for distributed SQL Server transactions to work properly – one of the main places you will encounter this problem is running distributed transactions across linked servers where part of the transaction runs on the local SQL Server instance and part of the transaction runs on the linked server. If there is a problem then the following error message may be displayed:

OLE DB provider "SQLNCLI10" for linked server "linked server name" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "linked server name" was unable to begin a distributed transaction.



To configure the MSDTC to handle distributed transactions you will need to configure it’s network access as per this technet article. The key steps to remediate this problem are:



Configuring network transactions for MS DTC


If your distributed transactions fail because of network connectivity problems, this failure might be related to the configuration of the DTCs that are participating in the transactions. Check each DTC and make sure that Network MS DTC Access is enabled. You can use the following procedure to accomplish this task.

To enable Network DTC Access for MS DTC transactions

1.Open the Component Services snap-in.

To open Component Services, click Start. In the search box, type dcomcnfg, and then press ENTER.

2.Expand the console tree to locate the DTC (for example, Local DTC) for which you want to enable Network MS DTC Access.

3.On the Action menu, click Properties.

4.Click the Security tab and make the following changes:
In Security Settings, select the Network DTC Access check box.


In Transaction Manager Communication, select the Allow Inbound and Allow Outbound check boxes.



5.Click OK.



 



It’s also advised to check your firewall settings and ensure that DTC can make it through the firewall.

Wednesday, December 21, 2011

Enable Ping Response In Windows Firewall

Note: This post applies to Windows 7.

By default the Windows Firewall will block any ping requests made to the host. To enable a ping response we need to add a rule to the firewall. First go to Control Panel –> Windows Firewall. On the left hand side select Advanced Settings.

Windows Firewall 1

Select the Inbound Rules. Sort the rules by name and find File and Printer Sharing (Echo Request – ICMPv4-In). There’s typically 3 versions of this rule, one for each “network” defined in Windows. Enable this rule for the required networks.

Windows Firewall Rules

Just some background – ping requests work using a special network protocol called ICMP. ICMP is a network management protocol and an Echo Request is a type of packet set via ICMP used to determine if a host is up or down (and indicate the response time). Often these ping requests are discarded in order to tighten security. One of the first thing potential hackers may do is ping an IP address to determine if the machine is “on” before trying anything further.

Tuesday, December 20, 2011

How To Use The Microsoft Security Assessment Tool

The Microsoft Security Assessment Tool is a questionnaire used to audit the security of your environment. It identifies areas where security is tight and areas that need improvement. It’s a great tool targeted at SME’s that are too small to employ a full time security administrator but want to have some confidence they are taking some precautions. At the very least it’s a great framework to identify potential security issues that you may not have thought of.

The tool is quite generic for the most part although it does cover off Microsoft technologies in more detail than other technologies.

After filling out the questionnaire a report is generated giving you a rating in different security areas. It also generates a priority task list that you can work through to make your environment more secure.

The tool can be downloaded from http://technet.microsoft.com/en-us/security/cc185712. You should be aware that the tool does install a copy of Microsoft SQL Server Compact Edition to store the backend information for the application.

Error 2738 Occurs When Installing An MSI Package

This occurs when there’s a problem running vbscript with elevated permissions. To confirm that its the problem run the MSI installer from the command line (with administrator permissions – right click on the command prompt link in the start menu and select run as administrator) like this:

MSIEXEC /i <msi package> /lv log.txt



Once the error occurs open the log file and look for the following output:



DEBUG: Error 2738:  Could not access VBScript runtime for custom action 


We need to re-register the vbscript dll for this error to be resolved. First step is to remove the old registration by deleting the registry key:



# 32 bit windows
HKCU\SOFTWARE\Classes\CLSID\{B54F3741-5B07-11CF-A4B0-00AA004A55E8}

# 64 bit windows
HKCU\SOFTWARE\Classes\Wow6432Node\CLSID\{B54F3741-5B07-11CF-A4B0-00AA004A55E8}


Once this is done you need to re-register the DLL using the following command:



# 32 bit windows
cd %windir%\system32
regsvr32.exe vbscript.dll

# 64 bit windows
cd %windir%\ syswow64
regsvr32.exe vbscript.dll

Monday, December 19, 2011

How To Install A LAMP Server On Ubuntu

Open up a terminal session (or SSH onto the Ubuntu server) and run the following command:

sudo apt-get install apache2 php5 libapache2-mod-php5 mysql-server libapache2-mod-auth-mysql php5-mysql phpmyadmin 



This will download approximately 40MB and use 105MB of disk space. During the install you will be prompted a couple of times:




  • mysql admin password – enter a password of your choosing


  • The next screen will ask you if you would like your webserver reconfigured automatically. Select apache2 and click ok.


  • Congigure database for phpmyadmin with dbconfig-common? Select Yes


  • Enter the mysql password you entered before for the phpmyadmin mysql configuration


  • Enter a new administrator password for phpmyadmin



And voila – we done! To test that it’s worked point your browser to http://< server IP>. If everything’s worked you should have a page saying “It Works!”. To use the phpmyadmin tool to administer your site point your browser to /phpmyadmin">http://<serverIP>/phpmyadmin.



And finally the default web root is located at /var/www.

Sunday, December 18, 2011

Setup Nagios Monitoring – The Easy Way Part 3

In the first two parts of this guide we’ve installed Nagios 3 onto an Ubuntu server. We’ve restructured the layout of the configuration files so that they are more manageable. In this step we will look at time periods and how to configure them.

Time periods are used just that, a schedule that defines when things should or should not happen. Typical time periods defined in Nagios include:

  • 24x7 – All the time, from 00:00 to 23:59 Monday to Sunday.
  • Work Hours – 09:00 to 17:00 Monday to Friday.
  • After Hours – All the time outside of the work hours.
  • Never – Empty schedule with no times defined.

These time periods are used in a few places. Firstly they can be used to determine when host and service checks occur. For example we may want critical production servers to be monitored 24x7 but only want non critical servers monitored during business hours.

The second major place they are used is to determine when contacts should be alerted that problems have occurred. For example we may send alerts to an administrators email group during business hours but send an alert via SMS after work hours.

In the /etc/nagios3/timeperiods folder we’ll create four different time periods:

  • /etc/nagios3/timeperiods/24x7.cfg
  • /etc/nagios3/timeperiods/never.cfg
  • /etc/nagios3/timeperiods/afterhours.cfg
  • /etc/nagios3/timeperiods/workhours.cfg

First we’ll start by defining the 24x7 time period. Create the file /etc/nagios3/timeperiods/24x7.cfg as shown below:

# This defines a timeperiod where all times are valid for checks,
# notifications, etc. The classic "24x7" support nightmare. :-)

define timeperiod{
timeperiod_name 24x7
alias 24 Hours A Day, 7 Days A Week
sunday 00:00-24:00
monday 00:00-24:00
tuesday 00:00-24:00
wednesday 00:00-24:00
thursday 00:00-24:00
friday 00:00-24:00
saturday 00:00-24:00
}


Next we’ll create the never time period as shown below:



# Here is a slightly friendlier period during work hours
define timeperiod{
timeperiod_name never
alias Never

}


 



Now we’ll create the afterhours definition:




# The complement of workhours
define timeperiod{
timeperiod_name nonworkhours
alias Non-Work Hours
sunday 00:00-24:00
monday 00:00-09:00,17:00-24:00
tuesday 00:00-09:00,17:00-24:00
wednesday 00:00-09:00,17:00-24:00
thursday 00:00-09:00,17:00-24:00
friday 00:00-09:00,17:00-24:00
saturday 00:00-24:00
}




And finally we’ll create the workhours definition:



# Here is a slightly friendlier period during work hours
define timeperiod{
timeperiod_name workhours
alias Standard Work Hours
monday 09:00-17:00
tuesday 09:00-17:00
wednesday 09:00-17:00
thursday 09:00-17:00
friday 09:00-17:00
}



 



These four time period definitions should cover most smaller IT shops. If you need another time period definition it’s as simple as creating a new text file in the /etc/nagios3/timeperiods folder and define the time periods accordingly.



So we’ve finished defining the time periods, in the next blog post we’ll look at defining contacts in Nagios which will make use of the time periods we’ve defined here.



 



 



 



Saturday, December 17, 2011

Removing GRUB and Restoring the Windows Boot Loader

Note: This has been tested with Windows Vista

In this post we’ll be using the Windows Vista boot disk to restore the boot loader.

  1. Insert the Windows Vista installation disk into your CD \ DVD Rom drive
  2. Boot your computer, when prompted press any key to enter the installation
  3. Confirm your language and input settings and click Next
  4. Click Repair Your Computer
  5. Select the operating system you want to keep and click on Next
  6. In the System Recovery Options click on Command Prompt
  7. In the command prompt type Bootrec.exe /fixmbr and press enter. The message “Operating completed successfully” will be displayed once the command has finished running.
  8. Now reboot your computer

And voila – the GRUB boot loader has now been replaced with the Windows boot loader. If you haven’t done so already you will want to remove your old Linux partitions and recover your disk space.

Disable The Auto Hide Feature For The Unity 2-D Launcher In Ubuntu 11.10

Really loving the Ubuntu Unity interface - it's taking a while to get used to but slowly becoming a convert. My main gripe however is that the launcher is set to Auto Hide by default. Each to their own I guess but I prefer to have the launcher always showing on the screen - it's kind of handy to have it there if you run quite a few programs at the same time. To stop the Unity launcher from auto-hiding we first need to install the Compiz Settings Manager using the command below:
sudo apt-get install compizconfig-settings-manager

In the Start menu do a search for compiz:




In the CompizConfig Settings Manger type in Unity in the filter box. Open the Ubuntu Unity Plugin:


Change the Hide Launcher setting from Auto Hide to Never:


The last step I had to do was reboot my computer for this setting to take affect. Kind of dissapointed that I had to do this - I'm not running Windows after all :P

Friday, December 16, 2011

How To Compress All Tables And Indexes In A Database

This code will compress all tables and indexes in a SQL Server database.

Warning: It doesn’t discriminate or try to work out which tables and indexes are best candidates for compression – it just does everything. I use this script mainly for archive databases that don’t get updated. Also if the database is large it can take a long time to run and it will hit the cpu hard whilst it’s doing the compression.

/*
Run in the database that will be compressed
*/

DECLARE @sqlcmd nvarchar(1000)


DECLARE compress_cursor CURSOR FOR
SELECT DISTINCT 'ALTER TABLE ' + '[' + s.[name] + ']'+'.' + '[' + o.[name] + ']' + ' REBUILD WITH (DATA_COMPRESSION=PAGE);'
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.schemas AS s WITH (NOLOCK)
ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK)
ON i.[object_id] = ps.[object_id]
AND ps.[index_id] = i.[index_id]
WHERE o.[type] = 'U'

UNION ALL

SELECT 'ALTER INDEX '+ '[' + i.[name] + ']' + ' ON ' + '[' + s.[name] + ']' + '.' + '[' + o.[name] + ']' + ' REBUILD WITH (DATA_COMPRESSION=PAGE);'
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.schemas s WITH (NOLOCK)
ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK)
ON i.[object_id] = ps.[object_id]
AND ps.[index_id] = i.[index_id]
WHERE o.type = 'U' AND i.[index_id] >0


OPEN compress_cursor;

FETCH NEXT FROM compress_cursor INTO @sqlcmd

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @sqlcmd
EXEC sp_executesql @sqlcmd
FETCH NEXT FROM compress_cursor INTO @sqlcmd
END
CLOSE compress_cursor
DEALLOCATE compress_cursor


Thursday, December 15, 2011

Change the owner on all SQL Agent Jobs

This SQL Script will change the owner of all the jobs on a SQL Server to the owner specified at the top of the script.

/*
Change the owner on all SQL Agent Jobs
@Author William Brown
@History 2011-12-15 Initial Script Write
*/

declare @owner varchar(100)
set @owner = 'SA' -- Set the name of the new owner here

DECLARE @name VARCHAR(1000)
DECLARE @sql NVARCHAR(2000)

DECLARE job_cursor CURSOR FOR
SELECT name
FROM MSDB.dbo.sysjobs j
ORDER BY name ASC;

OPEN job_cursor;
FETCH NEXT FROM job_cursor INTO @name
WHILE @@FETCH_STATUS = 0

BEGIN
SET @sql = 'EXEC MSDB.dbo.sp_update_job @job_name = ''' + @name + ''', @owner_login_name = ''' + @owner + ''''

PRINT @sql
EXEC sp_executesql @sql

FETCH NEXT FROM job_cursor INTO @name;
END

CLOSE job_cursor;
DEALLOCATE job_cursor;

Wednesday, December 14, 2011

SQL Server 2008 (and R2) Evaluation Expired Issues

I've been seeing the following error message popup on a regular basis lately. Turns out that when I installed the SQL Server Client Tools (SQL Server Management Studio and BIDS) that I was a bit lazy and didn't use a license key, instead selecting Evaluation Edition. I figured that since it was only the client tools it wouldn't be a drama... turns out I was wrong.

BNEVW03 on localhost


Reinstalling the tools or doing an Edition Upgrade doesn't help either - instead the same problem keeps on occuring. Turns out that you need to do a registry hack to get the Edition Upgrade to work for management studio. In particular we need to update HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\ConfigurationState\CommonFiles from 1 to 3. We then need to re-run the Edition Upgrade from Evaluation Edition to another edition (Express didn't work for me, standard worked fine though).

Tuesday, December 13, 2011

Using Arping To Scan An IP Range For Duplicate IP Addresses

This script will scan an IP range for duplicate IP addresses using the arping command (Linux). Firstly you will need to install arping – in Ubuntu this can be done using the command:

sudo apt-get install arping


Then we need to create the bash script below. Make sure you customise the IP range to suit the range you want to scan. In the below example the script just appends the last number in the IP address sequence so you can only do 254 addresses in a single hit.



for i in $(seq 1 254);
do
echo "Checking 192.168.196.${i}";
sudo arping -q -d -i eth0 -c 2 192.168.196.${i}; [ $? -ne 0 ] && echo "192.168.196.${i} duplicate";
done



Now run this script from the command line to scan the IP range.

Report Server Command Line Tools Missing From SSRS Install

I searched around my computer for about an hour for this… grr!!!

According to the documentation RS command line tools are installed as part of the reporting services install. They are installed to c:\Program Files\Microsoft SQL Server\100\Tools\Binn by default (unless you change the path in your installation).

When I checked this path they weren’t installed. After doing a Windows file search for rs.exe nothing turned up – I thought I must’ve missed something in the installation.

Turns out that instead they are installed to C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn. It must have something to do with it being a 32 bit utility even though the rest of the SQL Server and Reporting Services installation is a 64 bit install.

Monday, December 12, 2011

Updating SSRS Subscription Addresses Via TSQL

Just need to update the email addresses at the top of the script and run against the reportserver database.

/****** Script for SelectTopNRows command from SSMS  ******/
declare @OldEmailAddress varchar(1000)
declare @NewEmailAddress varchar(1000)

set @OldEmailAddress = 'Old Address'
set @NewEmailAddress = 'New Address'

--Now Update them to a new user that you want to receive the subscriptions
BEGIN TRANSACTION
UPDATE Subscriptions
SET ExtensionSettings = CONVERT(NTEXT,REPLACE(CONVERT(VARCHAR(MAX),ExtensionSettings),@OldEmailAddress,@NewEmailAddress))
FROM ReportServer.dbo.Subscriptions
WHERE CONVERT(VARCHAR(MAX),ExtensionSettings) LIKE '%' + CONVERT(VARCHAR(100),@OldEmailAddress) + '%'
COMMIT TRANSACTION

--OPTIONAL: Now just return a listing of those records that were updated
SELECT * FROM [ReportServer].[dbo].[Subscriptions]
WHERE CONVERT(VARCHAR(MAX),ExtensionSettings) LIKE '%' + CONVERT(VARCHAR(100),@NewEmailAddress) + '%'
GO

Howto Shrink All Logfiles On SQL Server

I use this script as a backup when something hasn’t gone to plan and the transaction logs have blown out on SQL Server. It will shrink all of the logfiles on the sql server and free up some space – provided the transaction log backups have been working of course.

-- Shrink every logfile on the database server
declare @ssql nvarchar(4000)
set @ssql= '
if '
'?'' not in (''tempdb'',''master'',''model'',''msdb'') begin
use [?]
declare @tsql nvarchar(4000) set @tsql = '
'''
declare @iLogFile int
declare @sLogFileName varchar(55)
declare LogFiles cursor for
select fileid from sysfiles where status & 0x40 = 0x40
open LogFiles
fetch next from LogFiles into @iLogFile
while @@fetch_status = 0
begin
set @tsql = @tsql + '
'DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 500) ''
fetch next from LogFiles into @iLogFile
end
--set @tsql = '
'USE [?]; '' + @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql
--print @tsql --for debugging
exec(@tsql)
close LogFiles
DEALLOCATE LogFiles
end'


exec sp_msforeachdb @ssql