Connection string properties
Applies to: SQL Server Analysis Services Azure Analysis Services Power BI Premium
This article describes connection string properties used by client applications that connect to and query Azure Analysis Services (Azure AS), SQL Server Analysis Services (SSAS), and Power BI Premium dataset data. These properties are used by the Analysis Services client libraries, ADOMD.NET, AMO, and OLE DB (MSOLAP) provider for Analysis Services. The majority of connection string properties can be used with all three client libraries. Exceptions are called out in the description.
Use the Version selector above the table of contents to the left to see only those properties that apply to a particular platform or version.
Specifies the server instance. This property is required for all connections.
Valid values for Azure Analysis Services include
<protocol>://<region>/<servername> where protocol is string
link when using a server name alias, region is the Uri where the server was created (for example, westus.asazure.windows.net), and servername is the name of your unique server within the region.
||Azure Analysis Services.|
||Azure Analysis Services using server name alias.|
Valid values for Power BI Premium include
<protocol>://api.powerbi.com/v1.0/[tenant name]/[workspace name] where protocol is string
powerbi, Uri is
api.powerbi.com, tenant name is the organization tenant name or
myorg, and workspace name is the name of a workspace assigned to a dedicated capacity.
||Power BI Premium workspace.|
Valid values for SQL Server Analysis Services include the network name or IP address of the server, local or localhost for local connections, a URL if the server is configured for HTTP or HTTPS access, or the name of a local cube (.cub) file.
||SSAS default instance and port (TCP 2383).|
||SSAS named instance.|
||SSAS default instance, specified port.|
||SSAS fully qualified domain name, default instance and port.|
||SSAS IP address of the server, bypassing DNS server lookup. Useful for troubleshooting connection problems.|
This property is required on the connection string when using an OLE DB provider like MSOLAP. It allows you to use either a version independent provider (usually the latest) like "Provider=MSOLAP", or you can also specify a version dependent provider like "Provider=MSOLAP.7". Valid version dependent values follow the pattern MSOLAP.<version>, where <version> is either 7 or 8. For example, MSOLAP.7 released in SQL Server 2016. Version ".8" is the latest and considered "evergreen". It's expected to keep updating with backward compatibility maintained. Earlier version numbers are also possible, but those releases of MSOLAP are now out of standard support.
This property is optional for ADOMD.NET and AMO. It's allowed for convenience when copying an MSOLAP connection string to use with ADOMD.NET and AMO.
||Connections requiring SQL Server 2016 version of the OLE DB provider for Analysis Services.|
Cube name or perspective name. A database can contain multiple cubes and perspectives. When multiple targets are possible, include the cube or perspective name on the connection string.
||A cube named Sales.|
||A perspective named SalesPerspective.|
Authentication and security properties
Azure Analysis Services and Power BI Premium use Azure Active Directory - Universal with MFA (recommended), Azure Active Directory authentication with username and password, Azure Active Directory access tokens, or Windows authentication.
SQL Server Analysis Services uses Windows authentication only, but you can set properties on the connection string to pass in a specific user name and password.
Properties are listed in alphabetical order.
Use when an user identity must be impersonated on the server. For SSAS, specify in a domain\user format. For Azure AS and Power BI Premium, specify in UPN format. To use this property, the caller must have administrative permissions in Analysis Services. In Power BI Premium, the caller must be a workspace admin where the dataset is located.
A string value to be passed to the engine in the connection string and then retrieved by using the CustomData() MDX function or CUSTOMDATA() DAX function. Most commonly used to pass a userID or username as a value, and then specify that value in the definition of a security expression.
Specifies whether a local password is to be used to encrypt local cubes. Valid values are True or False. The default is False.
The password used to decrypt an encrypted local cube. Default value is empty. This value must be explicitly set by the user.
Indicates the level of impersonation that the server is allowed to use when impersonating the client. Valid values include:
- Anonymous. The client is anonymous to the server. The server process cannot obtain information about the client, nor can the client be impersonated.
- Identify. The server process can get the client identity. The server can impersonate the client identity for authorization purposes but cannot access system objects as the client.
- Impersonate. This is the default value. The client identity can be impersonated, but only when the connection is established, and not on every call.
- Delegate. The server process can impersonate the client security context while acting on behalf of the client. The server process can also make outgoing calls to other servers while acting on behalf of the client.
The Windows identity of the caller is used to connect to Analysis Services. Valid values are SSPI, blank, Basic, and ClaimsToken*.
SSPI is the default value for TCP connections, allowing NTLM, Kerberos, or Anonymous authentication. For Azure AS and Power BI Premium SSPI indicates AD Translation. When using SSPI, ProtectionLevel property must be set to Connect, Pkt Integrity, or Pkt Privacy.
blank is the default value for HTTP connections.
*ClaimsToken is supported for Azure AS and Power BI Premium.
Persist Security Info
Valid values are True or False. When set to True, security information, such as the user identity or password previously specified on the connection string, can be obtained from the connection after the connection is made. The default value is False.
Determines the security level used on the connection. Values supported depend on the underlying transport. Valid values are:
- None. Unauthenticated or anonymous connections. Performs no authentication on data sent to the server.
- Connect. Authenticated connections. Authenticates only when the client establishes a relationship with a server.
- Pkt Integrity. Encrypted connections. Verifies that all data is received from the client and that it has not been changed in transit.
- Pkt Privacy. Signed encryption, supported only for TCP. Verifies that all data is received from the client, that it has not been changed in transit, and protects the privacy of the data by encrypting it.
To learn more, see Establishing Secure Connections in ADOMD.NET
Specify a comma-delimited list of predefined roles to connect to a server or database using permissions conveyed by that role. If this property is omitted, all roles are used, and the effective permissions are the combination of all roles. Setting the property to an empty value, for example,
Roles=' ' means the client connection has no role membership.
An administrator using this property connects using the permissions conveyed by the role. Some commands might fail if the role does not provide sufficient permission.
If specifying roles when connecting to a Power BI Premium workspace, use workspace roles from the Power BI security model.
Explicitly specifies which security package to use for client authentication when Integrated Security is set to SSPI. SSPI supports multiple packages, but you can use this property to specify a particular package. Valid values are:
- Anonymous User
If this property is not set, all packages will be available to the connection.
Use Encryption for Data
Encrypts data transmissions. Valid values are True or False.
User ID=...; Password=
User ID and Password properties provide the appropriate credentials to the server when the current active user in the client application cannot be automatically propagated to the server. The behavior depends on the transport protocol and the server being connected to:
- When connecting over TCP to SSAS, the client library will impersonate the Windows user using the specified username and password, and then connect as usual to the server.
- When connecting over HTTP(S) to SSAS, the credentials are provided to the web server based on the authentication mode configured on the web server, for example Basic auth or Windows auth. The web server will perform the appropriate Windows impersonation before connecting to the SSAS server, therefore providing the correct credentials flow to the server.
- When connecting to Azure AS or Power BI Premium, the User ID and Password are used to obtain an Azure Active Directory (AAD) token which is then presented to the service during authentication. AAD may also require multi-factor authentication (MFA), which can require additional user interaction before the token can be generated.
- If you've already acquired a valid AAD bearer access token from your own application, you can omit the User ID property and specify only the access token in the Password property. Authentication is supported for bearer tokens acquired interactively for a user, and by using OAuth 2.0 On-Behalf-Of flow (for example, a middle-tier web application connecting to Analysis Services on behalf of the user). Omit the token type when passing the access token in the Password property. The Analysis Services client libraries automatically add the auth-scheme value "Bearer" to the access token.
Note: "User ID" has an embedded space. An alternate alias for User ID is UID and an alternate alias for Password is PWD.
Special purpose properties
These properties are used to ensure specific connection behaviors required by an application. Properties are listed in alphabetical order.
Sets the name of the application associated with the connection. This value can be useful when monitoring tracing events, especially when you have several applications accessing the same databases. For example, adding Application Name='test' to a connection string causes 'test' to appear in a SQL Server Profiler trace. Aliases for this property include SspropInitAppName, AppName. To learn more, see Application Name for SQL Server Connections.
Auto Synch Period
Sets the frequency (in milliseconds) of client and server cache synchronization. ADOMD.NET provides client caching for frequently used objects that have minimal memory overhead. This helps reduce the number of round trips to the server. The default is 10000 milliseconds (or 10 seconds). When set to null or 0, automatic synchronization is turned off.
For performance reasons, the client libraries cache some information from the server, for example, certain schema rowsets. Auto Synch Period allows a user to change the time period after which the client library checks with the server whether or not the caches need to be emptied. In general, you should not need to change the value from default.
Defines how characters are encoded on the request. Valid values are Default or UTF-8 (these are equivalent), and UTF-16.
An XMLA property. Determines how long, in milliseconds, the commit phase of a currently running command waits before rolling back. When greater than 0, overrides the value of the corresponding CommitTimeout property in the server configuration.
Adjusts case-sensitive string comparisons for a specified locale.
If TransportCompression is compressed, you can set the compression level to control how much compression is used. Valid values are 0 through 9, with 0 having least compression, and 9 having the most compression. Increased compression slows performance. The default value is 0.
Determines the maximum amount of time (in seconds) the client attempts a connection before timing out. If a connection does not succeed within this period, the client quits trying to connect and generates an error.
Overrides the Memory\QueryMemoryLimit server property value for a connection.
Specified in kilobytes, this property can reduce the amount of memory used during a query from the maximum allowed memory (specified as a percentage) in QueryMemoryLimit. It cannot increase the amount of memory used beyond the maximum allowed memory specified in QueryMemoryLimit.
Default MDX Visual Mode
Set this property to control how members are aggregated when dimension security is applied.
For cube data that everyone is allowed to see, aggregating all of the members makes sense because all of the values that contribute to the total are visible. However, if you filter or restrict dimensions based on user identity, showing a total based on all the members (combining both restricted and allowed values into a single total) might be confusing or show more information than should be revealed.
To specify how members are aggregated when dimension security is applied, you can set this property to True to use only allowed values in the aggregation, or False to exclude restricted values from the total.
When set on the connection string, this value applies to the cube or perspective level. Within a model, you can control visual totals at a more granular level.
Valid values are,
- 0 is the default. Currently, the default behavior is equivalent to 2, where aggregations include values that are hidden from the user.
- 1 excludes hidden values from the total. This is the default for Excel.
- 2 includes hidden values in the total. This is the default value on the server.
The alias for this property is VisualMode.
The purpose of this property is to ensure a consistent set of MDX behaviors for applications that issue MDX queries. Excel, which uses MDX queries to populate and calculate a PivotTable connected to Analysis Services, sets this property to 1, to ensure that placeholder members in ragged hierarchies are visible in a PivotTable. Valid values include 0, 1, 2.
0 and 1 expose placeholder members; 2 does not. If this is empty, 0 is assumed.
MDX Missing Member Mode
Indicates whether missing members are ignored in MDX statements. Valid values are Default, Error, and Ignore. Default uses a server-defined value. Error generates an error when a member does not exist. Ignore specifies that missing values should be ignored.
A bitmask indicating which of the following query response optimizations are enabled.
- 0x01 Use the NormalTupleSet (default).
- 0x02 Use when slicers are empty.
Applies to TCP connections only. A network packet size (in bytes) between 512 and 32,767. The default network packet size is 4096.
Sets the format of the XML used by the XMLA communication protocol. Valid values are Default, XML, or Binary. You can specify the XML be sent in a binary format or as text XML. Binary format encodes XML elements and attributes, making them smaller. In addition, compression can also be enabled for the messages to reduce the size of requests and responses using the Transport Compression option. Requests and responses can use different protocol formats depending on what the client and server supports. For example, a client library may only support binary for responses, but not for requests, or a server may have disabled binary for incoming requests.
OLE DB provider can format requests and responses in binary or compressed format. AMO and ADOMD.NET format the requests as Text, but accept responses in binary or compressed format.
This connection string property is equivalent to the EnableBinaryXML and EnableCompression server configuration settings.
Real Time Olap
Set this property to bypass caching, causing all storage queries to fetch data from the source system. By default, this property is not set.
Sets the safety level for user-defined functions and actions. Valid values are 0, 1, 2. In an Excel connection, this property is Safety Options=2. Details about this option can be found in ConnectionString.
Specifies whether SQL queries include calculations. Valid values are Data, Calculated, IncludeEmpty. Data means that no calculations are allowed. Calculated allows calculations. IncludeEmpty allows calculations and empty rows to be returned in the query result.
Specifies how long (in seconds) the client library waits for a command to complete before generating an error.
Defines how client and server communications are compressed. Valid values are Default, None, Compressed. None indicates no compression is used. Compressed uses XPRESS compression.
Used when connecting to a local cube. This property specifies whether the local cube is overwritten. Valid values are True or False. If set to True, the cube file must exist. The existing file will be the target of the connection. If set to False, the cube file is overwritten.