Cannot connect to SQL Azure using a Linked Server
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'GOEXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Azure_ODBC1',@useself=N'False',@locallogin=NULL,@rmtuser=N'piqBackup@azureServer',@rmtpassword='########'GOI 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.BackupDataI'm not sure if this helps or not - but I hope it does. Good luck!