Hi @patwary8 ,
> but it looks like I don't have the rigt to do that.
In order to read data from a database you need permission to read the data. If you can (success) execute a SELECT query and get the data from the same machine where your SQL Server on-premises is, then you can do the task. If you do not have the rights to do so, then how do you want to do it?!?
> Therefore, I want to retrieve data from Azure
If you have the permission to read the data from the Azure SQL Database then as I already wrote before, the you can use SELECT from OPENROWSET for example.
Step 1: check that the local server has permission to use OPENROWSET (disable by default)
-- Open advance options
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
-- check permission to run OPENROWSET
EXEC sp_configure 'ad hoc distributed queries'
GO
-- if above returns: Ad Hoc Distributed Queries 0 1 0 0
-- The you need to open permission on local server. Run the following if you want to do it
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE WITH OVERRIDE
GO
Now you can run OPENROWSET and using a simple connection string you can connect to any database like any application do.
To connect Azure SQL Database you need:
Step 2: Open firewall to the Azure SQL Database -> check that you can use SSMS to connect the Azure SQL database
Now go back to the on-premises server
Step 3: use the following query to read the data from a table in the Azure SQL Database (from the local on-premises SQL Server)
SELECT c.*
FROM OPENROWSET(
'MSOLEDBSQL'
, 'Server=tcp:Use-Azure-SQL-Server-name.database.windows.net,1433;Database=Use-Database-Name-Full;UID=Use-username;Pwd=Use-password;'
, 'select * from Dimension.City;'
) c;
GO
That's all 🙂
lets use the database "WideWorldImportersDW-Full" in the Azure SQL database for the test.
Connect the local on-premises server and create new database
CREATE DATABASE patwary8
GO
USE patwary8
GO
Option 1: let's directly create new table on premises using SELECT INTO FROM the Azure Database (remember the steps to allow OPENROWSET first as explained above)
SELECT * INTO newtable
FROM OPENROWSET(
'MSOLEDBSQL'
, 'Server=tcp:server-name.database.windows.net,1433;Database=WideWorldImportersDW-Full;UID=UserName;Pwd=password;'
, 'select * from Dimension.City;'
) c;
GO
-- (116295 rows affected)
Option 2: create new table with the same structure as the remote table for the test and then INSERT the remote data
CREATE TABLE [City](
[City Key] [int] NOT NULL,
[WWI City ID] [int] NOT NULL,
[City] [nvarchar](50) NOT NULL,
[State Province] [nvarchar](50) NOT NULL,
[Country] [nvarchar](60) NOT NULL,
[Continent] [nvarchar](30) NOT NULL,
[Sales Territory] [nvarchar](50) NOT NULL,
[Region] [nvarchar](30) NOT NULL,
[Subregion] [nvarchar](30) NOT NULL,
[Location] [geography] NULL,
[Latest Recorded Population] [bigint] NOT NULL,
[Valid From] [datetime2](7) NOT NULL,
[Valid To] [datetime2](7) NOT NULL,
[Lineage Key] [int] NOT NULL,
CONSTRAINT [PK_Dimension_City] PRIMARY KEY CLUSTERED ([City Key] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
And now we can read the data from the Azure SQL Database from the local server using OPENROWSET and INSERT the data to the local table (remember the steps to allow OPENROWSET first as explained above)
insert dbo.City
SELECT c.*
FROM OPENROWSET(
'MSOLEDBSQL'
, 'Server=tcp:server-name.database.windows.net,1433;Database=WideWorldImportersDW-Full;UID=UserName;Pwd=password;'
, 'select * from Dimension.City;'
) c;
GO
