dbptk desktop Cannot connect to Network SQL Server Named instance, Certificate error

[dankocolNPS]

Description: Getting connection error see below. Also if I disable encryption, I get an error that it does not support integrated credentials. In my environment it needs to support encryption and integrated credentials to MS SQL Server. and Must support TLS 1.2.

Context:

• DBPTK Desktop: Windows 10 20H2, dbptk-desktop-2.6.0

Steps required to reproduce the bug:

1. Open DBPTK
2. Enter connection information
3. Test connection

Attach any related screenshots below.

Attach any relevant logs below.

Find DBPTK Desktop logs at Help -> Logs menu. [2022-06-07 08:10:30.648] [info] Java Path is: C:\Users\USERNAME\AppData\Local\Programs\dbptk-desktop\resources\app.asar.unpacked/resources/jre/windows/x64/bin/java.exe [2022-06-07 08:10:30.655] [info] DBPTK Desktop tmp dir at C:\USERNAME\DKocol\AppData\Local\Temp\1\dbptkDesktopTmpDir-o6wFIF [2022-06-07 08:10:30.658] [info] Port file at C:\Users\USERNAME\AppData\Local\Temp\1\port-01hNjA [2022-06-07 08:10:30.661] [info] JVM log at C:\Users\USERNAME\AppData\Local\Temp\1\jvm-l6Jwul.log [2022-06-07 08:10:30.666] [info] Memory is not set [2022-06-07 08:10:30.668] [info] Delegating responsibility to the JVM [2022-06-07 08:10:30.715] [info] Server PID: 29660 [2022-06-07 08:10:30.717] [info] Wait until C:\Users\USERNAME\AppData\Local\Temp\1\port-01hNjA exists... [2022-06-07 08:10:31.123] [info] Loading [2022-06-07 08:11:33.525] [info] Server at http://localhost:62789 [2022-06-07 08:11:34.148] [info] Server started! [2022-06-07 08:11:34.207] [info] Checking for update [2022-06-07 08:11:35.593] [info] main loaded [2022-06-07 08:11:36.230] [info] Update for version 2.6.0 is not available (latest version: 2.6.0, downgrade is disallowed).

[luis100]

You seem to be using a self-signed, invalid or expired certificate for your server. If it is a self-signed, you may be able to work around it using a truststore, but for that you'll need to use the DBPTK developer command-line tool.

Look for the MySQL use case in the examples, when you get to the same error, try to add your server certificate to a truststore and running the Java command-line with it, see the following blog post with an example: https://medium.com/@vishwanath.leo/how-to-pass-your-custom-truststore-as-argument-to-jvm-when-running-a-jar-file-f9e05adc5094

[dankocolNPS]

Thanks for the quick reply, I will look to resolve the certificate issue, However if I disable encryption. I still get.

[dankocolNPS]

After connection issues resolved, now we are running into time out issues. Can you please provide additional options on the connection string for specifying time out more than 30 seconds.

here is relevant log:

2022-06-07 14:37:06.196 INFO 29660 --- [o-auto-1-exec-6] c.d.c.server.controller.SIARDController : Metadata Export Options - meta-client-machine -> [removed] 2022-06-07 14:37:06.241 INFO 29660 --- [o-auto-1-exec-6] c.d.m.s.out.output.SIARDExportDefault : Exporting SIARD version 2.1 2022-06-07 14:37:36.643 ERROR 29660 --- [o-auto-1-exec-3] c.d.c.c.common.utils.AsyncCallbackUtils : [0:0:0:0:0:0:0:1] AsyncCallback error - RESTException:

[luis100]

We currently have no way to inject custom parameters into the connection string of the SQL Server module or to set the timeout for that driver.

https://github.com/keeps/dbptk-developer/blob/master/dbptk-modules/dbptk-module-sql-server/src/main/java/com/databasepreservation/modules/sqlserver/in/SQLServerJDBCImportModule.java#L176

You would need to use the generic JDBC module, which will have less quality as we have made several improvement in the SQL Server module, or you will need a custom development to allow the custom timeout feature (you can do it yourself and submit a PR or contact sales@keep.pt to request a custom development service).

[dankocolNPS]

Luis-

Thanks again for the quick reply, However after further investigation I believe the issue is not really a "time out", but may be in the "Create SIARD" module. While monitoring connections to the SQL instance I can see a successful connection when generating the schema diagram and list of tables, however a connection is never established when trying to generate the SIARD file. I believe it is expecting port 1433, instead of using the port specified on the connection definition. In our case we have multiple instances on same server so a different port is used for hosting the multiple instances. Is this something that could be addressed as a defect?

@.***

From: Luis Faria @.> Sent: Wednesday, June 8, 2022 4:09 AM To: keeps/dbptk-ui @.> Cc: Kocol, Dan J @.>; Author @.> Subject: [EXTERNAL] Re: [keeps/dbptk-ui] dbptk desktop Cannot connect to Network SQL Server Named instance, Certificate error (Issue #318)

This email has been received from outside of DOI - Use caution before clicking on links, opening attachments, or responding.

We currently have no way to inject custom parameters into the connection string of the SQL Server module or to set the timeout for that driver.

https://github.com/keeps/dbptk-developer/blob/master/dbptk-modules/dbptk-module-sql-server/src/main/java/com/databasepreservation/modules/sqlserver/in/SQLServerJDBCImportModule.java#L176https://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fkeeps%2Fdbptk-developer%2Fblob%2Fmaster%2Fdbptk-modules%2Fdbptk-module-sql-server%2Fsrc%2Fmain%2Fjava%2Fcom%2Fdatabasepreservation%2Fmodules%2Fsqlserver%2Fin%2FSQLServerJDBCImportModule.java%23L176&data=05%7C01%7Cdan_kocol%40contractor.nps.gov%7Cf6220ee67af14486181508da4936f51a%7C0693b5ba4b184d7b9341f32f400a5494%7C0%7C0%7C637902797659824465%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=AdHGO%2FYSXlB4YyZA6u84g%2FFAdMtouPsDXAeOSo5A%2BF4%3D&reserved=0

You would need to use the generic JDBC module, which will have less quality as we have made several improvement in the SQL Server module, or you will need a custom development to allow the custom timeout feature (you can do it yourself and submit a PR or contact @.**@.> to request a custom development service).

- Reply to this email directly, view it on GitHubhttps://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fkeeps%2Fdbptk-ui%2Fissues%2F318%23issuecomment-1149723575&data=05%7C01%7Cdan_kocol%40contractor.nps.gov%7Cf6220ee67af14486181508da4936f51a%7C0693b5ba4b184d7b9341f32f400a5494%7C0%7C0%7C637902797659824465%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=s9ISSO4p%2FqKO8k8aZimIVoN7BY84lmGMk8ZxnU2uznk%3D&reserved=0, or unsubscribehttps://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAJTBCNFKLBFCIZK5F6EGLVDVOBWM3ANCNFSM5YDL7CAQ&data=05%7C01%7Cdan_kocol%40contractor.nps.gov%7Cf6220ee67af14486181508da4936f51a%7C0693b5ba4b184d7b9341f32f400a5494%7C0%7C0%7C637902797659824465%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=AQVM36x3g%2FY4AxQLSI69jU9pMxSER%2FrG4UdoV2P6im0%3D&reserved=0. You are receiving this because you authored the thread.Message ID: @.**@.>>

[luis100]

DBPTK uses a stream model where the output module (in your case SIARD) pulls events from the input model (in your case SQLServer), which makes it very performant and memory efficient. So, in case there are issues, the exception may be thrown in the SIARD module that pulled the event.

Anyway, the exception seems like a timeout, but maybe not a login timeout but a query timeout, where SQLServer takes too much time to respond to the query you have set up (a normal select on a table or a view, or a custom query if you used the custom view option). If you send us more log, or you inspect it yourself, you may be able to check in what part of the process is the timeout occurring and, if it is in executing a query for a table, view or custom view, for which is this happening. Then you may be able to try exporting without that table, view or custom view, to isolate the issue.

The query timeout is also configurable in the SQLServer driver, but, again, we currently have no option for it.

[dankocolNPS]

Luis-

Thanks for the reply, I do believe it is a connection issue during the Create SAIRD file, please note that the port specified in the connection time out error is not the port specified in the connection string.

@.***

This can be replicated on the test connection page, If a inproper port is specifed (e.g. 1435) and a test connection is run, you recive a time out error and the improper port is returened in the error msg. @.***

I have alos furter investigated and confirmed that if the connection succeeds in the test page, I can generate the schema digram and table details, I also see a connection being made to SQL Server when viewing Database activity while generating the schema diagram and details. However If I monitor Database activity during the create SAIRD file operation, no connection is ever made to the SQL server.

Thanks for the assitance.

From: Luis Faria @.> Sent: Thursday, June 9, 2022 2:26 AM To: keeps/dbptk-ui @.> Cc: Kocol, Dan J @.>; Author @.> Subject: [EXTERNAL] Re: [keeps/dbptk-ui] dbptk desktop Cannot connect to Network SQL Server Named instance, Certificate error (Issue #318)

This email has been received from outside of DOI - Use caution before clicking on links, opening attachments, or responding.

DBPTK uses a stream model where the output module (in your case SIARD) pulls events from the input model (in your case SQLServer), which makes it very performant and memory efficient. So, in case there are issues, the exception may be thrown in the SIARD module that pulled the event.

Anyway, the exception seems like a timeout, but maybe not a login timeout but a query timeout, where SQLServer takes too much time to respond to the query you have set up (a normal select on a table or a view, or a custom query if you used the custom view option). If you send us more log, or you inspect it yourself, you may be able to check in what part of the process is the timeout occurring and, if it is in executing a query for a table, view or custom view, for which is this happening. Then you may be able to try exporting without that table, view or custom view, to isolate the issue.

The query timeout is also configurable in the SQLServer driver, but, again, we currently have no option for it.

- Reply to this email directly, view it on GitHubhttps://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fkeeps%2Fdbptk-ui%2Fissues%2F318%23issuecomment-1150825641&data=05%7C01%7Cdan_kocol%40contractor.nps.gov%7C6d1a78ccd0ff44f219e908da49f1b82f%7C0693b5ba4b184d7b9341f32f400a5494%7C0%7C0%7C637903599773006669%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=u8s7Bp94v7D%2Bv%2BCik36rxsc%2Fr6juq8fxhexniFZ1oFs%3D&reserved=0, or unsubscribehttps://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAJTBCNFKLHLF6FNYCWD2KJDVOGTBZANCNFSM5YDL7CAQ&data=05%7C01%7Cdan_kocol%40contractor.nps.gov%7C6d1a78ccd0ff44f219e908da49f1b82f%7C0693b5ba4b184d7b9341f32f400a5494%7C0%7C0%7C637903599773006669%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=wU2YeIvlWrpE510iafoYe1XTDkyFWiygFJfVoP0EsB4%3D&reserved=0. You are receiving this because you authored the thread.Message ID: @.**@.>>

[luis100]

The SIARD export module does not use any network connection, it writes files directly to disk. For the results I got from the Web, it seems SQL Server uses several ports, including the one giving the error:

By default, the typical ports used by SQL Server and associated database engine services are: TCP 1433, 4022, 135, 1434, UDP 1434. The table below explains these ports in greater detail.

https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-ver16#BKMK_ssde

It actually states that 1433 is the "Default instance running over TCP". You state you use a different port, maybe some part of the process is not using the port you have defined but using the default port instead?

This may be an interface issue, I highly recommend you try with the command-line option to isolate the issue.

[dankocolNPS]

Luis-

Thank you for sending the link, we are very familiar with this configuration and using the second scenario listed which is:

Named instances with default port The TCP port is a dynamic port determined at the time the Database Engine starts. See the discussion below in the section Dynamic Portshttps://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-ver16#BKMK_dynamic_ports. UDP port 1434 might be required for the SQL Server Browser Service when you're using named instances.

Our TCP port is a dynamic port with The SQL browser service listing on port 1434, My current understanding is that the dptak desktop client is not using the UDP protocol for port discovery and only using the default port (1433) when generating the SRAID file, and therefore it cannot create a Database connection at time of creating the SRAID file.

Dan

From: Luis Faria @.> Sent: Thursday, June 9, 2022 8:59 AM To: keeps/dbptk-ui @.> Cc: Kocol, Dan J @.>; Author @.> Subject: [EXTERNAL] Re: [keeps/dbptk-ui] dbptk desktop Cannot connect to Network SQL Server Named instance, Certificate error (Issue #318)

This email has been received from outside of DOI - Use caution before clicking on links, opening attachments, or responding.

The SIARD export module does not use any network connection, it writes files directly to disk. For the results I got from the Web, it seems SQL Server uses several ports, including the one giving the error:

By default, the typical ports used by SQL Server and associated database engine services are: TCP 1433, 4022, 135, 1434, UDP 1434. The table below explains these ports in greater detail.

https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-ver16#BKMK_ssdehttps://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fsql-server%2Finstall%2Fconfigure-the-windows-firewall-to-allow-sql-server-access%3Fview%3Dsql-server-ver16%23BKMK_ssde&data=05%7C01%7Cdan_kocol%40contractor.nps.gov%7C1295b131339047bc4f2208da4a289ea2%7C0693b5ba4b184d7b9341f32f400a5494%7C0%7C0%7C637903835565359766%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=M6zGHoNTJmZ4NDOLcPUNh0Nc1lSV3DxwK57%2B%2FqwyUoo%3D&reserved=0

It actually states that 1433 is the "Default instance running over TCP". You state you use a different port, maybe some part of the process is not using the port you have defined but using the default port instead?

This may be an interface issue, I highly recommend you try with the command-line option to isolate the issue.

- Reply to this email directly, view it on GitHubhttps://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fkeeps%2Fdbptk-ui%2Fissues%2F318%23issuecomment-1151232220&data=05%7C01%7Cdan_kocol%40contractor.nps.gov%7C1295b131339047bc4f2208da4a289ea2%7C0693b5ba4b184d7b9341f32f400a5494%7C0%7C0%7C637903835565515929%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=7s8a7z9nu4Hs24ZZVTYl6hOCi0rAZM%2FAZRga6jGJNKU%3D&reserved=0, or unsubscribehttps://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAJTBCNH7T2NNYFXI27TEAQ3VOIBD7ANCNFSM5YDL7CAQ&data=05%7C01%7Cdan_kocol%40contractor.nps.gov%7C1295b131339047bc4f2208da4a289ea2%7C0693b5ba4b184d7b9341f32f400a5494%7C0%7C0%7C637903835565515929%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=S4ohPCBXS%2F4OhIpV%2Ff1cSAV2NSHJQB1Z6iyq36AOCuY%3D&reserved=0. You are receiving this because you authored the thread.Message ID: @.**@.>>

[dankocolNPS]

Luis- I have confirmed if I host my enterprise instance on port 1433 the tool works as expected, however if any custom or dynamic port is used, dbptk desktop does not work, and fails in the create sraid step.

[luis100]

Thanks Dan, would you be able to use the DBPTK command-line tool to check if this is an issue with the Desktop UI or the import module?

https://github.com/keeps/dbptk-developer#how-to-use