API and JDBC

When I go to configure a connection to API in IPA, I cannot figure out which parameters to include on my JDBC connection URL.
One that works to a different sql server database is jdbc:sqlserver://172.x.y.z:1433;DatabaseName=testdb but applying the same logic to the api database doesn't seem to work. We've even tried jdbc:sqlserver://172.x.y.z;DatabaseName=APIHealthcare_Live;InstanceName=API_Live without success. I know the database name and instance name are correct. Any pointers?

Each database types has to have their own JDBC driver installed- have this been done?

Yes, connecting to a different sql server database works. This API Healthcare sql server database is what I cannot figure out. Does it have something to do with encryption?

About the only difference (per my DBA) is that this database now enforces encryption

Adding encryption to your connection string requires adding "encrypt=true" and possibly, depending on how you are verifying the certificate, "trustServerCertificate=true".

As I'm sure you are aware, encryption of this sort relies on SSL to actually do the encryption. If you do trustServerCertificate=true you are essentially saying you aren't going to verify that that certificate (and thus you don't care if the server you are connecting to is really who it says it is).

Otherwise you'd need to ensure the public certificate for that server was stored in your truststore for the JVM that you are using to do all the heavy lifting there. Otherwise the connection will be rejected when the server's certificate can't be verified as genuine.

If that doesn't solve your problem, you might want to get a lightweight JDBC based SQL browser such as SQuirreL and see if you can't get things working via it first (to cut the middlemen that could be obscuring any issues out of the loop) and then jump back on whatever you are trying to set up once you are sure you've got a good working link to the server.

thanks for that info. I'm now able to kindof get a connection in Squirrel. adding the encrypt and trust parameters. The connection closes almost immediately though..
in the IPADesigner tool I get "Unable to load table types." error message when trying to "build" on a sql query node. Probably related to the connection closing?

I would agree, you may need to tag API's support to see if they have a suggestion.

I'm assuming, because I'm not using API, that the database is hosted by them rather than on site with you?

If so, you may need to work with them to get the their certificate installed into your truststore. MS does have an article about it on MSDN - https://msdn.microsoft.co...9943(v=sql.110).aspx but you still have to get the key from them.

If not, then the problem might also be that the certificate for the server you are connecting to doesn't match the fully qualified name that your machine is using for it (i.e. if you have an machine named moose internally but it also goes by DB1, and the certificate is signed as DB1, not moose) then the connection will be rejected due to that.

Unfortunately it's been a long time since I've had to mess with JDBC setups, they tend to be fire and forget and my last one was at a different company back when Sun was still around. Outside of those hints and checking the logs that SQuirreL generates to see if they are leaving any clue/error message for the dropped connection, I'm out of general suggestions. Sorry.

API support is no help. Already tried that. Since they don't use JDBC, they tell me that they won't support it.
We host our own database in-house... Let me ask a few other techs around here, and I'll see if there is a cert involved.

SSL requires a cert to work.

So it's not so much a question of if one was used as it is what was it set up with and ensuring any clients connecting are using compatible settings.

For the most part, if you are forcing trustServerCertificate=true in the connection string, the only thing I can think of is the server name not matching the name the cert was signed with.

This actually happens a lot in my experience, folk will generate certificates that have wildcards (i.e. *.example.com) which work for the web but don't work for this purpose, or they'll run makecert to create a self signed cert without specifying the name, or with my previous example giving the cert 'one' of the names that the machine goes by but not actually the one that gets used when someone is connecting internally.

You might want to see if you can't double check the name on the cert and there is one last connection string property you can use "hostNameInCertificate=whatever" with whatever obviously being replaced by whatever name they signed the certificate with. That forces the certificate to be verified against that name instead of whatever name the client sees the server as using. If that's not it, I'm really out of ideas.

Well that or the server itself not really being set up correctly to do SSL, but I'd have to assume if API did this they had some sort of smoke test to ensure that wasn't the case. And it shouldn't matter if they support JDBC or not in that event, as the only method I'm aware of to encrypt a SQL connection is via SSL.