Office 365 “Access denied by Business Data Connectivity” connecting to SQL Server / Azure

I haven’t wrote a blog post on SharePoint in awhile but this one seemed worthy of the effort.

I’ve been experimenting with Office 365 lately and all I wanted to do was add a connection to a Windows Azure SQL Database and surface it up as an external content type.

There is of course no straightforward documentation on how to do this but plenty of content advising that you can. I walked through the motions of creating a DB. I then went into the secure store and created a Target Application ID (Removing from User Name and Password) and then set the user name and password (a separate step).

Next I loaded SharePoint Designer (why? because I couldn’t find NEW External Content Type button anywhere else) and created a new external content type. I selected the appropriate Application ID and tried to connect and was met with the ever so useful error:  “Access denied by Business Data Connectivity”

I’ve searched for quite a while and the most common resolution is someone saying that you cannot connect to SQL Azure directly and to use WCF wrapper or some 3rd party product.

I hate writing wrappers for things that should be simple so I kept looking. I finally tracked it down and I’m blogging about it so I can look it up later when I forget and hopefully help someone else 🙂

Step 1:

In the SharePoint admin center click BCS then Manage BCS Models and External Content Types
step1

Step 2:

Select Set Metadata Store Permissions
step2

Step 3:

Add your user account and select appropriate permissions.

You will now be able to complete creation of the external content type. You will need to come back in here and use the Set Object Permissions once it is created..

Hope this helps!

Windows Azure PowerShell Cmdlets 2.2 Released


Windows Azure PowerShell Cmdlets (v2.2)

Download the Latest Release

We have a brand new release of the Windows Azure PowerShell cmdlets that we hope will open up new opportunities to automate and manage your Windows Azure deployments along with just making your life easier as a PowerShell user. So what is new? Let’s take a look!

Scripting Usability Improvements

If you have used the cmdlets for any amount of time one thing that has likely annoyed you is the requirement to pass -SubscriptionID, -Certificate, -StorageAccountName and -StorageAccountKey around to almost every cmdlet. This design made it almost impossible to use from a command shell and only lended itself to easily being used from a script.

We have introduced three new cmdlets to make your life easier in this respect:

  • Import-Subscription
  • Set-Subscription
  • Select-Subscription

Set-Subscription and Select-Subscription

Set/Select Subscription allows you to specify the SubscriptionID, Certificate, DefaultStorageAccountName and DefaultStorageAccountKey and save them in session state. What this means is once you call these cmdlets you do not need to pass those arguments to every cmdlet any more. They will just use the data from session state and save you a  ton of typing. These cmdlets do support multiple subscriptions. Just call Set-Subscription once for each subscription you need to use and then call Select-Subscription to set the current subscription.

One thing that is important is using Set/Select-Subscription should be used mutually exclusive from passing the same data as parameters. In some cases it may work fine and in others you may get strange errors about -SubscriptionID or -Certificate not being known parameters.

Example:

     $cert = Get-Item cert:\CurrentUser\My\{your cert thumbprint}
    Set-Subscription -SubscriptionName "mysub" -SubscriptionId {your subscription id} `
                     -Certificate $cert `
                     -DefaultStorageAccountName "{your storage account}" `
                     -DefaultStorageAccountKey "{your storage key}"

    Select-Subscription -SubscriptionName "mysub"
    Get-HostedService -ServiceName "myService"
    

Import-Subscription

Import-Subscription allows you to import a .publishingsettings file that was previously downloaded from: https://windows.azure.com/download/publishprofile.aspx?wa=wsignin1.0.

This cmdlet adds the embedded management certificate into your local certificate store and saves an xml file that the cmdlets can use to automatically import the subcription information into your PowerShell session.

Used in conjunction with the new Set-Subscription and Select-Subscription cmdlets it makes for an easy way to get setup for the first time without having to deal with manually creating/importing the management certificates.
An example:

     Import-Subscription -PublishSettingsFile "C:\WindowsAzure\Field_ mwasham-1-15-2012-credentials.publishsettings" `
                        -SubscriptionDataFile "c:\WindowsAzure\mysub.xml"

    Set-Subscription -SubscriptionName "mysub" -SubscriptionDataFile "c:\WindowsAzure\mysub.xml" `
                     -DefaultStorageAccountName "{your storage account}" `
                     -DefaultStorageAccountKey "{your storage key}"

    Select-Subscription -SubscriptionName "mysub"
    

Windows Azure Traffic Manager Support

We’ve added the ability to fully manage and customize your deployments that use Windows Azure Traffic Manager.

Windows Azure Traffic Manager Cmdlets

  • New-TrafficManagerProfile
  • Get-TrafficManagerProfile
  • Remove-TrafficManagerProfile
  • Set-TrafficManagerProfile
  • Get-TrafficManagerDefinition
  • New-TrafficManagerDefinition
  • Add-TrafficManagerEndpoint
  • New-TrafficManagerEndpoint
  • Set-TrafficManagerEndpoint
  • Remove-TrafficManagerEndpoint
  • New-TrafficManagerMonitor

Here is an example of how you can use PowerShell to create a new profile and definition:

    # Create new Traffic Manager Profile
    New-TrafficManagerProfile -DomainName "woodgrove.trafficmanager.net" `
                              -ProfileName "ProfileFromPS"

    # Specify the monitor settings
    $monitors = @()
    $monitor = New-TrafficManagerMonitor -RelativePath "/" -Port 80 -Protocol http
    $monitors += $monitor

    # Create an array to hold the Traffic Manager Endpoints
    $endpoints = @()

    # Specify the endpoint for our North Central US application
    $endpoint1 = New-TrafficManagerEndpoint -DomainName "WoodGroveUS.cloudapp.net"
    $endpoints += $endpoint1

    # Specify the endpoint for our North Europe application
    $endpoint2 = New-TrafficManagerEndpoint -DomainName "WoodGroveEU.cloudapp.net"
    $endpoints += $endpoint2

    # Create the definition by passing in the monitor, endpoints and other settings.
    # -Status enabled automatically enables the profile with the new definition as the active one.
    $newDef = New-TrafficManagerDefinition -ProfileName "ProfilefromPS" `
					    -TimeToLiveInSeconds 300 -LoadBalancingMethod Performance `
					    -Monitors $monitors -Endpoints $endpoints -Status Enabled
    # Set the active profile version & enable
    Set-TrafficManagerProfile -ProfileName "ProfilefromPS" -Enable `
   		           -DefinitionVersion $newDef.Version
    

New-SqlAzureFirewallRule -UseIpAddressDetection

This cmdlet is not new however the -UseIpAddressDetection parameter is. It was actually released in a 2.1 release that wasn’t highly publicized. The -UseIpAddressDetection parameter allows you to add a firewall rule whether you know your external IP address or not. Perfect for getting up and running quickly in a new environment.

Here is an example of using -UseIpAddressDetection:

    New-SqlAzureFirewallRule -ServerName "{server name}" `
			    -UseIpAddressDetection `
			    -RuleName "testautodetect"
    

Set-RoleInstanceCount

This cmdlet is new and allows you to specify the number of instances for a given role. We’ve always had the ability to increment or decrement the number of instances but it wasn’t nearly as easy.

Here is an example that sets the instance count for myWebRole to 4.

    Get-HostedService -ServiceName "WoodGroveUS" | `
				  Get-Deployment -Slot Production | `
				  Set-RoleInstanceCount -Count 4 -RoleName "myWebRole"
    

New-PerformanceCounter

This cmdlet is new and just wraps the creation of perfmon counters for setting diagnostics. It makes your life easier when dealing with logging 🙂

Here is an example of adding some perfmon counters:

    function GetDiagRoles {
         Get-HostedService -ServiceName $serviceName | `
                         Get-Deployment -Slot $deploymentslot | `
                         Get-DiagnosticAwareRoles
    }
    $counters = @()
    $counters += New-PerformanceCounter -SampleRateSeconds 10 `
			    -CounterSpecifier "\Processor(_Total)\% Processor Time"

    $counters += New-PerformanceCounter -SampleRateSeconds 10 `
			    -CounterSpecifier "\Memory\Available MBytes"

    GetDiagRoles | foreach {
	    $_ | Set-PerformanceCounter -PerformanceCounters $counters -TransferPeriod 15
    }
    

Get-PerfmonLog <breaking change>

This cmdlet was introduced in the 2.0 release of the cmdlets. Some of our awesome field folks determined that it was not capturing perfmon counters correctly when there were multiple instances of a role being monitored. We have fixed this bug and it now appears to be capturing all of the relevant data. The problem is this is a breaking change. So if you are currently using this cmdlet your script will need to be updated. Thankfully, the script change is a minor one. Instead of taking a file name for -LocalPath it now takes a directory. Each instance being monitored will now get its own .blg or .csv file.

Example that uses a helper function called GetDiagRoles to download the perfmon logs.

    function GetDiagRoles {
         Get-HostedService -ServiceName $serviceName | `
                         Get-Deployment -Slot $deploymentslot | `
                         Get-DiagnosticAwareRoles
    }
    GetDiagRoles | foreach {
	$_ | Get-PerfmonLog -LocalPath "c:\DiagData" -Format CSV
    }
    

In addition to these improvements we have actually made quite a few other more minor improvements. One key request is to make a binary installation so you aren’t required to build the cmdlets before using them. That was a key priority for us and we made this happen in 2.2. Additionally, take a look at the Readme.docx in the new release for further details about other improvements.

Handy Library for Dealing with Transient Connectivity in the Cloud

The Windows Azure CAT team has built a library (available via Nuget) that provides alot of functionality around handling transient connection problems across the Windows Azure Platform with SQL Azure, ServiceBus, Cache, Configuration and Storage supported (this has been around awhile but it’s the first chance I’ve had to try it out).

To use add the TransientFaultHandlingFx reference:

Image

In addition to adding the assemblies it also adds a .chm to your project with full documentation on how to use the library.

There are numerous ways to actually use the library. For SQL Azure I would recommend reading the whitepaper the Windows Azure CAT team published.

The method I chose was to configure a retry policy in my web/app.config:

<configSections>
 <section name=”RetryPolicyConfiguration” type=”Microsoft.AzureCAT.Samples.TransientFaultHandling.Configuration.RetryPolicyConfigurationSettings, Microsoft.AzureCAT.Samples.TransientFaultHandling” />
 </configSections>
 
<RetryPolicyConfiguration defaultPolicy=”FixedIntervalDefault” defaultSqlConnectionPolicy=”FixedIntervalDefault” defaultSqlCommandPolicy=”FixedIntervalDefault” defaultStoragePolicy=”IncrementalIntervalDefault” defaultCommunicationPolicy=”IncrementalIntervalDefault”>
 <add name=”FixedIntervalDefault” maxRetryCount=”10″ retryInterval=”100″ />
 <add name=”IncrementalIntervalDefault” maxRetryCount=”10″ retryInterval=”100″ retryIncrement=”50″ />
 <add name=”ExponentialIntervalDefault” maxRetryCount=”10″ minBackoff=”100″ maxBackoff=”1000″ deltaBackoff=”100″ />
 </RetryPolicyConfiguration>

From there it’s simple to create a RetryPolicy object from the configuration:

public static RetryPolicy GetRetryPolicy()
 {
 // Retrieve the retry policy settings from the application configuration file.
 RetryPolicyConfigurationSettings retryPolicySettings = ApplicationConfiguration.Current.GetConfigurationSection<RetryPolicyConfigurationSettings>(RetryPolicyConfigurationSettings.SectionName);
 
// Retrieve the required retry policy definition by its friendly name.
 RetryPolicyInfo retryPolicyInfo = retryPolicySettings.Policies.Get(“FixedIntervalDefault”);
 
// Create an instance of the respective retry policy using the transient error detection strategy for SQL Azure.
 RetryPolicy sqlAzureRetryPolicy = retryPolicyInfo.CreatePolicy<SqlAzureTransientErrorDetectionStrategy>();
 
return sqlAzureRetryPolicy;
 }

You can pass the RetryPolicy object to the extension methods (for ADO.NET in my example):

sqlCon.OpenWithRetry(rp); // for SqlConnection
object rValue = sqlCmd.ExecuteScalarWithRetry(rp); // from SQLCommand

There is functionality for LINQ as well.

This library not only makes your code robust but can save you a massive amount of time too since they have already put the resources into testing/debugging it 🙂

Resetting the Passwords on all of Your SQL Azure Servers

Managing the passwords of your SQL Azure servers is now 100% scriptable. It’s so easy to reset them it’s almost like magic 🙂
The code below takes your subscription id and your management certificate and returns all of the SQL Azure servers in your subscription. It takes that collection and passes them to the Set-SqlAzurePassword Cmdlet along with the new password.

Resetting the Passwords of all SQL Azure Servers in your Subscription

$subscriptionId = "Your-ID-Goes-Here"
$cert = Get-Item cert:\CurrentUser\My\YOURCERTTHUMBPRINTGOESHERE
Get-SqlAzureServer -Certificate $cert -SubscriptionId $subscriptionid | `
	Set-SqlAzurePassword -NewPassword "abracadabra0!"

Creating a new SQL Azure Server and Firewall Rule with PowerShell

Creating SQL Azure Servers and Configuring firewall rules is surprisingly easy with the new Windows Azure PowerShell Cmdlets 2.0.

Step 1: Add the WAPPSCmdlets Snapin or Module

Add-PsSnapin WAPPSCmdlets

Step 2: Create a Few Needed Variables

$subscriptionid = "your subscription id"
$cert = Get-Item cert:\CurrentUser\My\yourcertthumbprint
$adminLogin = "sqlAzureLogin"
$adminPassword = "sqlAzurePassword"

Step 3: Call the Cmdlets With Your Variables

$newServer = New-SqlAzureServer -AdministratorLogin $adminLogin -AdministratorLoginPassword $adminPassword `
			       -Location "North Central US" -Certificate $cert `
				   -SubscriptionId $subscriptionid

$newServer | New-SqlAzureFirewallRule -RuleName "EveryBody" `
			-StartIpAddress "0.0.0.0" -EndIpAddress "255.255.255.255"

Obviously, you would want to change the firewall rule if you did not want EVERY IP address to be able to connect to your server but you get the idea.

Updating SQL Azure Firewall Rules with Windows Azure PowerShell Cmdlets 2.0

You’ve deployed a few Sql Azure servers and through no fault of your own the requirement comes up to update all of the firewall rules for each of the Sql Azure Servers.

No Problem!

Adding new SQL Azure Firewall Rules

Get-SqlAzureServer -Certificate $cert -SubscriptionId $subscriptionid | foreach {
  $_ | New-SqlAzureFirewallRule -RuleName "NewRule1" -StartIpAddress "0.0.0.0" -EndIpAddress "1.1.2.2"
  $_ | New-SqlAzureFirewallRule -RuleName "NewRule2" -StartIpAddress "100.1.0.0" -EndIpAddress "100.15.0.0"
}

Removing Rules is Just as Easy

Get-SqlAzureServer -Certificate $cert -SubscriptionId $subscriptionid | foreach {
  $_ | Remove-SqlAzureFirewallRule -RuleName "OldRule1"
  $_ | Remove-SqlAzureFirewallRule -RuleName "OldRule2"
}

This of course requires the Windows Azure PowerShell Cmdlets 2.0