![]() ![]() CREDENTIAL: we need to map the correct Credential, which in this case is the one we previously created as a DATABASE SCOPE CREDENTIAL in step 5.ĬREATE EXTERNAL DATA SOURCE RemoteDatabase.DATABASE_NAME: we need to specify which database we will be pointing to in the external data source.In location we will put our logical server's FQDN or servername. LOCATION: the location will let the external data source to know where to look.TYPE: for this example and for any Azure SQL Database, we will need to specify that it is a RDBMS engine, being RDBMS a Relational Database Management System.We need to explain a little bit more about the data we input in these parameters in the WITH statement. The remote data source for this example will be called "RemoteDatabase". ![]() This reference will define where to look for the remote database, being it in the same server as OriginDB or in another server. Now we will be creating the remote data source reference. Step 6: Creating the external data source origin database SECRET: It's the password you assigned the SQL Login when you created it.ĬREATE DATABASE SCOPED CREDENTIAL AppCredential WITH IDENTITY = 'RemoteLogger', SECRET='Pa$$w0rd'.IDENTITY: It's the user that we created in RemoteDB from the RemoteLogger SQL Login.We need to create a database scoped credential that has the user and password for the login we created in RemoteDB. Step 5: Create a Database Scoped Credential in the origin database We now need to create a new Master Key in our OriginDB.ĬREATE MASTER KEY ENCRYPTION BY PASSWORD='Credentials123!' - Add a stronger password here as well! ![]() Step 4: Create a Master Key in the Origin Database (Use OriginDB) This is the SQL login created in step 2.ĬREATE USER RemoteLogger FOR LOGIN RemoteLogger We now need to create a new user for the RemoteLogger login we previously created in master database. Step 3: Create a SQL User in the remote database (Use RemoteDB) We need to create a new login in master database for the RemoteLogger that will be assigned a user in RemoteDB further in this guide.ĬREATE LOGIN RemoteLogger WITH PASSWORD='StrongPassword' - Please add a stronger password! ![]() Step 2: Create a SQL Login in the logical server's master database (Use MASTER database) If you are facing any permission errors, please refer to your organization's DBA or create the databases in your Azure Portal.ĬREATE DATABASE OriginDB(EDITION='Standard', Service_Objective='S0') ĬREATE DATABASE RemoteDB(EDITION='Standard', Service_Objective='S0') Īfter you create these two databases, we are ready to proceed with the next steps in this guide. You can create a new database with the following commands using SQL Server Management Studio using Transact SQL (T-SQL). Step 1: Create some new databases (in case you need any new database or you are just trying to do a PoC) After this, we will proceed to create the database scoped credential, remote data source, remote table, external table reference and other settings that need to be configured before attempting to do cross-database queries in Azure SQL Database. For this guide to work, we will need two Azure SQL Databases, we will need at least one SQL Login in master database and a SQL user in the remote database that references the created SQL Login in master database. This guide will cover the basics on how to create an external table reference for Cross-database querying Azure SQL Databases. How to cross-database query in Azure SQL Database ![]()
0 Comments
Leave a Reply. |