Using SQL Server OLAP Security Functions

[ivankwk]

Hi all,

I was wondering if SQL OLAP uses Windows Authentication to filter off the OLAP data from respective users
America Sales VP only has access to America Sales Info while the CEO has access to all regions.

How can this be achieve if i intend to distribute the cubes via web where authentication using Windows Authentication is virtually impossible.

Thanks.

Ivan

 

[AMJackson]

Depends on how you are viewing the cubes

You have two choices:
1) Expose the cubes as http cubes (or iCubes)
2) If you are using some form of portal, then you may need to use Kerberos to provide a secure token to get into the server.

Some details:
1) To do iCubes, you need to copy msolap.asp into the root of your IIS server (running on the same machine as the OLAP server). You will need to turn off anonymous authentication but then cube roles should be applied correctly (for the logged on user). When the connection is made, the server name would just be

http://myserver

2) If you've got some 3rd party doing the distribution for you then depending on how that functions, you may need to be running Kerberos to get security to work. This is down to NTLM not supporting impersonation hops between machines. MSAS only supports NTLM and Kerberos security (AFAIK)

Hope this helps...

 

[MDXer]

ivankwk,

This is very much doable using Custom Security roles. However, Custom Security roles do work off of NT Users and Roles. THis does not mean that you cannot do this over the web it just requires a bit more work. Some options you can use are:

1) IF using IIS you can set the IIS Security to NT Auth.
This will force users to authenticate against the
domain. You would mapp these accounts to your
dimensional security roles.
2) Create a isapi DLL file that takes a Basic
authentication and makes it a NT Authentication.

To set up The Security Groups in Analysis Services
1) Expand the cube in the treeview that you want to add
the role to.
2) Right Click on "Cube Roles" and select "Manage Roles"
3) Click "New"
4) On the Membership Tab give the role a name, and add the
NT users or NT groups.
5) Go to the Dimensions tab (This is where the security
definition will be defined)
6) In the "Rule" Column Change "Unrestricted" to "Custom"
for the dimension that you want security on.
7) Click the elipse (...) This will open the Security
editor.
8) Make your definition and save it.

CAUTION If your dimensions are large and you define numerous Custom Roles on your dimensions it can affect performance.

The method that Analysis Services uses for Custom Security is not to display what a user has rights to BUT to create a Copy of the dimension in memory that is populated with only what the user has rights to see. The copy exists until 1) The Olap Service is restarted or 2) The server is rebooted.



"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[Acius]

Hi,

Is there a way of getting Analysis Services to authenticate against an SQL Server database user?

Thanks

 

[MDXer]

Acius,

Yes, Under the datasource properties you can choose either sql or domain user. Just be sure that you opt for password saving.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[Acius]

Thanks MDXer,

That's fine for the Analysis data source.
Since we can't rely on domains we were trying to figure out if we could authenticate the cubes (and set appropriate cube roles) against the SQL Server database users (since users are used to these user ids/passwords)

Cheers

 

[MDXer]

If your wanting to authenticat cube users against your SQL database users then the answer would be no. You could possibly make it work using a security cube. If your using Active Directory I would suggest creating AD roles and assigning them roles to your cubes.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck