THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

Local cubes password protection

Analysis Services security model was already considered as best among OLAP servers by OLAP Report. Analysis Services 2005 introduced even more security features. But usually we talk about server specific security features, such as fine grain administration permissions, transposrt encryption, stored procedures sandboxes and impersonation modes, default Negotiate SSPI for authentication etc. However, none of these features really apply to the local cubes. Indeed, while it is possible to create local cube with roles and dimension and cell permissions in it - they don't mean anything, because anybody who has access to the local cube is in reality administrator, since he has physical access to all the data. So the only method of protection for local cube files - is the ability to protect the entire file with password - the method successfully employed by Office for many years. This has been long standing feature request by users since OLAP Services 7.0 introduces local cubes, and Analysis Services 2005 supports it. The way this feature is implemented, is, of course, through the connection string property ! However, for the reasons that I don't want to get into right now, the standard OLEDB property "Password" is not used for that purpose (it is used for transport authentication, either HTTP Basic or TCP/IP Integrated), but a new connection string property "Encryption Password".should be used instead.Whenever this property is used on the connection to the local cube, it is encrypted and decrypted using the symmetric key derived from the password. The exact steps are outlined below
1. The local cube must be created with password to begin with. It is not possible to password protect/encrypt existing local cube. It is not possible to change the password on the encrypted file either. The easiest way to create password protected local cube file is to simply open ADOMD.NET or OLEDB connection to the new local cube file with the connection string as "Data Source=c:\temp\SalesEncrypted.cub;Encryption Password=abrakadabra"
2. The old BNF based synatx of "CREATE GLOBAL CUBE" doesn't support parameter for the password, therefore password protected local cubes must be populated using XML based DDL. This is good idea anyway, since the same XML can be used for both server and local cube files, and it exposes much more features then CREATE GLOBAL CUBE does. Typically the user will send statement to create the UDM inside local cube file, and then statement to process it. The exact XML for is easiest to get from SQL Server Management studio by scripting existing cube as "CREATE..." into new XMLA query window.
3. Every time the user wants to open the password protected local cube file, he must add "Encryption Password=abrakadabra" to the connection string. While most of the client tools allow to modify connection string - this method is not very user friendly. And for tools such as Excel, it is not really possible to specify additional connection string parameters on connect. Needless to say, that storing connection string with password in it inside connection file such as .odc or .oqy is not secure at all. I hope, however, that the client tools vendors will take a lead and change their UI for connecting to the cube to allow users to specify encryption password. I know that all the leading ISVs are reading my blog - so you can consider it as call to action :)
Published Wednesday, March 8, 2006 1:01 AM by mosha
Anonymous comments are disabled
Privacy Statement