Connecting to MSSQL 2005 SSAS cube using sql server authentication

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » Connecting to MSSQL 2005 SSAS cube using sql...

Connecting to MSSQL 2005 SSAS cube using sql server authentication

Rate Topic

Display Mode

Topic Options

Author

Message

Nilesh Mirajkar

Posted Tuesday, August 05, 2008 5:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2008 10:48 PM
Points: 2, Visits: 9

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.

Post #546634

Michael Earl-395764

Posted Tuesday, August 05, 2008 6:35 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

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.

Post #546680

Nilesh Mirajkar

Posted Tuesday, August 05, 2008 7:20 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2008 10:48 PM
Points: 2, Visits: 9

Thanks,

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

Post #546717

sql-oholic

Posted Tuesday, August 05, 2008 9:48 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 08, 2012 12:49 PM
Points: 339, Visits: 560

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

Post #546853

Michael Earl-395764

Posted Wednesday, August 06, 2008 4:56 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

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.

Post #547388

rockvilleaustin

Posted Tuesday, September 28, 2010 1:15 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 2:49 PM
Points: 168, Visits: 107

Is it the same for connecting from Excel to SSAS cubes?
You can only use windows authentication to connect Excel to SSAS cube?

Thanx!

Post #994757

stevefromOZ

Posted Tuesday, September 28, 2010 7:58 PM

SSCommitted

Group: Moderators
Last Login: 2 days ago @ 11:00 AM
Points: 1,763, Visits: 3,187

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.

Post #994892

« Prev Topic | Next Topic »

Permissions