You can connect to and access data from Microsoft SQL Server systems with the data connector for Microsoft SQL Server. On this page, you can find information about the capabilities, available settings, and things to keep in mind when you work with data connections to Microsoft SQL Server.
On this page
For help getting started with accessing data from Microsoft SQL Server in Spotfire, see the following resources:
To learn how to get started and create a new data connection, see Connecting to a data source.
To learn what data sources you can connect to, see Drivers and data sources in Spotfire.
To learn more about data connections and connectors, see What is a data connection?
The following functionality is available when you access data with the connector for Microsoft SQL Server.
Feature |
Supported? |
Load methods |
|
Data types |
|
Functions |
Supported functions for in-database data from Microsoft SQL Server |
Custom queries |
Yes |
Stored procedures |
Yes |
Custom connection properties |
Yes |
Single sign-on with identity provider |
Yes |
Authoring in web client |
Yes |
Supported on Linux Web Player |
Yes |
The following are the supported data source properties that you can configure when you create a data connection with the connector for Microsoft SQL Server. To learn more, see Properties in connection data sources.
Note: For more information about the properties and the corresponding settings in the driver software, see the official documentation from Microsoft about the .NET data provider for SQL Server.
Option |
Description |
Server |
The name of the server where your data is located. To include a port number, add it directly after the name preceded by comma. To include an instance name, add it directly after the server name preceded by backslash. Example with port number: Example with instance name: |
Database |
Determines how you select which database to access. Select from list Select from a list of all available databases in the Database drop-down menu. The Database drop-down menu is populated after you click Connect. Note: To use the Select from list option, you must have access to the default database in the data source. The default database is often called ‘master’. Enter name Manually enter the name of the database that you want to access. |
Authentication
method |
The authentication method to use when logging into the database. Windows authentication When using Windows authentication, e.g., Kerberos, the access token of the logged in user will be used. Users that have been given the appropriate access rights to SQL Server will be able to connect and read data. Domain credentials are not stored in the analysis file. SQL Server authentication With database authentication the authentication is done using a database user. Database credentials can be stored, unencrypted, as part of the analysis file, using a setting in the Data Source Settings dialog. If credentials are found in the analysis file they will be used to automatically authenticate against the database. If no credentials or credentials profiles are found in the analysis file all who open the file will be prompted for database credentials. Note that there will be no prompting for credentials if the credentials embedded in the analysis file fail. Active Directory - Integrated [This authentication method is not supported if you open the data connection in a Spotfire web client that is running on a Linux system.] If you are using Active Directory and your domain is federated with Azure Active Directory, you can connect to Microsoft Azure SQL Database and authenticate with your logged in Azure Active Directory credentials. Select the authentication method Active Directory - Integrated. With this authentication method, credentials cannot be saved in the analysis file. Active Directory - Password Enter your Azure Active Directory principal user name and password for authentication with Microsoft Azure SQL Database. Use Active Directory - Password authentication only to connect to Microsoft Azure SQL Database. Identity provider (OAuth2) Use an identity provider, such as Okta or Keycloak, to log in. When you open the data connection, a web browser window opens, and you log in with the identity provider's login procedure. To learn more, see Single sign-on for connectors with an identity provider (OAuth2). |
Encrypt |
Select this check box to require that encryption is used when connecting to the SQL Server. Note: By default, Encrypt is selected. |
Trust server
|
[Only applicable when Encrypt is selected.] If you want use encryption to connect to an SQL Server which does not have a verifiable server certificate (for example if it is using a self-signed certificate), you can select this check box to trust the server and connect without validation of the server certificate. |
Connection timeout (s) |
The maximum time, in seconds, allowed for a connection to the database to be established. You can also set this timeout as a preference in the Administration Manager, which will be used if you don’t set a timeout in the connection dialog. If you set a timeout in the connection dialog this value overrides the settings in the Administration Manager preference. If you specify a timeout neither in the connection dialog nor in the preference, the default value of 120 seconds will be used. Note: If you set the connection timeout to zero, it will be interpreted as no timeout. This means that there will be no upper limit for trying to execute the command. This is generally not recommended. |
Command timeout (s) |
The maximum time, in seconds, allowed for a command to be executed. You can also set this timeout as a preference in the Administration Manager, which will be used if you don’t set a timeout in the connection dialog. If you set a timeout in the connection dialog this value overrides the settings in the Administration Manager preference. If you specify a timeout neither in the connection dialog nor in the preference, the default value of 1800 seconds will be used. Note: If you set the command timeout to zero, it will be interpreted as no timeout. This means that there will be no upper limit for trying to execute the command. This is generally not recommended. |
Custom properties for Microsoft SQL Server connection data sources
The following is the default list of driver settings that are allowed as custom properties in Microsoft SQL Server connection data sources. To learn how to change the allowed custom properties, see Controlling what properties are allowed.
Default allowed custom properties
ApplicationIntent, Attestation Protocol, Column Encryption Setting, ConnectRetryCount, ConnectRetryInterval, Enclave Attestation Url, Enlist, Failover Partner, FailoverPartnerSPN, HostNameInCertificate, IPAddressPreference, Load Balance Timeout, Max Pool Size, Min Pool Size, MultipleActiveResultSets, MultiSubnetFailover, Packet Size, PoolBlockingPeriod, Pooling, ServerCertificate, ServerSPN, Transaction Binding, Type System Version, User Instance, Workstation ID
Working with Microsoft SQL Server data connections
The following is information specifically about working with data from a Microsoft SQL Server connection in Spotfire.
Telemetry - Query tagging
When you establish a connection with the connector for Microsoft SQL Server, the Spotfire application name and version number are added to the connection string as a telemetry tag.