Gotchas When Accessing an IaaS SQL Server in an Azure Cloud Service

It’s not that uncommon a scenario… You implement a spectacular BizTalk Server integration solution with well-designed Business Activity Monitoring, and you want to expose all that BAM goodness out through a custom-designed Web application built with HTML5, etc.

What might make this scenario slightly less common is when you’re hosting the BizTalk Server infrastructure in Azure. In this case, your custom Web application needs to be hosted within the same virtual network in order to access the BAM databases – unless of course you want to expose your SQL Server to the world by establishing a TCP endpoint on a port like 1433 (usually not a great idea).

Establishing that connectivity should be trivial – and it probably is if you get past a few potential stumbling blocks. I thought I might list some of them here in hopes that it may be helpful to others who try this.

1. Ensure the correct SQL Server authentication mode is enabled

With authentication to SQL, you have two options:

• Windows Authentication
• SQL Authentication

The former requires domain accounts and everything to be operating within the same network. While this is certainly possible in Azure, you may not necessarily be set up for this. For example, in an IaaS test environment you may only have a single server BizTalk installation – in which case there is no domain controller and only local machine accounts are used. Or your Web site / Web role may not be operating within the context of a domain account. In either of these cases, you have no choice – SQL authentication is the only answer.

For this you need to create an account in SQL that is not associated with any Windows profile, and then assign a password. SQL will happily let you do this – regardless of whether mixed mode is enabled or not. However, unless you have enabled mixed mode, the account will simply not authenticate – and you might be scratching your head wondering why it doesn’t when you are certain you’ve entered the right credentials in your connection string!

To enable mixed mode, right click the Server node in SQL Server Management Studio and select the Security page

. Here you’ll be able to configure the authentication mode:

For more information on setting the SQL Authentication mode, see this MSDN article.

2. Create Your SQL Authentication account without Password Policies

If you’ve determined you need SQL Authentication, then make sure that when you create the account, you un-tick the “Enforce Password Policy” option. If you forget to do this, your login won’t be usable from any application because the server requires the password to be changed on first use. Sound simple enough to fix?  Just try it… you’ll probably get this error:

The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON. (Microsoft SQL Server, Error: 15128)

The fix for this can be found in this blog post… but the best plan is to catch it at the beginning and avoid the hassle.

3. Ensure that your views have the correct permissions assigned

This is a requirement regardless of how you view the BAM data, and whether or not it is cloud hosted… but I thought it was worth mentioning here anyway. BAM requires that a view be created in order to expose the data in the BAM Portal (or in BizTalk360, if you happen to use that), even if the view is nothing more than a flat non-customised query of one of the default views. This is so that user permissions can be granted.

Typically, I create a role in the BAMPrimaryImport database (e.g. BAM_REPORT_VIEWER) and assign user accounts to that. If you forget to do this, your client simply won’t return any data.

Something to remember… if you update the definition of the custom view, make sure it doesn’t wipe out the permissions you’ve previously assigned to it. I’ve been caught out before on that one.

4. Create your Web Role in the same virtual network

It may seem obvious, but unless you plan to expose your SQL Server by opening up an endpoint on your virtual machine with port 1433 (or whatever TCP port your SQL instance is configured for), then your client needs to be installed within the same virtual network in Azure.

If your Web site is deployed within a Web role, you can add a <NetworkConfiguration> element to your ServiceConfiguration.Cloud.cscfg file before you deploy it, like so:

5. Get Your Connection String Right!

Finally, the last hurdle is likely to be getting that connection string right in your client configuration! A few things to be aware of:

• When referencing the server, you should use the internal domain name, not the external “.cloudapp.net” domain name.  You can find this by checking your domain controller config … or by launching a command prompt on your SQL Server and typing “ipconfig /all“, then looking at the “Primary Dns Suffix” (2nd line, right under the “Host Name”).
• If your port number is the default SQL port 1433, then you don’t need to specify it. Otherwise, you will need to list right after the domain name, delimited by a comma (e.g. “data source=<server_name>,<port#>).
• The name of the database server is actually the name of the cloud service, not the virtual machine name. So if your SQL server is exposed on port 1499 on a  machine named “MyVirtualMachine” hosted in the “MyCloudService” cloud service under domain “MyDomain“, then your connection string will look something like this:

connection string=”data source=MyCloudService.MyDomain,1499;initial catalog=BAMPrimaryImport;persist security info=True;user id=BAMReportViewer;password=myVeryLamePassword;”

• The exception to this rule is if you are using a single server BizTalk deployment in a cloud service with no domain controller; in this case, you will probably need to use the IP address of the virtual machine, with the port number (if applicable). Note that unless you have specifically configured a static internal IP address for the VM, this connection string may have to be updated from time to time.

If you do all these things, then you should be well on your way to establishing connectivity to your BAM database. Then you can present all that rich end-to-end business process tracking from a Web site hosted in the cloud – right from where the source data is!

If I’ve forgotten anything, please feel free to tweet me or leave a comment, and I’ll update this article accordingly.