SQL connection settings

[Michael A. Lowry]

The documentation on SQL Connection Objects does not provide detailed information about the Connection String Extension and Connection Properties Extension sections of the Advanced Settings of the SQL connection objects. This post is intended to remedy this situation and provide the missing details.

SQL Connection Object — Advanced Settings

The Advanced Settings of an SQL connection object are divided into two sections: Connection String Extension and Connection Properties Extension.

Base Connection String

Before connecting to a database, the SQL Agent assembles the base connection string using the values entered in the Connection section of the Database page of the connection object.

Vendor-specific

For most database types, the string is assembled from the values entered into the Server:Port and Database Name fields.

Generic JDBC

For Generic JDBC connection objects, these details must be provided in the form of a complete connection string in the Connection String field.

There are several JDBC connection string formats, depending on the database type.

Connection String Extension

Any values added in the Connection String Extension section of the Advanced Settings page will be appended to the end of the connection string before the agent establishes a connection to the database. A semicolon (;) is automatically added between connection string parameters.

Connection string parameters vary by database vendor.

Connection Properties Extension

Values added to the Connection Properties Extension section of the Advanced Settings page are used to by the agent to set Java properties prior to loading the JDBC driver. (See JDBC driver technical details, below.) These properties can be used to change the behavior of the JDBC driver.

JDBC properties vary by database vendor and version.

For example, adding sslConnection:true in Connection Properties Extension is equivalent to setting -DsslConnection=true on the command line when loading the JDBC driver.

JDBC driver technical details

• The values in the Connection section are used to build the base connection string. Then, any values in the Connection String Extension section are appended to the end of the URL, with each parameter separated from the one before by a semicolon (;). The resultant String is the DB connection URL
• The values in Connection Properties Extension are used to define a Properties object.
• These two objects are passed to DriverManager.getConnection when establishing the connection to the database.

Acknowledgements

Most of the information above was divined by trial-and error. Broadcom Support helped provide some missing details. @Markus Embacher validated the description.

---

Original Message

I'm trying to understand how the values in the Connection String Extension and Connection Properties Extension sections of the Advanced Settings of the SQL connection object are used by the SQL Agent. The documentation on SQL Connection Objects is not entirely clear, and the case I've had open with Broadcom Support has gone back and forth for more than a year without a clear and satisfactory answer.

For this reason, I'm going to write how I think it works, in the hopes that someone more knowledgeable than I can confirm my assumptions or correct me where I am wrong.

Thanks in advance for any feedback. Ping @Markus Embacher

​

[Michael A. Lowry]

Presumably, any values entered in Connection Properties Extension have no effect if a native DB driver is used, e.g., the native Oracle client.

[Michael A. Lowry]

I opened a case about this with Broadcom Support at the end of March 2022. In more than nine months, I have still not received a satisfactory answer. Broadcom Support have stopped responding to requests for updates, and are now pushing me to agree to close the case.

I hoped I might be able to get more help here in the discussion forum, but after almost a month and a half with no feedback, I'm beginning to lose hope of that too.

It's unfortunate that no one at Broadcom thinks this topic is worth spending time on.

One last try before I give up on getting help from Broadcom:
@Gabi Oberreiter, might this topic be interesting to you as you consider updating the documentation on SQL Connection Objects? After all, the whole reason for the support case and this thread was a lack of adequate documentation, leaving users to resort to guesswork and trial-and-error. If you can get confirmation that what I have described above is indeed how it works, then you would be welcome to use my text and screenshots in an updated version of the documentation.
​

[Michael A. Lowry]

Alright, I'm giving up.

It's unfortunate that no one at Broadcom considers this topic worthwhile.

[Markus Embacher]

Hi Michael,
after discussing that topic with our expert from development let me try to answer your question:
In general there are two ways of providing parameters in JDBC:
1) DriverManager.getConnection(url, props);
2) directly in the JDBC URL: for example "jdbc:postgresql://localhost/test?user=fred&password=secret&ssl=true";

Unfortunately every JDBC driver has a different syntax when it comes to the JDBC URL. In other words, when using the "Connection Properties Extensions" then it should work in any case, because it will be passed to getConnection using setProperty, like in the example here:
https://jdbc.postgresql.org/documentation/use/#connection-parameters

When using the "Connection String Extension" we are currently using semicolon to separate the parameters in the JDBC URL. It could be that the JDBC driver you are using does require a different character for separation. When using the properties then the separation character is out of the discussion.

When using a Generic JDBC driver then also the Connection String Extension will be appended to the connection string.

So your assumptions above are correct.

Regards, Markus

[Michael A. Lowry]

Thanks, @Markus Embacher!

I have updated the wording of the initial discussion post to convey that the described behavior has been confirmed.

​

[Michael A. Lowry]

Here is a list of example connections strings for the DB types supported by the AE SQL Agent. The links go to the respective JDBC documentation of each vendor.

Database	JDBC URL example	Separator for properties
EXASOL	jdbc:exa:192.168.6.11..14:8563;schema=SYS	;
IBM DB2	jdbc:db2://host-name:50001/BLUDB:sslConnection=true;	;
Informix	jdbc:informix:Server=10.0.1.2;Port=50000;User=admin;Password=admin;Database=test;	;
MS SQL Server	jdbc:sqlserver://mssql.db.server\\mssql_instance;databaseName=my_database	;
MySQL	jdbc:mysql://(host=myhost1,port=1111),(host=myhost2,port=2222)/db?key1=value1&key2=value2&key3=value3	&
Oracle	jdbc:oracle:thin:@myoracle.db.server:1521:my_sid	&
PostgreSQL	jdbc:postgresql://postgresql.db.server:5430/my_database?ssl=true&loglevel=2	&
SAP HANA	jdbc:sap://localhost:30015/?user=myUser&password=XYZ&latency=0&communicationtimeout=0	&
Sybase	jdbc:mysql://mysql.db.server:3306/my_database?useSSL=false&serverTimezone=UTC	&

[Michael A. Lowry]

@Markus Embacher wrote:

When using the "Connection String Extension" we are currently using semicolon to separate the parameters in the JDBC URL. It could be that the JDBC driver you are using does require a different character for separation. When using the properties then the separation character is out of the discussion.

Does the agent always use a semicolon to separate parameters defined in Connection String Extension, regardless of DB type? If so, this probably means that this function will not work for DB types that expect a different separator.

​

[Markus Embacher]

Yes, currently a semicolon is used to separate parameters defined in Connection String Extension, so this would not work for Sybase for example. But you can always use Connection Properties Extension to provide the parameters instead.

[Michael A. Lowry]

@Markus Embacher wrote:

Yes, currently a semicolon is used to separate parameters defined in Connection String Extension, so this would not work for Sybase for example. But you can always use Connection Properties Extension to provide the parameters instead.

This would work only in cases where the JDBC driver supports setting the parameter via a Java property. My understanding is that there is actually very little overlap between things that can be set via connection string parameters and things that can be set via Java properties.

To add connection string parameters using a separator other than ampersand (&), a possible work-around would be to append the parameters to the end of the Database Name field, with a question mark (?) between the database name and the parameter(s).
​
E.g., if the database name is my_database, and you want to add the connection string parameters ssl=true and loglevel=2, you could change the Datbase Name from:

my_database

to:

 my_database?ssl=true&loglevel=2

.

[Markus Embacher]

My comments refer to JDBC properties/parameters only, not Java properties.

[Michael A. Lowry]

@Markus Embacher wrote:

My comments refer to JDBC properties/parameters only, not Java properties.

I've been using these two terms interchangeably. As I wrote in my original post,

…[A]dding sslConnection:true in Connection Properties Extension is equivalent to setting -DsslConnection=true on the command line when loading the JDBC driver​​.

The distinction I was trying to make yesterday was between:

1. Connection string parameters that must be provided in the connection string itself.
2. Java/JDBC properties, such as those one might set with the -D command line option.

To the best of my understanding, the latter can be specified in Connection Properties Extension, but the former cannot.

Please correct me if I'm wrong about this.

[Keld Mollnitz]

Allow me to add the following information on how the Connection test is done, because this is not described in the Documentation.

1. If you click the "Test" button inside the Connection object, the test is done by the DB Service agent (not the SQL agent).
2. If you add the Connection object to an SQL job and then click the "Connection Test" button inside the SQL job, the test is done by the SQL agent (not the DB Service agent).

This may lead to unexpected results if your your SQL agent and DB service are not installed on the same host.