How to make cygwin publickey authentication to work again after upgrading from Windows 2000 to Windows 2003

Jephe Wu - http://linuxtechres.blogspot.com

Environment: Windows 2000 server sp4, cygwin sshd, password authentication and public key login were working fine. After upgrading to Windows 2003 sp2 server, cygwin sshd password login still works, but public key login doesn't work anymore

Objective
: to make ssh public key login work for cygwin after upgrading OS.


Steps:
1. create both sshd and sshd_server user accounts, sshd is just normal user who belongs to users group, sshd_server needs to be in Administrators groups, otherwise when you ssh into this cygwin server from Linux using public key authentication, you will encounter the errors below:

ssh from CentOS 4 to cygwin server under win2k03 server sp2:

debug1: Authentication succeeded (publickey).
debug1: channel 0: new [client-session]
debug1: Entering interactive session.
Last login: Thu May 26 14:06:59 2011 from 165.114.120.117
debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
debug1: channel 0: free: client-session, nchannels 1
Connection to web1 closed.
debug1: Transferred: stdin 0, stdout 0, stderr 34 bytes in 5.1 seconds
debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 6.6
debug1: Exit status 255


however, the password login without using public key authentication will work.

run services.msc to edit cygwin service to logon as user sshd_server and type in password.

Right click on my computer, properties, configure system variables CYGWIN as "ntsec tty", use 'set' command under command prompt to confirm it's been set.


2. double click cygwin, run 'ssh-host-config' to generate configuration files, answer yes to overwrite and yes for privileges seperation.
3. generate /etc/passwd and /etc/group files after user creations for sshd and sshd_server
under cygwin prompt window, run:
mkpasswd -l > /etc/passwd
mkgroup -l > /etc/group


4. make sure the /var/empty directory has permission like this and it should be empty
chown sshd_server /var/empty
chmod 755 /var/empty


note: you might be able to use cygwin to give permission like this:

$ chown sshd_server /var/log/sshd.log
  $ chown -R sshd_server /var/empty
  $ chown sshd_server /etc/ssh*


5. make sure /etc directory and /var/log directory and all files under it should be readable by running user sshd_server

6. modify group policy for sshd_server to be able to switch user to sshd for privileges seperation
run 'gpedit.msc', go to local computer policy - computer configuration - windows settings - security settings - local policies - user rights assignment
look for 'create a token object', to add sshd_server user inside.

run 'gpupdate' to refresh group policy immediately, then restart cygwin sshd service.

Note: you don't have to add sshd_server into 'replace a process token level'.

Error messages and issues:

Isses we encountered:
a. couldnot start up cygwin sshd service
solution: check /var/log/sshd.log for error message and event log for ssh error, setting correct permission for /etc for sshd_server user to read, and /var/log/sshd.log for sshd_server to read/write

b. error messages appeared under evern log
1. The description for Event ID ( 0 ) in Source ( sshd ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: sshd : PID 3996 : fatal: setreuid 1013: Permission denied.
2.
The description for Event ID ( 0 ) in Source ( sshd ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: sshd : PID 4848 : starting service `' failed: redirect_fd: open (1, /var/log/sshd.log): 13, Permission denied.
3. The description for Event ID ( 0 ) in Source ( sshd ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: sshd : PID 5376 : starting service `sshd' failed: execv: 1, Operation not permitted.
4. The description for Event ID ( 0 ) in Source ( sshd ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: sshd : PID 4588 : starting service `sshd' failed: execv: 128, Transport endpoint is not connected.
5. The description for Event ID ( 0 ) in Source ( sshd ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: sshd : PID 4668 : error: Could not load host key: /etc/ssh_host_key.

c. error message when you use public key authentication.

ssh from CentOS 4 to cygwin server under win2k03 server sp2:
# ssh -v SYSMGR@web1
debug1: Authentication succeeded (publickey).
debug1: channel 0: new [client-session]
debug1: Entering interactive session.
Last login: Thu May 26 14:06:59 2011 from 1.2.3.4
debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
debug1: channel 0: free: client-session, nchannels 1
Connection to web1 closed.
debug1: Transferred: stdin 0, stdout 0, stderr 34 bytes in 5.1 seconds
debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 6.6
debug1: Exit status 255


References:

How to setup the secure shell daemon on a Windows 2003 server - http://ist.uwaterloo.ca/~kscully/CygwinSSHD_W2K3.html


How to add additional columns for a table in db2 where default 4k tablespace is not enough

Jephe Wu - http://linuxtechres.blogspot.com

Environment: RHEL4 32bit with "DB2 v8.1.2.88", "s050422", "MI00117", and FixPak "9". We need to add an additional column for a table, but we encountered error message below:

The row length of the table exceeded a limit of "4005" bytes. (Table space "xxxxx".)

Objective: to create a 8k tablespace and migrate this table over to new tablespace


Steps:

1. backup the table jephe by using the following commands:(assuming database and schema name are wu, talbe name is jephe)

# more backup.sql
export to "jephe.txt" of del messages jephe.msg select * from  wu.jephe;
CONNECT RESET;

db2 connect to wu user wu
db2 -tvf backup.sql -l backup.sql.log -s

2. use db2look to extract the whole database ddl statements and grep the necessary table creation statements
db2look -d wu -e -z wu -o db2look.sql
vi db2look.sql to search string JEPHE to copy out the table create and alter statements for 'JEPHE' as well as alter statements for other tables which has foreign keys on table 'JEPHE'

e.g.
# more createtable.sql
CREATE TABLE "WU   "."JEPHE"  (
                  "LIFE_INSURANCE_OID" CHAR(30) NOT NULL ,
                  balabala
                  balabala
                  ....
                  "CONTACT_NUMBER" VARCHAR(200) )
                 IN "TB_JEPHE" ;


ALTER TABLE "WU    "."JEPHE"
        ADD PRIMARY KEY
                ("LIFE_INSURANCE_OID");


ALTER TABLE "WU    "."JEPHE"
        ADD CONSTRAINT "SQL050615160106790" FOREIGN KEY
                ("BENEFIT_FILE_OID")
        REFERENCES "WU    "."BINARY_FILE_REPOSITORY"
                ("BINARY_FILE_OID")
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
        ENFORCED
        ENABLE QUERY OPTIMIZATION;

# following is for another table 'TABLE1' which has foreign key for WU.JEPHE
ALTER TABLE "WU    "."TABLE1"
        ADD CONSTRAINT "SQL050615160109840" FOREIGN KEY
                ("JEPHE_OID")
        REFERENCES "WU   "."JEPHE"
                ("JEPHE_OID")
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
        ENFORCED
        ENABLE QUERY OPTIMIZATION;
       
Note: once you drop table 'WU.JEPHE', the foreign key for WU.TABLE1 will also be gone. After recreating table WU.JEPHE in 8k tablespace, you have to create this foreign key again.

You also need to extract view creation statements which depends on the table 'WU.JEPHE'. Because you need to create view again after dropping and creating again WU.JEPHE in 8k tablespace.

# more view.sql
create view emp_benefits_life_ins_v as select balabala.


3. create a 8k buiffer pool tablespace and a 8k system temporary tablespace for 'order by' operation on new table wu.jephe, specify container path.

If you don't create a 8k temporary tablespace, you will encouter the following error message when doing 'order by' operation

db2 "select * from wu.jephe order by status"
SQL1585N  A system temporary table space with sufficient page size does not
exist.  SQLSTATE=54048


creation statement is as follows for system temporary tablespace:

CONNECT TO WU;
CREATE  SYSTEM TEMPORARY  TABLESPACE TEMPSPACE2 PAGESIZE 8 K  MANAGED BY SYSTEM  USING ('/db2/db2inst1/db/WU/tb_temp2' ) EXTENTSIZE 16 OVERHEAD 10.67 PREFETCHSIZE 16 TRANSFERRATE 0.04 BUFFERPOOL  IBM8KBP ;
COMMENT ON TABLESPACE TEMPSPACE2 IS '8k temporary tablespace';
CONNECT RESET;


4 drop table
login as db2inst1
db2 connect to wu
db2 "drop table wu.jephe"

5 recreate table in 8k tablespace
db2 connect to wu
db2 set schema = jephe
modify createtable.sql to change
CREATE TABLE "WU   "."JEPHE"  (
                  "LIFE_INSURANCE_OID" CHAR(30) NOT NULL ,
                  balabala
                  balabala
                  ....
                  "CONTACT_NUMBER" VARCHAR(200) )
                 IN "TB_JEPHE" ;
               
to

CREATE TABLE "WU   "."JEPHE"  (
                  "LIFE_INSURANCE_OID" CHAR(30) NOT NULL ,
                  balabala
                  balabala
                  ....
                  "CONTACT_NUMBER" VARCHAR(200) )
                 IN "TB_JEPHE2";
db2 -tvf createtable.sql -l createtable.sql.log -s

6. restore data into table wu.jephe again from backup


#more restore.sql
import from "jephe.txt" of del messages "jephe.impmsg" insert into wu.jephe;
connect reset;

db2 connect to wu
db2 set schema = jephe
db2 -tvf restore.sql -l restore.sql.log -s

import from "jephe.txt" of del messages "jephe.impmsg" insert into wu.jephe

Number of rows read         = 77
Number of rows skipped      = 0
Number of rows inserted     = 77
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 77

7. run command to add additional comumns for new table wu.jephe
# more command.sql
alter table wu.jephe add column contact_detail varchar(250);

db2 connect to wu
db2 set schema = jephe
db2 -tvf command.sql -l command.sql.log -s

8. grant permission for table WU.JEPHE and related views from db2 control center GUI

How to backup Windows NTFS partitions before upgrading

Jephe Wu - http://linuxtechres.blogspot.com

Objective: Upgrade Windows 2000 sp4 standalone server to Windows 2003 server standard edition, in case of failure, we are still able to restore back to Windows 2000 server
Environment: Windows 2000 sp4 server, Dell PowerEdge 2450 server, 10G basic NTFS partition for C drive with 5G free space
Tools needed for backup NTFS partition: RIP(Recovery Is Possible) CD 12.3 non-X edition - http://www.tux.org/pub/people/kent-robotti/looplinux/rip/


Steps:
1. boot up with RIP non-X 12.3 CD, choose the second option which is without keyborad mapping,login as root without password.
If you need to do it remotely,start up ssh by running /usr/sbin/sshd, then 'passwd root' to give root password

2. connect a external 300G hard disk with USB cable, we are going to backup C drive to external hard disk

3. mount external hard disk ntfs partition as read/write under RIP Linux
mount.ntfs-3g /dev/sdc1 /mnt/usb
ntfsclone -s - /dev/sda2 | gzip -c | cat > /mnt/usb/jephe.sda2.gz

4. backup mbr of /dev/sda
dd if=/dev/sda count=1 bs=512 of=/mnt/usb/jephe.dd.mbr

5. in case of failure for upgrade, you can restore mbr and C drive back
dd if=/mnt/usb/jephe.dd.mbr of=/dev/sda
gzip -dc /mnt/usb/jephe.sda2.gz | ntfsclone -r - -O /dev/sda2


6. If you need to backup C drive and MBR to network server through ssh connection, please refer to article
Cloning Windows server - using RIP and ntfsclone
at http://linuxtechres.blogspot.com/2007/07/cloning-windows-server-using-rip-and.html

7. While Windows 2000 server is running, as an administrator in the I386 directory on the Windows 2003 CD run Winnt32.exe and follow the prompts.