Create external table in Azure SQL Database

External table has some great beneficials in different cases, one of the important scenario is with external table you’ll be able to run query in multiple databases. As you know in Azure SQL server you only can run the query for the database that you connected, it means the following command would not work if you’re not in “firstdatabase” database! Azure SQL Database that doesn't allow cross database querying.

 
Select * from firstdatabase.dbo.sample  

If you run the command from any other database, you’ll get Reference to database and/or server name in 'firstdatabase.dbo.sample' is not supported in this version of SQL Server. Error message.

So, in this topic I want to explain how you create an external table and fix this issue if you need to query in multiple Azure database. I have two databases in Azure one “firstdatabase” including the sample table and the seonddatabase which I’ll go and create the external Table there.

My main table which I use in firstdatabase is Sample table, you can use your own table if you wish and if you want to test, you also can use my sample table.

 
CREATE TABLE [dbo].[Sample](
	[ProductID] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[ProductNumber] [nvarchar](25) NOT NULL,
	[Color] [nvarchar](15) NULL,
	[StandardCost] [money] NOT NULL,
	[ListPrice] [money] NOT NULL,
	[Size] [nvarchar](5) NULL,
	[Weight] [decimal](8, 2) NULL,
	[ProductCategoryID] [int] NULL,
	[ProductModelID] [int] NULL,
	[SellStartDate] [datetime] NOT NULL,
	[SellEndDate] [datetime] NULL,
	[DiscontinuedDate] [datetime] NULL,
	[ThumbNailPhoto] [varbinary](max) NULL,
	[ThumbnailPhotoFileName] [nvarchar](50) NULL,
	[rowguid] [uniqueidentifier] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

How to configure Azure External table in Azure SQL Database in six steps!

1- Create Master Key

The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. When it is created, the master key is encrypted by using the AES_256 algorithm and a user-supplied password. See all details here

 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'UseStoragePassword'

2- Create the login in Master database

 
CREATE LOGIN dbuser WITH PASSWORD = 'SetStrongPassword22'

3- Create the Login in source database

Create the user in source database, in my case firstdatabase. then granted to the database. I only give read access to the user if you need more you can use other database roles.

 
CREATE USER dbuser FOR LOGIN dbuser
EXEC sp_addrolemember [db_owner], 'dbuser'

4- Create the credential

now time to connect to the target database (in my case 'seconddatabase' and create the credential like the following command.

 
CREATE DATABASE SCOPED CREDENTIAL MyTestCredential 
  WITH IDENTITY = 'dbuser', SECRET = 'SetStrongPassword22'

You can check the credential with the following command.

 
SELECT * FROM sys.database_scoped_credentials

5- create data source

Now in the target 'seconddatabase' you can create the data source. I use the credential which I've created in step 4.

 
    CREATE EXTERNAL DATA SOURCE SQLSample 
 WITH 
 ( TYPE = RDBMS,
   LOCATION='dbportal2022.database.windows.net',
   DATABASE_NAME = 'firstdatabase',
   CREDENTIAL =  MyTestCredential
 );

If you check the “External resource” under the database name you can see the data source which you have created in previous step.

With the following command in SSMS you also see the external datasource in the target database

 
SELECT * FROM sys.external_data_sources

6- Create External table in the second database

In the target database 'seconddatabase' create the external table like the following T-SQL commnad.

 
CREATE External TABLE [dbo].[Sample](
	[ProductID] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[ProductNumber] [nvarchar](25) NOT NULL,
	[Color] [nvarchar](15) NULL,
	[StandardCost] [money] NOT NULL,
	[ListPrice] [money] NOT NULL,
	[Size] [nvarchar](5) NULL,
	[Weight] [decimal](8, 2) NULL,
	[ProductCategoryID] [int] NULL,
	[ProductModelID] [int] NULL,
	[SellStartDate] [datetime] NOT NULL,
	[SellEndDate] [datetime] NULL,
	[DiscontinuedDate] [datetime] NULL,
	[ThumbNailPhoto] [varbinary](max) NULL,
	[ThumbnailPhotoFileName] [nvarchar](50) NULL,
	[rowguid] [uniqueidentifier] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL
) 
WITH (DATA_SOURCE = [SQLSample])
GO

After creating external table you can see it under the table like the below screenshot, you can can use the TSQL command

 
SELECT * FROM sys.external_tables

Test configuration

Now, lets test the configuration, as I mentioned before we do not have the sample table in our secondary database, we have this table as an external table.

 
SELECT * FROM dbo.sample

I deleted both databases and the resource group after testing.