Connecting to SQL Server
Note: This topic describes how to import data using the online Sisense Web Application. To import data through the desktop ElastiCube Manager, click here.
The web-based version of Sisense enables easy and quick access to databases, tables and views contained with SQL databases.
Note: Sisense connects to your SQL database through the default port 1433. This port should be open to Sisense so a connection can be made. Contact your IT department if this port is closed.
To import data from your SQL database:
- In the Data page, open an ElastiCube or click to create a new ElastiCube.
- In the Model Editor, click . The Add Data dialog box is displayed.
- Click SQL Server. The SQL Server Database Connect area is displayed.
- In Location: Enter the computer/server IP address of the database. To connect to a database running on your own computer enter localhost.
- If Windows Authentication is configured with the database, select Use Windows Authentication and enter your username and password in the relevant fields. There are a few limitations to connecting to SQL Server through Windows authentication. See Windows Authentication Limitations for more information.
Select Use the following user name and password and enter your SQL database credentials in the relevant fields below.
- (Optional) Select Encrypt Connection to configure the driver to encrypt all communication with the SQL Server instance before sending it over the network, and then select Trust Server Certificate.
- (Optional) In Additional Parameters, enter additional configuration options by appending key-value pairs to the connection string (e.g., application intent=readonly;). Examples for additional parameters can be found here.
- Click . A list of tables in the database are displayed. All tables and views associated with the database will appear in a new window.
- From the Select Table list, select the relevant database you want to work with, and click Done. To view a preview of data contained in a particular table, select the table or view in the list and in click the Preview Table.
- After you have selected all the relevant tables, click Done.
If you are connecting remotely to Sisense and trying to connect to your SQL Server with Windows Authentication, you need to update the login properties of the Sisense.ECMS service.
To update the Sisense.ECMS service:
- Go to Control Panel > Administrative Tools > Services.
- Find the Sisense.ECMS service, and right-click and select Properties.
- Select the Log On tab, select This account.
- In This account, enter your Windows account user name for the machine you are connecting to.
- In Password and Confirm password, enter your Windows password.
- Click OK.
- Restart the Sisense.ECMS service.
While you can connect to SQL Servers using Windows authentication, there are a few limitations:
- This option is supported for installation on Windows only
- You must be logged in on the Sisense Server locally
- The owner of the Sisense.ECMS process should be the user to authenticate
- When you log off, the Sisense.ECMS Service is terminated and must be restarted on the Sisense Server
You do not need to restart this service if you have set your concurrent users logon on the machine to more than one user and at least one user remains active all the time.