When you move to an Azure SQL Database, the idea is that you are working within a database, not an instance. In fact, for the most part, you don't access the master database at all. There is a logical SQL Server, which provides logins and connections, but you shouldn't be really writing an application that queries a master database.  Your application should be contained inside the database.

That being said, there are times you might want to query master. For example, you might want to know what SQL logins exist, or perhaps, what firewall rules you have. If you attempt to do something like this:

SELECT *
 FROM master.sys.firewall_rules

You'll get this message:

Msg 40515, Level 15, State 1, Line 25 Reference to database and/or server name in 'master.sys.firewall_rules' is not supported in this version of SQL Server.

That's expected. You have a database, not an instance here.

If you need to access a DMV, then this article will show you how you can actually gain access.

Cross Database Query

I wrote about this before in another article, and we'll use the same technique here. Essentially, we'll create an external table to query data in master. Let's walk through this for firewall rules. In order to do this, we'll open two connections in ADS/SSMS. One to the master database and one to the user database. Once you have those, let's get started.

In the user database

First, we create a master key in the user database. In my case, I have a database, called Speakers, and I'll run this command inside it:

CREATE MASTER KEY;

Once that is completed, I need to create a credential. This one needs access to master, so let's go start there. Ideally, you'd have this set up, but when you start this process, you likely haven't.

In the master database

You need the second connection as a USE statement doesn't work. Modern SSMS can switch to master and back, but let's not count on that. We want to create a login and a user.

CREATE LOGIN MasterQuery WITH PASSWORD = 'ReallyStrongPassword';
go
CREATE USER MasterQuery FOR LOGIN MasterQuery;
go

Once we have this, we go back to the user database.

In the user database

We now need to create a Database Scoped Credential, which will be used to access the master database, essentially treating it as though this is another PaaS database. We will use the username from our login above as the Identity parameter and the password as the Secret. The code looks like this:

CREATE DATABASE SCOPED CREDENTIAL MasterQueryCredential
WITH IDENTITY = 'MasterQuery', SECRET = 'ReallyStrongPassword';
GO

Now that we have a credential, we can create an External Data Source. This tells our query where the data is. In this case, we'll pick the server and master database, and then add our credential to the code. We run this:

CREATE EXTERNAL DATA SOURCE MasterDatabase
WITH
(
    TYPE = RDBMS,
    LOCATION = N'dkranchapps.database.windows.net',
    DATABASE_NAME = N'master',
    CREDENTIAL = MasterQueryCredential
);
GO

The next step is to create an External Table that points to the place we want to access. We do this by using the same schema as the target table/view. For me, that's the DMV definition for sys.firewall_rules. This is a normal table, so let's create it with this code:

CREATE EXTERNAL TABLE dbo.sys_firewall_rules
(
    id INT,
    [name] nvarchar(128),
    start_ip_address VARCHAR(45),
    end_ip_address VARCHAR(45),
    create_date DATETIME,
    modify_date DATETIME
) WITH
( DATA_SOURCE = MasterDatabase,
SCHEMA_NAME = 'sys',
OBJECT_NAME = 'firewall_rules');

Once that is complete, I'm ready to try and query the DMV. Let's do this. In Azure Data Studio, I have a connection to the database, and I can run a query:

I can even query this with a column list and WHERE clause. After all, it's just a table.

I could add another external table for sql_logins using similar code, but I'd have issues with permissions. Security roles are tightly controlled, and should be. There aren't server level roles, so I'd need to add a user to the loginmanager role with this code (in the master database):

ALTER ROLE loginmanager ADD MEMBER MasterQuery;

Once that is done, I can do this:

CREATE EXTERNAL TABLE dbo.sys_sql_logins
(
    [name] nvarchar(128),
    principal_id int,
    sid varbinary(85),
    type char(1),
    type_desc nvarchar(60),
    is_disabled int,
    create_Date DATETIME,
    modify_date DATETIME,
    default_database_name SYSNAME,
    is_policy_checked bit,
    is_expiration_checked bit,
    password_hash varbinary(256)
) WITH
( DATA_SOURCE = MasterDatabase,
SCHEMA_NAME = 'sys',
OBJECT_NAME = 'sql_logins');

and then query the table. From my user database, I now see logins.

Conclusion

The ability to query the master database for your logical SQL Server can come in handy. Using Database Scoped Credentials and External Tables, this is possible but be wary of the security implications. If someone can access the table, they can get access to information in the master database.