Creating a DSN-Less Connection

Some ODBC tools support connecting to an ODBC server without using a data source name (DSN). This is known as a DSN-less connection.

This section lists the parameters you have to use to create a DSN-less connection to Virtual DataPort.

DSN-less connection from a 64-bit client
DRIVER={DenodoODBC Unicode(x64)};UID=<user account>;PWD=<password of the user>;SERVER=<host name>;DATABASE=<database name>;PORT=9996;SSLmode=prefer;service=;krbsrvname=HTTP;UserAgent=<user agent>;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=set+i18n+to+us%5fpst%3b;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=0;UseDeclareFetch=1;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=0;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;PreferLibpq=1;GssAuthUseGSS=0;XaOpt=3;UseKerberos=0
DSN-less connection from a 32-bit client
DRIVER={DenodoODBC Unicode};UID=<user account>;PWD=<password of the user>;SERVER=<host name>;DATABASE=<database name>;PORT=9996;PWD=;SSLmode=prefer;service=;krbsrvname=HTTP;UserAgent=<user agent>;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=set+i18n+to+us%5fpst%3b;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=0;UseDeclareFetch=1;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=0;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;PreferLibpq=1;GssAuthUseGSS=0;XaOpt=3;UseKerberos=0

About the examples above:

  • To use the examples above, replace <user account>, <password of the user>, <host name> and <database name> and <user agent>. If you changed the default ODBC port, replace 9996 with the actual ODBC port of Virtual DataPort.

  • Note that the value of the property DRIVER has to be {DenodoODBC Unicode} or {DenodoODBC Unicode(x64)} depending on if you are creating the connection from a 32-bit client or a 64-bit one, respectively.

  • When using DSN-Less connections you may not be able to connect if the password contains the following characters four: %, {, } and +. If so you may need to escape these characters: % as %25, + as %2B, { as %7B and } as %7D.

Note

Do not leave any space between the names of parameters, the equal symbol, the values, the semicolon, etc.

Use Kerberos authentication

To connect to Virtual DataPort with Kerberos authentication, check with the administrator of Denodo that Kerberos is configured on Virtual DataPort. Otherwise, the connection with the OAuth protocol will fail.

To use Kerberos authentication in a DSN-less connection use the previous general templates with the following considerations:

  1. Remove the parameters UID and PWD.

  2. Set parameter UseKerberos to 1.

  3. The value of the property SERVER has to be the fully qualified domain name of the Denodo server. That is, if in the Kerberos configuration of the Denodo server the field Server principal is HTTP/denodo-prod.subnet1.contoso.com@CONTOSO.COM, enter denodo-prod.subnet1.contoso.com.

  4. Usually, the default value of krbsrvname is correct. This value has to match the “service class” of the Service Principal Name of the Server. For example, if in the field Server Principal of the Kerberos settings of the Server, you have HTTP/denodo1.contoso.com@CONTOSO.COM, the value of krbsrvname has to be HTTP.

Use OAuth Authentication

To connect to Virtual DataPort with OAuth authentication, check with the administrator of Denodo that OAuth is enabled on Virtual DataPort. Otherwise, the connection with the OAuth protocol will fail.

Denodo ODBC driver provides two paths to connect to Denodo using OAuth. Depending on the one you choose you have to add certain parameters or others to the connection string.

Path #1: OAuth 2.0 Authentication providing access token

The client application obtains an OAuth access token and passes it to the driver. Then, the driver uses this token to connect to Virtual DataPort, instead of using user/password.

This path is meant to be used by client applications that have the ability to obtain access tokens to connect to Virtual DataPort. This client application can obtain this access token for itself or on behalf of the end user of the application.

To use this method, do this:

  1. Add these parameters to the DNS-less connection string:

    • UseOAuth2=1: enable OAuth 2.0 authentication

    • AccessToken: the value is the access token obtained by the client application.

  2. Remove the parameters UID and PWD.

Path #2: OAuth 2.0 by Resource Owner Password Credentials

If the client application does not have the ability of obtaining OAuth tokens, the driver can obtain one on behalf of the application, if the Identity Provider of your organization supports the Resource Owner Password Credentials OAuth flow.

In this case, you configure the connection with information about the Identity Provider. That is, the URL to request tokens to the Identity Provider, client id and client secret of the application you registered on the Identity Provider, etc.

To use this method, do this:

  1. Provide a value for the parameters UID and PWD. For these, provide the credentials in the Identity Provider to obtain an access token.

  2. Add the following parameters to the connection string:

Parameters for OAuth authentication

Property

Meaning

UseOAuth2

Set to 1. Instructs the driver to open the connection with OAuth authentication.

TokenEndpoint

URL exposed by the OAuth server and used to request the access_token. For instance https://login.microsoftonline.com/common/oauth2/token.

ClientId

Application’s Client ID. Usually, you obtain this when registering the client application in the Identity Provider.

ClientSecret

Application’s Client secret.

Scope

Space-delimited list of requested scope permissions.

ExtraParams

Additional parameters that will be added to the body of the HTTP requests the driver will send to obtain OAuth tokens. The syntax of the value of this parameter is param1=value1&param2=value2&...

UseIdToken

If 1, the driver will use the “id_token” for authentication. If 0, it will use “access_token”. Set this to 1 in an environment with “OpenID Connect”.

OAuthSSLVerify

If 1, the driver will validate the SSL certificate of the Identity Provider. If 0, it will not validate the certificate. Default value: 1.