Search This Blog

2018-07-20

PowerShell: Download Movies from IPTV http Stream Using Invoke-WebRequest

Product: PowerShell
Version: 2

It is often that IPTV provider which provides http URL to access the m3u file will contain following output:

#EXTINF:-1,RAAZI (2018)
http://server.internext.tv:8080/movie/4621036/4621036/29542.mp4
#EXTINF:-1,Pari (2018)
http://server.internext.tv:8080/movie/4621036/4621036/28274.mkv
...cut...

This post will show how to use PowerShell to download them in series.  The reason not download in parallel is because IPTV provider often limit number of concurrent connections

1. Convert the m3u file to CSV file, so that it looks like this

RAAZI (2018),http://server.internext.tv:8080/movie/4621036/4621036/29542.mp4
Pari (2018),http://server.internext.tv:8080/movie/4621036/4621036/28274.mkv

2. Adds a header called OutFile and Uri.  This will be how it looks like

OutFile,Uri
RAAZI (2018),http://server.internext.tv:8080/movie/4103600/4103699/29542.mp4
Pari (2018),http://server.internext.tv:8080/movie/4103600/4103699/28274.mkv

3. Adds the full directory to save the file, as well as extension, e.g. D:\Movies.  Keep the extension identical to the input video stream

OutFile,Uri
D:\Movies\RAAZI (2018).mp4,http://server.internext.tv:8080/movie/4103600/4103699/29542.mp4
D:\Movies\Pari (2018).mkv,http://server.internext.tv:8080/movie/4103600/4103699/28274.mkv

4. Save the file, e.g. download1.txt
5. Download the movies in sequence

import-csv download1.txt | foreach-object {  write-host $_.OutFile;  Invoke-WebRequest -UserAgent "Mozilla/5.0 (Windows NT 6.1; Win64; x64)" -Uri $_.Uri -OutFile $_.OutFile -verbose }

2018-06-25

Honda Lawn Mower: Cheap Throttle Control Cable DIY

Brand: Honda
Lawn mower model: HRA214 PX, HRA 214 SX

This 40 year old lawn mower can be found cheaply and running strong, especially if the owner doing the oil change yearly.  The throttle cable in all the Honda lawn mower build in 1980 - 1990's are common to have the throttle cable rusted in the middle of the cable shell, and broken.

You are lucky if you are in US where you might be able to find the throttle cable for USD$5 online, but in the rest of the countries, it could be 5x - 20x more expansive to replace.  That means it could be USD$5 - USD$100 for just one cable.

Local lawn mower shop is charging me ~ USD$25 for the cable, so I find it not justify for it, and there is no aftermarket cable that is selling for cheaper price.  My other option is DIY the cable.

I don't find any specification of this cable in the Internet, as well as no description in Honda or any sellers' homepage.  Following is the measurement I found myself:

  1. Length: 1.5 m, 150 cm or 5'
  2. End: barrel ended
  3. Barrel dimension: 5.5 mm or 1/16"
  4. Cable diameter: 1.5 - 1.6 mm
  5. Outer housing internal diameter: 1.7 mm
  6. Outer housing outer diameter: 6 mm
A closer look at the throttle cable and its housing, I found that regular bicycle brake cable is a close fit.  Most of the time, the outer cable housing is good, so only the inner cable is rusted.  The inner cable can be remove by forcefully pulling on it.

Challenge using bicycle brake cable:
  1. it only has 1 barrel end.  There are "cable stop" that is barrel shape that can be used.  The kit with various barrel size cost ~ USD$5
  2. barrel diameter is 6 mm (or not mentioned).  Requires to sand it down to 5.5 mm
  3. cable diameter is not mentioned.  Tested that it can slip into the lawn mower cable housing

Parts List

After 1 week of research, and running around town for the parts, I come out with part list that cost USD$5 for DIY, and when replace again in future, it is USD$0.50 or less.  Following is the preparation

  1. ebay bicycle cable with barrel shape end - cost USD$0.20 - $0.50
  2. 60 grid sand paper - cost USD$0.50.  We just used a small piece of it to sand down the barrel to 5.5 mm from 6 mm
  3. Pliers - any type of pliers, but you need to have a good grip when pulling out the rusted cable
  4. Cable cutter - used to cut the bicycle brake cable to 1.5 m length
  5. 6 mm cable stop - this is the most challenging part.  It is one time investment.  Cost USD$0.50 - USD$10, and used to be able to get from bicycle shop, or motorbike shop.  Various sources I found
  6. 2 mm drill bit - used to drill the cable stop hold so that bicycle brake cable can slip in
https://www.amazon.com/dp/B074WBXVGB?pf_rd_m=ATVPDKIKX0DER&psc=1&pf_rd_p=50372716301351393&pf_rd_r=a5f5c788-6559-42aa-b38b-bd8d8b4b4ce1&pd_rd_wg=BcK5y&pf_rd_s=desktop-huc-carousels&pf_rd_t=40701&pd_rd_i=B074WBXVGB&pd_rd_w=ni7rT&pf_rd_i=desktop-huc-carousels&pd_rd_r=a5f5c788-6559-42aa-b38b-bd8d8b4b4ce1&ref_=pd_luc_rh_crh_rh_sbs_01_04_t_img_lh

https://www.amazon.com/Dorman-03337-Cable-Stop/dp/B000CO8ZPG?SubscriptionId=AKIAI3UJXNKQSHF57B4A&tag=t4m00-20&linkCode=xm2&camp=2025&creative=165953&creativeASIN=B000CO8ZPG

https://www.dormanproducts.com/p-549-03336.aspx

http://www.lucky2strokes.com/Cable-Stop-Pinch-Bolt-p/cable-stop-pinch-bolt.htm

https://www.1977mopeds.com/Cable-Stop-Throttle-Knarp-5-5mm-p/418.htm

Steps

  1. Removes the broken lawn mower throttle cable from the lawn mower using the pliers.  Keeps the cable attached to the mower for each of removal
  2. Do not loosen the cable housing as the rusted cable is very hard to remove
  3. At the engine, removes the barrel from the throttle body.  Use pliers to pull the rusted & broken cable out from the cable housing.  Expected to apply force
  4. At the handle bar with throttle control level, removes the hex screw using 8 mm socket/wrench.  Removes the old broken cable from it using pliers.  Expected to apply force
  5. Take note of the metal plats in this plastic housing so that you need to keep the notch at the same place when installing it later
  6. New bicycle brake cable's barrel is 0.1 mm too wide, and won't fit into the plastic housing
  7. Using 60 grid sand paper, polish the new brake cable's barrel with force so that it fit into the plastic throttle control level housing
  8. Sand for 10 sec, and try fitting.  Rotate and sand various angle of the brake cable barrel, so that the shape is still round.  We need to maintain its round shape, but don't need to be perfect
  9. As soon as the barrel can fit into the hole in the housing, sand a little bit more, so that it is loose
  10. Do not cut the new bicycle brake cable, so that it is easy to slip into the old cable housing.  The open end of the cable has a solder, which makes it easy to slip into the housing, so don't cut it yet.  The brake cable is longer than original throttle cable, so you should have plenty of excess
  11. You can apply bicycle brake lubricant, or any light lubricant to delay the rust build-up.  Do this prior of inserting the brake cable into the housing, as this is easier.  This is optional, especially if you keep the lawn mower indoor or covered
  12. Screw the throttle cable adjustment screw at the handle all the way in.  We will adjust the throttle after we cut the excess cable.  This is the screw that is next to the plastic housing at the handle
  13. Moves the plastic throttle control level so that it is in "STOP" position
  14. Pull and hold the brake cable at the other open end, and hold it at the other end at the throttle body
  15. While holding the open end of the cable next to the level with the barrel's hole, cut the cable with 2 - 5 mm extra excess length
  16. The hole of the cable stop is too narrow for the bicycle cable to slip in, so you need to use the 1.5 mm - 2 mm drill bit to expand the hole.  As soon as the cable can slip it, stop drilling.  Just drill one side of the cable stop, as we do not want the cable to come out from another end
  17. Slip the brake cable into the 5.5 mm cable stop (Doman 03337) and slip all the way in.  We only need to use one, and you can sell the rest
  18. Screw tight the cable stop, and make sure the cable is tightly hold by pulling it yourself
  19. If cable loosen, trim the end, and slip it in again.  We have 2 - 5 mm buffer for ~ 2 attempts to make it right
  20. Now the entire bicycle brake cable is converted into Honda lawn mower throttle control cable
  21. Slip the cable stop into the hole the throttle body level
  22. Back to the handle bar throttle control level.  Pull it all the way towards you, so that it is in "STOP" position
  23. Adjust the cable tightness using the screw.  When pull the level towards engine, you should see the throttle move at the engine throttle
  24. If not moving, then reverse out the throttle tightness screw so that it will move
  25. This should be the "STOP" position where the engine will shutdown
  26. Pull the throttle control level all the way to the "CHOKE" position
  27. You should see the throttle move all the way and become shortest length
  28. Start up the engine, and test both "STOP" and "CHOKE" position at both extreme
  29. "STOP" position will stop the engine
  30. "CHOKE" position will start the cold engine
When the cable broken in future, you just need to replace with another new bicycle brake cable that cost $0.20 - $0.50 in eBay.  Even from bicycle shop, it should cost USD$2 - $3.  You can find cheap bicycle brake cable anywhere in the world for a cheap price rather than lawn mower cable.

There are various type of cable stop other than the barrel shape shown in.  Newer Honda lawn mower doesn't use 2-way barrel stop anymore, so you need to find other cable stop to swap in.  This hack/mod still work.

In fact, any small engine is applicable, such as snowblower, leaf blower, gas power generator, boat motor

2018-06-13

MS SQL Server 2012: Step-by-step to Enable TLSv1.2 in Java 1.7

MS SQL Server 2012: Step-by-step to Enable TLSv1.2

Introduction

I assume this is not the first article you stumble in how to enable TLSv1.2 in Java 1.7.x for MS SQL Server 2012.  Java 1.8 or later is able to communicate in TLS v1.2 protocol with MS SQL Server, but not for Java 1.7 u80 and earlier.

Enabling TLS v1.2 implies you are going to enable database communication encryption, e.g. used for the login (this is in plain English, and it does not enable database level encryption which is for each DB).

This article is mainly covering audience who are using JDBC application, such as J2EE.  Application that used .NET, ODBC, or Windows base application will require patching of those framework and driver, which will be lengthy to specify.

The instruction I provided is going to be on a new MS Windows 2012R2 Server OS.  Following are the tasks that I am going to cover, which is needed:
  1. Patching MS Windows Server 2012 R2 OS - any edition
  2. Creating SSL self-sign digital certificate - openssl is used to generate the cert
  3. Configures database communication encryption
  4. Disabling protocol TLS 1.0 in Windows OS - this is the default protocol used by MS SQL Server 2012 if you don't disable it
  5. Disabling protocol TLS 1.1 in Windows OS
  6. Using latest MS SQL Server 2012 JDBC driver
  7. Testing

Patching MS Windows Server 2012 R2 OS

Only Windows Server 2016 pre-configured with TLS v1.2 with the rest disabled.  So the first thing you need to do is to enable Windows Update, and let it install the all the patches, and reboot several rounds if needed.  The MS documentation for this OS is not well maintain and several iteration of patches released on SSL 3.0, TLS 1.0 and TLS 1.1.  If you like, you can spend your own time to research all related articles and sort them in proper order.  Various piece and inter-related patch is wasting my valuable time to test them out to confirm their effective outcome, so I am going to skip those specific patch.

If you skipped this step, then you might encounter difficulty to in SQL Server Configuration Manager  where SSL certificate not showing.

In short, update the OS with all the patches.

Creating SSL self-sign digital certificate

There is no much updated documentation in the Internet about creating self-sign digital certificate using openssl for MS SQL Server.  Most of them are using openssl to create a certificate for IIS, which are missing some standard that MS SQL Server Configuration Manager will check.  If you created the certificate randomly, then Configuration Manager won't display the SSL certificate, and you won't be able to activate it.  In other word, the next step will not be possible

Creates following openssl configuration file, and give it any name that you like.  In this example, I am going to called it cert_config.txt, as my editor will open it faster if I used txt extension

cert_config.txt
#cert_config.txt

distinguished_name = req_distinguished_name
x509_extensions = v3_req
prompt = no

[req_distinguished_name]
C = CA
ST = ON
L = Toronto
O = Blogger
OU = SingCheong
CN = win2012-sql2012sp3-1

[v3_req]
keyUsage = keyEncipherment, dataEncipherment
extendedKeyUsage = serverAuth
subjectAltName = @alt_names
# PKIX recommendations harmless if included in all certificates.
subjectKeyIdentifier=hash

[alt_names]
DNS.1 = win2012-sql2012sp3-1.blogger.com
DNS.2 = win2012-sql2012sp3-1

This step is critical and all the above configuration must be exactly as it is.  Following is the highlight:

  1. CN entry must be the hostname of MS SQL Server
  2. DNS.1 and DNS.2 must contain the hostname of MS SQL Server
  3. keyUsage must contain keyEncipherment
  4. extendedKeyUsage must contain serverAuth so that MS SQL Server can encrypt the communication
  5. subjectKeyIdentifier must exists in the X509 extension.  I don't hard coded it, which is possible, but I let it auto generate using "hash."  Above 4 is specified in MS doc about TLS v1.2, except this one
Again, if you missed anything of the 5 listed above, the SSL certificate won't show up in SQL Configuration Manager


A lot of the homepage are using Windows build-in certificate request method to generate proper certificate or self-sign certificate.  This will require some setup in Active Directory, or pays CA (certificate authority) money to buy one.  As a hacker, I prefer to use free utility to generate the certificate so that I have better understanding of the content of the certificate, as well as how to verify everything I created

Download openssl 1.0.2 or 0.9.8.  I used 0.9.8 which already installed in my old computer, but I recommend 1.0.2 for future proof.  Any version that recognize above openssl syntax will do.

1. Creates the root certificate where the MS SQL Server cert will trust.  It is going to point to default openssl.cnf that comes with OpenSSL.  The key will use RSA 4096-bit and SHA256 for modern encryption, valid for 1 yr.  Generated cert file is called cert.pem, with key file as key.pem.  You can enter anything in the C, ST, L, O, OU, CN, as this certificate is just going to be trusted by the next certificate (used in MS SQL Server), and this one is not going to be used in MS SQL Server

openssl req -new -newkey rsa:4096 -x509 -sha256 -days 365 -nodes -out cert.pem -keyout key.pem -config D:\Utilities\OpenSSL\share\openssl.cnf

2. Created the SSL certificate that is going to be used by MS SQL Server. Valid for 1 yr, cert file is gfcert.pem, key file is selfsigned.key

openssl req -x509 -sha256 -nodes -newkey rsa:4096 -keyout selfsigned.key -out gfcert.pem -days 365 -config cert_config.txt

3. Convert it to PFX file, and includes the SSL cert created in step #1.  This is the certificate file importing into MS SQL Server's Windows OS.  Do not import the PEM file above, even it will be accepted

openssl pkcs12 -export -out gfcert.pfx -in gfcert.pem -inkey selfsigned.key -certfile cert.pem

4. Copy file gfcert.pfx to Windows OS where MS SQL Server 2012 resides

You can verify the gfcert.pem now to ensure you have following required fields
Command:

  • openssl x509 -text -noout -in gfcert.pem
Output
D:\temp\SSL>openssl x509 -text -noout -in gfcert6.pem
WARNING: can't open config file: /usr/local/ssl/openssl.cnf
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            b2:db:7f:8f:4a:58:ca:55
    Signature Algorithm: sha256WithRSAEncryption
        Issuer: C=CA, ST=ON, L=Toronto, O=Blogger, OU=SingCheong, CN=win2012-sql
2012sp3-1
        Validity
            Not Before: Jun 12 17:48:35 2018 GMT
            Not After : Jun 12 17:48:35 2019 GMT
        Subject: C=CA, ST=ON, L=Toronto, O=Blogger, OU=SingCheong, CN=win2012-sq
l2012sp3-1
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (4096 bit)
                Modulus:
                    00:b8:94:c5:91:5b:30:cf:d7:ce:58:82:7e:e0:cf:
                    49:23:79:23:18:97:93:e8:87:3b:9d:81:a4:47:b5:
                    cd:6d:2b:3a:50:08:7e:0b:64:e3:6a:15:8e:a9:98:
                    9e:e7:87:e6:52:0e:2e:82:b6:15:2e:07:c6:28:85:
                    08:01:81:33:fe:b2:f0:ae:62:d5:c0:ac:62:48:ac:
                    77:99:a5:33:a0:65:ef:3b:75:6a:94:06:a3:1b:ea:
                    5a:ad:b5:e7:4b:6f:b0:42:5f:2e:f3:d1:64:3a:a8:
                    d6:5b:23:11:51:09:13:eb:f9:72:84:86:a7:18:f8:
                    f8:7e:8f:d0:cc:24:ff:ee:d1:1c:80:ab:eb:31:87:
                    d1:37:0a:8f:37:31:51:57:12:64:5b:65:1f:84:d5:
                    26:b0:a7:1a:6d:82:1e:71:20:f9:07:90:52:d7:bc:
                    c9:15:1a:2c:08:e6:41:a3:21:83:28:8f:f5:49:19:
                    fd:de:1d:0d:54:60:5d:70:11:12:da:b0:01:5d:b7:
                    32:e6:a6:20:ab:39:68:11:94:8a:d0:9f:f9:d9:7b:
                    6e:a3:50:66:e5:75:7b:ab:90:73:8f:93:ee:8d:e1:
                    f8:62:de:b4:79:6b:db:03:c2:e6:fe:76:df:43:d5:
                    1b:b3:4a:d3:ba:e5:65:12:c6:86:a7:2b:b0:d2:ba:
                    9a:94:d8:ea:14:b9:74:e8:c0:d0:fa:ee:05:e1:61:
                    80:c8:96:04:a3:53:70:b0:7f:11:46:4c:35:9f:13:
                    37:2d:03:1d:6a:c4:15:e2:14:df:01:46:2b:ed:e9:
                    de:c2:9a:01:38:7a:fd:ab:e6:ac:7e:59:32:51:54:
                    29:19:0f:55:e5:c5:19:14:f8:7d:9d:0f:0a:f8:5f:
                    27:3d:fc:33:66:ee:b2:4d:90:d5:61:ea:d0:3e:db:
                    7f:00:2a:65:7b:6b:4f:45:ec:03:18:c2:60:2e:2e:
                    56:82:ab:67:45:7f:82:53:0b:fd:d7:1c:00:d9:79:
                    31:4d:c5:02:44:ce:4a:05:39:83:19:2b:1c:58:ed:
                    4f:ff:40:6f:84:b6:12:e2:4a:86:ad:7a:34:f3:49:
                    9f:2c:56:a5:cd:e3:e2:d1:1b:17:72:cf:85:0f:08:
                    82:81:f6:61:0b:65:f1:d1:a4:52:06:3a:b7:85:7a:
                    c1:8a:c5:cf:7e:b8:f1:e8:2e:8e:75:d9:25:4d:59:
                    80:95:a4:85:e1:9f:0c:71:6d:64:62:81:cb:c9:60:
                    8f:82:05:6a:3e:aa:95:3d:2b:b5:72:a4:a6:de:c7:
                    0a:6e:35:08:ac:d4:f8:73:ce:03:c6:d9:73:ef:af:
                    b3:36:f9:73:84:42:db:f8:8d:e5:75:2e:39:21:7b:
                    c0:21:fb

                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Key Usage:
                Key Encipherment, Data Encipherment
            X509v3 Extended Key Usage:
                TLS Web Server Authentication
            X509v3 Subject Alternative Name:
                DNS:win2012-sql2012sp3-1.blogger.com, DNS:win2012-sql2012sp3-1
            X509v3 Subject Key Identifier:
                50:AC:70:C2:F0:AE:C9:82:6B:D5:3E:4E:5F:F5:C1:D8:BF:6B:90:D0
    Signature Algorithm: sha256WithRSAEncryption
         78:0f:81:ee:40:0a:40:e9:52:6e:5c:89:6a:ab:99:be:f4:d3:
         d2:3d:4e:dc:79:0f:af:3a:1a:80:92:88:53:ec:8e:49:aa:f7:
         0c:45:8a:7b:e0:2b:14:03:63:fe:81:16:09:f7:70:38:59:7d:
         4a:b8:bc:af:aa:e3:d8:82:ca:7e:d8:16:64:21:33:79:e8:87:
         11:a5:4c:74:b5:33:95:c5:d4:4d:bb:6e:83:90:bc:1b:44:76:
         06:14:8b:01:7a:34:19:0a:46:91:38:cb:e3:e6:68:dd:ec:cc:
         e8:34:46:8a:dc:82:47:bb:31:48:6b:65:e3:ae:59:72:ee:2f:
         9d:95:d4:c2:b9:0e:9d:e2:9c:96:ce:f4:f5:e3:d6:7a:7c:b4:
         b6:07:a1:17:fa:bf:3f:80:5c:cf:32:1e:be:8e:6a:d5:fc:9e:
         dd:78:90:67:7e:27:54:7c:8e:55:92:36:6d:30:79:f2:63:8f:
         24:3a:ee:44:2e:ae:17:07:79:89:5c:75:40:c4:a2:22:46:02:
         d7:ff:d3:93:6c:92:04:6b:1c:04:6a:c9:4d:59:e6:44:bf:7d:
         06:7f:49:8d:f9:a2:04:47:b0:b2:9b:0e:91:9b:c1:3f:82:85:
         0d:bd:76:13:02:6d:68:fe:3f:ce:b5:a9:f2:d9:e5:6a:c9:21:
         3f:22:9d:49:d0:49:7f:61:db:52:ca:d5:dc:61:6f:f0:1a:21:
         ef:16:a6:12:25:aa:25:a9:b4:70:93:95:b7:bd:e9:a5:54:62:
         4e:12:bb:e2:d4:21:80:e3:97:1a:60:fa:ad:cc:03:26:64:1a:
         b8:56:0e:86:4d:ed:0d:95:be:3c:6b:0f:cc:cc:9a:51:81:2d:
         72:b7:8a:1b:2f:12:cc:4c:75:d9:52:a5:f7:d2:75:78:78:3d:
         c4:89:3b:77:a1:79:6a:06:26:8e:66:ef:e4:94:10:62:ec:7a:
         89:2f:d2:39:86:f9:94:c9:1e:61:3e:bf:16:88:a7:e5:b2:2f:
         7f:da:38:dd:a9:37:83:14:4b:47:ef:63:04:88:37:b9:c9:f4:
         5c:3f:f2:1e:da:84:84:6b:6b:36:d6:2b:af:11:98:a2:ec:9f:
         8e:76:69:bc:b4:fa:58:04:af:a5:d8:dd:8c:b9:bd:8e:d4:65:
         60:27:56:82:9d:32:01:86:c9:68:90:6f:da:be:c8:46:d5:db:
         ec:00:b2:fd:9b:ed:5f:d8:f6:3a:27:a9:c5:54:c1:ef:d4:27:
         58:c2:fa:86:35:16:6d:9a:9b:a2:3d:a7:a2:4f:67:fa:23:cf:
         fb:9d:fe:a1:6a:60:ab:ef:81:e9:48:a2:ad:66:8c:58:57:ea:
         f2:07:1f:c9:af:43:4c:56

Verify #1: Certificate is valid

Validity

    Not Before: Jun  9 03:07:02 2018 GMT

    Not After : Jun 10 03:07:02 2020 GMT

Verify #2: Subject line contain hostname in CN

Subject: C=CA, ST=ON, L=Toronto, O=Blogger, OU=SingCheong, CN=sup-sql-2012sp3

Verify #3: DNS line contain hostname


X509v3 Subject Alternative Name:

    DNS:win2012-sql2012sp3-1.blogger.com, DNS:win2012-sql2012sp3-1

Verify #4: keyEncipherment in X.509



Look into X.509 v3 extension to find keyEncipherment 



 X509v3 extensions:

   X509v3 Key Usage:

     Key Encipherment, Data Encipherment

Verify #5: serverAuth in X.509



Look into X.509 v3 extension to find serverAuth



X509v3 extensions:

  X509v3 Extended Key Usage:

    TLS Web Server Authentication




Verify #6: subjectKeyIdentifier in X.509




Look into X.509 v3 extension to find serverAuth subjectKeyIdentifier 



X509v3 extensions:

  X509v3 Subject Key Identifier:

    50:AC:70:C2:F0:AE:C9:82:6B:D5:3E:4E:5F:F5:C1:D8:BF:6B:90:D0



Windows OS doesn't need reboot

Import Self-sign Certificate to Personal Certificate Store

This section is taken from MS homepage below
https://technet.microsoft.com/en-ca/library/cc995171.aspx

  1. Press Windows-R, and type mmc
  2. Press Ctrl-M, and drag "Certificates" into right panel.  In the 1st prompt, choose "Computer account"
  3. In 2nd prompt that said "Select computer," choose "Local computer"
  4. Close it dialog by clicking OK button, and you will back to MMC screen
  5. Expand root "Certificates" folder, and you will see "Personal" as the 1st folder
  6. If you never import any certificate before, you won't see child "Certificates" folder.  This is where many internet forum giving misleading info that it will be visible
  7. Right-click "Personal" > All tasks > Import... to open up the dialog to import the pfx file
  8. Dialog box "Certificate Import Wizard" will show up, and you can fill in the gfcert.pfx file
  9. Remember to change the file filter to PKCS#7 so that the pfx file will show up, otherwise it will only show file with .cer extension
  10. Windows OS doesn't need reboot

Configures database communication encryption

This is the most crucial part of SSL/TLS enabling step.  If the SSL certificate is not created properly, then you will not able to pick the certificate created in previous step.

I will just show the screenshots.  Perform this step in MS SQL Server

If you created the certificate correctly as specified above, then the SSL certificate will be able to list below.  I did not manually update Windows registry to add the thumbprint of the certificate like some other homepage indicated, although I am using older Windows 2012 R2

Now the MS SQL Server can accept both encrypted and un-encrypted connection.  In other word, the client will specify whether to use SSL/TLSv1.2 encryption.  If you only want to enable encrypted SSL/TLSv1.2, then you can enforce it at server level.


Let me give you 2 JDBC URL and their behavior

Following JDBC URL will try non-encrypted connection first, before encrypted, if server doesn't enforce:
jdbc:sqlserver://192.168.1.2:2012;DatabaseName=cc807_sc;User=dbadmin1;Password=xx

Following JDBC URL will try encrypted connection only, even if server doesn't enforce.  Since our SSL certificate is self-signed, so we need to specify trustServerCertificate:
jdbc:sqlserver://192.168.1.2:2012;DatabaseName=cc807_sc;User=dbadmin1;Password=xx;encrypt=true;trustServerCertificate=true

Following JDBC URL will try encrypted connection with TLS v1.2 protocol first, as well as trust the self-signed SSL certificate:
jdbc:sqlserver://192.168.1.2:2012;DatabaseName=cc807_sc;User=dbadmin1;Password=xx;sslProtocol=TLSv1.2;trustServerCertificate=true

Restart SQL Server Windows service to activate SSL/TLS v1.2 encryption.  Now it will accept following connection:

  1. Un-encrypted connection
  2. Encrypted connection - SSL/TLS v1.0 - This is the default for MS SQL Server 2012 with JVM <= 1.7u80 (latest free download from Oracle.  Version higher only available to paid Oracle customer)
  3. Encrypted connection - SSL/TLS v1.1 - None of the JVM will use this encryption protocol
  4. Encrypted connection - SSL/TLS v1.2 - JVM 1.7u90 and higher or 1.8 and higher
  5. Encrypted connection - SSL/SSL v3.0 - JVM 1.7u80 and older will fall back to this protocol once it failed TLS v1.0.  Once you patched your client OS and Java, both will typically deny this protocol

Install Network Sniffer Utility

In this TLS v.1.2 step, I do not explicitly disable SSL 3.0, TLS 1.0, and TLS 1.1 in the OS hosting MS SQL Server instance (yes, if you still confuse, the way to disable SSL3-TLS1.1 is in OS level, not DB).  In other word, MS SQL Server 2012 will accept all 5 types of connections below:
  1. Un-encrypted connection
  2. Encrypted connection - SSL/TLS v1.0
  3. Encrypted connection - SSL/TLS v1.1
  4. Encrypted connection - SSL/TLS v1.2
  5. Encrypted connection - SSL/SSL v3.0
The main reason I do that is because it is very hard to troubleshoot these 5 encryption handshaking from client end (even it is a J2EE server application, it is consider a DB client).  No J2EE, Servlet, applet, or Java application will display a graceful encryption handshake error.  Therefore, I will leave the server to accept both encrypted and unencrypted communication, then disables it after configured the DB application to use TLS v1.2.

There is no utility that can determine from MS SQL Server end that the encryption protocol is TLS v1.2 or older.  Network packet sniffer is the only utility to confirm that.  The network sniffer utility I chosen is an old MS Network Manager utility because of following reasons:

  1. NM34_x64.exe installer is 6.52MB only
  2. netmon.exe executable used 55MB RAM, so the rest of the RAM can be used to hold the ntework packets
  3. Free

Download the software from Microsoft homepage below:
https://www.microsoft.com/en-ca/download/details.aspx?id=4865

If you prefer the new replacement utility from Microsoft, it is called Microsoft Message Analyzer, and can be found in following Microsoft homepage:
https://www.microsoft.com/en-ca/download/details.aspx?id=44226

1. Run the utility and click on "New Capture"
2. Click on "Capture Setting" to filter capturing from J2EE server, and port 2012 (or 1433 default)
Fill in "Current capture filter" as shown in above screenshot, i.e.
IPv4.Address == 192.168.1.2
and tcp.Port== 2012

3. Optionally, you can fill in the "Display filter" if you are capturing multiple J2EE server IP, but use display filter to control the output on the screen

4. You can run the J2EE application, or Java applet/application/servlet/Tomcat/WebLogin/WebSphere/Cognos etc while capturing it.  Following is what you should pay attention and able to tell which SSL protocol is used. In this example for Java 1.7u80, it is using SSL/TLS v1.0


The network frame detail will tell you whether it is SSL/SSL v3.0, TLS v1.0, v1.1, v1.2.

Following output is SSL/TLS v1.0:

TLS: TLS Rec Layer-1 SSL Application Data
  TlsRecordLayer: TLS Rec Layer-1 SSL Application Data
    ContentType: SSL Application Data
    Version: TLS 1.0


Following output is SSL/TLS v1.2:
TLS: TLS Rec Layer-1 SSL Application Data
  TlsRecordLayer: TLS Rec Layer-1 SSL Application Data
    ContentType: SSL Application Data
    Version: TLS 1.2

Testing MS SQL Server 2012 Using openssl

Following openssl command cannot be used to test SSLv3.0 - TLSv1.2 protocol reliably

openssl s_client -connect db_server:1433 -tls1_2
openssl s_client -connect db_server:1433 -tls1_1
openssl s_client -connect db_server:1433 -tls1
openssl s_client -connect db_server:1433 -ssl3

MS SQL Server SSL handshake works different way than https, smtps, ftps.  The MS SQL Server will always accept the openssl connection, but it won't display the SSL certificate, cipher, or will hang after shown it is connected

Sample output that hang:

D:\>openssl s_client -connect db_server:1433 -tls1_1
WARNING: can't open config file: /usr/local/ssl/openssl.cnf
CONNECTED(000001C0)

Sample output on MS SQL Server where TLS v1.2 is configured, as well as not configured.  They output are identical

D:\>openssl s_client -connect db_server:1433 -tls1_2
WARNING: can't open config file: /usr/local/ssl/openssl.cnf
CONNECTED(000001C0)
9240:error:1409E0E5:SSL routines:ssl3_write_bytes:ssl handshake failure:.\ssl\s3_pkt.c:659:
---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 0 bytes and written 0 bytes
---
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
    Protocol  : TLSv1.1
    Cipher    : 0000
    Session-ID:
    Session-ID-ctx:
    Master-Key:
    Key-Arg   : None
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    Start Time: 1529075233
    Timeout   : 7200 (sec)
    Verify return code: 0 (ok)
---

Configuring Java J2EE Application to Enable TLS v1.2

Most Java applications, e.g.Java servlet, Apache Tomcat J2EE, Java application, WebSphere EAR, WebLogic EAR, JBoss WAR, etc are having a configurable JDBC URL, and Java runtime parameters.

The JDBC URL can be easily configured to establish SSL/TLS v1.2 protocol

Ref 1: https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-2017
Ref 2: https://github.com/Microsoft/mssql-jdbc/wiki/SSLProtocol

Microsoft has very poor documentation for their older database in the JDBC URL connection string.  Above 2 links are only showing for their MS SQL Server 2017, while another link doesn't even mentioned which version it is applicable for.

The JDBC URL that should enforce TLSv1.2 protocol for MS SQL Server driver is below:

sslProtocol - Introduced in JDBC Driver v6.4.  Supported value are TLS, TLSv1, TLSv1.1, TLSv1.2

E.g.

jdbc:sqlserver://db_server:1433;sslProtocol=TLSv1.2;trustServerCertificate=true

jdbc:sqlserver://db_server:1433;sslProtocol=TLSv1.1;trustServerCertificate=true
jdbc:sqlserver://db_server:1433;sslProtocol=TLSv1;trustServerCertificate=true

Ref: https://docs.microsoft.com/en-us/sql/connect/jdbc/release-notes-for-the-jdbc-driver?view=sql-server-2017

I have not able to make my sample Java application to use TLS v1.2 in JVM 1.7u80 (latest free Oracle Java from download.oracle.com).

In term of troubleshooting, you can add Java command line parameter like below so that the console will display SSL low level debug messages.  If it is Tomcat server, then it will be in catalina.log

$JAVA17_HOME/bin/java -Djavax.net.debug=ssl -cp .;mssql-jdbc-6.4.0.jre8.jar JDBCVersion2

Note 1: -Djdk.tls.client.protocols=TLSv1.2 is not applicable for DB troubleshooting, but only used for web/SOAP https communication
Note 2: -cp is used to specify the MS SQL Server JDBC driver, so that you can quickly switch among many different drivers.  This is useful is the driver is provided by 3rd party, or you are testing your program's compatibility with various driver version, or Java version, or both

Unsupported Java Feature in 1.7

This section is specially listing down Java feature that available in 1.8, but not for 1.7.  There are many partial information in the Internet, and I specifically created this section to address all.

-Djdk.tls.disabledAlgorithms =SSLv3,TLSv1,TLSv1.1
  1. This is not available for 1.7, but valid for 1.8 and later
  2. Even for 1.8, this only affect https and not for MS SQL Server
-Dhttps.protocols="SSLv3,TLSv1.0,TLSv1.1"
  1. This only affects https outbound, and not application to MS SQL Server as it doesn't talk in https
-Djdk.tls.client.protocols="TLSv1.2,TLSv1.1"
  1. This is not available for 1.7u94 and earlier, but valid for 1.7u95 (paid) & 1.8 and later
  2. It is able to control the SSL/TLS protocol used


Reference:
https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-R2-and-2012/dn786418(v=ws.11)
https://docs.microsoft.com/en-us/sql/connect/jdbc/release-notes-for-the-jdbc-driver?view=sql-server-2017

2018-02-19

Kwikset Electronic Deadbolt Lock Review

Manufacturer: Kwikset
Product:  All electronic deadbolt locks, all smart locks

Battery operated devices has been around easily for more than 50 years.  In past 10 years, residential door locks have been slowly adapting the electronic locks due to the popularity of hotel key fob, and office key fob system.  More importantly, the introduction of bluetooth and WiFi in mobile apps that allows more transparent usage of door lock.  This also allow phone, and smartwatch be used as key to unlock the door.

Most of the residential locks are using AA batteries, unlike commercial models which uses power line with power adapter.  This blog post mainly to point out that the company that designed it, especially Kwikset, are not following the industry standard that battery operated devices should consider the depleted battery as 0.9V where the new battery is 1.5-1.6V.

I recently acquired 2 Kwikset 264 electronic deadbolt lock and found that the lock is complaining low battery when the battery is 1.3V.  This means it still has 0.4V to be considered depleted, yet it starts to stop to operate, and consistently buzz the alarm.  The lock is not able to lock, unlock, or auto lock.  Further investigation, this seems to be affecting the entire line of electronic locks:
  • Model 264, 275
  • Kevo series smart lock, e.g. Kevo Convert, Kevo Contemporary, Kevo Traditional
  • Premis Apple HomeKit Smart Lock
  • SmartCode 915, 916, 914, 913, 912, 911, 910, 909
  • Powerbolt 1, 2
This also happen to Honeywell battery operated 2-stage thermostat that is 5 yr old.  It also consider a 1.3V as depleted battery and shown low battery icon.

There are few factors why battery operated devices need to work in range of 0.9V - 1.6V:

  1. Industry standard - it is used to measure the battery capacity, and toys industry has long adopted that
  2. Fully used the energy stored in batteries
  3. Reduce waste due to not depleted batteries
  4. Extend longer operation of the devices
  5. Rechargeable battery can be used to further conserve the battery waste
I used Kirkland alkaline battery, and it last ~ 4 months.  I know that these battery hardly has 2000mAh, but if they can allow it to operate until 0.9V, it should able to last 6 months.  Curerntly, it only used up 42% of the battery's capacity, and 68% wasted.  As consumer, we would like the lock to operates longer, and won't get locked out by the electronic lock.  The landlord might gives the PIN code to tenant, while keeping the key for personal used.  Property manager also doesn't want to visit every house to swap the battery every 4 -6 months.

This is very important to operate above 0.9V, as all rechargeable AA batteries have 1.2V when fully charged.  The mAh capacity calculation that is advertised is also considering the depleted battery level as 0.9V.  This will made all rechargeable battery unable to be used in these battery operated devices, e.g. lock, thermostat.

I am a big fan of NiMH AA for last 20 yr, and their 2500mAh capacity and long life have been proven in my Panasonic phones to be very cost effective.

Hope that these manufacturers are able to see my blog post and revise their design.

2018-01-13

Windows: How to Disable Auto Startup

Windows: How to Disable Auto Startup of Any Programs

Overview

Windows Version: 95, 98, XP, Vista, 7, 2008, 2008R2, 2012, 2016

Many virus and applications will auto startup during OS boot up.  This includes:
  1. 3rd Party Firewall - an upgrade might block internet access, and you would like to temporary disable it
  2. virus
  3. Freemake upgrade notification
  4. Many Chinese ad-base software, e.g. Baofeng Storm Player, LETV, funshion, baidu, 迅雷影音 XMP
  5. Antivirus - you doesn't want it running any more to slow down your PC
Following are the areas you can check
  1. Start Menu > All Programs > Startup
  2. Windows Registry (regedit)
  3. Windows Services (services.msc)

Startup Menu

In Windows Start menu, where it will pop up by pressing Windows key on the keyboard, there is a folder called "Startup."  Any application shortcut will startup the program.

To remove it, right click on the shortcut icon, and click "Delete" to remove it.  This will permanently disable it.  You cannot undo it, unless it move it to other folder.

MS Config

Press Windows-R key on the keyboard, and enter "msconfig" to run this utility.

It provides quick and easy access to following which you can disable auto startup of supported programs:
  1. Windows Services
  2. Windows Registry for auto run programs
The disabled entries will remain on the screen, so you can undo them in future.

Windows Registry

This is the most common location where the auto startup is often configured, and it doesn't allow user to easily remove them

  1. HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\
    1. Run\
    2. RunOnce\
  2. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\
    1. Run\
    2. RunOnce\
  3. HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\
    1. Run\
    2. RunOnce\
Wow6432Node folder is used by 32-bit applications on 64-bit Windows.  Many 32-bit applications will create the auto run entry under this folder

2017-12-25

BMW: Enabling/Disabling Auto Door Lock

BMW E46, E60, E90, E91, E92, E36, E39

You can enable auto door lock for BMW cars in 1980's - 2000's using the remote control key, and doesn't need to pay dealer to enable/disable the feature.

These cars can auto lock all the car doors after the car's speed is above 10 km/h.  This feature is to prevent when you are waiting in traffic light, and motorbike/cyclist will open your door and grab laptop, tablet, jewel, etc from you.

Please note that these car model year doesn't has auto unlock, but added at the later year series.  Again, this is a traditional safety that pass from older car generations.  The reason is to ensure that once the car's engine is turn off, and even the key is pulled, motorbike/cyclist won't be able to open your door and grab laptop, tablet, jewel, etc from you.

There are many wrong information in the internet that this feature can only be enabled using OBDII programmer (those that could control BMW ECU), and often to be the dealer.  By accident, I found that this feature can be enabled and disabled using the remote control key fob.

Auto Door LockSteps
Enable
  1. Ensure door is lock.  If not, press the LOCK button on the key fob
  2. Insert the car key into the door lock.  Unlock the door by turning clock wise
  3. Now the feature is enabled.  You can drive the car to above 10 km/h to confirm the door is auto lock
  4. You can only manually unlock & open the door using following methods
    1. Pull the inside door handle twice to open the door
    2. Press the door unlock button near to the handbrake
Disable
  1. Ensure door is unlock.  If not, press the UNLOCK button on the key fob
  2. Insert the car key into the door lock.  Lock the door by turning counter-clock wise
  3. Now the feature is disabled.  Get into the car by using key fob (UNLOCK button), or car key (door lock).  Drive the car to above 10 km/h to confirm the door is not auto lock

For E39, if you lock the trunk by turning the key to the lock position, which is horizontal, then you will also enable auto door lock

Annoying Behavior with Auto Lock

  1. Everyone needs to pull the door handle twice in order to open the door
  2. The rear trunk will be locked
  3. If the passenger side has nobody sitting, or rear seats, then the door(s) will remain lock.  So if someone going to get into it, you have to use the remote key fob to unlock the doors

2017-10-20

MS SQL Server: Verify Login Timeout

Product: MS SQL Server
Version: 2000 - 2016

Microsoft MS SQL Server pre-install with login timeout of 10sec.  Many developers or support personnel might have a chance to encounter login timeout while DBA might tell you that there is no login timeout, as they could be confused with other timeout values

There are 3 different timeout setting in MS SQL Server, so there is no surprise that a junior DBA mistaken one for other.

In the Internet, there is not much info about how to verify these 3 timeout setting, including login timeout.  This blog post is specifically to cover it.

How to Verify Using SELECT 

Uses following SELECT statement from any MS SQL Server client, such as Toad, DbVisualizer, SSMS, as well as programming.  This approach is not widely documented especially on verifying login timeout

select configuration_id, name, value_in_use from sys.configurations where configuration_id in (1519, 1541, 1520)

Sample output:
configuration_idnamevalue_in_use
1519remote login timeout (s)10
1520remote query timeout (s)600
1541query wait (s)-1

Note: Above is default values for new MS SQL Server installation

You can see clearly that "remote login timeout" is 10 sec for default MS SQL Server installation.  DBA often confused that with "query wait" which is never, or no timeout.

Pros:

  1. It is a regular SELECT statement that be easily written in any programming language
  2. It is easily executes in web application
  3. SELECT statement is 100% supported by any DB client, and utilities, e.g. Toad, Eclipse, Excel, DbVisualizer
  4. Does not require to engage DBA to verify it
  5. Execute once to see all 3 timeout values without hoping into various screens

Cons:

  1. People who don't understand SELECT statement syntax might find it a lots of keys to enter
  2. Not GUI base
  3. Requires to run in SSMS or DB client
  4. Junior DBA less familiar with this output, and might has more push back

How to Verify Using sp_configure

sp_configure statement can be used to both verify as well as set the database instance value.

Following statement can be used to verify 3 different timeout parameters.  You need to execute it 3 times as sp_configure command only allow to display 1 value

sp_configure 'query wait (s)'
go
sp_configure 'remote query timeout (s)'
go
sp_configure 'remote login timeout (s)'
go

Sample output:

Pros:

  1. Simpler syntax compare to SELECT statement
  2. Less typing
  3. Junior DBA more familiar with this output
  4. Statement is 100% supported by any DB client, and utilities, e.g. Toad, Eclipse, Excel, DbVisualizer
  5. Does not require to engage DBA to verify it
  6. Minor modification to the command will allow DBA to set the new value in real time

Cons:

  1. Cannot obtain all 3 values in 1 statement
  2. More effort in programming to integrate it, as well as get the 3 parameters
  3. Not GUI base
  4. Requires to run in SSMS or DB client

How to Verify Using GUI

This is the most commonly documented in MSDN or Technet.  All junior DBA are familiar with this approach, as there is no learning curve

Launch SSMS, open up the DB instance properties to verify these 3 timeout values


Pros:

  1. GUI driven, and no need to memorize any statement or syntax
  2. No typing requires
  3. Junior DBA very familiar with this screen, and no push back
  4. Quick & easy clicking on 2 screens to see all 3 timeout values
  5. No need to memorize any command, such as SELECT and sp_configure

Cons:

  1. Requires SSMS
  2. Not supported by any DB client
  3. Requires some skill to map the values in GUI to the sp_configure values
  4. Impossible to written in any programming language
  5. Impossible to execute in web application
  6. Might need to engage DBA if firewall blocks SSMS
  7. Needs to check 2 screens to determine all 3 parameters