Microsoft SQL Server

DSS supports the full range of features on Microsoft SQL Server:

  • Reading and writing datasets
  • Executing SQL recipes
  • Performing visual recipes in-database
  • Using live engine for charts

Installing the JDBC driver

The SQL Server JDBC driver can be downloaded from Microsoft website (at time of writing, from https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774)

  • Make sure to select a version that is a “JDBC Type 4 driver”
  • Download the “tar.gz” version
  • Unarchive the downloaded file
  • Take only the “enu/jre7/sqljdbc41.jar”

The driver is the single JAR file called sqljdbc41.jar

  • Copy the JAR file to the lib/jdbc driver of DSS
  • Restart DSS

Azure DataWarehouse mode

DSS has experimental support for the Azure Data Warehouse. Configure a Azure Data Warehouse connection by enabling the “Azure Data Warehouse” option of a SQL Server connection

Warning

This option must only be used for “Azure Data Warehouse” databases. You must not use this option for regular SQLServer databases hosted on Azure.

Setting the “Azure Data Warehouse” option on a regular SQLServer database will result in a non-functional connection

Warning

Writing data to a Azure Data Warehouse dataset is slow (apart from the case where the input is on the same database and the recipe uses the SQL / In Database engine)

Importing data to Azure Data Warehouse can be speed up by using an Azure Blob Storage as data source and selecting “Azure to SQLServer” as recipe engine.

Warning

When using the Azure to SQLServer engine, discrepancies may appear in strings if the use of quote characters is not coherent throughout the input dataset.

Advanced connection properties

Kerberos authentication

In default connection mode, DSS authenticates to SQL Server by way of a username and password defined in the connection configuration page.

Alternatively, it is possible for DSS to connect to the database with Kerberos authentication, provided a number of prerequisites are met:

  • Kerberos authentication should be enabled on the SQL Server

  • The Kerberos client configuration file (typically /etc/krb5.conf) should be correctly configured on the DSS host.

  • Create a Kerberos account for DSS on the KDC (or domain controller) and note the Kerberos principal for it, say DSSKerberosPrincipal@KERBEROS.REALM.

  • Create a keytab file for this account, and store it in a file accessible only to DSS.

  • At the DSS level, configure the SQL Server connection as follows:

    • Login with Kerberos: enabled

    • Principal: the Kerberos principal created above

    • Keytab: absolute path to the keytab file for this Kerberos principal

    • Advanced JDBC properties: add any SQLServer-specific advanced connection properties required by your database setup. This would typically be:

      integratedSecurity : true
      authenticationScheme : JavaKerberos
      # the following is needed only if the database server service principal name (SPN)
      # is different from the default : MSSQLSvc/FQDN:[email protected]
      serverSpn : SERVER_SPN
      

User impersonation

Note

While this feature is distinct from the multi-user-security feature, it is only available for multi-user-security enabled DSS licenses.

This feature requires DSS to run under Java 8

This feature requires the database server to be integrated with a Windows Active Directory domain controler.

It is possible to configure DSS to authenticate to the database using one database account, and perform all data manipulation and SQL queries using another. This typically allows DSS to impersonate its end-users when accessing the database, much as is done for Hadoop or local computations when running in multi-user security mode.

At the DSS level, this is configured by entering the database account name to impersonate in the Impersonated user field of the connection configuration page.

Typical uses of this feature would include:

  • defining this field as %{dssUserLogin} to directly use the DSS user login name as SQLServer account name, in case the DSS and SQLServer account databases match one-for-one
  • defining this field as %{adminProperty:sqlServerLogin} where sqlServerLogin is a custom per-user admin property defined in the DSS user database, in the more general case where usernames have to be remapped

This impersonation mechanism uses the Active Directory “constrained delegation” feature, and should have been authorized accordingly by the domain administrator. This is typically done as follows (refer to Microsoft documentation for details):

  • Add a Service Principal Name to the Active Directory service account used by DSS. This is typically done with setspn, or directly with the ktpass command when creating the Kerberos keytab for DSS
  • In the “Active Directory Users and Computers” tool, open the DSS service account entry, and select the “Delegation” tab (this tab is only visible for accounts which have a SPN set).
  • Check “Trust this user for delegation to specified services only”
  • Check “Use any authentication protocol”
  • Locate or search for the SQL server service entry, and allow the DSS account to delegate to it.