Hi friends,

i Need some small help on azure sql.

I am trying to create the linked server which is used to pull the data from AZURE SQL.

i have used the below query to created the linked server..

but it is throwing in the error.

shall you please any one let me know who to  create a linked server.

--- Create DNS as well..

i want to know, what are all the setting we have to change in the SYSTEM DNS to 

EXEC master.dbo.sp_addlinkedserver
@server = N'Azure_ODBC1',
@srvproduct=N'Any', 
@provider=N'MSDASQL',---'MSDASQL', 
@datasrc=N'myazuredb'
--,@catalog='myDatabase' 
GO

 /* For security reasons the linked server remote logins password is changed to ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'Azure_ODBC1'
,@useself=N'False'
,@locallogin=NULL
,@rmtuser=N'myazuredb'
,@rmtpassword='4'

Duplicate thread.

linked server to connect to azure sql database

Hi Priya,

Unfortunately, Linked Server is not supported by SQL Azure DB. You can refer https://azure.microsoft.com/en-us/documentation/articles/sql-database-transact-sql-information/

Features that are not supported in SQL Database: Linked servers, OPENQUERY, OPENROWSET, OPENDATASOURCE, BULK INSERT

Thanks

Abhishek

• Proposed as answer by Simon_HouMicrosoft contingent staff, Moderator 15 hours 6 minutes ago

Hi friends,

i Need some small help on azure sql.

I am trying to create the linked server which is used to pull the data from AZURE SQL.

i have used the below query to created the linked server..

but it is throwing in the error.

shall you please any one let me know who to  create a linked server.

--- Create DNS as well..

i want to know, what are all the setting we have to change in the SYSTEM DNS to 

EXEC master.dbo.sp_addlinkedserver
@server = N'Azure_ODBC1',
@srvproduct=N'Any', 
@provider=N'MSDASQL',---'MSDASQL', 
@datasrc=N'myazuredb'
--,@catalog='myDatabase' 
GO

 /* For security reasons the linked server remote logins password is changed to ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'Azure_ODBC1'
,@useself=N'False'
,@locallogin=NULL
,@rmtuser=N'myazuredb'
,@rmtpassword='4'

Here is some good info, these give you info on how to setup a linked server to Azure DB:

http://blogs.msdn.com/b/sqlcat/archive/2011/03/08/linked-servers-to-sql-azure.aspx

http://azure.microsoft.com/en-us/blog/announcing-updates-to-windows-azure-sql-database/

/*Setting up an ODBC DSN

Run odbcad32.exe to setup a system DSN using SQL Server Native Client.  Or go into Control Panel\System and Security\Administrative Tools and click on Data Sources (ODBC).  Each database you create on SQL Azure needs a separate DSN and a separate linked server definition.  Use TCP, not named pipes because you will be communicating to the SQL Azure servers through the internet.  

 

Scale the creation of ODBC connections to multiple databases by modifying & importing a .REG file into the registry (regedit.exe) patterned after this sample:

 [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI]

 

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Azure_ODBC1]

"Driver"="C:\\Windows\\system32\\sqlncli10.dll"

"Server"="YourAzureServer"

"LastUser"="YourLogin"

"Database"="YourDatabaseName"

 

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]

"Azure_ODBC1"="SQL Server Native Client 10.0"

SQL Server Linked Server

An ODBC DSN and an associated Linked Server need to be created for each Database that will be queried.  Here are two sample T-SQL commands that will create a linked server and associated login: */

EXEC master.dbo.sp_addlinkedserver@server = N'Azure_ODBC1',@srvproduct=N'Any', @provider=N'MSDASQL', @datasrc=N'Azure_ODBC1'

GO



 /* For security reasons the linked server remote logins password is changed to ######## */

EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname=N'Azure_ODBC1',

@useself=N'False',@locallogin=NULL,@rmtuser=N'yourlogin@YourAzureServer',@rmtpassword=#####'

GO

 

Hi Priya,

Please look into below article and stackoverflow thread,

Linked Servers to SQL Azure

http://stackoverflow.com/questions/7191740/cannot-connect-to-sql-azure-using-a-linked-server

In which they have explained how to setup Linked Server from SQL Server 2008 R2 to an SQL Azure instance

Hi Priya,

Unfortunately, Linked Server is not supported by SQL Azure DB. You can refer https://azure.microsoft.com/en-us/documentation/articles/sql-database-transact-sql-information/

Features that are not supported in SQL Database: Linked servers, OPENQUERY, OPENROWSET, OPENDATASOURCE, BULK INSERT

Thanks

Abhishek

He's wanting to create the linked server in an on premise instance of SQL Server and connect it to Azure. This article refers to what you can do in Azure not what you can do in SQL.  You can create a Linked Server connection to Azure SQL DB in SQL Server to pull your data locally. See my post above.