none
EXTERNAL DATA SOURCE for an EXTERNAL TABLE in the same logical server RRS feed

  • Question

  • Hi,

    I have two Azure SQL databases named, for example dblocal and dbremote in the same logical server.

    I want to create an external table on dblocal that references a table on dbremote.  When I follow the various examples on the web I am successful.  My setup looks like:

    --dblocal
    CREATE DATABASE SCOPED CREDENTIAL remoteCredential WITH IDENTITY = 'myuser', SECRET 'mypassword';
    
    CREATE EXTERNAL DATA SOURCE remoteExternalDataSource
    WITH
    (
    TYPE=RDBMS,
    LOCATION='<myserver>.database.windows.net',  
    DATABASE_NAME='dbremote',
    CREDENTIAL= remoteCredential
    );
    
    CREATE EXTERNAL TABLE external_remote_table(
       ...
    ) WITH (
      ...
      DATA_SOURCE = remoteExternalDataSource
    );

    So, all good providing that Allow Azure services and resources to access this server is switched on.

    If I switch Allow Azure services and resources to access this server off then I when I query the external table I start getting errors such as:

    .., Client with IP address <Microsoft Shared IP Address> is not allowed to access the server.

    However, both databases are within the same logical server and it's the server that has the firewall rules.

    Is there a way of telling the external data source that the both databases are in the same server?  I understand that I can use elastic databases but don't want to go down that route just yet if there is an alternative because my use case for doing this isn't that critical.

    Any pointers would be much appreciated.

    Thanks

    Karl

    Monday, October 14, 2019 3:16 PM

All replies

  • Hi Karl_IOM,

    You can switch off the "Allow Azure services and resources to access server" access and use specific IP ranges that are applicable for the region your service is deployed. 

    Microsoft Azure Datacenter IP Ranges

    Please be aware that this format (Excel workbook) will be deprecated on June 30, 2020 and you should be using the new JSON format going forward.

    This file will be deprecated by June 30, 2020. Please start using the JSON files listed below. IP Ranges for each cloud, broken down by region and by the tagged services in that cloud are now available on MS Download:

    Public: https://www.microsoft.com/en-us/download/details.aspx?id=56519

    US Gov: http://www.microsoft.com/en-us/download/details.aspx?id=57063

    Germany: http://www.microsoft.com/en-us/download/details.aspx?id=57064

    China: http://www.microsoft.com/en-us/download/details.aspx?id=57062

    These JSON files are updated weekly and include versioning both for the full file and each individual service tag in that file. The “AzureCloud” tag provides the IP ranges for that entire cloud (Public, USGov, Germany, China) and is also broken out by region within that cloud. Finally, the list of service tags in the file will be increasing as we’re constantly on-boarding new azure teams to service tags.

    Monday, October 14, 2019 10:12 PM
    Moderator
  • Please let me know if you require additional assistance with this issue.

    Regards,

    Mike

    Friday, October 18, 2019 1:02 AM
    Moderator