CSIM Technical Notes

Accessing an MS SQL server from Perl is a topic that lack complete information (or at least information that I can relate to.)

One solution is to use DBD::Sybase module in a compatible way. Better is to use DBD::ODBC in a more native way.

While the file path and name are specific to freeBSD, I am confident that the mechanism is the same fo other Unixes and the relationship between the components should be the same.

Install the needed components

You need to install:

• DBD::ODBC the Perl module
• unixODBC the ODBC library for Unix
• FreeTDS the Microsoft TDS library for Unix

Configure and test FreeTDS

FreeTDS configuration file is located in /usr/local/etc/freetds/freetds.conf and you need to add a section of the form:

# A typical Microsoft server
[SOMENAME]
        host = IP or hostname of MS SQL server
        port = 1433
        tds version = 7.0

You can test the configuration with:

tsql -S SOMENAME -U usernamne -P password -D database

At the prompt >1 you can enter SQL queries, enter go to execute the query.

Configure and test unixODBC

unixODBC configuration files are located in /usr/local/etc/odbcinst.ini and /usr/local/etc/odbc.ini.

The file odbcinst.ini defines the driver used by the database and should have a section of the form:

[some driver name]
Driver = /usr/local/lib/libtdsodbc.so
FileUsage   = 1

The Driver option points to the library in use, in that case, it points to FreeTDS driver.

The file odbc.ini defines the database server and should have a section of the form:

[SOMENAME]
Driver = some driver name (sqame as the section name above)
Database = database name
Server = IP or hostname of MS SQL server
TDS_Version = 7.0
Port = 1433

You can test the configuration with:

isql -v SOMENAME username password

At the prompt, you can enter SQL queries.

Configure and use DBD::ODBC

If everything worked so far, it is as simple as using:

use DBI;
my $dbh = DBI->connect("dbi:ODBC:DSN=SOMENAME", "username", "password");

How to replace the battery in the UPS at CSIM.

EPC powercom 2kVA

Battery: 6x12V 9Ah
Last replacement: May 2018

1.	Pull on the middle front bezel, then slide it to the right to un-hook it.
2.	Remove the screw then slide the retaining bar to the left.
3.	Pull on the battery drawer.
4.	Follow the diagram to install the new bateries: two sets of 3 batteries in serie

APC Smart UPS 3000XL

Power: 3kVA
Battery: 4x12V 18Ah
Last replacement: May 2018

1.	Remove the front bezel, then remove both screws to open the battery compartment.
2.	Follow the diagram to install the new bateries: two sets of 2 batteries in serie

APC Smart UPS 5000

Power: 5kVA
Battery: 16x12V 9Ah
Last replacement: May 2018

1.	Remove the front bezel, then remove both screws to open the battery compartment.
2.	Pull on the cord to disconnect the battery set, then use the tab to extract the battery set.
3.	Follow the diagram to install the new bateries: four sets of 4 batteries in serie&parallel (2+2)

With ESXi 6.5, the only way to grant permission to a domain (active directory) user is through command line: SSH connect to the ESXi server and use the command:

esxcli system permission set --id='smb4\on' --role=Admin

Note: the permission created in command line interface will not show on web interface. Use the command esxcli system permission list to see the active permissions.

Every user on the domain must be granted read permission on the system before they can have higher permission on a dedicated VM:

esxcli system permission set -g -i 'smb4\domain users' -r ReadOnly

Permission on a Virtual Machine can be set using the web interface, the name of domain user is of the form smb4\st112345

In CSIM, Posyfix uses LDAP to resolve email aliases. It can also resolve static mailing lists.

Mailing list

A mailing list is an entry in ou=Alias,ou=csim,dc=cs,dc=ait,dc=ac,dc=th. To create a new mailing list you must:

1. Create a new entry in ou=Alias,ou=csim,dc=cs,dc=ait,dc=ac,dc=th;
2. Choose the default template and the objectClass groupOfNames;
3. The RDN must be cn, the cn is the name of the mailing list and one member must be added in the form of uid=username,ou=People,ou=csim,dc=cs,dc=ait,dc=ac,dc=th.

Extrenal members

Because the mailing list is a group of member in LDAP, external email addresses (not @cs.ait.ac.th) must exist and be added in LDAP. Such external email addresses are listed in ou=External,ou=Alias,ou=csim,dc=cs,dc=ait,dc=ac,dc=th. To create a new external email address, you must:

1. Create a new entry in ou=External,ou=Alias,ou=csim,dc=cs,dc=ait,dc=ac,dc=th;
2. Choose the default template and the objectClass inetOrgPerson;
3. The RDN must be cn, the cn and the Email is the external email address and the sn must anything but not be empty (I use the word user);
4. The external address can then become member of a a mailing list.

In this document, I try to sumarize all the information I have gathered along the years, about certificates.

All the commands below are to be run on the machine that will use the certificate. For this example, we will use test.cs.ait.ac.th.

First step, create a private/public key

For the first two steps, I find the FAQ of mod_ssl to be a very good source of information.

openssl genrsa -out /usr/local/ssl/key/test.cs.ait.ac.th.key 2048

The minimum key length is now (year 2017) 2048 bits, you should not use shorter keys; Let's Encryot will refure to certify shorter keys.

We do not set a password on the provate key to allow the daemons to start automatically.

chmod 400 /usr/local/ssl/key/test.cs.ait.ac.th.key

The private key must be well protected, make it readonly. You may need to change the ownership to that of the daemon that will use the key; for example, LDAP key must belong to user ldap and FreeRadius key must belong to use freeradius.

Second step, create the certificate request

openssl req -new -key /usr/local/ssl/key/test.cs.ait.ac.th.key -out /usr/local/ssl/csr/test.cs.ait.ac.th.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:TH
State or Province Name (full name) [Some-State]:Pathumthani
Locality Name (eg, city) []:Klong Luang
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Asian Institute of Technology
Organizational Unit Name (eg, section) []:CSIM
Common Name (e.g. server FQDN or YOUR name) []:test.cs.ait.ac.th
Email Address []:olivier.nicole@cs.ait.ac.th

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

The only compulsory information is the FQDN name (fully qualified). It must reflect the name of the service, in our example test.cs.ait.ac.th, that is the name that will be used across the network when making the connection and that is the name that will be tested and certified by the certificate.

While the next step is not always needed, it help making sure you are not making mistake, check that the .csr and .key files are related:

openssl req -noout -in /usr/local/ssl/csr/test.cs.ait.ac.th.csr -modulus |md5
affeeeeca2036788d070d7bdef0ea971
openssl rsa -noout -in /usr/local/ssl/key/test.cs.ait.ac.th.key -modulus | md5
affeeeeca2036788d070d7bdef0ea971

both MD5 should be the same.

Third step, install acme.sh

Create a directory ~on/.acme.sh and copy on@banyan:.acme.sh/acme.sh to that directory.

Also copy on@banyan:.acme.sh/renew; The renew Perl script needs the pakages p5-Mail-SendEasy and p5-File-Copy-Link, as well as Time::ParseDate from CPAN.

acme.sh needs to access the network, you need to create p[roxy configuration for vurl:

setenv http_proxy http://192.41.170.23:3128/
setenv https_proxy http://192.41.170.23:3128/

Fourth step, send the certificate request

~on/.acme.sh/acme.sh --issue --dns --signcsr --csr /usr/local/ssl/csr/test.cs.ait.ac.th.csr
[Tue Feb 21 15:59:54 ICT 2017] Copy csr to: /root/.acme.sh/test.cs.ait.ac.th/test.cs.ait.ac.th.csr
[Tue Feb 21 15:59:54 ICT 2017] Signing from existing CSR.
[Tue Feb 21 15:59:54 ICT 2017] Getting domain auth token for each domain
[Tue Feb 21 15:59:54 ICT 2017] Getting webroot for domain='test.cs.ait.ac.th'
[Tue Feb 21 15:59:54 ICT 2017] _w='dns'
[Tue Feb 21 15:59:54 ICT 2017] Getting new-authz for domain='test.cs.ait.ac.th'
[Tue Feb 21 16:00:02 ICT 2017] The new-authz request is ok.
[Tue Feb 21 16:00:02 ICT 2017] Add the following TXT record:
[Tue Feb 21 16:00:02 ICT 2017] Domain: '_acme-challenge.test.cs.ait.ac.th'
[Tue Feb 21 16:00:02 ICT 2017] TXT value: 'PHEthoYwZznDv_qD4nMJY57r4r00JPytkhk3h4ZymAk'
[Tue Feb 21 16:00:02 ICT 2017] Please be aware that you prepend _acme-challenge. before your domain
[Tue Feb 21 16:00:02 ICT 2017] so the resulting subdomain will be: _acme-challenge.test.cs.ait.ac.th
[Tue Feb 21 16:00:03 ICT 2017] Please add the TXT records to the domains, and retry again.
[Tue Feb 21 16:00:03 ICT 2017] Please add '--debug' or '--log' to check more details.
[Tue Feb 21 16:00:03 ICT 2017] See: https://github.com/Neilpang/acme.sh/wiki/How-to-debug-acme.sh

Edit the zone file for cs.ait.ac.th DNS and add a line:

_acme-challenge.test 1 IN TXT "PHEthoYwZznDv_qD4nMJY57r4r00JPytkhk3h4ZymAk"

Note that the string PHE...ymAk must copy exactely the string sent by the acme.sh request above.

Note too that the TTL is set to 1.

To find what package installed a given file, use the command:

pkg which filename

Berkeley DB is used as a backend for LDAP at CSIM. To upgrade it:

1. Stop LDAP;
2. Goto /var/db/openldap-data;
3. db_checkpoint-old.version -l to create a checkpoint;
4. db_recover-old.version to make sure the files are in a stable state;
5. db_recover-new.version -e to migrate the files to the new version;
6. Start LDAP.

On ufo go to the directory where the images reside and run reduce.pl.

It works in GIF images only for for an images image.gif it creates the reduced image image-s.gif which is 200 pixels wide.

It also prints the code that should be used for that image:

<!--
<a class="MagicThumb" href="../laboratory/printer/ricoh.gif">
<img src="../laboratory/printer/ricoh-s.gif" alt="" width="200"
height="185" hspace="5"/></a>
-->

find . -mtime +21d -depth -delete

To delete files and directories with a modification date older than 21 days.

-depth is needed to ensure a in-depth tree recursion, so the directories can be deleted only after they have been emptied.

Add the new printer to Samba configuration

Edit the Samba configuration file smb.conf (usually located in /usr/local/etc/smb.conf) to add the new printer:

[sec PCL]
        comment = HP2200dn PCL in #123
        printer name = sec
        path = /tmp
        printable = Yes
        use client driver = No
        printer admin = on, sanjeet, adul, root
        default devmode = No

Upload the driver to Samba

The following works for Samba 3 and Windows XP only.

On a Windows XP machine, connect with and account that has Samba administrator privileges, in CSIM it should be "User Name: root" "Domain: SAMBA". Open Windows Explorer and go to the directory Printers and Faxes of the Samba server: \\banyan\Printers and Faxes

Right click on the background of Windows Explorer window and choose Server Properties from the contextual menu.

Select the tab Drivers and proceed to add a new drivers on the Samba server.

You will see that the files of the new driver are copied to \\banyan\print$\M32X86

On the Samba server, you can check that new files have been added to the directory defined in the path of the share print$. For Windows XP, the files are added in the subdirectory M32X86.

On the Samba server you can use the following command to get the list of installed drivers:

# rpcclient -U'root%******' -c 'enumdrivers' banyan

[Windows NT x86]
Printer Driver Info 1:
        Driver Name: [HP LaserJet P4010_P4510 Series PCL 6]

Printer Driver Info 1:
        Driver Name: [HP LaserJet 4300 PS]

Printer Driver Info 1:
        Driver Name: [HP LaserJet 4300 PCL 6]

Printer Driver Info 1:
        Driver Name: [HP Universal Printing PS]

#

Assign a driver to the new printer

On the Samba server, use the following command to assign one driver to one printer:

# rpcclient -U'root%*****' -c 'setdriver sec\ PCL HP\ LaserJet\ 4300\ PCL\ 6' banyan
Successfully set sec PCL  to driver HP LaserJet 4300 PCL 6.
#

Note that if the name of the printer of the name of the driver contains any space, they are escaped with \.

To see if a driver has been configured, use the command:

# rpcclient -U'root%*****' -c 'getdriver sec\ PCL' banyan
[...]
#

It lists all the files corresponding to the driver or returns an error message.

Configure the driver for the printer

On a Windows XP machine, connect with Samba administrator account, in CSIM it should be "User Name: root" "Domain: SAMBA". Open Windows Explorer and go to the directory Printers and Faxes of the samba server:\\banyan\Printers and Faxes

Right click on the name of the printer and select "Properties".

Configure the tab "Device Settings" and "Advanced/Printing Defaults".

Add the pinter to the Windows clients

Use Add Printer Wizard.