title: Is it possible to use cross database queries in both Azure and SQL?
body: <p>I am working on a project designed to run on azure platform using Azure database and Locally using local sql server. We have stored procedure that contain cross database calls. However It doesn't works on Azure server.I need cross database queries that capable of working in both azure and local sql server.</p>
  
  <p>We an use Elastic Query which allows us to query across Azure SQL Databases </p>
  
  <p><a href="https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-query-overview/" rel="nofollow noreferrer">https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-query-overview/</a></p>
  
  <p>We can setup external data source in azure server Using following code. In this case we can execute cross database calls likes joining tables in a single database.</p>
  
  <pre><code> CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yourPassword';  
  
   CREATE DATABASE SCOPED CREDENTIAL yourServeradminlogin  
   WITH IDENTITY = 'yourServeradminlogin',  
   SECRET = 'yourPassword';  
  
   CREATE EXTERNAL DATA SOURCE RefmyDemoDB2  
   WITH  
   (  
      TYPE=RDBMS,  
      LOCATION='testdbdemoserver.database.windows.net',  
      DATABASE_NAME='myDemoDB2',  
      CREDENTIAL= yourServeradminlogin  
   );  
  
   CREATE EXTERNAL TABLE [dbo].[Department](  
      [DeptId] [int] NOT NULL,  
      [Name] [varchar](50) NULL  
   )  
   WITH  
   (  
      DATA_SOURCE = RefmyDemoDB2  
   );  
  </code></pre>
  
  <p>It doesn't seem possible to use above method on our local SQL server without adding a third party database engine.
  Install something called PolyBase to do cross database query in local sql server version 2017 and above.It only supported with external data source of type such as HADOOP rather than referencing another database(or any data storage) within the sql server.</p>
  
  <p>We can accomplish that using following code</p>
  
  <pre><code>  CREATE MASTER KEY ENCRYPTION BY PASSWORD='MyP@ssword123secretword';
  
    CREATE DATABASE SCOPED CREDENTIAL mycredential  
    WITH IDENTITY = 'credential', Secret = 'secretkey' 
  
    CREATE EXTERNAL DATA SOURCE mycustomers
    WITH (
      TYPE = HADOOP,
      LOCATION =  'wasbs://azurestorage.blob.core.windows.net/',
      CREDENTIAL = mycredential
    );
  
    CREATE EXTERNAL FILE FORMAT csvformat 
    WITH ( 
      FORMAT_TYPE = DELIMITEDTEXT, 
      FORMAT_OPTIONS ( 
        FIELD_TERMINATOR = ','
      ) 
    );
  
    CREATE EXTERNAL TABLE TableName
    ( 
      [did] [int] NOT NULL,  
      [Dname] [varchar] (50) NULL 
    ) 
    WITH 
    ( 
       LOCATION = '/', 
       DATA_SOURCE = mycustomers, 
       FILE_FORMAT = csvformat
    )
  </code></pre>
  
  <p>Using HADOOP and polybase we can create an external data source in local sql server. But it create external data source of that external data storage. That is External data table located in that external storage.Exactly, my requirement is create an external data source of database within the sql server. So that i can use same corss data base query in both azure and local sql server</p>
  
  <p>Is there any solution to solve this. or any solution to run cross database queries in both azure and local sql server?</p>