0

I signed up for an Azure account and deployed a website together with a database which all went very smoothly.

First I had it on the free service, but after experiencing performance issues, upgraded it to the shared model (which has the same performance). The "linked resource" database is a web-edition database. In the portal it looks like this:

enter image description here

The following code is used to enable cache invalidation in the Admin part of my website:

ConnectionStringSettings Config = ConfigurationManager.ConnectionStrings["MyConnectionString"];
System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(Config.ConnectionString);
System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(Config.ConnectionString, "MyTable");

It works on my local SqlServer Express, as well as on a live database at one of my hosting providers. Only on Azure, I get the following exception:

System.Data.SqlClient.SqlException (0x80131904): Could not find stored procedure 'sp_addrole'.
Cannot find the user 'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess', because it does not exist or you do not have permission.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Web.Caching.SqlCacheDependencyAdmin.SetupNotifications(Int32 flags, String table, String connectionString)

So it seems like a stored procedure and a 'user' (?) are missing. I'd expect the web edition to have these features by default?

I would have gone for the Express edition if it were available, however I only get to choose between "Web Edition" and "Business Edition" from the Azure control panel.

Any way to get this working?

Louis Somers
  • 2,560
  • 3
  • 27
  • 57
  • It is not clear from your question if you are using Windows Azure Websites (WAWS) or Cloud Services (WACS)? With WAWS there are limitation about what you could configure however with WACS you definitely can do it. If you make it clear your deployment type, I sure can help you. – AvkashChauhan Mar 15 '13 at 18:17
  • I think it's Azure, I added a screenshot of the portal. I didn't know Microsoft had two cloud services. Why would they do that? Do they need internal competition to stay in front of the rest? :-) – Louis Somers Mar 15 '13 at 22:31
  • Azure Websites and Azure Cloud Services are very different internally however looks similar from outside. You do have Windows Azure Websites. – AvkashChauhan Mar 15 '13 at 22:39

1 Answers1

1

You are using SQL Database and calling stored procedure sp_addrole. As it is reflected in the error that this stored procedure is not available.

If you look at link below you will see that sp_addrole is not supported SP in SQL Database: http://msdn.microsoft.com/en-us/library/windowsazure/ee336237.aspx#sqlazure

The following table lists the security stored procedures that 
Windows Azure SQL Database does not support:

sp_addrole sp_dropremotelogin sp_helpuser

So what you really need is have such work item done directly on SQL database first instead of using it directly from your code.

AvkashChauhan
  • 20,495
  • 3
  • 34
  • 65
  • Thanks, that 's a helpful link. What do you mean with doing it directly o SQL? I can access the database via SSMS. I could try adding the user and role manually and run the EnableNotifications procedure again, is that what you mean? By the way, do you know what kind of caching is meant [here](http://www.windowsazure.com/en-us/pricing/details/#header-8)? (scroll down till after the CDN stuff). $45,- for 128Mb per month sounds like quite allot of 'cache' to me. Does this mean that caching is not supported at all by default? In that case there's nothing to invalidate after all. – Louis Somers Mar 15 '13 at 23:04
  • 1
    Yes, you can use SSMS for that. The price on the link are for Shared case which you sure can access from Azure Websites. Keep in mind that there is another caching called "Role Based caching" and that is only for Azure Cloud Service. This link will be super helpful for Shared Cache http://msdn.microsoft.com/en-us/library/windowsazure/hh697519.aspx – AvkashChauhan Mar 15 '13 at 23:35