Connecting to MSSQL 2005 SSAS cube using sql server authentication

  • Is it possible to use sql server authentication to connect to cube in mssql 2005 server?

    If yes, please help me out with a sample connection string.

    Thanks in advance.

  • Not really.

    The only authentication Analysis Services supports is windows. You can give the "Everyone" group access to a cube and windows ignores the security. Or, you could create a linked server from a SQL Server, use the service account for authentication, and grant SQL Users access to run queries on the linked server, but it is quite the hack.

    Analysis Services does support http access through a SOAP/XMLA layer - using this you can also use anonymous access through a single windows login in IIS. This gets a bit tricky to configure though.

  • Thanks,

    Will try it out and let you know the outcome, hopefully positive.

  • Hey Mike,

    So, does it mean Authentication mode in SSIS is different than in SSAS.

    Because, In SSIS..we can use SQL server Authentication. But is the same not true for SSAS?

    Thanks

  • SSIS and SSAS are different products, their authentication does not have to work the same way.

    However, although you can use lots of different authentications in SSIS to connect to different data sources, connecting to an SSIS server is always using windows authentication. You may not have noticed, but when you connect through Management Studio to an SSIS Server, the authentication mode is set to windows and cannot be changed.

  • Is it the same for connecting from Excel to SSAS cubes?

    You can only use windows authentication to connect Excel to SSAS cube?

    Thanx!

  • As Mike pointed out, SSAS *only* supports Windows Auth, so yes, when using Excel, you'll be authenticated by your windows account (unless using http, in which case it's determined by your IIS settings, which will either pass your credentials or use the anon user or potentially even the app pool account - but again, these are all windows accounts). There is no concept of SQL Authentication.

    Steve.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply