Proactive Caching - SQL Server Notification Permissions

I did a presentation at the Melbourne SQL Server User Group this week on the proactive caching feature in Analysis Services 2005. One of the topics that I covered was the different notification methods that Analysis Services 2005 can make use of in order to discover that something has changed in the underlying relational source.

The first notification method on the list is SQL Server notifications, but you will notice that as soon as you select this option a warning message appears at the bottom of the screen.

What the message at the bottom of the screen says is:

And I heard this repeated on 2 different web casts, the US TechEd video and a live talk at TechEd 2005 in Australia, so I did not really have a reason to doubt it. It is even repeated again at the bottom of the partition storage settings after you have said OK to the proactive caching settings.

How ever this did seem strange to me. The SQL Server notifications work by watching the trace events raised by SQL Server, these are the same events that you can see using SQL Profiler. In SQL Server 7.0 and 2000 it is true that you had to be an system administrator in order to receive these trace notifications. However in SQL Server 2005 the ability to access these trace notifications can be assigned as a security privilege.

The connection for one of my proactive caching demos was set up with sysadmin rights (as I believed at the time that these were necessary) - so I took the user out of the sysadmin role and promptly got the following message:

error: OBJIDUPDATE permission denied in database 'ProactiveCaching'.; 42000. Trace: The SQL Server notification read operation failed. SQL Trace status code: 0.

And one thing to note was that this also killed my Analysis Services service. So be VERY careful of altering permissions on a production server. This may be just related to trace notifications when using proactive caching, but I did not expect it to bring the whole service down.

I then setup the connection with just db_datareader rights on the database and added the ALTER TRACE right on the server, which with the default CONNECT SQL and VIEW ANY DATABASE rights that the user got by default, left the login with the following permissions.

All that remained was to setup the proactive caching demo again. All I did was to redeploy the OLAP Database to the server and fire up my little sample application that injects random records into the cubes fact table.

Firing up SQL Profiler and having a look at the trace for Analysis Services proved conclusively that the database was in fact receiving notifications in spite of the connection not being configured as a sysadmin. And removing the ALTER TRACE permission from the login crashed the Analysis Services service in the same way that removing the sysadmin role did orginally.

I have logged this issue in ladybug here

The issue is mainly to do with misleading information, but as it stands SQL Server notifications would be hard to recommend in a production environment. Apart from the fact that the delivery of the notifications is not guaranteed (a network issue could prevent delivery) setting up the connection to the data source with sysadmin privileges is not something that a lot of DBAs would like to do.

Update 14 May 2006: I just noticed that Microsoft has responded to the bug I posted, saying that the text in the UI will be replaced and that they will look into updating the UI and fixing the documentation for the next major release.

Print | posted on Sunday, March 26, 2006 7:18 PM