How do I set the authorization type of an SQL connection?

Answer

This can be done using a custom selection dialog that will allow the user to choose the desired authorization type. Using this dialog, we will set the value of the properties Uid and Pwd. This How To is presented as a step by step operation:

• go to the SQL Script page and create a "Custom SQL Connection"
• the "Connection string" will contain the following values:

Driver=[\{][DBDRIVER_PROP][\}];Server=[DBSERVER_PROP];Port=[DBPORT_PROP];Uid=[DBUSER_PROP];Pwd=[DBPASS_PROP];Database=[DBNAME_PROP];

• from this string you will remove the following elements:

Uid=[DBUSER_PROP];Pwd=[DBPASS_PROP];

• the new string should look like this:

[\{][DBDRIVER_PROP][\}];Server=[DBSERVER_PROP];Port=[DBPORT_PROP];Database=[DBNAME_PROP];

• now to this string you will add a public property that you define, called lets say:

"SQL_AUTHORIZATION"

• the new string should look something like this:

Driver=[\{][DBDRIVER_PROP][\}];Server=[DBSERVER_PROP];Port=[DBPORT_PROP];Database=[DBNAME_PROP];[SQL_AUTHORIZATION]

• go to the Dialogs page and create a new dialog that will contain the options for setting the SQL Connection
• on this dialog you should add the following elements using the toolbox:
  • a radio button group
  • two buttons into that group ( named for example: Windows Trusted Connection and SQL Server Login )
  • next to that group add two more edit boxes, for the username and password (with the associated properties: USER_NAME and PASSWORD), in case the user chooses SQL Server login

• lets suppose the radio buttons have the values "TrustedConnection" and "UserDefined", and the radio button group's property is "RADIO_GROUP"
• if "Trusted Connection" button is selected the property "RADIO_GROUP" will contain the text: "TrustedConnection"
• if "SQL Server Login" button is selected the property "RADIO_GROUP" will contain the text: "UserDefined"
• add two Published Events on the [Next] button of the dialog with the following settings:

For the two edit boxes you will have to set Control Conditions, in oder to enable/disable them based on the radio button selection. Here is how it is done:

• select an edit box and go to Control Conditions tab
• use the [New] button to create a new control condition
• in the Control Condition dialog that will appear set the fields like this:

• the above settings are used for enabling the edit boxes
• now add another control condition to disable them in case the first radio button is selected, like this:

Repeat the same steps for the second edit box. Please note that the two edit boxes should be disabled by default. To do so, you can select them, then go to Properties pane, in the right side of the page. Under the "Enabled" column select "False".

This way if the user selects the first radio button the settings for Windows Trusted Connection will be applied. If the second radio button is selected, a username and password will be requested from the user for the connection to take place.