Connecting to SQL Server Analysis Services
To connect, set the Url property to a valid SQL Server Analysis Services endpoint and provide authentication. You can connect to SQL Server Analysis Services instances hosted over HTTP with XMLA access. See the Microsoft documentation to configure HTTP access to SQL Server Analysis Services.
Instead of retrieving metadata every connection, you can set CacheLocation.
Authenticating to SQL Server Analysis Services
The provider supports the major authentication schemes, including HTTP and Windows.
Set AuthScheme to use the following authentication types.
- HTTP Authentication
Set AuthScheme to "Basic" or "Digest" and set User and Password. Specify other authentication values in CustomHeaders.
- Windows (NTLM)
Set the Windows User and Password and set AuthScheme to "NTLM".
- Kerberos and Kerberos Delegation
To authenticate with Kerberos, set AuthScheme to NEGOTIATE. To use Kerberos delegation, set AuthScheme to KERBEROSDELEGATION. If needed, provide the User, Password, and KerberosSPN. By default, the provider attempts to communicate with the SPN at the specified Url.
Securing SQL Server Analysis Services Connections
By default, the provider attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store. To specify another certificate, see the SSLServerCert property for the available formats.
The provider enables the granular control useful in more complex deployments. The following sections outline how to traverse a firewall and configure logging to troubleshoot a connection problem.
Customizing the SSL Configuration
By default, the provider attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store. To specify another certificate, see the SSLServerCert property for the available formats to do so.
Connecting Through a Firewall or Proxy
To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.
In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.
Set the following properties:
- To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
- To tunnel the connection, set FirewallType to TUNNEL.
- To authenticate, specify FirewallUser and FirewallPassword.
- To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.
Troubleshooting the Connection
To show provider activity from query execution to network traffic, use Logfile and Verbosity. The examples of common connection errors below show how to use these properties to get more context. Contact the support team for help tracing the source of an error or circumventing a performance issue.
- Authentication errors: Typically, recording a Logfile at Verbosity 4 is necessary to get full details on an authentication error.
- Queries time out: A server that takes too long to respond will exceed the provider's client-side timeout. Often, setting the Timeout property to a higher value will avoid a connection error. Another option is to disable the timeout by setting the property to 0. Setting Verbosity to 2 will show where the time is being spent.
- The certificate presented by the server cannot be validated: This error indicates that the provider cannot validate the server's certificate through the chain of trust. If you are using a self-signed certificate, there is only one certificate in the chain.
To resolve this error, you must verify yourself that the certificate can be trusted and specify to the provider that you trust the certificate. One way you can specify that you trust a certificate is to add the certificate to the trusted system store; another is to set SSLServerCert.
Other Connection Properties
Set the following other properties may also be useful in certain situations:
- UseMDX: Indicates if MDX queries are being submitted. By default this is false, which will cause the driver to accept only SQL-92 compliant queries. Setting this property to true will cause all queries to be passed through directly to SQL Server Analysis Services.
- ExtraProperties: Additional properties to submit along with an MDX query. Only meaningful if UseMDX is true.
- IncludeJoinColumns: Boolean indicating if extra columns used to make ON conditions with joins should be added. These do not come back with any values - they are added purely to enable tools that require them in order to automatically set up relationships between tables when creating joins.
- ResponseRowLimit: Sets a calculated limit on the number of rows to allow the user to select before throwing an error. Because queries are being translated to MDX, selecting only a few columns may exponentially multiply the number of expected results. For this reason, ResponseRowLimit is available to try and give some guidance on what types of queries are likely to result in a Timeout. May be disabled by setting to 0.