Cannot connect to SQL Azure using a Linked Server

Question

I've been following this article here:

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

on how to setup a Linked Server from SQL Server 2008 R2 to an SQL Azure instance. I am using SQL Native Client 10.0 as the ODBC driver and judging by the default databases that are shown the connection is valid; however when I attempt to establish a linked server as the above article suggests; I get the following error:

OLE DB provider "MSDASQL" for linked server "Azure_Test" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

Now I've seen a few forum posts (Nothing conclusive unfortunately) suggesting it may be a 64bit issue. Has anyone successfully setup a Linked Server to SQL Azure from a local instance?

EDIT: Davids answer was indeed correct; just thought I'd share this awesomeness I found you can do now with a linked server:

DELETE OPENQUERY (AzureTest, 
'SELECT * FROM [AzureDB].static.MyTable');

INSERT OPENQUERY (AzureTest, 
'SELECT * FROM [AzureDB].static.MyTable')
SELECT *
FROM static.MyTable

SELECT * FROM OPENQUERY(AzureTest, 'SELECT * FROM [AzureDB].static.MyTable')

score 4 · Accepted Answer · answered Aug 25 '11 at 17:26

I have done this, following the steps that are listed in the document that you linked to.

Here were the two SQL statements that I needed to make it work for me:

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

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Azure_ODBC1',
@useself=N'False',@locallogin=NULL,@rmtuser=N'piqBackup@azureServer',@rmtpassword='########'
GO

I set my ODBC connection up as a SYSTEM DSN (I originally set it as a User DSN and had some issues). I just used my server name (no 'tcp:' in front) -- azureServer.database.windows.net. Once I set up the DSN and ran the three statements above, it worked fine. I could execute this statement without issue:

SELECT * FROM Azure_ODBC1.piqBackupData.dbo.BackupData

I'm not sure if this helps or not - but I hope it does. Good luck!

That was it; System DSN. I was using User DSN; as soon as I switched to System DSN it worked perfectly. I did have issues accessing some tables with a standard `SELECT * FROM Azure_ODBC1...` connection however OpenQuery worked for all tables: `SELECT * FROM OPENQUERY(Azure_ODBC1, 'SELECT * FROM dbo.MyTable')` — Luke Merrett, Aug 26 '11 at 08:40

Cannot connect to SQL Azure using a Linked Server

1 Answers1