When connecting to an Azure SQL DB endpoint (somename.database.windows.net), 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)

2016-06-04_12-49-23
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: https://feedback.azure.com/forums/223579-azure-portal/suggestions/6239996-choose-default-directory

2 thoughts on “Creating an Azure SQL DB Firewall Rule from SSMS 2016 and Error 401

Let me know what you think, or ask a question...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.