{"id":14,"date":"2007-04-19T20:04:50","date_gmt":"2007-04-20T01:04:50","guid":{"rendered":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/?p=14"},"modified":"2008-06-02T08:15:20","modified_gmt":"2008-06-02T13:15:20","slug":"ssas-security-different-methods","status":"publish","type":"post","link":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/14_ssas-security-different-methods","title":{"rendered":"SSAS security &#8211; different methods"},"content":{"rendered":"<p>Last week I answered question in MSDN Analysis Services forum <a href=\"http:\/\/forums.microsoft.com\/MSDN\/ShowPost.aspx?PostID=1464926&amp;SiteID=1\" target=\"_blank\">thread<\/a> that at first appeared to be very simple. Somebody asked how do you control access to SSAS cubes if\u00a0information about users and groups\u00a0is inside SQL Server table and not in the Active Directory.\u00a0I answered that you cannot do that. This was my understanding from reading Books Online and various articles.\u00a0I was sure that Microsoft SQL Server Analysis Services 2005 works just with integrated security.<\/p>\n<p><!--more--><\/p>\n<p>For clients that do not use Active Directory\u00a0you have an option to setup\u00a0OLAP\u00a0HTTP access and then control security to the folder where http dlls are.\u00a0This does\u00a0work, but you compromise security, as\u00a0all\u00a0people have the same access rights to database. So you\u00a0have to implement additional security methods in the front end and in the firewalls (example access to http folder is allowed just for the specific front end, etc).<\/p>\n<p>But I was\u00a0wrong with my answer. <a href=\"http:\/\/www.ssas-info.com\/component\/option,com_bookmarks\/Itemid,78\/task,view\/id,50\/\" target=\"_blank\">Mosha Pasumansky<\/a>\u00a0noticed that thread and replied that it is possible to do this if you have middle tier. And he actually describe\u00a02 different methods on how you can do this. All you need to do is add additional parameters into connection string and then setup matching SSAS role security.<\/p>\n<p>Method 1:<\/p>\n<p>User connects to middle tier application, lets say IIS. IIS is setup to connect to SSAS as some named user with certain assigned rights. Then you middle tier application dynamically builds SSAS connection string with additional parameter&#8221;Roles=Role1,Role2,Role3&#8243;. This parameter specifies what additional roles have to be assigned for that specific user.<\/p>\n<p>Method 2:<\/p>\n<p>Sames as Method 1, but this time into connection string you pass parameter: &#8220;CustomData=appuser1&#8221;. Here appuser1 is any string that you use to identify specific connection. Then in SSAS role definitions you can use function CustomData() in the\u00a0similar way as you would use function UserName() and retrieve value you pass from connection string.<\/p>\n<p>Note: You can find\u00a0example on how to use UserName() to setup dynamic security in <a href=\"http:\/\/www.ssas-info.com\/analysis-services-articles\/51-security\/385-using-username-to-control-data-access-and-default-member-in-ssas-2k5-carrie-williams\" target=\"_blank\">this paper by Carrie Williams<\/a>.<\/p>\n<p>Both of these methods are not very well documented. I have not tested them, as I just found about them now.\u00a0But it is good to know that such\u00a0security options exists.<\/p>\n<p>\u00a0Vidas Matelis\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Last week I answered question in MSDN Analysis Services forum thread that at first appeared to be very simple. Somebody asked how do you control access to SSAS cubes if\u00a0information about users and groups\u00a0is inside SQL Server table and not in the Active Directory.\u00a0I answered that you cannot do that. This was my understanding from [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/14"}],"collection":[{"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/comments?post=14"}],"version-history":[{"count":0,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/14\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/media?parent=14"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/categories?post=14"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/tags?post=14"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}