Connection String Properties (Analysis Services)
This topic documents the connection string properties you might set in one of the designer or administration tools, or see in connection strings built by client applications that connect to and query Analysis Services data. As such, it covers just a subset of the available properties. The complete list includes numerous server and database properties, allowing you to customize a connection for a specific application, independent of how the instance or database is configured on the server.
Developers who build custom connection strings in application code should review the API documentation for ADOMD.NET client to view a more detailed list: P:Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectionString
The properties described in this topic are used by the Analysis Services client libraries, ADOMD.NET, AMO, and the OLE DB 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.
This topic includes the following sections:
For more information about how to specify an Analysis Services connection in existing Microsoft applications, see Connect from client applications (Analysis Services).
The following table describes those properties most often used when building a connection string.
|Specifies the server instance. This property is required for all connections. Valid values 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.|
|Specifies the name of the Analysis Services database to connect to. The database must be deployed on Analysis Services, and you must have permission to connect to it. This property is optional for AMO connections, but required for ADOMD.NET.|
|Valid values include MSOLAP or MSOLAP.<version>, where <version> is either 3, 4, or 5. On the file system, the data provider name is msolap110.dll for SQL Server 2012 version, msolap100.dll for SQL Server 2008 and 2008 R2, and msolap90.dll for SQL Server 2005.|
The current version is MSOLAP.5. This property is optional. By default, the client libraries read the current version of the OLE DB provider from the registry. You only need to set this property if you require a specific version of the data provider, for example to connect to a SQL Server 2008 instance.
Data providers correspond to versions of SQL Server. If your organization uses current and previous versions of Analysis Services, you will most likely need to specify which provider to use on connection strings that you create by hand. You might also need to download and install specific versions of the data provider on computers that do not have the version you need. You can download the OLE DB provider from SQL Server Feature Pack pages on the download center. Go to Microsoft SQL Server 2012 Feature Pack to download the Analysis Services OLE DB provider for SQL Server 2012.
MSOLAP.4 was released in both SQL Server 2008 and SQL Server 2008 R2. The 2008 R2 version supports PowerPivot workbooks and sometimes needs to be installed manually on SharePoint servers. To distinguish between these versions, you must check the build number in the file properties of the provider: Go to Program files\Microsoft Analysis Services\AS OLEDB\10. Right-click msolap110.dll and select Properties. Click Details. View the file version information. The version should include 10.50.<buildnumber> for SQL Server 2008 R2. For more information, see Install the Analysis Services OLE DB Provider on SharePoint Servers and Data providers used for Analysis Services connections.
MSOLAP.3 was released in SQL Server 2005.
MSOLAP.4 was released in SQL Server 2008 and again SQL Server 2008 R2
MSOLAP.5 was released in SQL Server 2012
|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.|
This section includes connection string properties related to authentication and encryption. 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 end user identity must be impersonated on the server. Specify the account in a domain\user format. To use this property, the caller must have administrative permissions in Analysis Services. For more information about using this property in an Excel workbook from SharePoint, see Use Analysis Services EffectiveUserName in SharePoint Server 2013. For an illustration of how this property is used with Reporting Services, see Using EffectiveUserName To Impersonate in SSAS.|
|Encrypt Password||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 blank, SSPI, and BASIC.|
|Set this property when the client application requires the data source object to persist sensitive authentication information, such as a password, in encrypted form. By default, authentication information is not persisted.|
|Valid values are True and 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. Valid values are:|
For more information, 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=’ ‘) 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.
|Explicitly specifies which security package to use for client authentication when |
|Encrypts data transmissions. Value values are True and False.|
The user name and password must be the credentials of a Windows identity, either a local or a domain user account. Notice that
This section describes the remainder of the connection string parameters. These 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, as shown in the following screenshot:|
Aliases for this property include
|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.|
|Defines how characters are encoded on the request. Valid values are Default or UTF-8 (these are equivalent), and UTF-16|
|Adjusts case-sensitive string comparisons for a specified locale. For more information about setting this property, see CompareCaseSensitiveStringFlags Property.|
|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.|
|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.
|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: Default. Use the NormalTupleSet
0x02: Use when slicers are empty
|A network packet size (in bytes) between 512 and 32,767. The default network packet size is 4096.|
|Sets the format of the XML sent to the server. Valid values are Default, XML, or Binary. The protocol is XMLA. You can specify that the XML be sent in compressed form (this is the default), as raw XML, or in a binary format. Binary format encodes XML elements and attributes, making them smaller. Compression is a proprietary format that further reduces the size of requests and responses. Compression and binary formats are used to speed up data transfer requests and responses.|
You must use a client library on the connection if using binary or compressed format. 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
|Set this property to bypass caching, causing all partitions to actively listen for query notifications. 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 P:Microsoft.AnalysisServices.AdomdClient.AdomdConnection.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 milliseconds) the client library waits for a command to complete before generating an error.|
|Defines how client and server communications are compressed, when compression is specified via the |
|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.|
|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, 1, and 2.
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.
Aliases for this property include
The following properties are allowed on a connection string, but are not operational in current releases of Analysis Services.
Cache Mode (Use of this property was investigated in earlier releases. Although you might find blog posts recommending its usage, you should avoid setting this property unless instructed by Microsoft Support).
Dynamic Debug Limit
Use Formula Cache
This section shows the connection string that you’ll most likely use when setting up an Analysis Services connection in commonly used applications.
Generic connection string
You might use a connection string like this one if you are configuring a connection from Reporting Services.
Data source=<servername>; initial catalog=<databasename>
Connection string in Excel
The default ADOMD.NET connection string in Excel specifies the data provider, server, database name, Windows integrated security. The MDX Compatibility level is always set to 1. Although you can change the value for the current session, Excel will reset MDX Compatibility to1 when the file is next opened.
Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Adventure Works DW 2008R2;Data Source=AW-SRV01;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error
This section lists all of the connection string formats supported by Analysis Services. With the exception of connections to PowerPivot databases, you can specify these connections strings in applications that connect to Analysis Services.
Native (or direct) connections to the server
Data Source=server[:port][\instance] where “port” and “\instance” are optional. For example, specifying “Data Source=server1” opens a connection to the default instance (and default port 2383) on a server named “server1”.
“Data Source=server1:port1” will open a connection to an Analysis Services instance running on port “port1” on “server1”.
“Data Source=server1\instance1” will open a connection to SQL Browser (on its default port 2382), resolve the port for the named instance “instance1”, then open the connection to that Analysis Services port.
“Data Source=server1:port1\instance1” will open a connection to SQL Browser on “port1”, resolve the port for the “instance1” named instance, then open the connection to that Analysis Services port.
Local cube connections (.cub files)
Data Source=<path>, for example “Data Source=c:\temp\a.cub”
Http(s) connections to msmdpump.dll
Data Source=<URL>, where the URL is the HTTP or HTTPS address to the virtual IIS folder that contains the msmdpump.dll. For more information, see Configure HTTP Access to Analysis Services on Internet Information Services (IIS) 8.0.
Http(s) connections to PowerPivot workbooks (.xlsx, .xlsb or .xlsm files)
Data Source=<URL>, where the URL is the SharePoint path to a PowerPivot workbook that has been published to a SharePoint library. For example, “Data Source=http://localhost/Shared Documents/Sales.xlsx”.
Http(s) connections to BI Semantic Model Connection files
Data Source=<URL> where the URL is the SharePoint path to the .bism file. For example, “Data Source=http://localhost/Shared Documents/Sales.bism”.
Embedded PowerPivot connections
Data Source=$Embedded$ where $embedded$ is a moniker that refers to an embedded PowerPivot data model inside the workbook. This connection string is created and managed internally. Do not modify it. Embedded connection strings are resolved by the PowerPivot for Excel add-in on client workstations, or by PowerPivot for SharePoint instances in a SharePoint farm.
Local server context in Analysis Services stored procedures
Data Source=*, where * resolves to the local instance.
Analysis Services encrypts and stores the connection strings it uses to connect to each of its data sources. If the connection to a data source requires a user name and password, you can have Analysis Services store the name and password with the connection string, or prompt you for the name and password each time a connection to the data source is required. Having Analysis Services prompt you for user information means that this information does not have to be stored and encrypted. However, if you store this information in the connection string, this information does need to be encrypted and secured.
To encrypt and secure the connection string information, Analysis Services uses the Data Protection API. Analysis Services uses a separate encryption key to encrypt connection string information for each Analysis Services database. Analysis Services creates this key when you create a database, and encrypts connection string information based on the Analysis Services startup account. When Analysis Services starts, the encrypted key for each database is read, decrypted, and stored. Analysis Services then uses the appropriate decrypted key to decrypt the data source connection string information when Analysis Services needs to connect to a data source.
Configure HTTP Access to Analysis Services on Internet Information Services (IIS) 8.0
Configure Analysis Services for Kerberos constrained delegation
Data providers used for Analysis Services connections
Connect to Analysis Services