SQL Database Browse Connection String

[sbrown@nacintl.com]

I have a client who has hardened their Servers. They have limited communication protocol to TLS 1.2. According to a MS SQL Server ODBC search, "ODBC Driver 11 for SQL Server" has been updated to be compatible with TLS 1.2. I have created a simple install program to figure out all of the settings and how to use the Driver throughout the install process. The issue I'm having is that I do not know where the connection string for the "SQL Database Browse" is located or how to set the ODBC driver and SQL Authorization setting in the connection string .

On the "SQLServerConnectionDlg" dialog, I have a "Test SQL Connection" button with the following setting. The test is always successful when I check "Trusted Connection (Windows Integrated authentication)".

SQL_AUTHORIZATION
UID=[USERNAME_PROP]; Pwd=[PASSWORD_PROP]
Condition: NOT SQL_TRUSTED_CONNECTION

SQL_AUTHORIZATION
Trusted_Connection=Yes;
Condition: SQL_TRUSTED_CONNECTION

SqlConnectionString
Driver=[\{]ODBC Driver 11 for SQL Server[\}];Server=[SERVER_PROP];[SQL_AUTHORIZATION]

When I execute the built-in Custom Actions "ShowDatabases", I get the ODBC Connection Timeout error message.
SQL Database Browse Settings
Server Name: {SERVER_PROP]

Trusted Connection (Windows integrated authentication): False
Username: [USERNAME_PROP]
Password: [PASSWORD_PROP]

Display the result into a Combobox control: True
ComboBox Property: DATABASE_PROP

Attached to an event
Dialog: SQLServerConnectionDlg
Control: BrowseDatabases

[Daniel]

Hello,

If you are using our "SQLServerConnectionDlg" dialog, then the "ShowDatabases" action (browsing for databases) uses the connection string set for the "DlgPredefinedConnection" SQL connection. More exactly the ODBC driver set in the "ODBC Driver" field of the "DlgPredefinedConnection" SQL connection.

screen.jpg (141.81 KiB) Viewed 5591 times

Let us know if this helped.

All the best,
Daniel

[sbrown@nacintl.com]

Thanks for your reply. The information was helpful to a point. On the "SQLServerConnectionDlg" screen I use the following connection string to test the connection after specifying the database server.

Driver=[\{]ODBC Driver 11 for SQL Server[\}];Server=[SERVER_PROP];[SQL_AUTHORIZATION]

Note that "SQL_AUTHORIZATION" can equal one of the following if the user selects "Trusted Connection" on the "SQLServerConnectionDlg".
1. UID=[USERNAME_PROP]; Pwd=[PASSWORD_PROP]
2. Trusted_Connection=Yes;

When "Trusted Connection" is selected, option 2 has to be added to the connection string otherwise the user receives an "ODBC Timeout". I tried setting "Trusted Connection" on the "SQL Database" setup screen to true. I still receive the "ODBC Timeout" error message when I select the button to show databases. When "Trusted Connection" is selected on the "SQLServerConnectionDlg", does the connection string created by "DlgPredefinedConnection contain option 2? Is there a way to append "SQL_AUTHORIZATION" to the connection string created by "DlgPredefinedConnection".

[Daniel]

Hello,

I've talked with our development team about the implementation of our "SQL Database Browse" support and it seems I previously gave you a wrong information. Our "SQL Database Browse" support (e.g. the "ShowDatabases" action) do not use the ODBC Driver set for the "DlgPredefinedConnection" SQL connection. I apologize for this.

It seems our "SQL Database Browse" support always uses the default ODBC driver of the target system (which usually is "SQL Server") and we do not have any option you can use to change the ODBC driver used by our browse for databases feature. We have added this improvement on our TODO list and hopefully it will be available in the future, thank you for bringing this to our attention. We will update this forum thread as soon as the improvement will be available.

Until then, the only workaround will be to create your own custom action to browse for databases using a custom ODBC driver.

All the best,
Daniel

[sbrown@nacintl.com]

I need some help with your suggested solution. I've created a custom action (dll) to return a list of databases. I'm not sure which "Add Custom Action" to select to attach my vb.net Custom Action. Also, I do not know how to connect the returned list of Databases to my combobox.

Code: Select all

Partial Public Class CustomActionsInstaller
    Public Sub New()
        MyBase.New()

        'This call is required by the Component Designer.
        InitializeComponent()

        'Add initialization code after the call to InitializeComponent

    End Sub

    Public Overrides Sub Install(ByVal stateSaver As IDictionary)
        MyBase.Install(stateSaver)

        Try
            Dim listOfDBs As New List(Of String)
            Dim dbServer As String = Context.Parameters("SERVER_PROP")
            Dim connectionString As String = Context.Parameters("SqlConnectionString")
            Dim conn As New SqlClient.SqlConnection(connectionString)
            Dim databases As DataTable = Nothing

            Using dbConnection As New SqlClient.SqlConnection(connectionString)
                dbConnection.Open()
                databases = dbConnection.GetSchema("Databases")
                For Each row As DataRow In databases.Rows
                    listOfDBs.Add(CStr(row.Item("database_name")))

                Next

            End Using
            Context.Parameters("LIST_OF_DATABASE") = String.Join(";", listOfDBs.ToArray)

        Catch ex As Exception
            Context.LogMessage(ex.Message)
            Context.LogMessage(ex.StackTrace)

        End Try

    End Sub

End Class

[Daniel]

Hello,

To launch your custom action you can use the predefined "Launch attached file" custom action type.

To populate the returned database list into your combo box you can use our "PopulateComboBox" predefined custom action. Basically within your .Net custom action you should set the AI_COMBOBOX_DATA property like this:

Code: Select all

COMBO_PROP|Value1#Text1|Value2#Text2|Value3#Text3|...

and then just call our "PopulateComboBox" action.

Also, you can take a look on our "Set an installer property using custom actions" article which should be useful.

All the best,
Daniel

[Daniel]

Hello,

I've talked with our development team about the implementation of our "SQL Database Browse" support and it seems I previously gave you a wrong information. Our "SQL Database Browse" support (e.g. the "ShowDatabases" action) do not use the ODBC Driver set for the "DlgPredefinedConnection" SQL connection. I apologize for this.

It seems our "SQL Database Browse" support always uses the default ODBC driver of the target system (which usually is "SQL Server") and we do not have any option you can use to change the ODBC driver used by our browse for databases feature. We have added this improvement on our TODO list and hopefully it will be available in the future, thank you for bringing this to our attention. We will update this forum thread as soon as the improvement will be available.

Until then, the only workaround will be to create your own custom action to browse for databases using a custom ODBC driver.

All the best,
Daniel

Hello,

Just wanted to let you know that starting with AI 15.7 version we have added support to specify ODBC driver used for the SQL Database Browse operation. This can be done from "ODBC Driver" field of your sql db browse operation on "SQL Databases" page of your setup project.

All the best,
Daniel