Connecting to SQL Azure with dbExpress

Did you notice that there is a free trial of SQL Azure? Well, I stepped into it – just to gain experience.

As the database behind SQL Azure is just SQL Server 2008 R2, there should be a possibility to access that thing with the dbExpress driver for MSSQL that ships with Delphi Enterprise, Ultimate and Architect.

What should I say, the first attempt just failed!

Although I have been aware that SQL Azure won’t work with Windows Authentication, simply changing the host name and giving the user credentials didn’t work out of the box.

Googling didn’t reveal much, but a post from da-soft pushed me into the right direction:

• You have to use “tcp:” prefix in Server parameter. This is because only TCP/IP protocol is supported by SQL Azure.
• You have to use “@server” suffix in User_Name parameter. This is probably because the farm has a lot of users from other SQL Azure accounts.

A sample host name looks like: tcp:a1b2c3d4e5.database.windows.net where a1b2c3d4e5 is your Azure host.

The corresponding user name should then look like: <user>@a1b2c3d4e5

Don’t forget to disable OSAuthentication!

Worth to mention that despite stated otherwise in the blog post, it is indeed possible to use plain dbExpress with SQL Azure.