Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I use sql server 2012, and have multi cube.
I can connect to my created cube from Excel (Data Tab\Connections) with windows authentication. How can set permission to SQL Server user on my cube. and how can connect to cube with SQL Server user.
thanks in advance.
It's possible and not ;) Basicly SSAS allow only to have a Windows Authentication. If you want to restrict some data/cubes and so on you can prepare a role and everything should work fine.
Another solution that you can use is the dynamic security. You can add to ssas a table with a user mapping and then modify the cube to return only atributes for current user (windows user)
Last solution that match the best for you, but is not good at all is to share the ssas via http (IIS Server and msmdpump.dll). Than you can connect to ssas using for example one windows account and than in the CUstomData connection stirng parameter send the user sql id and check this custom data in the ssas via custom assemblies. It's a horros in management so i am not recommanding this solution. Addictionally if you will want to connect to this cube from Excel you will write some addin to excel or teach the user to prepare a connection string... Don't do that..
You can also write a script using AMO to crate dynamicly the ssas Role. You can read the sql user make for him the ssas role and set what he can see in your cube.
Maybe the best solution for you will be to use the SSRS reports where you can easily set some custom authentication, what do u think?
Not possible; a connection to Analysis Services can only be established using a Windows principal.
From the examples at ConnectionStrings.com, it appears you can use either integrated security, or provide the Windows principal user name and password over secure HTTP (note: I don't know if this is possible using Excel). If it's available, that might be a good solution for you, since I'm assuming the idea was to get away from integrated security, rather than necessarily use a SQL login.
