Monday 15 August 2011

MS-SQL: Run a SQL Script on Database Server


How do I execute a SQL script (.sql file) on my MS-SQL database server under Windows Sever 2003?

 You can easily run any .sql file on MS-SQL database server using the Query Analyzer tool.

How can I execute a SQL script (.sql file) on my SQL Server ?

Open the Query Analyzer by visiting Start → Programs → MS SQL Server → Query Analyzer
Once opened, connect to the database that you are wish running the script on.
Next, open the SQL file using File → Open option. Select .sql file.
Once it is open, you can execute the file by pressing F5.

Linux Remote Desktop For Controlling Windows XP / Vista / Server 2003 ( rdesktop )


Iam responsible for couple of MS-Windows servers and Windows XP/Vista/7 workstations too. When I work from home, I need a way to get into Windows XP/2000/Vista/2003/2008/7/Vista operating systems for work.

I have Debian Linux at home, I needed a way to login into Microsoft windows desktop from Linux OS. Many of us working at tech support use rdesktop to connect to customers' MS-Windows systems.
Fig.01: rdesktop login window
Fig.01: rdesktop login window
Especially it is very useful to configure Outlook or something else when customers do not understand how to configure or troubleshoot problem. This is the best way to fix a problem.

Say Hello To rdesktop

Fortunately, Linux has rdesktop utility. It is a client for remote desktop protocol (RDP), used in a number of Microsoft products including Windows NT Terminal Server, Windows 2000 Server, Windows XP and Windows 2003 / 2008 Server. You do not need to install VNC server. All you need is rdesktop client on Linux or BSD workstation.

Install rdesktop

Type the following command as root user:
# apt-get install rdesktop
To connect to MS-Windows systems from Linux, type the following command at a shell prompt (connect to Windows server called mw2sn100.mycorp.com)
$ rdesktop mw2sn100.mycorp.com
Or connect to windows XP/Vista workstation having IP 192.168.1.17:
$ rdesktop 192.168.1.17
Fig.01: rdesktop in action
Please note that you must enable remote desktop connection under MS0Windows operating systems; otherwise it will not work. Turn on remote desktop under MS-Windows operating systems:
  • Go to MS-Windows desktop
  • Right Click on My Computer
  • Select properties
  • Select Remote tab
  • Enable Remote desktop.
  • Save the changes.
Make sure enterprise firewall allows incoming connection on TCP port 3389. rdesktop supports many other options, type the following command to read the man page of rdesktop or visitproject website for more information.
man rdesktop

Executing Linux / UNIX commands from web page


A Web interfaces is almost used by routers and many other sophisticated programs such as webmin. However, why go for a web interface or execute commands from web page? For automation purpose, you need to use a web interfaces. Another advantage is you can access your web-based interface from any computer, running any operating system, anytime in the world :D
In this first part, you will see how to use simple bash (shell) script from web page. In order to execute commands or shell script from a webpage you need:
  1. CGI support with Apache / lighttpd web server.
  2. I'm assuming that you have a properly configured web server.
You need to store program in cgi-bin directory. If you are using Debian Linux default location for cgi-bin directory is /usr/lib/cgi-bin. Under Red Hat / Fedora it is /var/www/cgi-bin. Use text editor such as vi to create a first.cgi program:
$ cd /usr/lib/cgi-bin
$ vi first.cgi
first.cgi code listing:
#!/bin/bash
echo "Content-type: text/html"
echo ""
echo "<html><head><title>Bash as CGI"
echo "</title></head><body>"

echo "<h1>Hello world</h1>"
echo "Today is $(date)"

echo "</body></html>"
Save and close the file. Setup execute permission on the script:
$ chmod +x first.cgi
Fire up your web browser and test the script, for example type url http://localhost/cgi-bin/first.cgi or http://your-ip/cgi-bin/first.cgi
You need to send headers, first three lines are almost same for all your script:
  • #!/bin/bash : First line tell Linux/UNIX how file first.cgi should be run. So it will use /bin/bash interpreter to execute your rest of program.
  • echo "Content-type: text/html" : Send html headers, you must include this line.
  • echo "" : Send a blank line, you must include this line.
Rest is html code. Take a close look at following echo command:
echo "Today is $(date)"
It use shell feature called command substitution. It allows the output of a command to replace the command name:
$(command)
Your bash shell performs the expansion by executing command and replacing the command substitution. So date command get executed by replacing the its output.

Real life example

Here is simple script that collects system information. Create script in cgi-bin directory:
#!/bin/bash
echo "Content-type: text/html"
echo ""
echo "<html><head><title>Bash as CGI"
echo "</title></head><body>"

echo "<h1>General system information for host $(hostname -s)</h1>"
echo ""

echo "<h1>Memory Info</h1>"
echo "<pre> $(free -m) </pre>"

echo "<h1>Disk Info:</h1>"
echo "<pre> $(df -h) </pre>"

echo "<h1>Logged in user</h1>"
echo "<pre> $(w) </pre>"

echo "<center>Information generated on $(date)</center>"
echo "</body></html>"
Save and close the file. Setup execute permission on script:
$ chmod +x script.cgi
Fire up web browser and test it (http://localhost/cgi-bin/script.cgi):

Next time you will see:
  • How to use and place form elements (from POSTs and GETs)
  • Cookies in your environment
  • Use of perl scripting
  • And finally use of special tools

How do I Use Multiple Screens on One Terminal over ssh session?


Most of the time GUI is not available on remote Linux system, you login over ssh and start to work, if you need to run two or three task at a time, you login over ssh two or three times. However, with screen windows manager utility you can run multiple terminals at the same time from single console login over ssh session (UNIX guru uses the term called multiplexing for this concept). Any seasoned Linux admin should be aware of this nifty tool :)

Install screen if it is not installed under Debian Linux

Type the following command:
# apt-get install screen

FreeBSD user can use ports to install screen

# cd /usr/ports/misc/screen
# make install clean

Login to remote server over ssh

$ ssh me@myserver.com
(C) Start screen session. You can name your window/session (1 is name of session):
$ screen -S 1
Let us start pine mail reader on first session or window.
$ pine
(D) Next you would like to download something from ftp/http site while you are reading emails. You need to create another screen window by pressing special key combination. Press CTRL + a followed by c key (first hit CTRL+a, releases both keys and press c). As soon as you hit 'c' you will get new shell prompt at console. Now use wget to download belenix.iso.bz2 (Solaris live CD) from net:
$ wget http://www.genunix.org/distributions/belenix_site/binfiles/belenix.iso.bz2

(E) But how do I switch between these two tasks?

  • Switching between windows is the specialty of screen utility. So to switch between pine and wget window (or session) press CTRL+a followed by n key (first hit CTRL+a, releases both keys and press n).
  • To list all windows use the command CTRL+a followed by " key (first hit CTRL+a, releases both keys and press " ).
  • To switch to window by number use the command CTRL+a followed by ' (first hit CTRL+a, releases both keys and press ' it will prompt for window number).

Common screen commands

screen commandTask
Ctrl+a cCreate new window
Ctrl+a kKill the current window / session
Ctrl+a wList all windows
Ctrl+a 0-9Go to a window numbered 0 9, use Ctrl+a w to see number
Ctrl+a Ctrl+aToggle / switch between the current and previous window
Ctrl+a SSplit terminal horizontally into regions and press Ctrl+a c to create new window there
Ctrl+a :resizeResize region
Ctrl+a :fitFit screen size to new terminal size. You can also hit Ctrl+a F for the the same task
Ctrl+a :removeRemove / delete region. You can also hit Ctrl+a X for the same taks
Ctrl+a tabMove to next region
Ctrl+a D (Shift-d)Power detach and logout
Ctrl+a dDetach but keep shell window open
Ctrl-a Ctrl-\Quit screen
Ctrl-a ?Display help screen i.e. display a list of commands

Suggested readings:

See screen command man page for further details:
man screen

Keep Linux / UNIX program running while you logs out


we already wrote about nohup command, which allows you to ignore HUP (hangup) signal and keep running the command after user logged out. You can accomplish the same behavior using screen or disown command.
setsid is another program that runs a program in a new session. For example run a script called ~/scripts/mirror.site:
ssh you@remote-box.com
setsid ~/scripts/mirror.site
logout 

You can also pass argument to command or script:
setsid /usr/bin/beep-media-player '/nas/mp3/xyz.mp3'
Please note that setsid included in miscellaneous system utilities package called util-linux under Debian / Ubuntu Linux.

HP-UX UNIX Backup and Recover Data to Tape Device


 How do I backup data under HP-UX UNIX? How do I use mt, fbackup and frecover commands to perform full and incremental backups?

A. HP-UX offers various commands just like any other UNIX system to backup file system, data and applications such as
tar command
a] dump / restore command (HFS filesystem only)
b] vxdump / vxrestore (vxfs filesystem only)
c] cpio command
d] fbackup / frecover command
e] dd command
f] tar command
g] make_net_recovery or make_tape_recovery commands

HP-UX Tape device names

First device will have name such as /dev/rmt/0m/dev/rmt/1m and so on.
  1. Raw magnetic tape interface - /dev/rmt/*
  2. Default tape interface - /dev/rmt/0mnb

HP-UX Tape - mt ~ manipulating program

mt command winds the tape in the requested direction (forward or backward), stopping after the specified count EOF marks or records are passed. If count is not specified, one is assumed. Each EOF mark counts as one record. When winding backwards, the tape always stops at the BOT marker, regardless of the number remaining in count.See mt command man page for more information. For example, Rewind the tape associated with the device file /dev/rmt/0mnb:
# mt -f /dev/rmt/0mnb rew

How do I backup data with fbackup command?

Backup /data directory to /dev/rmt/0m, enter:
# fbackup -i /data -I index.data -f /dev/rmt/0m
Where,
  • -i /data : Backup /data directory. There is no limit on how many times the -i option can be specified i.e. -i /data -i /home -i /path/to/something
  • -I index.data : index.data specifies the name of the on-line index file to be generated. It consists of one line for each file backed up during the session. Each line contains the file size, the volume number on which that file resides, and the file name. If the -I option is omitted, no index file is generated.
  • -f /dev/rmt/0m : Specify tape device name
  • -e /tmp : Exclude /tmp from backup
  • -g /var/adm/fbackupfiles/hporabox11.backup.list.txt : File that contains a list of files / dirs to be included / excluded from the tape backup
  • -u : Update the database of past backups so that it contains the backup level, the time of the beginning and end of the session, and the graph file used for this fbackup session. Only use this if you are using -g /path/to/file option.
Using the normal tape location to do a full backup, insert a new tape and enter:
# fbackup -f /dev/rmt/0m -i -v
Using the normal tape location to do a full backup but exclude /tmp and /cdrom /nfs directories, insert a new tape and enter:
# fbackup -f /dev/rmt/0m -i / -e /tmp -e /cdrom -e /nfs -v
You can also create a text file that contains a list of all files and directories to be included or excluded from the backup. For example, backup full file system but exclude /tmp, /cdrom and /nfs, enet:
# cd /var/adm/fbackupfiles
# vi hporabox11.backup.list.txt

Append following list
i /
e /tmp
e /nfs
e /cdrom

Save and close the file. To backup enter,
# fbackup -f /dev/rmt/0m -g /var/adm/fbackupfiles/hporabox11.backup.list.txt -v

How do I perform full and incremental backups?

The -0-9 option used to specify backup level. This single-digit number is the backup level. Level 0 indicates a full backup. Higher levels are generally used to perform incremental backups. When doing an incremental backup of a particular graph at a particular level, the database of past backups is searched to find the date of the most recent backup of the same graph that was done at a lower level. If no such entry is found, the beginning of time is assumed. All files in the graph that have been modified since this date are backed.
First, perform full backup, enter:
# fbackup -f /dev/rmt/0m -u -0 -g /var/adm/fbackupfiles/hporabox11.backup.list.txt -v
To perform incremental backup, enter:
# fbackup -f /dev/rmt/0m -u -1 -g /var/adm/fbackupfiles/hporabox11.backup.list.txt -v

How do I view contents of an fbackup tape?

Type the following command:
# frecover -f /dev/rmt/0m -x -N -v

How do I backup data with frecover command?

To restore all files and directories from tape, enter:
# frecover -f /dev/rmt/0m -r -v
To restore selected files or directories use -i /path option. For example, just restore /home/vivek directory, enter:
# frecover -f /dev/rmt/0m -x -v -i /home/vivek
Where,
  • -r : Recovery all data from tape
  • -x : Extracted / recover selected files / dires only specified by -i option
  • -i /path/to/file : Recover only this file or directory
  • -v : Verbose output

How do I verify tape backup?

The -N option (no recovery) prevent frecover from actually recovering any files onto disk, but read the backup as if it was, in fact, recovering the data from the backup, producing the same output that it would on a normal recovery. This option is useful for verifying backup media contents in terms of validity (block checksum errors are reported), and contents (a listing of files can be produced by using the -N and -v options together). Note that the listing of files produced with the -N and -v options requires the reading of the entire backup, but is therefore a more accurate reflection of the backup's contents than the index stored at the beginning of the backup (which was created at the start of the backup session, and is not changed during the course of the backup).
# frecover -f /dev/rmt/0m -N

Checking Rootkits with Chkrootkit and rkhunter Software (Linux)


How do I detect rootkits under CentOS or Debian Linux server?

 A rootkit is a program (or combination of several programs) designed to take fundamental control (in Unix terms "root" access, in Windows terms "Administrator" access) of a computer system, without authorization by the system's owners and legitimate managers.
Most rootkits use the power of the kernel to hide themselves, they are only visible from within the kernel. 

Detecting rootkits under Linux

You can try the following tools to detect Linux rootkits:
WARNING! These examples should run from Live CD (Linux Live Security CD) for the best result.

Zeppoo Software

Zeppoo - Zeppoo allows you to detect rootkits on i386 and x86_64 architecture under Linux, by using /dev/kmem and /dev/mem. Moreover it can also detect hidden tasks, connections, corrupted symbols, system calls and so many other things. Download source code here

Chkrootkit Software

Chkrootkit - chkrootkit is a tool to locally check for signs of a rootkit. Type the following command to install chkrootkit
$ sudo apt-get install chkrootkit
Start looking for rootkits, enter:
$ sudo chkrootkit
Look for suspicious strings, enter:
$ sudo chkrootkit -x | less
You need to specify the path for the external commands used by chkrootkit such as awk, grep and others. Mount /mnt/safe using nfs in read-only mode and set /mnt/safe binaries PATH as trusted one, enter:
$ sudo chkrootkit -p /mnt/safe

rkhunter software

rkhunter - rkhunter (Rootkit Hunter) is a Unix-based tool that scans for rootkits, backdoors and possible local exploits. rkhunter is a shell script which carries out various checks on the local system to try and detect known rootkits and malware. It also performs checks to see if commands have been modified, if the system startup files have been modified, and various checks on the network interfaces, including checks for listening applications. Type the following command to install rkhunter:
$ sudo apt-get install rkhunter
The following command option tells rkhunter to perform various checks on the local system:
$ sudo rkhunter --check
The following command option causes rkhunter to check if there is a later version of any of its text data files:
$ sudo rkhunter --update
The following option tells rkhunter which directories to look in to find the various commands it requires:
$ sudo rkhunter --check --bindir /mnt/safe
Browser Name:
Browser Version:
Browser Code Name:
User-Agent: