TLS 1.2 Support on Windows 7

[MBlumberg73]

I have an excel macro that I use to interact with APIs. Everything works great... Today I was adding a 2nd API endpoint while working on my W10 laptop - everything worked fine and the API provided expected results. So, I deployed the revised spreadsheet to my AWS EC2 Server 2008r2 where I run the scripts daily. The original API still works, but the new API gets all 408 errors. Very strange. So I tested the file on on W7 latop - original works, new API gets 408 errors (same issue as the 2008server). I then tested on my kids W10 desktop, both APIs work perfectly. ALL are running Office 365.

So, I believe I found a host computer related issue. To recap, I have 2 endpoints within one Excel macro - both work fine on W10, one returns 408 errors on W7 and Server2008r2. What could be causing the 408 errors only on the older OS?

[Sophist-UK]

408 is a Request Timeout error.

Some thoughts:

a. Run the API query from a browser on the W7 and Server2008R2 machines to check that there is not a firewall issue. b. Put in Debug statements in your macros to see how long it takes for it to timeout. This might indicate whether it is a real timeout (long) or something else causing it (short). c. Check that WinHttp / IE patches from Windows Update are the same on both machines.

Otherwise, there is insufficient detail in your description for me to narrow it down further.

[MBlumberg73]

Thanks for your response. Some additional information: 1) the API connection that is failing is https://api.dropship.com/api/v2/hello - from a browser it connects and returns correctly on all machines (in this case an authentication error) 2) The 408 is returned in less than 2 seconds always.
3) From what I can tell, all machines are fully patched.

The code is exactly same to call both API endpoints... so what has me baffled is why one works and the other gets a 408 every time.

Below is my test code (authorization headers have been removed, but the APIs should still respond. The DSCOtest returns a 408, the TGtest returns a 401 (authorization error). I run them from the same module.

Any thoughts/insights are appreciated.

Sub DSCOtest() 'returns 408 timeout WebHelpers.EnableLogging = True Dim Client As New WebClient Dim Request As New WebRequest Request.Method = WebMethod.HttpGet Client.BaseUrl = "https://api.dropship.com/api/v2/help" Dim Response As New WebResponse Set Response = Client.Execute(Request) Debug.Print "Response:" & Response.StatusCode Debug.Print "content:" & Response.Content End Sub

Sub TGtest() 'returns 401 authorization error WebHelpers.EnableLogging = True Dim Client As New WebClient Dim Request As New WebRequest Request.Method = WebMethod.HttpGet Client.BaseUrl = "https://api.tradegecko.com/orders" Dim Response As New WebResponse Set Response = Client.Execute(Request) Debug.Print "Response:" & Response.StatusCode Debug.Print "content:" & Response.Content End Sub

UPDATE: Support docs: https://api.dropship.com/docs/v2/ Also, the endpoint is "https://api.dropship.com/api/v2/" - while "help" is not a real function, it does return a 401 on W10 and 408 on W7 to show the issue (a real function is "hello", but requires a authorization token).

[timhall]

I'm also getting a 408 for Windows 7 while I get a 401 in Chrome and Postman. Specifically, I'm getting WinHttp 12029: A connection with the server could not be established (Link).

ERROR_WINHTTP_CANNOT_CONNECT 12029 Returned if connection to the server failed.

@MBlumberg73 Do you have any documentation for the API? (I couldn't find any and help returns a 401 :wink:)

[timhall]

Update, here are the docs: https://api.dropship.com/docs/v2/

[MBlumberg73]

Thanks for confirming. Do you have any other OS platforms you can test the VBA code on?

[MBlumberg73]

Tim, Thanks for you help getting to the bottom of this.

I have tested the CURL via the command line (captured from execute class), and it works on W7 via command prompt - but fails via VBA. I added the "--insecure" since i have not setup my curl for ssl. Can you test? My gut thinks this might be related to the SSL certificate or how your code handles SSL verification, but I could be completely wrong.

curl -i --connect-timeout 200 --max-time 600 --insecure --location -H "User-Agent: VBA-Web v4.0.19 (https://github.com/VBA-tools/VBA-Web)" -H "Content-Type: application/json" -H "Accept: application/json" -H "Content-Length: 0" -X GET -d "" "https://api.dropship.com/api/v2/hello"

[timhall]

@MBlumberg73 I tried Request.Insecure = True, Request.EnableAutoProxy = True, using Chrome's User-Agent, using the IP address directly, explicitly setting the port, and looked into additional WinHttp Options (although I didn't see anything that seemed relevant) and still couldn't get it to connect on Windows 7. I don't have any other OS platforms to test on at the moment (my Mac died on me). I started working on using Fiddler to investigate what may be going on, but haven't gotten it working just yet, I'll investigate further this evening if I have time.

[MBlumberg73]

@timhall Try a curl from the command prompt: curl -i --connect-timeout 200 --max-time 600 --insecure --location -H "User-Agent: VBA-Web v4.0.19 (https://github.com/VBA-tools/VBA-Web)" -H "Content-Type: application/json" -H "Accept: application/json" -H "Content-Length: 0" -X GET -d "" "https://api.dropship.com/api/v2/hello"

This should return an unauthorized. But the same request via VBA-Web gets a 408.

[timhall]

Hmm, using curl from git bash, I'm getting the following:

curl: (35) Unknown SSL protocol error in connection to api.dropship.com:443

Adding --verbose, I get:

* timeout on name lookup is not supported
* About to connect() to api.dropship.com port 443 (#0)
*   Trying 52.72.74.224...
* connected
* Connected to api.dropship.com (52.72.74.224) port 443 (#0)
* SSLv3, TLS handshake, Client hello (1):
* Unknown SSL protocol error in connetion to api.dropship.com:443
* Closing connection #0
curl: (35) Unknown SSL protocol error in connection to api.dropship.com:443

$ curl --version
curl 7.26.0 (i686-pc-mingw32) libcurl/7.26.0 OpenSSL/0.9.8x zlib/1.2.7

Looks like a pretty old version (2012) so that may be affecting it. Also tried --sslv2 and --tlsv1

[MBlumberg73]

@timhall small update. I have notified the provider that they might have a ssl cert config issue (https://www.ssllabs.com/ssltest/analyze.html?d=api.dropship.com). It is definitely part of the issue, but still doesn't explain why curl insecure works and VBA-Web does not on the same machine. Have you had a chance to investigate further? Thanks.

[timhall]

Wow, cool SSL tester. The multitude of "Server closed connection" in the handshake simulation seems like the issue to me. My guess is that they are TLS 1.2-only which as far as I know has issues with WinHttpRequest that I've invested somewhat in the past, but will look at more.

[MBlumberg73]

@timhall correct, they confirmed that they are TLS 1.2-only. It is odd that WinHttpRequest works for this on a W10 machine, but not other platforms.

[timhall]

I've looked into it a little further and found this: https://msdn.microsoft.com/en-us/library/windows/desktop/aa384066(v=vs.85).aspx#WINHTTP_OPTION_SECURE_PROTOCOLS

By default only SSL3 and TLS1 are enabled in Windows 7 and Windows 8. By default only SSL3, TLS1.0, TLS1.1, and TLS1.2 are enabled in Windows 8.1 and Windows 10. The value can be a combination of one or more of the following values.

I've tried setting the option to use TLS 1.2 and it throws an error in Windows 7:

' (in WebClient.PrepareHttpRequest)
' TLS 1.0 (works)
web_Http.Option(web_WinHttpRequestOption.web_WinHttpRequestOption_SecureProtocols) = 128

' TLS 1.1 (fails)
web_Http.Option(web_WinHttpRequestOption.web_WinHttpRequestOption_SecureProtocols) = 512

' TLS 1.2 (fails)
web_Http.Option(web_WinHttpRequestOption.web_WinHttpRequestOption_SecureProtocols) = 2048

From what I've seen/heard, this may just be a limitation of the WinHttp COM support in Windows 7. I received an email with an alternative approach that uses the WinHttp dll directly that should fix this, I'll dig it up and see what can be done.

[bl4met]

Here is a MS KB article that was able to fix an issue i was having with trying to access a site using TLS 1.2 on Win7.

"This update provides support for Transport Layer Security (TLS) 1.1 and TLS 1.2 in Windows Server 2012, Windows 7 Service Pack 1 (SP1), and Windows Server 2008 R2 SP1."

https://support.microsoft.com/en-us/kb/3140245

[timhall]

Awesome! I was tempted to write this off as not fixable, this is great news. I'll be sure to add a link to that in the docs/wiki.

[MBlumberg73]

Thanks @bl4met !!! I went back to some W7 machines and W2008R2 server and tested the code that works on my w10 machines.... They NOW work on them without issue. The update had previously automatically installed and... problem SOLVED!!! Thanks!

[leizmendi]

Maybe I'm out of wave or it's been a long time since the last message ... I was needing to establish an http connection through the TLSv1.2 protocol and finally I have achieved it through this code ...

Private Function PostCheckTLS() As String
    On Error GoTo Error_PostCheckTLS
    Dim oHTTP As Object 'WinHttp.WinHttpRequest
    Dim strResponse  As String
    Set oHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    Call oHTTP.Open("POST", "https://tlscheck.wubook.net/", False)  ', gwiUserName, gwiPassword)
    oHTTP.Option(9) = 2048'<----------this is the key, but I don't know really what it does.. :)
    DoCmd.Hourglass True

    SysCmd acSysCmdSetStatus, "Comunicando https://tlscheck.wubook.net/"
    Call oHTTP.Send("")
    strResponse = oHTTP.responseText
    Set oHTTP = Nothing
    PostCheckTLS = strResponse
    MsgBox strResponse, vbInformation, "H4 - Abauntz software"
Salir_PostCheckTLS:
    SysCmd acSysCmdClearStatus
    DoCmd.Hourglass False
    Exit Function
Error_PostCheckTLS:
    Select Case Err
        Case Else
            MsgBox "Error nº " & Err & " en PostCheckTLS" & vbCrLf & Err.Description
            Resume Salir_PostCheckTLS
    End Select
End Function

[leizmendi]

Sorry, I really was out of wawe and the proposed solution worked but only in windows 10. In windows 7 I get an error in the assignment oHTTP.option (9) = 2048. I have updated the operating system to windows 7 + SP1 and applied the update kb3140245-x64 and the MicrosoftEasyFix51044 but it still gives the same error. Do you know of any possible solution? Thank you very much Lorenzo

[zgrose]

Just curious since the fixes appear to apply to the registry, what's the reply from the server if you just leave out setting the option?

[leizmendi]

Yeahh, you are right!: Thank you, zgrose!