Getting the SQL Server PowerShell tools

They're apparently not that easy to find. The SQL Server PowerShell Tools come as part of the SQL Server Feature Pack for their respective versions. You can find the SQL Server 2014 Feature Pack (along with the PowerShell Tools) here.

Querying PowerShell for module and cmdlet information

I've recently realized that it's of incredible benefit to start designing our systems such that they're open and easily queryable by any system, particularly .NET and PowerShell. As a result, I've started designing modules for PowerShell to let my team administrate our systems. This has led me to realize that they need some of the basics of PowerShell to query what's available to them, because none of them have really used PowerShell before. The following commands should be of help to beginners:

Show the currently loaded PowerShell modules: Get-Module

Show the available PowerShell modules: Get-Module -ListAvailable

Show the cmdlets available in a particular module: Get-Command -Module [module name]

* in the case of our custom compiled C# module assembly, this is the name of the assembly (not the name of the assembly file, e.g. MyCompany.MyAssembly, not MyCompany.MyAssembly.dll)

Accessing TFS via PowerShell

I'm ashamed to admit that only today did I find out that there are PowerShell cmdlets for TFS.

You can load them into a PowerShell session by executing this:

add-pssnapin Microsoft.TeamFoundation.PowerShell

Once that's done, you can get started with the following commands:

Get-Help Get-TfsServer

See this post on Hey Scripting Guy.

AADSTS90093: User cannot consent to web app requesting user impersonation as an app permission.

According to the Azure Graph API team's blog, they've changed the way permissions are handled in Azure AD-authenticating apps.

This error has been driving me nuts for the past month while I've been able to get into an app we're writing in Azure using AD, but my team hasn't.

Here's how we fixed our issue:
1) I could get into our app (because I setup the permissions with my account in the management portal), but my team couldn't.
2) Had to go talk to one of our DevOps guys who's a Global Administrator in our Azure tenant, got him to remove the permission in the Azure AD Application, then re-add it.

Now my team could get in.

Hope this helps anybody stuck on this

SqlPackage.exe fails to deploy to Azure with error "The database platform service with type Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider is not valid."

I've recently started trying to deploy to one of my own databases in Azure using SqlPackage.exe. I've used it numerous times at work without problem, but on my own system at home, I keep running into the following error:

"Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider is not valid. You must make sure the service is loaded, or you must provide the full type name of a valid database platform service."

As it turns out, you need at least SSMS CU#6 to get a version of SqlPackage.exe recent enough to deploy to Azure with SQL Azure v12. The copyright on the SqlPackage.exe executable must be 2015 (or later). Such a version comes with the latest versions of SQL Server Data Tools (SSDT). If you're unable to use the version of SqlPackage.exe that comes with SQL Server 2014 because it's too old ("C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe"), you can use the version that comes with SSDT for Visual Studio 2013 ("C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe").

Setting up continuous Webjobs in Azure

First things first: you need to properly setup your dashboard:

This means that in the 'Application settings' area of the panel for your Web app, you need to go into the 'Connection strings' setting and add blob storage connection strings with the names 'AzureWebJobsDashboard' and 'AzureWebJobsStorage', with a type of 'Custom'.

Second: if you have continuous web jobs associated with a Web app, you need to ensure that the Web app setting 'Always On' is toggled *on*.

Setting up delegated authentication between web apps in Azure Active Directory - quirks

When setting up your Azure App ID for your web apps, it's extremely important that you follow the recommended format of "https://<your tenant name>/<your app name>"

Otherwise, delegated authentication will NOT work!!

Using ADAL in Javascript to enable use of Azure Active Directory from an AngularJS SPA

Check this out, apparently it came out last fall in Azure:

http://www.cloudidentity.com/blog/2014/10/28/adal-javascript-and-angularjs-deep-dive/

Enabling group membership claims in delegated tokens in Azure Active Directory

In your application manifest, add the following to the root object:

{
   ....
  "groupMembershipClaims": "All",
   ....
}

Fixing redirect loops in Azure web apps that use Azure Active Directory for authentication

We've recently been using Azure Active Directory to handle authentication for a bunch of our Line-Of-Business applications that we're moving into the cloud. Unfortunately, we've been noticing that in some circumstances, we encounter redirect loops that the browser can't break out of and I've been scratching my head over it. As it turns out, the loops had a common cause: going to a URL in the application that started with http://.

Since we're all good programmers here ;), we always want to be using best practices and enforcing secure connections anyway, but it also turned out to be the fix: adding [RequireHttps] to the top of our filters list in FilterConfig.cs solved the problem and closed a security hole at the same time.

As for the initial cause: why would anybody be using HTTP anyway ? It turns out that HTTP is the default scheme for URLs in the Azure portal when you're viewing the settings for an application and people were clicking on those links to go straight into the app.

Alex

Parameterizing ApplicationInsights.config

I've been working on a lot of web applications in Azure recently, and we're using Application Insights to analyze logs and instrument events in the application. Additionally, we're using MSDeploy to deploy all of our applications as we're trying to get to the point of having Continuous Delivery on-premise. Because we use multiple environments, we need to use the parameters.xml file and SetParameters files to set parameters at deployment time, meaning that we also need to parameterize the InstrumentationKey used by Application Insights to determine which instance of App Insights gets the instrumentation for which application.

This is a bit of a challenge because the XPath support for the parameters.xml file is incomplete and leaves something to be desired. At first glance, I tried to use the naive XPath expression to parameterize our InstrumentationKey:

Match="/ApplicationInsights/InstrumentationKey"

I realized that this wasn't working after checking our deployments, so after some Googling, I found the recommendation that to parameterize an *element* rather than an attribute, you need to use "/text()" in your XPath expression (which makes sense if you know enough about XPath and XQuery). So I tried it:

Match="/ApplicationInsights/InstrumentationKey/text()"

Still didn't work. Then I was stumped for a while and let things simmer.

After a couple more hours and getting frustrated, it hit me: when parameterizing another element a few weeks prior, I ran into issues because a parent element of the one that I wanted to parameterize overrode the default namespace. I checked the ApplicationInsights.config file, and sure enough, the default namespace was the non-empty namespace. I had to use a wildcard to solve the previous problem, so I tried the following:

Match="/*/InstrumentationKey/text()"

Still didn't work, much to my surprise this time. After a little bit more Googling around, I found out that this time it was because the *root* element itself used a non-empty namespace, and overrode the default. I stumbled across this response by Vishal Joshi to a comment on one of his blog posts, which included a suggestion for it using something I didn't even know you could do with XPath (and I've actually got quite a bit of experience using XPath): use the local-name query operator to match only on local name rather than fully qualified name. If you're querying a document that uses an empty default namespace, they're both the same, hence why the simple match expressions work 99% of the time. Updating my query to this:

Match="//*[local-name()='InstrumentationKey']/text()"

... worked like a charm!

Creating users in a SQL Azure database

... is not as straight forward as you would think. It has some slightly different requirements for syntax than what you'd normally use to create users in a local or on-premise SQL Server instance.

You can see the tutorial page on MSDN here.

Logging your application with App Insights instead of Windows Azure Diagnostics

We've recently started deploying applications to Microsoft Azure. Unfortunately, we don't use Cloud Apps for our web apps, and instead use MSDeploy on the command line to enable automated deployment. Consequently, we don't have any .csdef files in which to implement Windows Azure Diagnostics (WAD).

Fortunately, there's a suitable replacement for applications in Azure App Service (rather than Cloud Services): Application Insights. Not only does this provide a TraceListener for tracing your application, but also provides a whole suite of other useful diagnostics, making this something that you should really be using anyway.  Using the links here and here, I was able to get up to speed with App Insights within an hour and a half, and implement a working solution for our application.

Using the Azure Key Vault: an experiment

So, I've found this article on MSDN about how to use the Azure Key Vault for storing secrets, e.g. encryption keys. Let's see how it goes.

**UPDATE** : So, apparently we won't be able to use the Key Vault because use of the key vault at this time requires that your application be used in conjunction with Azure Active Directory, which ours does not. That's unfortunate.

A really quick note about deploying SQL Server .dacpac files to SQL Azure

When you're using SqlPackage.exe to deploy a .dacpac to Azure, you must have access to the 'master' table in order for the tool to retrieve information necessary to create a diff of the database and deploy the differences during the Publish action.

For that reason, you need the 'sa' account, because simply having 'db_owner' on the Azure database is not sufficient.

Debugging database deployments with MSDeploy and SqlPackage

I recently had problems deploying a database to Azure, and thanks to this article, I now know why. The best part of this experience is that I've learned to debug this in a much easier manner than relying on the unhelpful error message:

*** An error occurred during deployment plan generation. Deployment cannot continue.
Failed to import target model my-database-name. Detailed message Value cannot be null.
Parameter name: conn
Value cannot be null.
Parameter name: conn

The short of it is that:

1) you need to enable a log trace with these commands for dbDacFx and SSDT:

logman create trace -n DacFxDebug -p "Microsoft-SQLServerDataTools" 0x800 -o "%LOCALAPPDATA%\DacFxDebug.etl" -ets

logman create trace -n SSDTDebug -p "Microsoft-SQLServerDataToolsVS" 0x800 -o "%LOCALAPPDATA%\SSDTDebug.etl" -ets

2) Go execute the command that's causing you grief
3) Execute the following commands to stop the logging trace:

logman stop DacFxDebug -ets

logman stop SSDTDebug -ets

4) Open 'eventvwr' in the Run dialog available from the Start Menu
5) Go to the right side 'Actions' pane, and click on 'Open Saved Log'.
6) When prompted to convert the log, click 'No'.

The logs will display under the 'Saved Logs' folder in the left hand navigation tree, and you'll be able to inspect the errors that occurred. Good luck with deployment!

Adding extra files to your MSDeploy web package with MSBuild

I've recently started getting much more in depth with Azure. I've ported some web apps up into Azure, and now I need WebJobs. I've written the jobs, manually uploaded them into my web apps, tested them out and they work great. Now I need to ensure that the WebJobs get automatically deployed with my Web Application.

As it turns out, web jobs are just a sub folder with some naming conventions underneath the App_Data folder in your web application. Unfortunately, getting those files into that folder as part of the packaging process is a couple extra (non-obvious) steps.

Following this article on ASP.NET, I was able to add in the extra files. It took a lot more Googling than I care to admit to in order to find this article. As it turns out, you really need to pick your search terms well.

My website doesn't accept my Active Directory credentials and just keeps prompting me over and over. Or if it's Chrome, just gives me a middle finger straight up.

Recently, I've been having the problem of trying to reach some of the internal applications that my company develops that uses Windows Authentication. Thanks to this article:

http://www.leftycoder.com/windows-authentication-chrome-iis/

... I found out why. I had to remove the option for 'Negotiate' with Windows Authentication. The settings had changed when we moved our applications to a new IIS Web Site. Something to keep in mind.

Bitten by IIS, or lack thereof

Here at the office, we build a number of web applications. I recently started refactoring one such application to change and improve how we deploy it. As soon as I made the change to the .csproj to remove some old configurations and add new ones, I mysteriously started getting this error on our build machine (and only our build machine):

C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v12.0\Web\Microsoft.Web.Publishing.targets (2664): Filename: redirection.configError: Cannot read configuration file

After examining the changes I made, I figured "there's nothing that should be causing this. WTF?" Then after a while, I went on to our build machine and found that we didn't actually have IIS installed on the build machines (which is a good idea from a security standpoint, because there's really no reason for a build machine to have IIS installed). So why did this start suddenly ?

I went and examined the exact line that was causing the problem in the targets file, and the only thing that appeared to be including this conditional statement was the flag 'IncludeIisSettings'. Sure enough, the old configuration which we never used before and were using now had this set.

A problem with redirect loops in IIS (8.5) with ASP.NET MVC 5 (and Identity)

Ever see something like this ?

http://localhost:80/MyApp/Account/Login?ReturnUrl=%2FMyApp%2FAccount%2FLogin%3FReturnUrl%3D%252FMyApp%252FAccount%252FLogin%253FReturnUrl%253D%25252FMyApp%25252FAccount%25252FLogin%25253FReturnUrl%25253D%2525252FMyApp%2525252FAccount%2525252FLogin%2525253FReturnUrl%2525253D%252525252FMyApp%252525252FAccount%252525252FLogin%252525253FReturnUrl%252525253D%25252525252FMyApp%25252525252FAccount%25252525252FLogin%25252525253FReturnUrl%25252525253D%2525252525252FMyApp%2525252525252FAccount%2525252525252FLogin%2525252525253FReturnUrl%2525252525253D%252525252525252FMyApp%252525252525252FAccount%252525252525252FLogin%252525252525253FReturnUrl%252525252525253D%25252525252525252FMyApp%25252525252525252FAccount%25252525252525252FLogin%25252525252525253FReturnUrl%25252525252525253D%2525252525252525252FMyApp%2525252525252525252FAccount%2525252525252525252FLogin%2525252525252525253FReturnUrl%2525252525252525253D%252525252525252525252FMyApp%252525252525252525252FAccount%252525252525252525252FLogin%252525252525252525253FReturnUrl%252525252525252525253D%25252525252525252525252FMyApp%25252525252525252525252FAccount%25252525252525252525252FLogin%25252525252525252525253FReturnUrl%25252525252525252525253D%2525252525252525252525252FMyApp%2525252525252525252525252FAccount%2525252525252525252525252FLogin%2525252525252525252525253FReturnUrl%2525252525252525252525253D%252525252525252525252525252FMyApp%252525252525252525252525252FAccount%252525252525252525252525252FLogin%252525252525252525252525253FReturnUrl%252525252525252525252525253D%25252525252525252525252525252FMyApp%25252525252525252525252525252FAccount%25252525252525252525252525252FLogin%25252525252525252525252525253FReturnUrl%25252525252525252525252525253D%2525252525252525252525252525252FMyApp%2525252525252525252525252525252FAccount%2525252525252525252525252525252FLogin%2525252525252525252525252525253FReturnUrl%2525252525252525252525252525253D%252525252525252525252525252525252FMyApp%252525252525252525252525252525252F

As it turns out, IIS can generate redirect loops whenever there's a freshly deployed (installed) MVC application that hasn't had its application pool correctly setup.

Just thought I'd put this here in case anybody else runs across it.