AZURE: Error: (400) Bad Request

Have you been playing with Azure storage, trying to create Blobs, Tables or Queue’s ??

Have you been getting any of the following errors:

[Azure Blob Storage]
Microsoft.WindowsAzure.StorageClient.CloudBlobClient ccb = account.CreateCloudBlobClient();
CloudBlob cb = ccb.GetBlobReference("myblob2871618");
cb.DeleteIfExists();

FAILS with: StorageClientExecption
"One of the request inputs is out of range."
"The remote server returned an error: (400) Bad Request."
Error code: BadRequest

[Azure Table Storage]
Microsoft.WindowsAzure.StorageClient.CloudTableClient cct = account.CreateCloudTableClient();
cct.DoesTableExist("MyTable");
"Exception of type 'Microsoft.WindowsAzure.StorageClient.StorageClientException' was thrown."
StatusCode: System.Net.HttpStatusCode.BadRequest

[Azure Queue Storage]
Microsoft.WindowsAzure.StorageClient.CloudQueueClient ccq = account.CreateCloudQueueClient();
CloudQueue cq = ccq.GetQueueReference("myqueue");
cq.Exists();

"One of the request inputs is out of range."
"The remote server returned an error: (400) Bad Request."
System.Net.HttpStatusCode.BadRequest

Well, the solution is as I found a little simpler than imagined.

When I created my Storage account online I created it with my standard Camel Case name. PaulStorage. On my connection string I had the name of the storage and my key.

So it looked like:

DefaultEndpointsProtocol=https;AccountName=PaulStorage;AccountKey=XYZ…..

When you try and open the connection for my Cloud Storage Account, and pass this in, it will work fine, when you try and do anything like the statements above you get the errors listed.

I knew that for other things like Queue’s in Azure you need to specify them in lowercase or they don’t work. I asked around and found that Azure was very fussy when it came to case. Even though I had specified the Storage account as Camel case I needed to pass it in as lower case to have it work:

DefaultEndpointsProtocol=https;AccountName=paulstorage;AccountKey=XYZ…..

Once I made this remarkable at the time, discovery.. Everything started working.

One thing to note is that development storage is a lot more forgiving and you don’t have to worry about any of this.

Development storages connection string is as simple as it gets:

UseDevelopmentStorage=true

Azure Queue Explorer

Having played a great deal with Queues in the past, the opportunity to expand the abilities of a real world solution to use the concept of Queues in the cloud as a great way of doing what Queues do best, hold onto something for someone later on to pick it up, in a managed and ordered fashion.

It seems Azure Queues do just that, and I was so happy that I made a tool that enabled me to manage my Queues:

View and Create Queues
Send and Receive Messages
Peek what messages I Have
Delete Messages from the Queue

I have made this publicly available for all to use and play with, along with the all important source code, it's no trade secret on how to use them, it's just nice to have something ready to go that does it for me.

Check out the link here: Azure Queue Explorer

Source code

BizTalk 2010 On MSDN

You can now download from MSDN the full suite of BizTalk 2010. It came up today.

BizTalk Server 2010 Branch Edition (x86 and x64) - DVD (English) 660 (MB)

BizTalk Server 2010 Developer Edition (x86 and x64) - (English) 552 (MB)

BizTalk Server 2010 Enterprise Edition (x86 and x64) - DVD (English) 965 (MB)

BizTalk Server 2010 RFID Enterprise (x86 and x64) - DVD (English) 81 (MB)

BizTalk Server 2010 Standard Edition (x86 and x64) - DVD (English) 965 (MB)

Host Integration Server 2010 Developer Edition (x86 and x64) - (English) 152 (MB)

Host Integration Server 2010 Enterprise Edition (x86 and x64) - CD (English) 321 (MB)

Interestingly the developer edition which is a smaller download, actually contains the same contents as the Enterprise Edition.

What you have to do in SQL Azure that you take for granted in SQL Server

I have been using the cloud quite extensively and have had to come up with strategies for many things to adapt them to the restrictions on the cloud. I'll be doing a few posts on this as I go.

The first of these was the database setup in SQL Azure.

You usually need to store your data somewhere, I’m not going to go into the merits of Table Storage or SQL Azure, and I’ll cover that separately as the choice comes with penalties each side and needs a whole post about that.

What I do want to cover is the differences in the SQL Script you need to have to setup your databases in the first place in SQL Azure. The first thing you notice is that it’s a lot of extra work, and you notice this pretty early on. Many of the statements you are used to in SQL Server don’t work, or are partially supported, or not supported at all.

Take for example a simple user, you get by default an super user with full rights to the database. This is great to set it all up, but I want my application to have only read access to my database, so I need a new user, NEVER run your application with the privileges of the super user.

I then need to create the user, so I do the click on the database/security/users and say create user, a nice script comes up:

-- =================================================
-- Create User as DBO template for SQL Azure Database
-- =================================================
-- For login , create a user in the database
CREATE USER
FOR LOGIN
WITH DEFAULT_SCHEMA =
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N''
GO

The first thing about SQL azure is there are NO popup wizards to help you, it’s all script, however you should ideally not be using the admin console, I always use script. In SQL Server my script for this would look like so:
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET ANSI_WARNINGS OFF
SET XACT_ABORT ON


/*
*********************************************************************************************************
-- TAB SIZE 4
IDENT SIZE 4
---------------------------------------------------------------------------------------------------------
Purpose: Create a new SQL Server Login, assign a default database
and associates the Login with a database read permission


Notes: none.

Revision History:
----------------------------------------------------------------------------------------------------------
| Date | Changed By | Reference | Comments
----------------------------------------------------------------------------------------------------------
| 20100804 | Paul Somers | Create a new login with DB access | Initial release
***********************************************************************************************************
*/
USE master
GO

IF NOT EXISTS (SELECT * FROM syslogins WHERE [NAME] = N'NewReader')
BEGIN
CREATE LOGIN NewReader WITH PASSWORD=N'Password@2',
DEFAULT_DATABASE=GEO1,
DEFAULT_LANGUAGE=us_english,
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
END
USE GEO
GO
IF NOT EXISTS (SELECT * FROM sysusers WHERE [name] = N'NewReader')
BEGIN
CREATE USER NewReader FOR LOGIN NewReader
END
USE GEO
GO
EXEC sp_addrolemember N'db_datareader', N'NewReader'
GO

SET QUOTED_IDENTIFIER OFF
GO
SET XACT_ABORT OFF
GO
SET ANSI_NULLS ON
GO

The very next thing you notice about SQL Azure is that if you run this it will not work, whilst this works fine in SQL Server. You get all sorts of errors.

Take the simple, USE statement. USE master

Msg 40508, Level 16, State 1, Line 1
USE statement is not supported to switch between databases. Use a new connection to connect to a different Database.

You can’t use it… ? Between databases

Ok, so I need to connect to master run that bit and then run the master part script? Ok so we try that, and then discover that there are MANY parameters that are not allowed, well all of them to be precise.

IF NOT EXISTS (SELECT * FROM syslogins WHERE [NAME] = N'NewReader')
BEGIN
CREATE LOGIN NewReader WITH PASSWORD=N'Password@2'
--DEFAULT_DATABASE=GEO1,
--DEFAULT_LANGUAGE=us_english,
--CHECK_EXPIRATION=ON,
--CHECK_POLICY=ON
END

My traditional way of creating a user will NOT work, or will be extra cumbersome.
It even complains about this statement:

Msg 40530, Level 16, State 1, Line 3
The CREATE LOGIN statement must be the only statement in the batch.

It actually wants you to do a single line statement in the master database:

CREATE LOGIN NewReader WITH PASSWORD=N'Password@2'

That done, I have to switch over to the database I want to give the user permissions to so I run this:

CREATE USER [DataReader]
FOR LOGIN [DataReader]
WITH DEFAULT_SCHEMA = dbo
GO

EXEC sp_addrolemember 'db_datareader', 'DataReader'

To finish it off and make it more robust for repeated deployments I can use:
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'DataReader')
DROP USER [DataReader]
GO

So my final steps are:

Connection to master:
CREATE LOGIN NewReader WITH PASSWORD=N'Password@2'

Connection to my specified database where I want to give the user permsission:

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'DataReader')
DROP USER [DataReader]
GO
CREATE USER [DataReader]
FOR LOGIN [DataReader]
WITH DEFAULT_SCHEMA = dbo
GO
EXEC sp_addrolemember 'db_datareader', 'DataReader'

It’s a bit of an effort, however if you use a database project in VS you need to keep this in mind.