Archive

Archive for December, 2012

System Documentation Framework

22nd December, 2012 Leave a comment

Within IT, one of the things we know we should do, but never seem to have the time is System Documentation. One of the worst aspects is where to start!
Below is a framework that could be used for system documentation and assisting in capturing all the relevant information and ensuring that all aspects of the system are considered (e.g. Disaster Recovery, etc.). As it’s a framework, not all areas will necessarily need to be completed for each system.

Wherever possible, include the supplier’s documentation rather than writing your own version, but it is worth recording the configuration changes to make the system operate in your own environment.

System Documentation Framework

Contact Information

  • Supplier Details
    Include Supplier Name, Address, Telephone, Web Site and any other useful information.
  • Supplier Support
    The supplier’s Helpdesk telephone number and email. Also include any additional information that may be asked for (such as a serial or support number). It is also worth including support agreement information (e.g. their SLA, if they work 24 hours, etc.) and this will assist giving estimated time to fix for our internal customers.
  • Internal Engineers
    List the Primary and Secondary engineers who installed and support the system. This saves time asking who knows about the system. You may also wish to list System Support staff if it is appropriate (i.e. If they maintain a desktop client installation).
  • Internal Customer information
    This should include who “owns” the system and the internal customer base (this will allow us to know who should be contacted if there is downtime).

System Overview

  • Visio Topology
    A visual representation of the system.
  • Overview
    Include Server names, IP addresses and descriptions of the components of the system.
  • Service Accounts
    Include the usernames of account used in the system and what access rights these have.
  • Interfaces
    If the system interfaces with other system (either to send or receive information), specify how the interface works.

Installation

  • Prerequisites
    List any prerequisites that are required before the system can be installed.
  • Installation
    The supplier should supply full installation instructions, however, any changes to the default options or any configuration details (such as SQL Server names) should be recorded here.

Shutdown and Start-Up Sequences

  • Shutdown Sequence
    List any actions that need to be taken to shut the system down.
  • Start-up Sequence
    List any special actions that need to be taken when the system is started.

Business As Usual

  • System Specific Procedures
    Describe any maintenance procedures or any other system specific procedures that need to be performed.
  • How To’s/Knowledge Base
    List any procedures that may need to be performed to correct the system, or configuration issues.

Recovery Procedure

  • Backup Policy
    Include how often the system(s) are backed up and the reasons for this (i.e. a web server that does not store data, may not need to be backed up).
  • Backup Process
    Describe how the backup process works. For example: The internal backup of SQL Server is used to create a BAK file of the database at 17:00 each day and NetBackup copies this to tape at 22:00.
  • Restore Process
    Describe the restore process and any additional configuration changes that need to be made after this process.

 

Advertisements
Categories: Uncategorized

NDR for disabled Active Directory accounts

10th December, 2012 Leave a comment

When staff leave the organisation, are on long term sick, or take a sabbatical we disable their account in Active Directory. This also used to stop emails being delivered to their mailbox (returning a None Delivery Report [NDR] to the sender), which was very useful to let other staff know they were no longer available and stopped important emails sitting in mailboxes that were no longer checked.

Unfortunately, one of the Service Packs (for Exchange 2003, if I recall correctly) “fixed” this issue. Our workaround was to restrict disabled accounts to only accept emails from themselves. Whilst this can be done via the GUI, a script is a lot quicker.

The PowerShell script below adds restrictions to disabled accounts, the removal is removed on accounts that are re-enabled.

# Constants to modify multi-valued AD attributes.
$ADS_PROPERTY_CLEAR = 1
$ADS_PROPERTY_UPDATE = 2
$ADS_PROPERTY_APPEND = 3
$ADS_PROPERTY_DELETE = 4

# LDAP path to start search from
$RootOU = "LDAP://OU=User Accounts,DC=domain,DC=net"

Write-Host "Accounts that are disabled but accepting emails"

$search = New-Object DirectoryServices.DirectorySearcher([ADSI]$RootOU)
$Search.PageSize = 1000
$search.filter = "(&(objectCategory=person)(objectClass=user)(userAccountControl:1.2.840.113556.1.4.803:=2)(!authOrig=*)(|(homeMDB=*)(msExchHomeServerName=*))(!sAMAccountName=command*))"
$results = $search.FindAll()

foreach($result in $results){
   $User = $result.GetDirectoryEntry()
   $distinguishedName = $User.distinguishedName
   $distinguishedName
   $User.PutEx($ADS_PROPERTY_UPDATE,"authOrig",@($distinguishedName))
   $User.SetInfo()
}

Write-Host "Accounts that are enabled but not accepting emails"

$search = New-Object DirectoryServices.DirectorySearcher([ADSI]$RootOU)
$Search.PageSize = 1000
$search.filter = "(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2)(authOrig=*)(|(homeMDB=*)(msExchHomeServerName=*)))"
$results = $search.FindAll()

foreach($result in $results){
   $User = $result.GetDirectoryEntry()
   $distinguishedName = $User.distinguishedName
   $distinguishedName
   $User.PutEx($ADS_PROPERTY_CLEAR,"authOrig",$null)
   $User.SetInfo()
}
Categories: Accounts, PowerShell

PowerShell Script to export data from Active Directory

9th December, 2012 Leave a comment

I was asked to assist a vendor who was working with our Unix/Oracle team. He was trying to import data from Active Directory into an Oracle database.

 

I’m no expert on Oracle, but it appears that the LDAP connector simply returned blobs of data, this then had to be parsed, but there seems to be no order to the data returned. The was before we starting looking at converting the objectGUID or seeing if we’d hit the 1000 record AD limit (which SQL Server suffers from).

 

I’d recently been on a PowerShell course delivered by Microsoft, so I thought I’d write a quick script to export the data. In this case, we output the result to a CSV file, but it is possible to update the database directly. Anyway, here’s the script we used:

 

# LDAP path to start search from
$RootOU = "LDAP://OU=Regional Accounts,OU=User Accounts,DC=domain,DC=net"

# CSV path
$CSVpath = "C:\TEMP\ADusers.csv"

$search = New-Object DirectoryServices.DirectorySearcher([ADSI]$RootOU)
$Search.PageSize = 1000
$search.filter = “(&(objectCategory=person)(objectClass=user))"
$results = $search.FindAll()

$myData = @()
foreach($result in $results){
   $User = $result.GetDirectoryEntry()
   $myData += ( $User | Select-Object -Property @{Name="objectGUID";Expression={($_.objectGUID | foreach { $ofs="" } { "{0:X2}" -f $_})}},
   @{Name="sAMAccountName";Expression={$_.sAMAccountName}},
   @{Name="homeDirectory";Expression={$_.homeDirectory}},
   @{Name="mail";Expression={$_.mail}},
   @{Name="sn";Expression={$_.sn}},
   @{Name="givenName";Expression={$_.givenName}},
   @{Name="mailNickName";Expression={$_.mailNickName}},
   @{Name="title";Expression={$_.title}},
   @{Name="postOfficeBox";Expression={$_.postOfficeBox}},
   @{Name="accountexpires";Expression={[datetime]::fromfiletime($_.ConvertLargeIntegerToInt64($_.accountexpires[0])).ToString("s")}},
   @{Name="employeeNumber";Expression={$_.employeeNumber}}
)
}
$myData | Export-csv -path $CSVpath -notype

# Or, you can output to a grid
#$myData | Out-GridView
Categories: Accounts, PowerShell

Check List – Move SQL Database

8th December, 2012 Leave a comment

I’ve been tasked with moving almost 100 SQL databases from our old SQL Cluster to our brand new SQL Servers. To assist me, I’ve created a checklist, which may be useful to others…

Check List – Move SQL Database

This check list is to assist in moving a database from one SQL server to another.

  • Identify application connection logon details
  • Create logins
  • Create destination folders for MDF and LDF files
  • Take frontend application off line
  • Backup Database
  • Take Database Offline
  • Copy backup file to new SQL server
  • Restore Database
  • Change Database SQL Compatibility Level
  • Re-create connection logon details
  • Update application connection logon details
  • Delete the original Database

Identify application connection details

Identify how the application connects to SQL. If a SQL user account is used, obtain the password (this may be in a connection string in the application). If it’s not available, discuss with Application support about creating a new password. Check if other accounts have access to the database.

Create logins

Create logins for Windows Users/Groups or SQL logins. Check language settings.

Create destination folders for MDF and LDF files

Create the folders when the database files will be stored.

Take frontend application offline

If possible, take the frontend application offline. This will stop new data being written to the database.

Backup Database

Name the backup file similar to “<DatabaseName>_PreMove.BAK” so it can be identified from other database that may also be being move.

Take Database Offline

Right click the database, highlight Tasks, and then click Take Offline.

Copy backup file to new SQL server

Copy the BAK files to the new SQL server.

Restore Database

Create subfolders for the database with the Database and Log folders. Ensure that the file paths restore to these new locations.

Change Database SQL Compatibility Level

Right click the database. Select Options from the Select a page menu and change the Compatibility Level to the highest number (unless the vendor dictates otherwise).

Re-create connection logon details

Create a new login and ensure the default database is selected, but do not perform any user mappings. Select the database and open a New Query window.  Enter the following command:

sp_change_users_login 'auto_fix', 'NewLoginUserName'

This is not required for Active Directory users or groups, as the SID remains the same, so it automatically associated with the database.

Update application connection string

Ensure that the Server Team or Application Support team have updated the frontend connection to the SQL server and tested the functionality.

Delete the original Database

Delete the Database (which should be offline) or the original SQL Server.

Categories: Uncategorized