Connecting to Microsoft SQL Server

Concept Information

ACCESSDATA command

Microsoft SQL Server is a widely-used relational database management system. You can use the SQL Server data connector to import your organization's SQL Server data.

Note

Analytics provides SQL Server as an optional connector and if it is not available in your Data Access window, it is likely that the connector was not selected during installation. For more information, see Install optional Analytics data connectors and Python engine.

Before you start

To connect to SQL Server, you must gather the following:

  • the database server's host name
  • the correct connection port
  • the Service Principal Name (SPN) if using Integrated Windows authentication
  • your username and password if using standard authentication
  • read access for the schema and tables you want to connect to

For help gathering the connection prerequisites, contact the SQL Server administrator in your organization. If your administrator cannot help you, you or your administrator should contact SQL Server Support.

Create a SQL Server connection

  1. From the Analytics main menu, select Import > Database and application.
  2. From the New Connections tab, in the ACL Connectors section, select SQL Server.

    Tip

    You can filter the list of available connectors by entering a search string in the Filter connections box. Connectors are listed alphabetically.

  3. In the Data Connection Settings panel, enter the connection settings and at the bottom of the panel, click Save and Connect.

    You can accept the default Connection Name, or enter a new one.

The connection for SQL Server is saved to the Existing Connections tab. In the future, you can reconnect to SQL Server from the saved connection.

Once the connection is established, the Data Access window opens to the Staging Area and you can begin importing data. For help importing data from SQL Server, see Working with the Data Access window.

Connection settings

Setting Description Example
Server

The name or network address of the SQL server instance.

  
Port The port of the server hosting the SQL server database. 1433
Database The name of the SQL server database running on the specified server.  
Encrypt

To set whether the driver will attempt to negotiate TLS/SSL connections to the server.

By default, the driver checks the server certificate against the trusted certificate store of the system.

To specify another certificate, set SSLServerCert.

 false
CA Certificate

The certificate to be accepted from the server when connecting using TLS/SSL.

You can provide any of the following:

  • a full PEM certificate
  • path to a local file containing the certificate
  • public key
  • MD5 or SHA1 thumbprint (hex values can also be either space or colon separated)

Any other certificate that is not trusted by the machine is rejected.

 cacerts.pem
Trust Server Certificate

Specifies whether to authenticate to SQL server with Windows Integrated Security.

When this is set to true, a Windows identity will be used to perform Windows authentication.

If this value is false, SQL Server authentication will be used.

 false
Use Trusted Connection Specifies whether the driver uses Kerberos protocol to authenticate connections to SQL Server. true
Server SPN The service principal name of the SQL Server instance.  
User The username provided for authentication with SQL Server.  
Password The password to authenticate the specified user with SQL Server.