SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Q: How to setup SSAS anonymous authentication - to access data from internet or another domain?

A: Sometimes you might need to setup access to your Microsoft SQL Server Analysis Services using anonymous authentication. For example, you would like to allow access to your SSAS data from another active directory domain or from the internet and you DO NOT CARE ABOUT DATA SECURITY.

To setup SSAS anonymous authentication you need to complete 3 steps :

• Change SSAS server property "Security\RequireClientAuthentication" value to False.
• Edit your connection string and add following parameter: Impersonation Level=Anonymous;
• Create SSAS database role and grant necessary permission to cubes and dimensions. Add user "NT Authority\Anonymous Logon" as a member of this role.

After such setup any user should be able to access your SSAS database without authentication.

Troubleshooting

If you are still exepriencing problems, here are few tips that might help you troubleshoot your setup:

• Check file <SSASLogFolder>\msmdsrv.log for any errors at the time you were trying to establish connections
• Start SQL Server Profiler and connect to your SSAS instance. Make sure that your default template captures "Audit Login" events and start trace. At the time of attempted connection you should see trace record with following values:

EventClass: Audit Login
NTUserName: ANONYMOUS LOGON
NTDomainName: NT AUTHORITY

If you do not see such trace record, then your connection is not reaching this SSAS instance. Check if your firewall is configured properly to let through SSAS connection (read more about SSAS Ports here).

• Read post Resolving Common Connectivity Issues in SQL Server 2005 Analysis Services Connectivity Scenarios.

Known issues

For SQL Server 2008R2 RTM there is a known bug that might prevent you from connecting to SSAS server using anonymous authentication.If this bug affects you, then after configuring everyting properly, when you try to connect to SSAS server you still will get error message:

Either the user, NT AUTHORITY\ANONYMOUS LOGON, does not have access to the AdventureWorks database, or the database does not exist.

There is no hotfix available for this bug at the time of writing this post, but Marius Dumitru from Microsoft SSAS team provided this workaround (apply this at your own risk):
On the SSAS server edit registry and change value for following key from 0 to 1: Computer\HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\Lsa\everyoneincludesanonymous

Read more about Registry key EveryoneIncludesAnonymous here. If that does not work, then add user group "Everyone" to SSAS database role and grant that role necessary permissions.

Notes

• You should never use SSAS anonymous authentication on production environment, as this will open your data to anybody.
• You can also consider configuring HTTP Access to SQL Server Analysis Services as described here.