|
Hi SQLAnywhere Forum, OS: Windows Server 2016 SQLAnywhere Version: 17.0.11.7312 PHP Version: 7.4.13 TLS Versions: ssl, tls, tlsv1.0, tlsv1.1, tlsv1.2, tlsv1.3 all registered in PHP The database that houses our data - a SQLAnywhere 17 database - now requires TLS encryption. Our team adjusted our ODBC data sources to reflect this change and the data sources are all testing as successful. However, the PHP PDO connection that was working before the shift to TLS encryption is now failing with: " Invalid Connection: SQLSTATE[08S01] SQLConnect: -829 [SAP][ODBC Driver][SQL Anywhere]TLS handshake failure1 " I've been unable to find a solution to this. The ODBC connections that are being used by PDO are all testing successful with a "Certificate source" of "The operating system certificate store", and AFAIK PHP PDO_ODBC is fairly straightforward and is just using the ODBC data sources themselves to connect to the database, so I'm nonplussed on how this change broke our PHP connection when the ODBC connections are working as expected. Any help on this would be greatly appreciated. I was directed to this forum as I've been unable to find sufficient help or information elsewhere. Kind Regards, Ben David |
|
Do you use the min_tls_version option? See https://help.sap.com/docs/SAP_SQL_Anywhere/61ecb3d4d8be4baaa07cc4db0ddb5d0a/11fc8f6c559946c18c0fd6a9c5626dd4.html Do you have a correct CN? Use viewcert to check. If not, try skip_certificate_name_check. Are you negotiating a supported cipher suite? Use the following steps to determine this. Create a file called sectrace.ini and place it in the SAP Crypto library location. For Linux/Unix that’s the IQ-16_1/lib64 folder (for example, "/opt/sybase/IQ-16_1/lib64/sectrace.ini"). For Windows, it is bin64. The content of sectrace.ini looks like the following. The ini file below sets the trace Directory to /temp/CCLTrace. You’ll want to change this to some valid Linux/Unix or Windows path string. I think you have to make sure that the folder exists (like CCLTrace in my case). I also set the trace Level to 4 (the maximum verbosity). = = = = = = = = ; Trace configuration for CommonCryptoLib [trace] ; Directory where trace files are written ; You can use environment variables (%VARNAME%) in the specified path. ; %.BINDIR.% will be replaced by the directory of the CommonCryptoLib installation ; There will be one trace file for each process. ; Directory = %.BINDIR.%/CCLTrace Directory = /temp/CCLTrace ; Trace level ; 0: Deactivated ; 1: Errors ; 2: + Warnings ; 3: + Info ; 4: + Developer Trace Level = 4 ; Log Rotation ; Specify a file size (in bytes). If the trace file sec-*.trc becomes much bigger than this size, ; it's content is moved to a backup file named sec-*.<number>.trc and <number> is incremented ; The value 0 turns log rotation off. RotateFileSize = 10000000 ; Number of trace backup files ; If this configured number of backup files has been reached then <number> is set to 0 again ; so the oldest backup file will be overwritten. RotateFileNumber = 10 = = = = = = = = The files that are generated are named something like this. They will be located in whatever folder is specified for “Directory”. sec-iqsrv16.exe-27784.trc sec-iqsrv16.exe-29304.trc The server should be shut down to ensure the trace file is flushed to disk. There will be a lot of information recorded to this file, but look for the server and client hello (the cipher negotiation). Rename or delete the ini file when done. One last thing. Use dbping -d -m etc. to test a connection to the server. Include -d to connect to the database. If you don't include -d, then you are merely doing a "locate" and not using any TLS. Use -m to test ODBC connections (you'll need to include a DSN or "Driver=..."). Hope this helps. Thank you for your thoughtful response, JBSchueler! Before applying your suggestions, I dbpinged the DSN and received a successful response. Considering that the dbping response was successful, would that change any of the suggestions you've otherwise made? I ask because I do not have authorization access to the server myself and want to max out the troubleshooting client-side before I pursue server access authorization, which may be a thorny issue considering everything else appears to be working except for this PHP PDO ODBC connection.
(17 Oct '23, 17:34)
bdbp
Replies hidden
Did you use -d -m options?
(17 Oct '23, 18:06)
JBSchueler
You might also consider posting the output from dbdsn (the SQLA tool for managing ODBC Data Sources). It might be helpful to see your complete connection string. If there's any sensitive info, like a UID/PWD, just blot it out before posting.
(17 Oct '23, 18:14)
JBSchueler
Thank you, JBSchueler! I did not use the -d option the first time around and intend to do so tomorrow as well as posting the output from dbdsn. Kind Regards, Ben David
(18 Oct '23, 18:30)
bdbp
Replies hidden
1
As I mentioned before, when you don't use -d, all you are doing is seeing if a server is at the other end. Like doing a "ping". You know the system is there, but trying to actually do something with it is another thing. When you use -d, you are actually connecting to the database on the server, thus exercising all the TLS, hostname, port, user id, password, etc. options. And -m does it with ODBC (instead of DBLIB/ESQL).
(18 Oct '23, 18:40)
JBSchueler
Great, thank you for the clarification JBSchueler. Oddly enough it appears that our vendor is also having difficulty with a remote script in the form of an SQL statement and batch file working even though the ODBC connections themselves are working. They were logged in today working on that so I haven't had a chance to dbping and intend on it tomorrow barring any additional interruptions. Thanks again for all your help and knowledge on this!
(19 Oct '23, 18:23)
bdbp
Hi JBSchueler, the ping was successful with -m and -d: C:>dbping -d -m -c "DSN=Proto" SQL Anywhere Server Ping Utility Version 17.0.10.6089 Loaded ODBC driver ODBC32.DLL Connected to SQL Anywhere 17.0.11.7312 server "cannonBP1" and database "cannon". Ping database successful. The above is pinging the 32 bit connection by default whereas PDO appears to be using the 64 bit connection. I'll look up how to specifically ping the 64 bit connection on Monday to see if the results are different (both DSN's - 32 and 64 bit - have the same name). Thanks again for all your help, JBSchueler!
(20 Oct '23, 18:13)
bdbp
Replies hidden
That is easily done if you have both 32-bit and 64-bit client tools installed. If you really have a 32-bit and/or a 64-bit SYSTEM DSN then do this (my install path is \sa17).
In this example, I don't have a 32-bit DSN called SystemDSN. If I then correct this by using the 32-bit dbdsn tool to create a 32-bit SYSTEM DSN, then I see this.
(20 Oct '23, 19:02)
JBSchueler
Thank you, JBSchueler! Both pings were successful. I received the following error in the ODBC logs when attempting to connect with PHP PDO_ODBC: 14:57:01 Connected to the server, attempting to connect to a running database... 14:57:01 No system certificate files were found 14:57:01 Communication function SQLPresSyncPoint code 8 14:57:01 unknown error 0 14:57:01 Client disconnected 14:57:01 Disconnected from server Do you happen to know why I would receive this error when connecting through PHP PDO_ODBC even though the ODBC data sources themselves are set up with a Certificate Source of "The operating system certificate store"? My impression is that PDO_ODBC uses the data source, so I'm a bit nonplussed on why it's not using the certificate that the data source is using. In the "Use the certificate only if it has the following attributes" section in "Security" it has the "Certificate Company" field as "1234e.thiscompany.com" (changed to protect privacy). I searched the certificates in the system and there is no certificate with this information that I can see, it's not coming up in search as either the "Issued By" or "Issued To" fields and do see anything in the lists that resemble it. It doesn't look like the certificates themselves even have a "Certificate Company" field.
(23 Oct '23, 15:21)
bdbp
I can't speak for why these confusing identifiers where chosen. Here's a "translation". Certificate company = Organization (O) Certificate unit = Organizational Unit (OU) Certificate name = Common Name (CN) If the certificate is in the Windows certificate store, then you should be able to find it using the Windows Certificates tool (certmgr.msc). I usually look under Trusted Root Certification Authorities. When I find it and I want to learn how SQLA interprets the certificate, I export it to a file (All tasks/Export) and pick Base-64 encoded X.509 (.CER) format. Then I use the SQLA viewcert tool to study it. And it still would be useful to understand the complete picture by seeing the output from dbdsn (i.e., the DataSource).
(23 Oct '23, 16:32)
JBSchueler
Thank you for the translation, JBSchueler. I'm not finding organization, organization unit or common name as fields in the certificate store, and there doesn't appear to be a way to search for organization. I'm looking at the Trusted Root Certification Authorities. Nothing sticks out to me specifically as being related to this connection but there is much that is ambiguous. I was having some difficulty figuring out how to appropriately run dbdsn to obtain the output you're looking for. When I run "dbdsn -g mydsn" (which in the command line help says it will get details of a data source) it says "Cannot find user data source "mydsn"". After running it similarly to dbping to no avail (dbdsn -c "DSN:mydsn"), I decided to run "dbdsn -l" to see if there was some naming convention I was missing, the result was: SQL Anywhere User Data Sources: And then no data sources are listed. I checked the DSN to make sure and the driver is a SQL Anywhere 17 driver. It looks like either dbdsn is not seeing the data sources or I'm misunderstanding it's usage.
(23 Oct '23, 17:24)
bdbp
All tools normally display a usage message when requested. For example, dbdsn -? lists syntax/options. I believe you said you were using "system" data sources (you mentioned 32 vs 64-bit). Try dbdsn -ls (for "list system data sources"). Then dbdsn -gs <somedsn> for details on the named DSN. If dbdsn is the 64-bit tool, then 64-bit system dsns will be listed. If bin32\dbdsn, then 32-bit system dsns will be listed. But note that "user" data sources have no bitness.
(23 Oct '23, 17:33)
JBSchueler
Comment Text Removed
Is this exact text? >>> Host=company=1234e.myCOMPANY.com:2638; If so, that doesn't look right. The "company=" shouldn't be there. Does the server's identity certificate really have company=1234e.myCOMPANY.com for Organization? For example, it would more likely be something like "My Company Corporation". It is more than likely that the identity certificate has a Common Name field set to the hostname: 1234e.myCOMPANY.com In which case, you probably want ENC='TLS{trusted_certificates=;certificate_name=1234e.myCOMPANY.com}'; or its shorter form: ENC='TLS{trusted_certificates=;name=1234e.myCOMPANY.com}' I say this because it is usually the case that the HOST URL and the Common Name are the same or similar. It would help if you have the output from viewcert for the server's identity certificate (but I understand this may be an issue for you). I would try the skip_cert_name_check option if all else fails: ENC='TLS{trusted_certificates=*;skip_cert_name_check=yes}' The drawback to this option is that the client is doing a little less checking of the other end (the server) to ensure the Common Name is as expected (i.e., am I talking to whom I think I am talking to). But when you are not sure about the correctness of the CN, then skip_cert_name_check is your friend. How do you know that the signer certificate (the root certificate) is in your Windows certificate store? The certificate store must contain a certificate that was used to sign the database server's identity certificate file. If you knew who the "Issuer" was in the identity certificate, that would help find it. Can the database operator describe what certificate authority was used to sign their server's identity certificate? Someone like Verisign, Thawte, DigiCert, and so on?
(23 Oct '23, 18:45)
JBSchueler
Facepalm Sorry, JBScueller, that was my poor copying and pasting skills. The host line is indeed: Host=1234e.myCOMPANY.com:2638; The following line is correct from the output: company=1234e.myCOMPANY.com When you say "the server's identity certificate" is there a way for me to remotely check that in the database itself? I did login via SQL central and found a certificates section that has one certificate in it with the following information: Name myApplication ID 123456789123 Common Name 1234e.myCOMPANY.com Serial Number 123456789123459879123456789 Issued Sep 19, 2023 16:00:00 Expires Dec 19, 2023 15:59:59 Signature Algorithm RSA, SHA384 Key Type RSA Key Size 2048 Key Usage Digital Signature, Key Encipherment (information changed to protect privacy) There's nothing there that appears to state the organization name, unless that's the intention of the first field "Name". There were no certificates client-side that matched that expiration date (not that I'm sure there needs to be).
I guess I don't? I was using the fact that the ODBC connections were testing successful in the "ODBC Data Source Administrator" app as evidence that the certificates were present in the certificate store. Otherwise, I was presuming those tests would fail. Is that an incorrect assumption?
(24 Oct '23, 12:01)
bdbp
BTW I did add the "ENC='TLS{trusted_certificates=*;skip_cert_name_check=yes}'" line to the advanced settings and the connection still fails in the same manner.
(24 Oct '23, 12:15)
bdbp
Huh, the comments start to get unreadable... FWIW, when you test the connections via the ODBC admin (or connect successfully via DBISQL), is this on the same client machine that runs your PHP code, too? If so, as the same user? (I'm ust asking to clarify whether those connections make use of the same system certificate store or not, as the store contents can obviously vary between different machines, and may vary between user on the same machine...) I would also try to export the desired certificate (including its complete chain!) from the certificate store as a file (as Jack has mentioned in his second-last comment, say as file C:\MyRootCert.crt) and would try provide it within your connection instead of relying on the store, such as ...ENC=TLS(trusted_certificate=C:\MyRootCert.crt;SKIP_CERTIFICATE_NAME_CHECK=ON)... Note, the client needs to provide a certificate (either via the system certificate store or explicitly) that verifies the "signer" of the server's certificate (so a CA), not the issuer of the certificate.
(24 Oct '23, 12:47)
Volker Barth
Just another question (unless I have not noticed your according answer): What does the LOG contain for your successful connections via the ODBC admin? Does it tell which certificate from the system certificate store has been used? Or what else does appear there in contrast to the statement "No system certificate files were found" from the failing PHP PDO requests?
(24 Oct '23, 12:50)
Volker Barth
Thank you, Volker Barth! The ODBC admin is on the same machine as PHP; however... The user for executing from admin is my user account: OSUSER=bd; whereas PHP appears to be using another user: OSUSER=IUSR. Regarding the certificate, the suggestion sounds great, I just need to find the certificate first which is giving me some difficulty. There's no certificate that's obviously related to this connection that I can see from the information given about them in the Windows certificate manager.
(24 Oct '23, 14:22)
bdbp
1
Thank you, Volker Barth; I don't see any indications about the certificate in the logs. The log for a failure contains the following lines around the connect: 14:57:01 Connected to the server, attempting to connect to a running database... 14:57:01 No system certificate files were found 14:57:01 Communication function SQLPresSyncPoint code 8 14:57:01 unknown error 0 14:57:01 Client disconnected The log for a success: 15:08:32 Connected to the server, attempting to connect to a running database... 15:08:32 [813716] Connected to database successfully 15:08:32 [813716] Client disconnected 15:08:32 [813716] Disconnected from server
(24 Oct '23, 14:26)
bdbp
I can reproduce this error by referencing the Windows certificate store when I know the trusted root certificate is not present. By the way, can you shorten the line in the comment above that has the really long line beginning with ENP=03a17731bca92...? It would be nice to fix the appearance of these replies.
(24 Oct '23, 16:49)
JBSchueler
Thank you, JBSChueler. I deleted the comment as there was no option to edit it. I did save it if we want the information there again. Is there a place where I can download viewcert so I can check the certificates on the client machine and see if I can find the certificate with the correct common name? We download the SQLA17 client and it doesn't appear to include it (I just re-installed to make sure).
(24 Oct '23, 18:21)
bdbp
I can't tell for the SQL Anywhere client download, but the SQL Anywhere full install does include the viewcert tool in the bin32 and/or bin64 subdir, so your dbadmin should be able to supply it to you. He should also be able to provide you with the desired certificate: In my understanding, the folks setting up encrypted communication on the database server should also supply trusted clients (and trusted client app developers) with the required certificate information and distribute the according server certificate files, unless those are globally signed by a CA that your system trusts itself...
(25 Oct '23, 02:37)
Volker Barth
Thank you, Volker! I'm working with our vendor on getting the certificate information and will update here once we receive it. This may not be until next week depending on circumstance. Thank you both for your help so far!
(26 Oct '23, 15:37)
bdbp
I was finally able to get our vendor to login and run viewcert on the server. It looks like there are four certificates on the server, one certificate is a "Go Daddy Class 2 Certification Authority" that matches the thumbprint and expiration date of one of the certificates on the server, the other appears similar to the certificate on the server but differs in both thumbprint and expiration date, that one is a "Go Daddy Root Certificate Authority" certificate. The remaining two of the four certificates were not found in either the user or computer certificates on the client, and I imagine they're not relevant if they are not present and the ODBC connections work(?). The two certificates that are present are present in both the "User" certificates and the "Local Computer" certificates store; my understanding is the "Local Computer" certificates should be available to all, so I'm wondering why the ODBC connections work when I log in as myself, but they do not work when PHP attempts to run a script with IUSR. Regarding common names, of the two certificates, the "Go Daddy Class 2 Certification Authority" only has an organizational unit and it's the same in the Subject as it is in the Issuer, so it doesn't appear to have a common name. The "Go Daddy Root Certificate Authority - G2" does have a common name of "Go Daddy Root Certificate Authority - G2" and the organizational unit is "Go Daddy Class 2 Certification Authority". As the apparent root certificate has a different thumbprint and expiration date on the server than it does on the client, is this the most likely cause of the issue; if so, why would the ODBC connections work, do they not require access to this root certificate?
(21 Nov '23, 15:33)
bdbp
Thanks again, Volker, I was finally able to get ahold of the vendor to run viewcert. There are two certs that also appear in the user and computer store, one of the two, the "root" cert, appears to have a different expiration date and thumbprint on the server than it does on the client. I'm not 100% sure this is the specific certificate that needs to be used, but these are the only go daddy certificates in the certificate store so it seems like they should match in issuer date and thumbprint.
(21 Nov '23, 15:35)
bdbp
More comments hidden
|
I don't have a clue (and probably folks with more clues would need more information about the used connection parameters), but I'd suggest
See answer.
Thank you, Volker! I'm going to work on the logs as a first step, I wasn't aware that the ODBC data source itself had logging capabilities. Thank you!
I may proceed with the connection string if need be, I do find it strange that I would need to reference the driver if the data source already utilizes the driver and this wasn't an issue before the TLS changes, but I will certainly give it a shot and report back.
And if no solutions are forthcoming I'll definitely be checking out that PHP driver, thank you!
Just for clarification, it's not the ODBC data source itself that can log (FWIW, the ODBC driver manager supports generic tracing but that won't help here), it is the SQL Anywhere ODBC driver that supports the LOG connection parameter, whether it is specified within a DSN or as connection string...
Thank you for the clarification, Volker, it's an important point that I will keep in mind moving forward! I didn't realize that was driver based but after having seen a few non-Sybase connections I see what you mean.
Hi Volker, when I test the ODBC connection the log is created as expected. When I attempt to use the PHP PDO connection the page hangs and no log is created. :/
Bleh, shouldn't have put the log directory as root. After changing log directories I received the following log after using PHP PDO (some information changed to protect privacy and line breaks added for readibility):
Fri Oct 20 2023 14:57:01
14:57:01 Attempting to connect using:
UID=ecrs;PWD=;DBN=cannon;ServerName=cannonbp1;
CON=SQL_DBC_1f86707cae0;INT=NO;ENP=;
ENC='TLS{trusted_certificates=*;company=2570c.mycompany.com}';
LOG=C:\Logs\ODBC\ODBC_64_LOG.txt;Host=2570c.mycompany.com:2638
14:57:01 Attempting to connect to a running server...
14:57:01 Trying to start TCPIP link ...
14:57:01 TCP using Winsock version 2.2
14:57:01 My IP address is 10.10.10.10
14:57:01 My IP address is ::1
14:57:01 My IP address is 127.0.0.1
14:57:01 TCPIP link started successfully
14:57:01 Attempting TCPIP connection (address 10.10.10.10:2638 found in sasrv.ini cache)
14:57:01 Looking for server with name cannonbp1
14:57:01 Trying to find server at cached address 10.10.10.10:2638 without broadcasting
14:57:01 Found database server cannonbp1 on TCPIP link
14:57:01 Connected using client address 10.10.10.10:49923
14:57:01 Connected to server over TCPIP
14:57:01 Connected to SQL Anywhere Server version 17.0.11.7312
14:57:01 Application information:
14:57:01 IP=10.215.76.105;HOST=CANNON;OSUSER=IUSR;OS='Windows 2016 Build 14393 ';EXE='C:\Program Files\iis express\PHP\v7.4\php-cgi.exe';PID=0x1e20;THREAD=0x1e24;VERSION=17.0.10.6089;API=ODBC;TIMEZONEADJUSTMENT=-420
14:57:01 Connected to the server, attempting to connect to a running database...
14:57:01 No system certificate files were found
14:57:01 Communication function SQLPresSyncPoint code 8
14:57:01 unknown error 0
14:57:01 Client disconnected
14:57:01 Disconnected from server
Looks like no certificates were found. This is helpful, thank you Volker, I guess my Monday is going to be creating a certificate with for company name "2570c.mycompany.com" if possible.
On the other hand, if the data source already has an associated certificate and is testing as successful, why would an additional certificate need to be created for a PDO connection using that data source? Am I off in my assessment above?