MSSQL Query from Linux- FreeTDS for Nagios Monitoring

FreeTDS for MSSQL queries from Linux

Using FreeTDS

Need to monitor a MSSQL server instance from Nagios? You can use FreeTDS to execute a MSSQL query from a Linux box.

Here’s how:

  1. Install FreeTDS:
    yum install freetds
  2. Configure FreeTDS (vi /usr/local/etc/freetds.conf):

    [servername]
    host = 192.168.0.10
    port = 1433
    tds version = 8.0
  3. Configure ODBC driver for FreeTDS (/etc/odbcinst.ini):

    # Define where to find the driver for the Free TDS connections.
    [freetds]
    Description = MS SQL database access with Free TDS
    Driver = /usr/lib/libtdsodbc.so
    Setup = /usr/lib/libtdsS.so
    UsageCount = 1
  4. Test connectivity:

    [root@techpain]# tsql -H servername -p 1433 -U dbuser -P dbpass
    locale is "en_US.UTF-8"
    locale charset is "UTF-8"
    using default charset "UTF-8"
    1> select field11 from column1 where field1 like '10%'
    2> GO
    field1
    101
    102
    103
    105
    106
    108
    109
    (7 rows affected)
    1> quit
    [root@techpain]#

Nagios MSSQL query

FreeTDS is cool because it allows you to use Nagios to query a MSSQL server using check_mssql command. Here’s how:

Download the check_mssql plugin to the plugins dir on your Nagios server. You can find it at the Nagios Exchange site.

Create command, host, and service definitions for it:

define command {
command_name check_mssql
command_line $USER1$/check_mssql -H hostname -U dbuser -P dbpass -q "select count(*) from column1 where field1 like '10%';" -r "7" -w 8 -c 10
}

define host {
host_name ServerName
alias MSSQL server
address 192.168.0.10
check_command check-host-alive
notification_interval 15
notification_options d,u,r
max_check_attempts 3
active_checks_enabled 1
passive_checks_enabled 0
notifications_enabled 1
check_period 24x7
notification_period 24x7
contact_groups admins
}

define service {
use generic-service
host_name ServerName
service_description MSSQLquery
check_command check_mssql
}

In the above example, Nagios executes the query expects a return of 7, warns at 8, and goes crit at 10.

Share

Installing CentOS on >2TB – machine cannot boot using GPT

While trying to install CentOS 5.5 x86 on a Dell PowerEdge the other day:

“Your boot partition is on a disk using the GPT partitioning scheme but this machine cannot boot using GPT.”

idolinux.blogspot.com has a post on “Perc RAID and EFI GPT weirdness” that seemed to have a good solution, bypassing GPT entirely.

I found that configuring two virtual disks in RAID (I made VD0=2TB) works as well. Just put /boot on the VD that is less than (or equal to) 2TB.

Share

RHEL 4 – Reset root Password

Recently received a server running RHEL4 and didn’t have user credentials, let alone root access. Used the following method, taken from redhat.com:

Help! I forgot my root password. How do I log in now?

You can log in using single-user mode and create a new root password.

To enter single-user mode, reboot your computer. If you use the default boot loader, GRUB, you can enter single user mode by performing the following:

1. At the boot loader menu, use the arrow keys to highlight the installation you want to edit and type [A] to enter into append mode.

2. You are presented with a prompt that looks similar to the following:
grub append> ro root=LABEL=/

3. Press the Spacebar once to add a blank space, then add the word single to tell GRUB to boot into single-user Linux mode. The result should look like the following:
ro root=LABEL=/ single

4. Press [Enter] and GRUB will boot single-user Linux mode. After it finishes loading, you will be presented with a shell prompt similar to the following:
sh-2.05b#

5. You can now change the root password by typing
passwd root
You will be asked to re-type the password for verification. Once you are finished, the password will be changed. You can then reboot by typing reboot at the prompt; then you can log in to root as you normally would.

Share

VSFTPD on CentOS 5 – FTP server setup

1) Configure VSFTPD: vi /etc/vsftpd/vsftpd.conf and include the following:

chroot_list_enable=YES
chroot_local_user=NO

Also, disallow anonymous access by setting anonymous_enable=NO, if this is commented out, anonymous access is enabled!
Using these settings you NEED to add ftp usernames to /etc/vsftpd/chroot_list, so create the file and add the ftp username(s). This will allow ftp users, but lock them down to their home directory.

2) Add users: useradd -m -d /home/ftpusername ftpusername

3) Adjust IPTABLES: Edit iptables-config: vi /etc/sysconfig/iptables-config and add
IPTABLES_MODULES="ip_conntrack_ftp"
and allow port 21 in iptables:
-A RH-Firewall-1-INPUT -m state --state NW -m tcp -p tcp --dport 21 -j ACCEPT
and then restart iptables.

4) Use Audit2Allow to create a policy for SELinux

4) Turn it on and get started:
service vsftpd start
chkconfig vsftpd on

There are a couple options for login (welcome/greeting) banners. vsftpd.conf has a banner string option:
# You may fully customise the login banner string:
ftpd_banner=Your login banner here

But alternatively, you can use banner_file=/path/to/banner_message to point to a text file that contains your banner message. This is especially handy for long legal messages.

Share

KoH Virtual Servers – KoHvs.com

KoH Virtual Servers is offering really great virtual server access.

Every virtual server includes:

  • Unlimited monthly bandwidth
  • Full root access
  • Dedicated system resources, never shared or oversold
  • Choose and load your own operating system
  • Web-based control center
  • Secure, graphical maintenance console

They can be activated INSTANTLY, right from the KoHvs site.

Once you’ve activated your virtual server, simply log in to the site and click on ‘Control Center’. This will display the basic information regarding your server, along with graphs for CPU usage and network activity. You can reboot your server here (or install a different OS), and you’re only one click away from controlling your server remotely.

KoHvs Control Panel - Kohntrol

From the Control Center just click on ‘Launch Maintenance Console’, and you’re right in front of your server!
KoHntrol - My Virtual Server running CentOS 5

Share

Share

SElinux basics on CentOS 5

Check SElinux status: getenforce

Change SElinux status: setenforce 0
(0=permissive, 1=enforcing)

Disable SElinux: edit /etc/selinux/config and reboot

Troubleshoot SElinux: sealert and semanage
For more info on using sealert and semanage, see wiki.centos.org on troubleshooting selinux

Using audit2allow to create custom SElinux policies: SElinux denials are logged to /var/log/audit/audit.log. Issuing audit2allow -a -w will provide human readable info regarding the denial. To show the Type Enforcement rule, use audit2allow -a and to use the rule displayed here issue a audit2allow -a -M policyname which will then instruct you to run semodule -i policyname.pp

Share

Share

Installing Fail2Ban on CentOS 5

Fail2Ban scans log files like /var/log/pwdfail or /var/log/apache/error_log and bans IP that makes too many password failures. It updates firewall rules to reject the IP address. These rules (how many attempts before banning, amount of time banned, etc.) can be defined by the user. Fail2Ban can read multiple log files such as sshd or Apache web server ones.
You may need an additional repo for this. If so, see my post on rpmforge for additional info.

To install and configure fail2ban:

Install: yum install fail2ban

Configure: Edit vi /etc/fail2ban/jail.conf and fail2ban.conf, with special attention to the following:
# Option: ignoreip
# Notes.: space separated list of IP's to be ignored by fail2ban.
# You can use CIDR mask in order to specify a range.
# Example: ignoreip = 192.168.0.1/24 123.45.235.65
# Values: IP Default: 192.168.0.0/16
#
ignoreip = 192.168.0.1/16

[ssh-iptables]

enabled = true
filter = sshd
action = iptables[name=SSH, port=ssh, protocol=tcp]
sendmail-whois[name=SSH, dest=youraddress@yourdomain.com, sender=fromaddress@your domain.com]
logpath = /var/log/secure
maxretry = 3

Note that the logpath needs to be changed to the logfile that you want to use to block IP’s, on CentOS 5 this is /var/log/secure

The finishing touches:
chkconfig fail2ban on
service fail2ban start

Fail2Ban can be used for all kinds of other things too… Blocking spammers from a mail server, and all kinds of other things.

Also, keep in mind that you’ll have to have sendmail (or some other mail) enabled if you want to receive the Fail2Ban notifications! You can simply relay through your mail server by modifying /etc/mail/sendmail.cf. Just add your mail server FQDN to the DS line and restart sendmail.

Share

Installing and Configuring RT with mysql on CentOS 5

RT (Request Tracker) is an enterprise-grade ticketing system which enables a group of people to intelligently and efficiently manage tasks, issues, and requests submitted by a community of users. The RT platform has been under development since 1996, and is used by systems administrators, customer support staffs, IT managers, developers and marketing departments at thousands of sites around the world. Written in object-oriented Perl, RT is a high-level, portable, platform independent system that eases collaboration within organizations and makes it easy for them to take care of their customers.

Installation

Here are the basic steps for installing RT 3.8.4 (w/ mysql db) on a CentOS 5.2 server.

Repos: Enable the centosplus repo and install the rpmforge repo

Install mod_perl, CPAN module dependencies, etc.
yum install httpd-devel apr-devel mysql-devel mod_perl freetype-devel gd-devel libjpeg-devel libpng-devel

Install nctfp:
wget ftp://rpmfind.net/linux/epel/5/i386/ncftp-3.2.2-1.el5.i386.rpm
rpm -i ncftp-3.2.2-1.el5.i386.rpm

You can find ncftp for other distros/architectures here.

Add the rt group: group add rt

Edit /etc/group and add :apache like so:
rt:x:500:apache

Change Apache log permissions:
chmode -R 755 /etc/httpd/logs

Install Perl modules:
yum install perl-Apache-Session perl-Class-Container perl-Class-Data-Inheritable perl-Crypt-DES perl-Devel-StackTrace perl-Exception-Class perl-GD perl-GD-Graph perl-GD-Text-Util perl-Hook-LexWrap perl-HTML-Mason perl-HTTP-Server-Simple perl-HTTP-Server-Simple-Mason perl-Net-SNMP perl-Params-Validate perl-Socket6 perl-IO-Socket-SSL perl-IO-Socket-INET6 perl-XML-RSS perl-DBD-mysql
perl -MCPAN -e shell
cpan> install Bundle::CPAN
cpan> exit

Install RT:
wget http://download.bestpractical.com/pub/rt/release/rt.tar.gz
tar -xzvf rt.tar.gz
cd rt-3.8.6
./configure --with-web-user=apache --with-web-group=apache --with-modperl2 --with-mysql
perl sbin/rt-test-dependencies --with-mysql --with-modperl2 --install

Check for missing dependencies:perl sbin/rt-test-dependencies --with-mysql --with-modperl2 --verbose|grep MISSING
and then
make install
Now you need to modify the installed [=etc/RT_SiteConfig.pm] to specify the connections to your DBMS engine and then type:
make initialize-database
For some database backends (MySQL at least) it is not able to create the database user. So you have to create that user beforehand and you have to give it rights for the database.
mysql>GRANT ALL PRIVILEGES ON rt3.* TO 'rt_user'@'localhost' IDENTIFIED BY 'rt_pass'
If you screw something up, simply make dropdb, fix it, and start back at make install
If you run into problems, check out step 5 of Best Practical's Manual Install instructions

Configuration

Edit [=etc/RT_SiteConfig.pm] in your RT installation directory, by specifying any values you need to change from the defaults as defined in RT_Config.pm. It is easiest to do this by copying RT_Config.pm to RT_SiteConfig.pm, and then uncommenting and changing anything you need to set, though perhaps this isn't quite the best approach.

In many cases sensible defaults have been included. In others, you must supply a value. Some values (such as the RT log directory) will come from values you supplied in the Makefile. You'll find further explanation inline in the RT_SiteConfig.pm file. You should look at and consider changing the following entries:

$DatabasePassword = 'rt_pass'

which is the password the DatabaseUser should use to access the database.

NOTE: Some MySQL users have had trouble with passwords of longer than 8 characters; if you cannot connect, try a password of 8 characters or fewer.

$CanonicalizeEmailAddressMatch = 'subdomain.example.com$';
$CanonicalizeEmailAddressReplace = 'example.com';

The $CanonicalizeEmailAddress variables allow you to keep incoming messages consistent, such as when a site removes the subdomain from an email address. In the example presented by the defaults, if messages from your organization sometimes come from exchange.example.com and sometimes from example.com, you'd set $CanonicalizeEmailAddressMatch to exchange.example.com and $CanonicalizeEmailAddressReplace to example.com

$SenderMustExistInExternalDatabase = undef;

If $SenderMustExistInExternalDatabase is true, RT will refuse to auto-create non-staff accounts for unknown users filing new tickets by email if you are using the "LookupSenderInExternalDatabase" option elsewhere in RT_SiteConfig.pm. Instead, an error message will be returned and RT will forward the user's message to $RTOwner as defined in RT_SiteConfig.pm. If you are not using $LookupSenderInExternalDatabase, this option has no effect. If you define an AutoRejectRequest template, RT will use this template for the rejection message.

$CorrespondAddress = 'RT::CorrespondAddress.not.set';
$CommentAddress = 'RT::CommentAddress.not.set';

$CorrespondAddress and $CommentAddress are the default addresses that will be listed in both From: and Reply-To: headers of reply and comment mail, respectively, sent by RT, unless they are overridden by a queue-specific address.

$MailCommand = 'sendmailpipe';

$MailCommand defines which method RT will use to try to send mail. We know sendmailpipe works fairly well. If sendmailpipe doesn't work well for you, try sendmail. Note that you should remove the '-t' from $SendmailArguments if you use sendmail rather than sendmailpipe. Also note that sendmailpipe and sendmail aren't the names of commands on your system, but instructions that tell RT what mail delivery subsystem to try.

*Don't forget to restart the Apache webserver after doing changes in RT_SiteConfig.pm!* This is true of any change, but we mention it here since this is the configuration option you're most likely to have to experiment with.

$SendmailArguments = "-oi -t";

$SendmailArguments defines what flags to pass to $Sendmail, assuming you picked sendmail or sendmailpipe as the $MailCommand. If you picked sendmailpipe, then $SendmailArguments must include the "-t" flag. The default options are good for most sendmail wrappers and workalikes.

$SendmailPath = "/usr/sbin/sendmail";

If you selected sendmailpipe as $MailCommand, you must specify the path to your sendmail executable file in $SendmailPath. If you did not select sendmailpipe this has no effect.
In case of Exim, the following configuration works:

Set($MailCommand , 'sendmail');
Set($SendmailArguments , "-bm -- ");
Set($SendmailPath, "/usr/sbin/exim4");
Set($NotifyActor, 1);
$Timezone = 'US/Eastern';

$Timezone is used to convert times entered by users into GMT and back again. It should be set to a timezone recognized by your local Unix box, and -- in general -- you should pick the timezone the majority of your users reside in.

$UseFriendlyToLine = 0;

RT can set a "friendly", rather than blank, To: header when sending messages to Ccs or AdminCcs. This feature does not work with Sendmail(tm)-brand sendmail. If you are using sendmail, rather than postfix, qmail, exim, or some other program, you must disable this option (by setting it to 0 rather than 1).

$WebPath = "";

A variable used to help RT construct URLs that point back to RT. If you've put RT somewhere other than at the root of your webserver, you need to define a WebPath. RT uses this in the construction of relative URLs. $WebPath requires a leading / but no trailing /
Example: if your installation is at http://www.fsck.com/rt/ set this to "/rt".

$WebBaseURL = "http://not.configured:80";

A variable used to help RT construct URLs that point back to RT. $WebBaseURL is the base of the URL. it should usually include the scheme, the host, and the port if non-standard.
Example: "https://fsck.com" or "http://fsck.com:88"
$WebBaseURL doesn't need a trailing /

$WebURL = $WebBaseURL . $WebPath . "/";

A variable used to help RT construct URLs that point back to RT, [=$WebURL] is the combination of [=$WebBaseURL] and [=$WebPath]. Generally, you shouldn't change it.

$WebImagesURL = $WebURL . "/NoAuth/images/";

[=$WebImagesURL] points to the base URL where RT can find its images. If you're running the FastCGI version of the RT web interface, you should make RT's [=WebRT/html/NoAuth/images] directory available on a static web server and supply that URL as [=$WebImagesUrl] -- alternately, you can tell Apache not to run it through FastCGI.

Next, you'll want to configure Apache. Append the following to /etc/httpd/conf.d/perl.conf:

Listen ip_of_the_rt.example.com:80
<virtualhost ip_of_the_rt.example.com:80=""></virtualhost>
    ServerName rt.example.com
    ServerAdmin Email.Address@example.com
    DocumentRoot /opt/rt3/share/html
    AddDefaultCharset UTF-8
    PerlRequire /opt/rt3/bin/webmux.pl
    <directory html="" opt="" rt3="" share=""></directory>
        Order allow,deny        Allow from all
        SetHandler perl-script
        PerlResponseHandler RT::Mason
   </Directory>
</VirtualHost>

Share

Share

Installing HylaFAX on CentOS 5

HylaFAX is an enterprise-class system for sending and receiving facsimiles as well as for sending alpha-numeric pages.

The software is designed around a client-server architecture. Fax modems may reside on a single machine on a network and clients can submit an outbound job from any other machine on the network. Client software is designed to be lightweight and easy to port.

Here’s a quick guide for installing HylaFAX on a CentOS 5 server:

1) Install dependencies:

yum -y install ghostscript sharutils

2) Get the RPM and install it:

wget http://yum.trixbox.org/centos/5/RPMS/hylafax-4.4.4-1rhel5.i386.rpm
rpm -Uvh /var/ftp/hylafax-4.4.4-1rhel5.i386.rpm

3) Start the hylaFAX config process: /usr/sbin/faxsetup

4) Accept defaults, except where info specific to your setup is necessary (area code, serial port for modem, fax number, company name, etc.). Replace ‘faxmaster@company.ca’ by the email address where all status messages will be received by HylaFAX. This enables the system status monitoring:

Should an entry be added for the FaxMaster to /etc/aliases [yes]?
Users to receive fax-related mail [root]? faxmaster@company.ca

In the last three lines here, we need to assign user rights. If you don’t want to assign user rights, use 644, 644 and 666:

Protection mode for received facsimile [0600]? 0644
Protection mode for session logs [0600]? 0644
Protection mode for ttyS0 [0600]? 0666

A the end of the config, say no to finish:

Should I run faxmodem for each configured modem [yes]?

5) Add usernames. To add users without passwords, use:

/usr/sbin/faxadduser username

or with passwords:

/usr/sbin/faxadduser -p password username

NOTE: DO NOT USE “!” IN THE PASSWORDS

6) Edit vi /etc/inittab for the modem automatic answer. At the end of the file:

# Run faxgetty in runlevel 2 and 3
t2:23:respawn:/usr/sbin/faxgetty ttyS0

7) To setup fax send/recieve, edit /var/spool/hylafax/etc/FaxDispatch. On the 7th line change ‘FaxMaster’ to the email address you will use to send and receive. Directly under that line, you can add

FILETYPE=pdf;
MIMENCODE=bin/uuencode_it;

to enable sending/receiving faxes as a .pdf attachment

8) Disable page chopping to insure full pages will be sent. Edit /etc/hylafax/hyla.conf and remove the comment from the Page Chopping line.

9) Restart the server, should be ready to fax!

taken from sertec.ca

Share

Dimdim OpenSource v4.5 on CentOS 5.2

Dimdim is a web conferencing tool that allows shared desktop, video, voice, and more. They offer an open source version that was designed for CentOS 5.2 (32-bit). Dimdim Open Source Community Edition v4.5 “Liberty” is meant for developers, highly technical enthusiasts and for use in non-critical environments. It has the most of the features of Dimdim Enterprise and is based on open source streaming and media components. You can grab it from Sourceforge.net.

The README includes the entire pre-req process, and is very complete. I did need to install Java Runtime Environment (JRE) v1.6, install it in /usr/bin, and set the path by adding the following line to ~/.bash_profile:
export JAVA_HOME=/usr/bin/java

Also, you’ll want to set Dimdim to run at startup:
ln -s /usr/local/dimdim/ConferenceServer/apache-tomcat-5.5.17/bin/dimdim /etc/init.d/
chkconfig dimdim on

And don’t forget to turn off httpd!
chkconfig httpd off
or change the port:
cd /usr/local/dimdim
./Configure_Dimdim.pl

To restrict who can start a presentation:
Add the following line to /usr/local/dimdim/ConferenceServer/apache-tomcat-5.5.17/webapps/dimdim/WEB-INF/classes/resources/dimdim.properties

dimdim.authenticationPolicy=CHECK_EMAIL

Then comment out the following lines:
## start_meeting_user_email=admin
## start_meeting_user_name=Host

Next, change the dimdimPresenters.txt file to only include email addresses that you would like to allow.

Then edit /usr/local/dimdim/ConferenceServer/apache-tomcat-5.5.17/webapps/dimdim/html/signin/signin.jsp to provide input for the username/email address:
Change:

<code><b>
<tr>
<td width="30%" align="right"><dm:I18NDisplayString component="forms" dictionary="ui_strings" key="meetingname.label"/></td>
<td width="70%" align="left">
<input type="text" name="confName" id="confName" class="TextBox_format" TABINDEX="2" />
<input type="text" name="email" id="email" class="Hide"/>
<input type="text" name="displayName" id="displayName" class="Hide"/>
</td>
</tr></b></code>

To:

<code><b>
<tr>
<td width="30%" align="right"><dm:I18NDisplayString component="forms" dictionary="ui_strings" key="meetingname.label"/><br/><br/>Username</td>
<td width="70%" align="left">
<input type="text" name="confName" id="confName" class="TextBox_format" TABINDEX="2" />
<input type="text" name="email" id="email" class="TextBox_format"/>
<input type="text" name="displayName" id="displayName" class="Hide"/>
</td>
</tr></b></code>

Restart dimdim

To change the default page that loads when a meeting ends (trackback/return URL):
Edit /usr/local/dimdim/ConferenceServer/apache-tomcat-5.5.17/webapps/dimdim/WEB-INF/classes/resources/dimdim.properties
The URL setting is located at dimdim.trackbackURL

To customize the default shared meeting screen:
The page that is displayed when you’re not sharing a doc, screen, whiteboard, etc. contains somewhat obtrusive advertising for SynchroLive. You can remove it (and add you’re own branding and or text) by editing /usr/local/dimdim/ConferenceServer/apache-tomcat-5.5.17/webapps/dimdim/share_wait/share_wait.html

To disable prompt for camera and mic access at meeting start:
change
## Enable / Disable Features
## Enable / Disable AV
start_meeting_option_enable_av=1
## Enable / Disable Audio
start_meeting_option_enable_audio=1

to:
## Enable / Disable Features
## Enable / Disable AV
start_meeting_option_enable_av=0
## Enable / Disable Audio
start_meeting_option_enable_audio=0

Share