Connecting to MSSQL 2005 SSAS cube using sql server authentication

Question

Connecting to MSSQL 2005 SSAS cube using sql server authentication

Nilesh Mirajkar

SSC Enthusiast

Points: 192
More actions
August 5, 2008 at 5:30 am

#191983

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.

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

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

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.

Nilesh Mirajkar SSC Enthusiast Points: 192 More actions · Answer 2

Thanks,

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

MuditGupta SSCrazy Points: 2596 More actions · Answer 3

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

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.

rockvilleaustin SSC-Addicted Points: 412 More actions · Answer 5

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

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

Thanx!

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

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.