Search This Blog

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

2017-10-03

How to Maintain Old Cars?

Following is a preventive maintenance for old cars with budget conscious, as most old car owner are in control of their financial spending.  This mainly for 8 - 30 yr old Japanese car, i.e. Honda and Toyota, as they are more reliable and have shorter list than Germany and US cars.

They are break into priority, and severity

# Failure Priority Severity Part Cost Estimate Justification
1 Oxygen Sensor - front High High $50 - $100 It has 7-10 yr life, and will impact mpg
2 Oxygen Sensor - rear High High $50 - $180 It has 10-15 yr life, and will impact mpg.  The reason is has longer life compare to front sensor is due to it is further away from exhaust header, so it is under lower temperature
3 Transmission oil Low Low $20 - $120 For non-CVT car, you need to replace less frequent, which is ~ 5 yr, 60,000 miles, 96,000 km.  For CVT, replaces every 3 yr, especially for generation 1 - 2 of the CVT cars.  Honda tends to have CVT failure, and it is unknown whether yearly transmission fluid replacement will prevent it.  Transmission overhaul/repair cost $1000 even for Civic
4 Spark plugs High High $70 - $120 Cars starting 2000 are starting to use long live platinum plugs, which last until 10 yr, or only replace the one that starts miss firing to save cost.  I only waited max of 15 yr.  Platinum plug cost $15/each, so if you want to save cost, just keep 1 spare, and replace whichever one that start breaking.  Out of 4 or 6 plugs, some could last 20 yr
5 Timing belt High High $50 - $100 7 yr, or 110,000 miles, 176,000 km.  If the car's VIN starts with J (made in Japan), then it could last more than 15 yr
6Coolant LowLow $10 5 yr.  No urgency as engine won't suddenly overheat, and this is a gradual process.  Ensure the coolant is compatible with your manufacturer, which will be written on the rear label.  Coolant compatible with US made or Germany often not compatible with Honda & Toyota.  Wrong coolant type will degrade the life of the components
7 Boot - Front drive shaft CV boot Low Low $10 15 - 20 yr.  Replacing the boot when it shows early sign of crack.  After it cracked, then you have to spend $60+ to replace the shaft & boot.  There are left & right boot, so inspects both.  Shaft failure will cause sound when turning
8Tie rod end bushing Low Low $10 - $30 The rubber will torn after ~ 10 yr.  It affects the car alignment, and indirectly causing shorter tire life.  One new tire easily cost more than 1 tie rod
9 Wheel bearing Low Low $20 - $50 In 10 yr, 5% of the car might experience bearing failure.  It will affect mpg by 5%, as well as noise (louder as speed increase)
10 Car alignment Low Low $70 - $100 10 yr.  It shorten tire life due to uneven wear
11 Tire alignment Low Low $15- $40 If you are replacing tire every 5 yr, then perform tire alignment every 5 yr.  If there is a drive shaft, or toe rod failure, then it could causes uneven tire wear, so you need to perform tire alignment
12 Exhaust - Muffler Low Low $50- $150 Rust will build up under the car, and exhaust pipe as well as muffler are commonly the victim.  After 10 yr of rust build up, the pipe or muffler can have a hole anytime.  Knocks on the entire exhaust from exhaust header all the way to muffler at the end to detect any early sign of rust that will rust through and form a hole.  Knock gently or you will have to immediately replace the pipe/muffler if you crack it (as it could be 90% rust through)
13 Catalytic converter Low Low $100 - $180 (aftermarket), $2000 (original)After 10-20 yr, this sometimes last for the entire life of the car.  Its failure will affect mpg & engine power (incomplete combustion).  It will trigger post O2 sensor check-engine-light.  If you replaced your O2 sensor and it still triggering O2 sensor error code, then you know you need to replace this part.  The inspection can only be perform visually, and cannot use on-board computer sensors.  Remove it and see whether the platinum honeycomb has crack.  Shake it to hear any cracked metal.  90% of the time, this is accurate.   My 17 yr old Honda/Acura's unit still look like new.  Recommends to buy original part (> $2000) as after market often need longer bolts, different gasket, or length is a few mm different, which will delay the repair as you need to buy another bolts.  None original can be found 1/10 of the price, but are either having 50% of platinum honeycomb, or has <50 life="" of="" original.="" p="" the="">
Another point to indicate is for old car, when replacing rear/post O2 sensor that attach to catalytic converter, the rust might might crack through it where it is holding the O2 sensor.  There is a risk of breaking it when replacing O2 sensor.

Junk yard doesn't carry used catalytic converter for some reason
14 Exhaust - flex pipe or spring bolt Low Low $50 - $100After ~ 10 yr it is common that you will need to replace either the flex pipe or spring bolt before the catalytic converter.  Flex pipe typically torn and will leak a small amount of exhaust gas, which you can smell in sitting inside the car.  Spring bolt tends to last longer, but will snap when changing the engine oil pan gasket or catalytic converter when you touch it.  Spring bolt should cost $20 - $30
15 Engine valve cover gasket Low Low $12 Minor oil leak at the top of engine starting ~ 7 yr as the gasket get harden, and can't seal tight any more.  Some car can last until 15 yr old, but my recommendation is to change at 10 yr age.  It is cheap and quick to replace for all car models.  The slow leak will cause the engine slowly covered with thick dust.  When it started to leak more, then you can smell the burning engine oil smell inside the car.  There will be oil dripping on the floor as well.  By looking at the top of the engine, you can detect the leak which starts slowly, so you have ample of time to plan the budget
16 Engine oil pan gasket Low Low $10 ~ 10 yr is my recommendation.  It tends to occur few years after engine valve cover gasket leak.  The labor is 2 - 3 hr, but the part is cheap.  Some exhaust part will break, such as flexpipe/spring bolt upon replacement (they are blocking the oil pan) so it is good to replace them together.  Unlike valve cover gasket, you won't smell burning engine oil, but it will drip on the floor.  You need to ask technician to inform you when you do the oil change.  You have ample of time to plan the budget, as it starts slowly even after 20 yr
17 Upper control arm Low Low $30 - $100 ~ 10 yr the rubber bushing will crack, and entire arm needs to be replaced.  It affects car alignment, and uneven tire wear.  It is a cheap part
18 Lower control arm Low Low $30 - $100 ~ 10 yr the rubber bushing will crack, and entire arm needs to be replaced.  It affects car alignment, and uneven tire wear.  It is a cheap part
19 Engine valve cover PVC valve Low Low $3 ~ $15 yr, the carbon could build up in this small plastic valve on the valve cover (circulate the air back into intake boot).  If you use WD-40 or any spray to clean this valve every yr to remove the carbon, then it will last forever.  Else spends a couple dollar to replace it when it doesn't make a knocking sound on shaking.  This affects mpg as well as emission check
20 Vacuum hoses Low High $10 ~ 10 yr, the vacuum hose will be harden, and could affects various components.  For mine, it started with cruise control, as it can't keep the speed.  The hose is $1.50/ft, so you can buy as less as possible
21 Fast idle valve solenoid Low High Free This is specific to Honda old car.  At ~ 15 yr old, the valve will loosen, and needs to remove it and tighten it by screwing it in by 2-5 turns.  No need to replace any part, and all the gasket can be reuse.  Those gasket seems to last more than 20 yr
22 Radiator hoses Low High $15 ~ 15 yr, the radiator hose might become too soft with age, and could burst at random.  The hose is cheap.  Shorter one ~ $10, and longer one ~ $20.  One of them is lower and another is upper
23 Thermostat Low Low $10 ~ 15 yr old.  Buy only original the original thermostat, and don't buy higher temperature model (after market), as higher temperature will degrade other parts, such as PCV valve, radiator hose, although it could improve mpg and warmer heater air
24 Fuel filter Low Low $5 ~ 10 yr.  This is just preventive maintenance in case of poor fuel quality has cause some dirt accumulated.  I have never see car break down due to broken fuel filter.  There is no rubber gasket involve, and all metal.  No electronic pumps involve as well, so no electronic failure can cause car breakdown
24 Battery Low Low $0 - $300 Invests in a cheap 12V battery charger that can charge 1A or 2A.  Every 6 months, charge the battery with 1A/2A, and it will last for more than 10 yr.  If your battery is weak, or fully drain, charge it with 1A (or 2A), and don't use 3A or higher.  You might able to get more life from it, instead of thinking it is dead.
Avoid smart charger, as they confuse a weak or fully drained old battery as bad battery.  If you only have smart charger, then you need to buy another small 12V battery to charge in parallel to fool it

Other Unexpected Repair

Honda & Toyota are the most reliable car in the world, but there are still some random failure.  These could happen to any of the 10% car owner

  1. Fuel pump $70 - issue should be intermittent before it totally break
  2. Idle air control valve - only causes intermittent high idle, and poor mpg.  Intermittent before it get more consistent
  3. MAP sensor - issue should be intermittent before it totally break.  Car won't start on failure
  4. Motor mount $10
  5. Air cond leak $5 - due to aged o-ring
  6. Transmission clutch plate - gear stick or slip, and should be intermittent at the beginning
  7. Front/rear hood lift support - oil leak due to age ~ $20
  8. Starter motor $200- intermittent can't start car (no crack, no start).  Knock on the starter motor, and it should start, or prepare a small power tool battery (12V or 18V) to jump start it
  9. Air cond compressor $250
  10. Alternator $200 - can't charge battery (after turn off, can't turn on any more as battery is low)
  11. Fuel injector relay cracked solder $10 - occurs to most Honda prior of 2000.  Needs to resolder.  Intermittent issue
  12. Ignition distributor $10 - aged o-ring ($1), bearing ($10)
  13. Shock absorber $50
  14. Stabilizer bar bushing $4
  15. Ignition coil $20 - intermittent issue, random misfiring.  You can replace the one which is failing to save cost
  16. Tire air leak $100 - commonly due to bend rim, or rusted steel rim, so that the rubber can't seal the air tight, and slow leak.  Replaces with a used rim for $20 to save money

Preventive Maintenance Consideration for TCO (Total Cost of Ownership)

First thing to consider when maintaining an old car is first asks yourself how long you are going to keep the car, and your reason of keeping it
  1. If you are going to sell soon, then fix those oil leaks to increase its resale value.  Spends $100 - $150 for 3-stage polish to remove the swirl marks, as well as clean engine bay
  2. If you only care about only car breakdown, then repair the Severity High issues.  You can't plan ahead for those "unexpected" repair list
  3. If you going to keep for 5 year, then repair the Severity High, and asks technician to advise which of the Severity Low items that show early sign of failure
  4. If you are going to keep more than 5 year, then perform preventive maintenance of $300 (parts only).  Reserve another $300 for the rest.  The entire list is $600 (parts)

Tips to Save Money

  1. Learn some DIY online, e.g. car forum, and YouTube.  Some of those items are easy for DIY with regular socket wrench, and no special tools required
  2. If you want to get more complete tools, spend $200 - $400, as sending to mechanics can easily cost $300 per visit.  Assumes 1 hr cost $75, then 4 hr cost $300 (which is about 2 repair)
  3. If you owned a 7 yr or older BMW, Merc Benz, Audi, then $300 is just the labor cost for 1 visit, as the mechanics charge $100/hr
  4. Starts by DIY things that is easy to replace
  5. Spends $20 to buy a used car ramp, instead of car jack

2017-10-01

How to Choose Thermostat?

How to Choose a Residential Thermostat?

This post is about choosing and installing a residential universal thermostat for most homes in Canada and USA with central force air HVAC.  All the houses that build in 1980 and furnace and A/C sold since 1990 are using natural gas furnace and split system (force air) A/C with a big outdoor unit.  Heat pump, hot water heating, electric heat board are not cover in this post, as home builds in last 20 yr are not common to use those, especially I am talking about residential homeowner.

There have been so many "how to choose thermostat" articles in the Internet, and why do I need to write one again?  Following are the reasons:
  1. Most of them are not telling that modern HVAC is applicable to units that is 20 yr old.  Reader will think it only applicable to HVAC sell in last 5 yr
  2. None of them tell you how to identify the thermostat support multi-stage HVAC
  3. None of the thermostats installation/user guide explains the installation of multi-stage furnace/AC, as well as the wiring.  They all telling you to plug in what is existing to their
  4. Labels on furnace, and A/C do not tell how to connect to thermostat

Introduction

Furnace and A/C that sold ~ 20 yr ago are starting to become 2-stage (simply put as low and high speed blower), or 3-stage (low, med, high speed blower).  They equip with ECM blower motor that can run at slower speed when the room/house temperature is stables.  These are called multi-stage HVAC (furnace and/or A/C).  They are widely see in all HVAC marketing brochures, but sadly not properly connected, especially for a home owner who bought a used or new home.

I have lived in several houses, which built in 1980's (37 yr old), and some replaced their HVAC once, which probably has a 20 yr old HVAC or more commonly 10 yr old.  Some have 1-stage furnace, some have 3-stage furnace, and current one has 3-stage furnace + 3-stage A/C.  However, the thermostat is not connected to use those mult-stage feature.  Some doesn't even connecting the thermostat cable to the furnace controller board at all, except the base minimum 4 wires (G-fan, R-power, W-heat, Y-cold).

Problems

In reality, not much home owner know whether their house come with a multi-stage (2 or 3) furnace or multi-stage A/C.  Most homeowner bought an existing home, instead of new home, so it becomes norm that the all these houses might have replaced with a HVAC (furnace/AC) 20 yr ago, which are multi-stage.  It is sad that most of these multi-stage HVAC system is not properly connected to the thermostat, and resulting not utilizing these multi-stage features.  These HVAC are running as 1-stage system, which always run at high speed, except the first 5 - 15 min where it will try to run at low/med speed thinking that it will save energy.

Homeowner Frustration

Following is summary of my discovery in HVAC for 10 yr of homeowner experience:
  1. All furnaces have no clear label on its casing, and often have to go to basement and open the front cover to find its model #.  Sadly, it is impossible to determine whether it is multi-stage furnace from its label
  2. All A/C has no clear label on its casing to indicate it is multi-stage A/C, although it is easy to find its model # on the casing
  3. All thermostats are often have 4-wire connected to the thermostat, but it needs more, and have to hire electrical contractor to re-wire (fish the wire) and spends few hundred dollars, or few thousand dollars if stuck in the dry wall and need to remove/install/sand/paint
  4. All thermostat can only control 2-stage for furnace, and 2-stage for A/C, i.e. low, and high.  For high, the control board in the blower unit will spin at mid for 15 min, and high after that.  Latest smart/WiFi/bluetooth enabled brands, e.g. Nest, ecobee, Honeywell, which cost CAD$300+
  5. For thermostat to fully control all the 3-stage or multi-stage HVAC, you have to buy the manufacturer's specific thermostat, which has V wire connection in the thermostat.  It comes impossible when the manufacturer for furnace is different than A/C.  The V wire uses PCM (pulse code modulation), which is manufacturer specific
  6. 2-stage or more furnace requires additional 1 cable called W2, in addition of W.  This will certainly missed out in 4-wire thermostat cable.  This requires 6-wire thermostat wire
  7. 2-stage or more A/C requires  additional 1 cable called Y2, in addition of Y.  This will certainly missed out in 4-wire thermostat cable.  This requires 6-wire thermostat wire
  8. To control both multi-stage furnace and A/C, it requires 7-wire thermostat wire
  9. Battery-less (wall powered) thermostat requires additional 1 cable called C.  This will certainly missed out in 4-wire thermostat cable.  All smart thermostat requires C wire, as battery won't be able to operate WiFi for months, and could probably last only 1 day, if one exists
  10. Controller board in the furnace blower casing has mult-stage disabled by manufacturer as default.  Technician always leave it as default, and running as 1-stage instead of multi-stage.  So the W2 wire is always ignore for furnace, and Y2 wire is always ignore for A/C even if they are connected
  11. When multi-stage HVAC runs as 1-stage, they will run in low speed at first 5 min, mid speed at 5-15 min, then high speed after 15 min.  Thermostat can't control their speed at all
  12. Modern thermostats requires wall power, which is the C wire (min 5 wires).  So a re-wire is required (and spend money), or have to live with battery operated thermostat, AND can't control HVAC's multi-stage feature
  13. All the thermostat which mention about multi-stage, they often refer to heat pump, electric heater, unclear "second level heating," 

Research

If you have a 15 yr old or newer furnace & A/C, then you will most likely have 2 or 3 stage HVAC, with 2-3 speed ECM blower.  Following are the steps to do the research in order to choose the right thermostat:

Multi-stage Furnace Research

  1. Find out the model of the furnace - Go to basement and open up the top and bottom cover of the furnace unit.  There should be a white sticker and shows the model #.  Take a picture with your phone as you will need to refer to it often
  2. Find out the thermostat cable connections at blower motor - Before close the cover of the furnace blower cover, look at the edge of the PCB controller board for wire connections.  There should be a series of wires with label (on the PCB board): R - power, C - ground, G - fan, Y - cold, Y2 - cold (low speed), W - hot, W2 - hot (low speed), V - variable speed (or called modular in my model)
  3. Trace the cable to exterior of furnace - Above cable will connect all the way to the wall thermostat upstair.  Take a good look at the cable
  4. Count how many wires/conductors for thermostat wire at furnace blower motor - don't close the door and continue to examine the thermostat wire in #2.  Spread the wire out, and cut the sleeve if needed to find out how many wire (technically we called conductor) contains in it.  If you have 4 wire, then you have to re-wire to use battery-less thermostat or multi-stage HVAC
  5. Find furnace installation manual - most manufacturers do not have PDF copy of their manual, if you don't have yours.  Go to HVAC forum and asks people to give you the PDF copy, or call/email the manufacturer.  For 10 yr old model, manufacturer might not keep it.  They change their model after 10 yr, even internally has almost no change.  It is more often that their supplier, e.g. ECM motor supplier, replaces/upgrades some part rather than building a more energy efficient unit
  6. Enable multi-stage feature - read the furnace installation manual how to enable multi-stage feature.  For my model, it is my flipping 2 DIP switches to ON.  Manufacturer often leave it off, so that if only W wire is connected, then it operates as 1-stage, and if both W and V wire are connected, then it operates as multi-stage (support multiple stages instead of 2)
  7. For multi-stage furnace, there must be a W2, or V cable.  If it is not connected, then connect the existing wire from thermostat wire into W2.  Leave V alone, as we are installing universal thermostat.  Only manufacturer specific thermostat can make use of V, and not universal thermostat.  If you have both multi-stage furnace and A/C, then connects both W2 and Y2 to the thermostat wire
Figure 1: Left edge of the controller board are the thermostat wire connections labeled W, W2, Y1, Y2, G, C, R

Multi-stage Thermostat Research

  1. Go up stair to the thermostat and remove it, so that you can see the thermostat wires
  2. Take a picture for all the connected wires with labels.  Move them around so that the pictures clearly show the label for every wire.  It is helpful to refer to it whenever you swap another thermostat, including replacing a broken thermostat
  3. Count how many wires/conductors for the wire at the thermostat - it must match the number of wires in #4
  4. If the thermostat wire at the thermostat has 4 wires/conductors, then you have to re-wire to use battery-less thermostat or multi-stage HVAC
  5. Check your thermostat whether it has a V connection.  If it has, then it is a thermostat that capable of controlling multi-stage HVAC.  This is the thermostat that is specific for your HVAC.  If you replaced it universal thermostat, e.g. Nest, Ecobee, Honeywell Lyric (app name is Total Connect Comfort), then you will only able to control 1 low speeds, and mid-high (the blower will slowly increase from mid-high, then remain at high)
Figure 2: Universal Honeywell RTH8500D, which has W2 (2 stage furnace) and Y2 (2 stage A/C), as well as AUX (heat pump to be used as additional heater/A/C) and E (typically heat pump used as backup).  No Y cable so it can't fully control all the multi-stage/speed of the blower

Multi-stage A/C Research

  1. Find out the model of the A/C - Go outdoor and look at the bottom at each corner for a label which indicating the model of the A/C unit.  Take a picture with your phone
  2. Find the thin thermostat wire - trace the thin thermostat wire that comes out from the house to the A/C.  We need to open the cover to find out its wiring
  3. Open the cover where the thermostat cable is connecting to.  You should see a series of wires connecting to a board with label, C, Y, Y2, R.  Take a picture as you will need to refer to it often
  4. Count how many wires/conductors for the wire at the A/C - If it has only 2 wire, then you have to re-wire to use multi-stage AC feature
  5. Examine the controller board for Y2 or V wire.  If it is a 2-stage A/C, then there should be Y (regular 1-stage A/C, and treated as high speed), and Y2 (low speed).  If it is 3-stage or more, then there should be a V on the board
  6. Find A/C installation manual - most manufacturers do not have PDF copy of their manual, if you don't have yours.  Go to HVAC forum and asks people to give you the PDF copy, or call/email the manufacturer.  The reason for lack for PDF is basically the same like furnace
  7. Enable multi-stage feature - read the A/C installation manual how to enable multi-stage feature.  Manufacturer often leave it off, so that if only Y wire is connected, then it operates as 1-stage, and if both Y and V wire are connected, then it operates as multi-stage (support multiple stages instead of 2)
  8. For multi-stage A/C, there must be a Y2, or V cable.  If it is not connected, then connect the existing wire from thermostat wire into Y2.  Leave V alone, as we are installing universal thermostat.  Only manufacturer specific thermostat can make use of V, and not universal thermostat.  If you have both multi-stage furnace and A/C, then connects both W2 and Y2 to the thermostat wire

Architecture


Above architecture diagram shown how a universal thermostat will be connected to multi-stage furnace, and multi-stage A/C.  The controller board in blower motor unit (air sender unit) has wire termination for outdoor A/C unit, so all the thermostat wire connections are connected to thermostat.  There is no direct connection between wall's thermostat with outdoor A/C unit.

Please note about following label:
  • R can be labeled as Rc (24V for cold), or Rh (24V for hot)
  • There could be just 1 R instead of Rc + Rh
  • Both Rc and Rh often has a jumper cable connecting both together in the thermostat, because they are connecting to the same furnace air blower controller board
  • Y is Y1.  For single-stage A/C, you only connect to Y (or Y1), and Y2 is not connected
  • W is W1  For single-stage furnace, you only connect to W (or W1), and W2 is not connected

Choosing Thermostat

As shown in above architecture diagram, the you must know:
  1. Whether your furnace is multi-stage, and wires C, W, W2 are available at the thermostat wall unit
  2. Whether your A/C is multi-stage, and wires C, Y, Y2 are available at the thermostat wall unit
  3. If wires are not connected, is there a spare wires that you can connect C, W2, and Y2 to the air blower controller board (air sender unit)?
  4. If you need to re-wire, find out the quote for that.  1' of 8-wire thermostat wire cost CAD$1, and labor is extra.  If dry wall needs to be cut, then find out the quote to patch the drywall, including painting.  If you have basement's ceiling covered, then the cost will be more to remove and patch them as it is impossible to re-wire
  5. If you have both units are 1-stage system, then you need to find out whether you have 4-wire (no C wire), or 5 wire (with C wire) to power the thermostat.  C stands for common ground, which used together with R (24 Vac) wire to power the thermostat, so that it doesn't need battery
  6. Do you want WiFi thermostat?  It will need wall power, and won't run on battery
  7. If you have C wire available to connect to your thermostat as wall power, you can still consider buying a thermostat that support battery, so that during power failure, the thermostat setting still available.  Most high end thermostat has a coin battery (might not be replaceable) to retain its setting, while low end model don't
  8. If you want to control humidifier, then the thermostat wire will need to have 1 - 2 extra wire
  9. If you want to have outdoor temperature, then the thermostat will need to have 1-3 extra wire, as well as you need to buy the accessory for that thermostat.  Smart thermostats are using Internet to obtain the outdoor temperature, so no longer relies on thermometer sensor

Choices #1: No C wire (only has 4-wire), No multi-stage HVAC

This thermostat can't utilize multi-stage HVAC.  It is rare to find it as this become less common.  Smart thermostat is not going to work
  1. Buy battery operated thermostat without C wire connection
  2. Buy battery operated thermostat with C wire connection.  C wire is future proof, in case you decided to re-wire it with 8-wire thermostat cable

Choice #2: With C wire (only has 5-wire), No multi-stage HVAC

This thermostat can't utilize multi-stage HVAC.  Most low end thermostat will work, which is cheap and commonly available.  Smart thermostat will work
  1. Buy battery operated thermostat without C wire connection
  2. Buy battery or wall power thermostat with C wire connection.  C wire is future proof, in case you decided to re-wire it with 8-wire thermostat cable
  3. Buy smart thermostat

Choice #3: No C wire (only has 5-wire), 1 multi-stage HVAC

Either you have 1 multi-stage furnace, or A/C, but not both.  Most mid model thermostat can be battery operate without C wire, and they have C wire connection for future use.  Smart thermostat is not going to work
  1. Buy battery or wall power operated thermostat with 2H1C (2H means it has W and W2 wire connection to the furnace) if you have multi-stage furnace, or 1H2C (2C menas you have Y and Y2 connection to the A/C) if you have multi-stage A/C
  2. Buy battery or wall power thermostat with 2H2C (this means it has W, W2, Y, Y2) so it is future proof for multi-stage furnace + A/C
  3. Skips 3-stage thermostat models as those are used to control split A/C system with heat pump, which is not common in Canada, USA

Choice #4: With C wire (has 6-wire), 1 multi-stage HVAC

The thermostat choice is same as #3, but buy a unit without battery.  Smart thermostat will work
  1. Buy smart thermostat

Choice #5: No C wire (only has 6-wire), 2 multi-stage HVAC

Smart thermostat is not going to work
  1. Buy battery operated thermostat with 2H2C (this means it has W, W2, Y, Y2).  Recommend to buy a unit with C wire for future proof

Choice #6: C wire (only has 7-wire), 2 multi-stage HVAC

Smart thermostat will work
  1. Buy thermostat with 2H2C (this means it has W, W2, Y, Y2)
  2. Buy smart thermostat

Multi-stage HVAC Wiring Preparation

  1. Smart thermostat needs C wire.  Ensure C (common ground) wire is available and can be connected to thermostat.  Measure the Volt (AC) between R and C at the thermostat, and it must show 24V (same voltage as the small transformer in the furnace blower case)
  2. Follows furnace's installation guide to enable multi-stage furnace, else the thermostat can't control the low speed furnace.  This means activating both W (aka W1) and W2 wiring
  3. Follows A/C's installation guide to enable multi-stage A/C, else the thermostat can't control the low speed A/C.  This means activating both Y (aka Y1) and Y2 wiring
  4. Press the special key in the thermostat (for smart thermostat, it is configuration menu), and configures the thermostat to operate with 2-stage furnace (conventional 90% high efficiency) and 2-stage A/C (conventional).  If you only have one of those, then choose 2 for one, and 1 for another one

Wire Connections

You need 7 wires to connects a multi-stage furnace + A/C, e.g. R, C, G, W1, W2, Y1, Y2
  • If you have a multi-stage furnace, but single-stage A/C, then connects W1, W2, Y1, but leave Y2 empty (not connected)
  • If you have a multi-stage A/C, but single-stage furnace, then connects W1, Y1, Y2, but leave W2 empty (not connected)
  • If the furnace has 3-stage (3 speed) or more (as of today, they have only 3 speed), universal thermostat still going to connect to W1 (also called as W) and W2
  • If the A/C has 3-stage (3 speed) or more (as of today, they have only 3 speed), the same thing apply, universal thermostat still going to connect to Y1 (also called as Y) and Y2

Wire Connections Limitation

If you reluctant to spend money to re-wire the thermostat wire, and you don't have spare, then you are not going to make use of multi-stage HVAC.  There is currently no wireless solution available in the market, although this is possible to have one.

Wire Connections Workaround

If you have some spare wires, pull an extra thermostat wire (with 4 wires or more), but not sufficient to connect to all, then you have some workaround
  1. Only use multi-stage furnace, wire W (aka W1) and W2 - this only need 6 wires, i.e. R, C, G, W1, W2, Y1
  2. Only use multi-stage A/C, wire Y (aka Y1) and Y2 - this only need 6 wires, i.e. R, C, G, W1, Y1, Y2