Creating an Azure SQL DB Firewall Rule from SSMS 2016 and Error 401

When connecting to an Azure SQL DB endpoint (, your IP must be listed in that endpoint’s (server’s) firewall:

Azure SQL DB firewall showing access is allowed from Azure services but not other IPs

Figure 1: Azure SQL DB firewall showing access is allowed from Azure services but not other IPs.

If you’re connecting to your Azure SQL DB from SQL Server Management Studio 2016 and your IP address is not in the list, SSMS will offer to add it:

SQL Server Management Studio 2016 offering to add your IP to the firewall rules for Azure SQL DB

Figure 2: SQL Server Management Studio 2016 offering to add your IP to the firewall rules for Azure SQL DB.

You’ll need to sign in with a Microsoft Account or Azure AD account that has permissions to modify those firewall settings. That account’s default directory must be the directory that’s associated with the subscription where the Azure SQL DB server is created though. If it isn’t, you’ll get this error message:

An error occurred while creating a new firewall rule (HTTP Status Code 401)) (ConnectionDlg)


Figure 3: Error message while trying to add an Azure SQL DB firewall rule using an account whose default directory is not the directory associated with the subscription that contains the Azure SQL DB server.

Which as you can see isn’t exactly telling you what the problem is.

How can you change your default directory? Apparently, that’s a feature that’s been requested for more than 2 years:

Performing a Silent Install of SQL Server Management Studio (2016)

With the release of SQL Server 2016, SQL Server Management Studio (SSMS) is now a stand-alone tool. It requires no licensing and can be downloaded without as much as registering. Because it is a separate download, it requires a separate installation.

The computer labs I manage have SQL Server installed, including Management Studio. Prior to SQL 2016, Management Studio came as part of the SQL Server ISO and silently installing SMSS was part of a silent install of SQL Server. This has changed now: the SQL Server 2016 ISO does not include SSMS anymore and therefore two separate silent installations are required. (Note that silent or unattended installations of SQL Server haven’t changed in 2016.)

Here is how to silently install SQL Server Management Studio:

  1. Download the installer from
  2. Run the silent installer using the following command line:

SSMS-Setup-ENU.exe /install /quiet /norestart

How did I arrive at these switches? By running

SSMS-Setup-ENU.exe /?

you will be presented with a helpful dialog documenting the supported switches.

SSMS installer help showing the supported command line switches

Figure 1: SSMS installer showing the supported command line switches

Other supported modes are uninstall, repair, and layout. It is also possible to set the location of the log file created by the installer.

My first NuGet package: Repository and UnitOfWork patterns for EF 6.1.3

Since I’ve been developing software again, I’ve had to sharpen my skills on the latest enterprise architecture patterns and how they are used in .NET. The Repository and UnitOfWork patterns aren’t new, but implementing them correctly to achieve the most benefit without going overboard is nevertheless a learning curve. I found a presentation posted on YouTube by Mosh Hamedani that demonstrates implementing the Repository and UnitOfWork patterns using Entity Framework 6. (This video is part of a larger course he’s developed.)

I like his approach very much. Some of the plumbing work can easily be abstracted into a class library. I am sure many developers have done this already, but this was part of my learning. I had also never published a NuGet package. I figured if I wanted to manage dependencies in my own projects, it wouldn’t hurt to set that up. Because the library isn’t particularly advanced, I see no need to keep it private. So, I published my first package to the NuGet Gallery. I wasn’t expecting anyone else to stumble across that, but some did and wanted to use it.

I am encouraged by that and will continue to publish updates to NuGet. Note that at this time, I don’t have a public source code repository for this but I understand that that’s important to many. I will get the code up on GitHub sometime soon though.

Why I wish Microsoft would unify Office 365 groups and Team Sites

A group in Office 365 (link to a Sway) is a very useful concept: it allows any user to quickly create a shared mailbox, document repository, OneNote notebook and shared Calendar. Compared to a Team Site, it’s quick to set up and requires no special privileges. (That does create some potential governance issues, but I am not going to dwell on that in this post.)

Based on the URL format, a group becomes a Team Site “lite.” Unfortunately, there are some aspects of Team Sites that I like better than groups and vice versa. So here is what I wish Microsoft would do:

  • If the “group” becomes some permanent fixture of the organization (some organizations would say “task force” versus “committee,” etc.), it could be useful to allow a group to be converted to a Team Site so additional apps can be added.
  • The functionality of subscribing every member of a Team Site to a Site Mailbox would be an excellent option.
  • The functionality of subscribing to the OneDrive space of a Group, like you can create alerts on Document Libraries, would be excellent to add to Groups.

I will add that I am anxiously anticipating the release of Planner, which is of course, a Groups feature.

Categories: Office 365

Visual Studio 2015: New file marked as “checked in” instead of pending add in TFS.

I’ve recently started using Visual Studio 2015 quite extensively for a development project. Overall, my impression of the latest release is favorable. It feels snappier than older versions and the touch of color that’s been brought back compared to 2012 is most welcome.

However, I have run into an issue I believe is new to version 2015. Once in a while, a coworker or I will add a file to a project and Team Foundation Services will immediately mark the file as “checked in.” I am sure there are certain conditions under which this happens, but I haven’t determined what those are. The file of course is not checked in, VS 2015 just thinks it is. The file will not show up in the Pending Changes view. Moreover, attempting to edit or delete the file and then perform a check-in also won’t work. TFS/VS mark the file for edit or delete, but then conclude that the file doesn’t exist in the source control database and fail the check-in.

Other symptoms of this may be hard to spot. If you’ve got a large check-in, you likely won’t notice this the file isn’t in the Pending Changes list until a co-workers tells you you “broke the build.” That’s because the file will be missing from their project even after they get the latest version. You won’t see the file in Source Control Explorer, even on the machine where the file exists.

The solution fortunately is rather simple: select the file in Solution Explorer and click the Refresh icon at the top of Solution Explorer. This will get Visual Studio to realize that the file is in fact not checked in. It will instead mark the file as not under source control (you can tell because there will not be any source control icon next to it). Now, you can right-click on the file and select “Add file to source control.” The file when then be marked as “Add” and you can check-in your changeset as usual.

Categories: Visual Studio

SQL Saturday #439 Columbus 2015 to be held at Troy University

I am excited to share that SQL Saturday #439 Columbus 2015 will be hosted by Troy University’s Sorrell College of Business and the Phenix City Campus.

In about one month, on October 3, 2015, make plans to attend this free, one-day SQL Server event. Not only can you pick up some new SQL skills or refresh existing ones, it’s also a great network opportunity. Troy University is also proud of its new facility on the banks of the Chattahoochee River, dubbed the Riverfront Campus.

Troy University's new Phenix City Riverfront campus

Troy University’s new Phenix City Riverfront campus

Speaker selections have not been finalized yet, but I expect to present at least one session in addition to volunteering for the event. I hope to see you there!

Categories: SQL Saturday Tags:

ASP.NET config in Azure: Couldn’t be easier

I’ve started a new software development project recently, and the entire solution will be hosted on Azure PaaS resources, including Web Apps. Part of application development involves storing configuration settings for the application. Notably, this could include the database connection string(s). In the case of the app under development, there are Azure Active Directory, Azure Key Vault and Azure Storage Account settings (to name but a few).

Over the years, storing configuration settings in web.config has been a common practice. Visual Studio enhancements in recent versions, especially Web.config transformation, have made web.config deployment to different environments (such as test, QA, staging and production) also much easier.

On Azure, things get especially interesting though. In addition to the many settings for the different Azure resources, the concept of deployment slots on a Web App allow for different versions of the code to run in a single VM. This is useful for test, QA, A+B testing, etc. However, you might very well have the same Visual Studio build configuration for code that lives in different deployment slots.

On top of that, there are some settings that will keep the same values across deployment slots (for example, your database connection string would not change for A+B testing), but will change for other deployment slots. You’d quickly end up with an exponentially growing number of Web.config transformation files and the added risk of accidentally deploying an incorrect build configuration to a particular slot.

So, instead of trying to create Web.config transformation files for every combination of settings, you would instead add your connection string(s) and app setting values to the Azure Portal UI. This will not actually update the values in web.config, but using the standard ConfigurationManager API, will cause those values to be used instead of those found in web.config. Don’t worry, you don’t need to specify all app settings keys in the Azure Portal. Add only those you care to have Azure change for you based on the deployment slot.

In addition to being very convenient, there are also security benefits, as described in this post:

A full walk-through of creating deployment slots and how configuration settings can swap or “stick” to deployment slots, see this tutorial:

Categories: ASP.NET, Azure Development Tags: