Using the @ in SQL Azure Connections
- by BuckWoody
The other day I was working with a client on an application they were changing to a hybrid architecture – some data on-premise and other data in SQL Azure and Windows Azure Blob storage. I had them make a couple of corrections - the first was that all communications to SQL Azure need to be encrypted. It’s a simple addition to the connection string, depending on the library you use.
Which brought up another interesting point. They had been using something that looked like this, using the .NET provider:
Server=tcp:[serverName].database.windows.net;Database=myDataBase;
User ID=LoginName;Password=myPassword;
Trusted_Connection=False;Encrypt=True;
This includes most of the formatting needed for SQL Azure. It specifies TCP as the transport mechanism, the database name is included, Trusted_Connection is off, and encryption is on. But it needed one more change:
Server=tcp:[serverName].database.windows.net;Database=myDataBase;
User ID=[LoginName]@[serverName];Password=myPassword;
Trusted_Connection=False;Encrypt=True;
Notice the difference? It’s the User ID parameter. It includes the @ symbol and the name of the server – not the whole DNS name, just the server name itself. The developers were a bit surprised, since it had been working with the first format that just used the user name. Why did both work, and why is one better than the other?
It has to do with the connection library you use. For most libraries, the user name is enough. But for some libraries (subject to change so I don’t list them here) the server name parameter isn’t sent in the way the load balancer understands, so you need to include the server name right in the login, so the system can parse it correctly. Keep in mind, the string limit for that is 128 characters – so take the @ symbol and the server name into consideration for user names.
The user connection info is detailed here: http://msdn.microsoft.com/en-us/library/ee336268.aspx Upshot? Include the @servername on your connection string just to be safe. And plan for that extra space…
