Cross Database Query in Azure SQL Database

21Mar 2016 by Grant Fritchey 56 Comments

You can’t query across databases in Azure SQL Database… or can you?

Let’s check. I’ve created two new databases on an existing server:

dblist

I’ve created two tables on each respective database:

CREATE TABLE dbo.DB1Table (
     ID INT IDENTITY(1, 1)
            NOT NULL
            PRIMARY KEY,
     Val VARCHAR(50)
    );


CREATE TABLE dbo.DB2Table (
     ID INT IDENTITY(1, 1)
            NOT NULL
            PRIMARY KEY,
     Val VARCHAR(50)
    );

Now, let’s query the DB2 table from the DB1 database:

SELECT  *
FROM    DB2.dbo.DB2Table AS dt;

And here’s the lovely error message:

Msg 40515, Level 15, State 1, Line 35
Reference to database and/or server name in ‘DB2.dbo.DB2Table’ is not supported in this version of SQL Server.

So, like I said, you can’t do three part name cross-database queries in Azure SQL Database… oh wait, that’s not quite what I said is it. Let’s do this. Let’s create a new security credential within DB1 for a login that can get us into DB2:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'securitymatters';

CREATE DATABASE SCOPED CREDENTIAL DB2Security 
WITH IDENTITY = 'Grant',
SECRET = 'securitymatters';

Then, we’ll use that to define an external data source:

CREATE EXTERNAL DATA SOURCE DB2Access
WITH (
	TYPE=RDBMS,
	LOCATION='myservernotyours.database.secure.windows.net',
	DATABASE_NAME='DB2',
	CREDENTIAL= DB2Security);

With this, we can put Elastic Query (corrected from Polybase see note below) to work and create an external table:

CREATE EXTERNAL TABLE dbo.DB2Table (
	ID int,
	Val varchar(50))
WITH
(
	DATA_SOURCE = DB2Access);

And that’s it. If I query dbo.DB2Table from DB1, I get to see data in DB2. In short, you can do a cross database query within Azure SQL Database. Yeah, it’s going to require some setup and possibly some code modifications since you can’t use the old three part naming for performing the query, but, you can do it. Further, note that these are Standard, not Premium databases. Further further, they’re not a part of an elastic pool. It’s just using the external data source and external table to connect the two databases. However, if the one thing keeping you from moving into Azure SQL Database is the ability to query across databases, that’s gone.

 

56 thoughts on “Cross Database Query in Azure SQL Database”

  • Eirikur Eiriksson

    Quick question, in your experience how does the the performance of these compare to the normal SQL Instance’s cross DB queries?
    I’ve found that if migrating systems which either use three part references or synonyms, the polybase (java overhead) tends to be a lot slower.

    Eirkur

    Reply

  • Hey Eirikur,

    Performance is not as good. It’s not as bad as you’ll sometimes hear, but it’s not very good. While we can now do cross-database queries, we still can’t assume the same kind of behavior as we would get on an instance under our direct control. It’s different and the performance is not as good.

    Reply
  • Casey Karst

    Hey Grant,
    This is a great write up of how to do Cross-database queries in Azure. There is one piece that is incorrect though. PolyBase is only available on SQL Server, SQL DW, and APS today; and PolyBase is only a connector to HDFS (Azure Blob Storage or Hadoop distributions). In this case you were using Elastic Query. The confusion most likely came from the fact that both technologies use the same syntax (create external data source, create external table, etc.)
    For more information about Elastic Query https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-query-overview/
    For more information about PolyBase:
    https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-query-overview/

    -Casey

    Reply
  • Casey Karst

    Hey Grant,
    This is a great write up for cross database queries on SQL Database, but there is one incorrect piece of information in it. PolyBase isn’t supported on SQL Database today, only SQL Server, SQL DW, and APS. In the example above you were using Elastic Query, a feature in SQL Database that uses the same syntax as PolyBase to enable SQL to SQL connections. PolyBase today only supports SQL to HDFS connections (Azure Blobs or Hadoop Distributions).
    Great write up though!
    -Casey Karst

    Reply
  • Trevor

    While this works it certainly doesn’t scale, in the sense that if one wanted to do a large amount of cross database querying, it is extremely cumbersome to configure all of this for access via Polybase.

    Are you aware of any fundamental changes to this issue that Microsoft may have in the works?

    Reply

  • Trevor,

    No, I’m not aware of anything else coming up in this area. You’re correct that if there is a large amount of cross database queries this becomes cumbersome. I wouldn’t recommend using Azure in that case. However, you would want to ask why so much cross-database querying is going on. If it’s that massive a part of the process, why? Wouldn’t it make more sense to have those tables be a part of the database that they’re used so much from?

    Anyway, no, I’m not aware of additional work in this area. This is relatively new and the first true cross database queries of any kind that we’ve had in Azure SQL Database so far.

    Reply
  • Trevor

    I believe this sort of thing is going to be increasingly needed as we move more towards a self-service analytics approach – rather than nightly ETL jobs consolidating everything in once place, I expect we will simply read data directly from its native storage location.

    Reply

  • Trevor,

    I don’t think you’re wrong. We’re going to see more and more access from the analytics, straight to the source of the data. However, it’s quite the same thing as a cross-database query. In fact, it’s kind of worse. Someone is going to be combining data from Azure SQL Database, Azure SQL Data Warehouse, and some spreadsheet somewhere, all through PowerBI and there’s going to be no way to know for sure that the data is being combined correctly. We’re living in exciting times.

    Reply
  • Trevor

    True, from PowerBI people can be connecting to everything and there’s not really any way I can think of to stay on top of that as an administrator.

    However, if SQL Server offered something like a “connection data type” a lot of these problems would go away. Then you could write something like:

    declare myDB CONNECTION = “datasource connection string”

    SELECT *
    FROM myDB.dbo.AnyTableYouWantFromTheEntireDatabase

    I can’t see any technical reason why we must be limited to configuring references to specific single tables one by one, do you?

    Of course there are many issues such as statistics involved here that Microsoft would have to consider, but I don’t see any technical reason why it isn’t achievable.

    Reply

  • That gets into a whole can of worms. Do we expose the tables or just views where we pre-join the tables for them? I’ve yet to see a definitive answer to this problem. I sure don’t have one.

    Reply
  • Christian Haase

    Hello Grant.

    This is a very neat and simple way to do cross database queries (or elastics queries). I acme across a single problem though: How would you create views like this? Consider having two DBs were you wish to create a view that’ll show data from both DBs. Do you have an example that shows this?

    Reply
    • Adil

      while creating DATABASE SCOPED CREDENTIAL, you have to provide exact UserID and Password which you are using to connect with another database. this will be used to verify your existing permission

      Reply
      • Adil

        while creating DATABASE SCOPED CREDENTIAL, you have to provide exact UserID and Password which you are using to connect with another database. this will be used to verify your existing permission

        Reply
  • Sushant

    Hi Grant,
    I followed above all steps to access cross database, but for the last statement it gives following error.. Can you please let me know what is the problem.

    select * from dbo.DB2Table

    Msg 46824, Level 16, State 2, Line 1
    Login failed on one or more shards. Please verify that the shards are accessible and that the credential information affiliated with external data source DB2Access is correct.

    Reply
    • Adil

      while creating DATABASE SCOPED CREDENTIAL, you have to provide exact UserID and Password which you are using to connect with another database. this will be used to verify your existing permission

      Reply

  • I am getting the same error

    Msg 46824, Level 16, State 2, Line 1
    Login failed on one or more shards. Please verify that the shards are accessible and that the credential information affiliated with external data source DB2Access is correct

    Reply

  • Grant – many thanks for this helpful post.

    Do you, or anyone else on here, know if there is a way to achieve cross database “Inserts” where synonyms are being used with Azure SQL?

    This is something that we use in our standard SQL and VM environment, but have found no way to achieve the same to be able to use Azure SQL.

    Many thanks..!

    Reply

  • Hi Daniel,

    I don’t know. It’s not something that I’ve attempted. I haven’t heard of anyone else doing it either. The nature of Azure SQL Database pushes back somewhat against cross-database access. That one might not be possible currently.

    Reply
  • Michael

    Meeting the same blocker: Login failed on one or more shards. Please verify that the shards are accessible and that the credential information affiliated with external data source DB2Access is correct.
    In fact I’ve noticed this problem has been put forward a lot across forums/comments. No solution yet, even the content writer Torsten Grabs who wrote the sql-azure-cross-db-query didn’t know the answer 🙁

    Reply
  • Michael

    The login failure issue solved. The key is the credential you use when creating the external data source has to have permission to visit the external DB.

    Reply
  • Peter

    Can you provide more details on how you resolved this problem

    Login failed on one or more shards. Please verify that the shards are accessible and that the credential information affiliated with external data source DB2Access is correct

    Reply
  • Greg

    In case anyone else runs into the “login failed…” situation, for me, the problem was w/ the secret value for the credential.

    On DB2:
    create login DB2Login with Password = ‘DB2LoginPassword’;
    create user DB2User for login DB2Login;

    On DB1:
    create master key encryption by password ‘securitymatters’

    create database scoped credential DB2Security
    with identity = ‘DB2Login’
    , secret = ‘DB2LoginPassword’ — use the login’s password, not the Master key password (kind of obvious after the fact but it took me a while…)

    create external data source DB2Access
    …
    credential = DB2Security);

    create external table dbo.DB2Table
    …
    with (data_source = DB2Access);

    — make sure “DB2User” actually has select rights to whatever you’re query against.

    Reply
  • Ryan Wade

    Great post! I am not a DBA so forgive me if this question is too basic. What is the purpose of line 1: CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘securitymatters’;? Is it necessary? How is it being used?

    Reply

    • You have to set up encrypted security on the database in order to allow the databases to communicate with each other. The fake password I made up for this ‘securitymatters’ might be a little confusing. I just didn’t want to show what password I actually used.

      Reply
  • Adil

    while creating DATABASE SCOPED CREDENTIAL, you have to provide exact UserID and Password which you are using to connect with another database. this will be used to verify your existing permission

    Reply

  • Thanks. Yeah for the error people were getting, the answer is correct. The credentials are an existing user on the database. You’re not creating a new user you’re just encrypting the external tables with an existing user’s credentials.

    Reply
  • sai

    I’ve one server. inside this server, i’ve created 1 azure SQL DB and Azure DWH databases. I’m creating data source connection from Azure DWH to Azure SQL and facing below error. Can you please help us on this.

    Data Source:
    CREATE EXTERNAL DATA SOURCE myempblob
    WITH (
    TYPE = RDBMS,
    LOCATION = ‘server496.database.windows.net’,
    CREDENTIAL = cross_query_db1,
    DATABASE_NAME=’database496′
    );

    Error Message:
    Msg 103010, Level 16, State 1, Line 7
    Parse error at line: 3, column: 12: Incorrect syntax near ‘RDBMS’

    Reply

  • Hi Can you please demo on how to use the external data source in a query, I have successfully created the Master key, SCOPED CREDENTIAL and the EXTERNAL DATA SOURCE but still i’m not able to use the query to cross connect databases.
    I’m still getting the same error ‘Reference to database and/or server name in ‘tylVenturesdb.dbo.PicklistSourceAndTarget’ is not supported in this version of SQL Server.’

    Reply

    • After you create the external table, you’re just going to query that external table like it was regular table in your database. From the example code above:
      SELECT * FROM dbo.DB2Table;

      That’s it. Nothing special after you make the connection.

      Reply
  • Blake Murray

    i just tried doing this in a DEV environment and get the following error:

    Failed to execute query. Error: There is already an object named ‘DB2Table’ in the database.

    I am not sure what I missed.

    Reply

    • Did you run the very first script that creates a DB2Table in both databases? That should only be run in the second database. It’s not clear in the script, but the instructions says “on each respective database”. Sorry about that. You should have a db1 with table1 and a db2 with table2. Then do the rest.

      Reply
      • Blake Murray

        Yes that is what I did, thank you. Here is a question though, what if you have the same table name in two databases that is the same? For example, same team (azure sql server), different dbs, same table (product for example) and they need to compare products from one to the other? ETL the only option? Was hoping a way like this would work for that as well.

        Reply

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.