SQL Server

Connect & Ingest data from / to a SQL Server database

Setup

The following credentials keys are accepted:

• host (required) -> The hostname / ip of the instance

• user (optional) -> The username to access the instance

• database (optional) -> The database name of the instance

• instance (optional) -> The SQL Server instance to use

• schema (optional) -> The default schema to use

• password (optional) -> The password to access the instance

• port (optional) -> The port of the instance. Default is 1433.

• authenticator (optional) -> Can be used to specify use of a registered authentication provider. (e.g. ntlm, winsspi (on windows) or krb5 (on linux))

• ssh_tunnel (optional) -> The URL of the SSH server you would like to use as a tunnel (example ssh://user:[email protected]:22)

• ssh_private_key (optional) -> The private key to use to access a SSH server (raw string or path to file).

• ssh_passphrase (optional) -> The passphrase to use to access a SSH server.

• fedauth (optional) -> The Azure Active Directory authentication string (e.g. for Fabric connection). See here for more details. Accepted values: ActiveDirectoryDefault, ActiveDirectoryIntegrated, ActiveDirectoryPassword, ActiveDirectoryInteractive, ActiveDirectoryMSI, ActiveDirectoryManagedIdentity, ActiveDirectoryApplication, ActiveDirectoryServicePrincipal, ActiveDirectoryServicePrincipalAccessToken, ActiveDirectoryDeviceCode, ActiveDirectoryAzCli.

• use_adbc (optional) -> Enable Arrow Database Connectivity (ADBC) driver for high-performance data transfer. See ADBC for setup and details. (v1.5.2+)

• adbc_uri (optional) -> Override the automatically constructed ADBC connection URI when using use_adbc=true.

Additional Parameters

Sling uses the go-mssqldb library and thus will accept any paremters listed here. Some parameters that may be of interest:

• encrypt -> strict, disable, false or true - whether data between client and server is encrypted.

• log -> logging level (accepts 1, 2, 4, 8, 16, 32).

• trusted_connection -> true or false - whether to connects to SQL Server with a trusted connection using integrated security (also will use the -T flag when using bulk loading with bcp)

• trust_server_certificate -> true or false - whether the server certificate is checked

• certificate -> The file that contains the public key certificate of the CA that signed the SQL Server certificate. The specified certificate overrides the go platform specific CA certificates.

• hostname_in_certificate -> Specifies the Common Name (CN) in the server certificate. Default value is the server host.

• server_spn -> The kerberos SPN (Service Principal Name) for the server. Default is MSSQLSvc/host:port.

• driver -> A way to override the sql driver to connect with. Default is sqlserver. Other option is azuresql

• bcp_auth_string -> A way to override the way bcp authenticates. Accepts an array of strings, for example ['U', 'username', '-T', '-G'] will append flags -U username -T -G to the bcp command.

• bcp_extra_args -> A way to add additional args to the bcp command. Accepts an array of strings, for example ['b', '5000'] will append flags -b 5000 to the bcp command.

• bcp_entra_auth -> Appends the -G flag to the bcp command for MS Entra Auth.

• bcp_path-> The path to the bcp binary. This is useful if you need to specify a custom path for bcp.

• bcp_azure_token_resource (v1.4.24+) -> where using fed_auth=ActiveDirectoryAzCli, this indicates the resource to use to obtain an access token for BCP to use. Default is https://database.windows.net

• az_path -> The path to the az binary. This is useful if you need to specify a custom path for az to obtain an access token.

Kerberos Parameters

• authenticator - set this to krb5 to enable kerberos authentication. If this is not present, the default provider would be ntlm for unix and winsspi for windows.

• krb5_config_file (optional) - path to kerberos configuration file. Defaults to /etc/krb5.conf. Can also be set using KRB5_CONFIG environment variable.

• krb5_realm (required with keytab and raw credentials) - Domain name for kerberos authentication. Omit this parameter if the realm is part of the user name like username@REALM.

• krb5_keytab_file - path to Keytab file. Can also be set using environment variable KRB5_KTNAME. If no parameter or environment variable is set, the DefaultClientKeytabName value from the krb5 config file is used.

• krb5_cred_cache_file - path to Credential cache. Can also be set using environment variable KRB5CCNAME.

• krb5_dns_lookup_kdc - Optional parameter in all contexts. Set to lookup KDCs in DNS. Boolean. Default is true.

• krb5_udp_preference_limit - Optional parameter in all contexts. 1 means to always use tcp. MIT krb5 has a default value of 1465, and it prevents user setting more than 32700. Integer. Default is 1.

Sling supports authentication via 3 methods. See here for more details.

• Keytabs - Specify the username, keytab file, the krb5.conf file, and realm.

  authenticator: krb5
  krb5_realm: domain.com
  krb5_config_file: /etc/krb5.conf
  krb5_keytab_file: ~/user.keytab

• Credential Cache - Specify the krb5.conf file path and credential cache file path.

  authenticator=krb5
  krb5_config_file=/etc/krb5.conf
  krb5_cred_cache_file=~/user_cached_creds 

• Raw credentials - Specity krb5.confg, Username, Password and Realm.

  authenticator=krb5
  krb5_realm=comani.com
  krb5_config_file=/etc/krb5.conf

---

Using sling conns

Here are examples of setting a connection named MSSQL. We must provide the type=sqlserver property:

$ sling conns set MSSQL type=sqlserver host=<host> user=<user> database=<database> password=<password> port=<port>

# Or use url
$ sling conns set MSSQL url="sqlserver://myuser:[email protected]:1433?database=mydatabase"
$ sling conns set MSSQL url="sqlserver://myuser:[email protected]:1433/my_instance?database=master&encrypt=true&TrustServerCertificate=true"

Environment Variable

See here to learn more about the .env.sling file.

export MSSQL='sqlserver://myuser:[email protected]:1433?database=mydatabase'
export MSSQL='{ type: sqlserver, user: "myuser", password: "mypass", host: "host.ip", port: 1433, database: "mydatabase" }'
export MSSQL='sqlserver://myuser:[email protected]:1433/my_instance?database=master&encrypt=true&TrustServerCertificate=true'
export MSSQL_FABRIC='{ type: sqlserver, host: "xxx.datawarehouse.fabric.microsoft.com", database: "warehouse1", "trust_server_certificate": false, "encrypt": true, fedauth: ActiveDirectoryAzCli, bcp_entra_auth: true }'

Sling Env File YAML

See here to learn more about the sling env.yaml file.

connections:
  MSSQL:
    type: sqlserver
    host: <host>
    user: <user>
    port: <port>
    instance: <instance>
    database: <database>
    schema: <schema>
    password: <password>
    encrypt: 'true'
    trust_server_certificate: 'true'

  MSSQL_URL:
    url: "sqlserver://myuser:[email protected]:1433?database=mydatabase"

  MSSQL_KERBEROS:
    type: sqlserver
    host: <host>
    user: <user>
    port: <port>
    instance: <instance>
    database: <database>
    schema: <schema>
    password: <password>
    authenticator: 'krb5'
    krb5_config_file: /etc/krb5.conf
    krb5_realm: domain.com
    krb5_keytab_file: ~/MyUserName.keytab
    krb5_credcache_file: ~/MyUserNameCachedCreds
  
  MSSQL_FABRIC:
    type: sqlserver
    database: warehouse1
    host: xxx.datawarehouse.fabric.microsoft.com
    encrypt: true
    trust_server_certificate: false
    fedauth: ActiveDirectoryAzCli  # after running `az login` using Azure CLI Tool
    bcp_entra_auth: true  # in order to use BCP with fabric

If you are facing issues connecting, please reach out to us at [email protected], on discord or open a Github Issue here.