Connection Settings dialog box for SQL Server

The Connection Settings dialog box for SQL Server appears when you click KwikSelect for the Database Connection String field in the Connection dialog box.

When using this dialog box for connecting to Microsoft SQL Server via ODBC, the following information is required:

• Driver Name - select the appropriate ODBC driver to use

• Server Name - there are a number of formats on what can be entered here

  • [<Protocol>:]<Server Name>[,<Port Number>] - the name, protocol used and/or port number of the host running the default instance of Microsoft SQL Server

  • [<Protocol>:]<Server Name>\<Instance Name>[,<Port Number>] - the name, protocol used and/or port number of the host and the named instance of Microsoft SQL Server.

  • [<Protocol>:]<Server Name>\SQLEXPRESS[,<Port Number>] - the name, protocol used and/or port number of the host running the Microsoft SQL Server Express Edition.

  • [<Protocol>:]<IP Address>[,<Port Number>] - the IP Address, protocol used and/or port number of the host running the default instance of Microsoft SQL Server.

  • [<Protocol>:]<Listener Name>[,<Port Number>] - the name of the listener, protocol used and/or port number setup for a Microsoft SQL Server AlwaysOn Availability Group.

  • tcp:<Server Name>.database.windows.net,<Port Number> - Microsoft Azure connections only - these values comes from the connection string entered in on the Connection dialog box and is provided by Microsoft Azure.

    NOTE:
    • For a local install <Server Name> can be represented by ‘localhost’ or a ‘.’ (dot).
    • For all the examples above except for the Azure database <Protocol> and/or <Port Number> are not mandatory.

• Authentication - select one of the following options:
  • Windows Integrated - the currently logged in Windows user (setting up this dataset) must have the appropriate privileges on Microsoft SQL Server identified by Server Name.

  • SQL Server - the User Name and Password provided must have the appopriate privileges on Microsoft SQL Server identified by Server Name.

  • Azure Active Directory - Password - use this method if you are logged into Windows using your Azure Active Directory credentials from a federated domain, or a managed domain that is configured for seamless single sign-on for pass-through and password hash authentication.
  • Azure Active Directory - Integrated - use this method when connecting with an Azure AD principal name using the Azure AD managed domain. You can also use it for federated accounts without access to the domain, for example, when working remotely.
    Use this method to authenticate to the database in SQL Database or the SQL Managed Instance with Azure AD cloud-only identity users, or those who use Azure AD hybrid identities. This method supports users who want to use their Windows credential, but their local machine is not joined with the domain
• Encrypt - this field allows client connectivity to be encrypted there by ensuring secure connectivity. The default is checked, i.e., encryption is true.
• Trusted server certificate - this field validates the Trusted server certificate. By default, it is checked
• Host name in certificate - enter the host name mentioned in the certificate.
• Database Name - this field will contain a list of databases retrieved by Enterprise Studio from the given Microsoft SQL Server identified by Server Name. When choosing a database from this list, it must either be empty (when creating a new dataset) or already contains the Content Manager objects (when registering an existing dataset).
• NOTE:
  • If this list is empty it means that Enterprise Studio was unable to connect to the Microsoft SQL Server identified by Server Name.
• Use AlwaysOn Availability Groups - only used in the case of connecting to a Microsoft SQL Server AlwaysOn Availability Group’s Listener identified by Server Name.
• Click the Test Connection button to verify these details.
  A message that the test connection succeeded will appear when the connection to the database is working. Otherwise, refer to your RDBMS documentation to resolve any errors that may occur.

Press OK to accept the details and return to the Connection dialog box.