"Account has no subscriptions" when adding new firewall rule from SQL Server Management Studio

Question

1. A user is trying to connect to an Azure SQL Database via SQL Server Management Studio (SSMS v16.5.1).

2. The user's current IP address is not listed in the Azure SQL Server's firewall rules.

3. Because of this the user is shown the New Firewall Rule dialog.

4. When the user signs in using their Azure Active Directory (Azure AD) credentials they get an error message saying:

   Account has no subscriptions

5. Clicking "Details" shows:

   We experienced issues in connecting to Azure

   Additional information:
   login account does not have Azure subscriptions.
   (Microsoft.SqlServer.Management.ApplicationAuthenticationManagement)

Why is the user getting this error message and how can they add a new firewall rule for their IP address?

Answer 1

Azure support have shared the below with me, based on an Azure documentation article.

Given an Azure SQL Server, only the Active Directory admin or SQL Server admin have permissions to add firewall rules at the server level.

The above error message (somewhat obscurely) indicates that the user is neither.

My personal experience also suggests that Azure AD accounts that are assigned as Subscription Co-administrators can also set firewall rules.

If, and only if, giving the user permissions is appropriate, this can be done by one of three ways:

1. Specifying the user as the SQL Server admin.

2. Setting the server's Active Directory admin to be an Azure Active Directory Group and making the user's AD account a member of this group.

3. Making the user a Co-administrator for the Subscription via https://manage.windowsazure.com/

Be sure to understand the security implications of each option before taking action.

Answer 2

Look at these two tutorials that will be live shortly (later on 1/13/2017): https://docs.microsoft.com/en-us/azure/sql-database/sql-database-control-access-sql-authentication-get-started https://docs.microsoft.com/en-us/azure/sql-database/sql-database-control-access-aad-authentication-get-started

These tutorials discuss using server and database level firewall rules with SQL Server authentication and Azure Active Directory authentication. I hope these help, Carl

Answer 3

In Azure Portal (https://portal.azure.com/#resource/subscriptions/{Your-GUID}/resourceGroups/AdminPortalDev/providers/Microsoft.Sql/servers/wjidentityserver/databases/{Your-DATABASE_SERVER}/overview )

• Select your database
• Select "Overview"
• "Set server firewall" on the toolbar
• Add client IP on the toolbar

Then you will be able to connect from the new IP address.
I was able to add the IP address without being administrator.

The steps in more details are described in https://docs.microsoft.com/en-us/azure/sql-database/sql-database-security-tutorial#create-a-server-level-firewall-rule-in-the-azure-portal.

Other question Client with IP addres is not allowed to access the server Azuredb describes more meaningful message and the same resolution.