October 19, 2015
When Azure was launched, we missed one important feature, the linked server, It seemed impossible to do cross database queries from your local database to the Azure database, but Microsoft listened and made it possible to do database queries that spans multiple databases across platforms.
There is plenty of hits when googled on the errors, but none of these are complete! I will write down the process of linking your server to Azure.
when I first created a linked server to Azure, I got this error "reference to database and/or server name is not supported in this version error: 405150", When trying to add a linked server from SSMS, you are not given option to set a default database. So I used Tsql instead, because without a specific catalog, your linked server will not work.
Azure doesn't allow you to alter the master db, so you have to connect directly on the database you are going to use.
What people never tell LS newbies and make you google this error "Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'sp_addlinkedserver' " and read tons of blogs and tutorials all providing different queries, but still no work, follow these steps".
The Tutorial, the Solution:
Open up ODBC, or simply run odbcad32.exe. and start by making a new system DNS with the Native client.
In the next step, you provide your datasource name and the complete server name as you can find on the Azure server.
Also provide the database you want to connect to, don't skip this one!
Test the connection, see it succeed! if not, go through the above printscreens you probably missed a step.
Now we can create the linked server on your database, by creating a query on your SSMS and can run your linked server query, it should look similar to this:
EXEC master.dbo.sp_addlinkedserver @server = N'Azure_server', @srvproduct=N'', @provider=N'sqlncli', @datasrc=N't20ko02v18.database.windows.net', @catalog=N'Teletraan IV' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Azure_server', @useself=N'False',@locallogin=NULL,@rmtuser=N'skyscream',@rmtpassword='password'
To test if the LS is added, you could run exec sp_helpserveror just test the connection.
These are all the steps to a Linked Azure server. Congratulations, you are now connected to the Azure mothership of data.
Data manipulation
Next step is to insert data from the Azure linked server into our local database. Btw, this query works both ways, you could also insert data into the Azure database.
Create an INSERT statement
BEGIN TRAN INSERT INTO [Azure_demo].[dbo].[Demo] (Initials,Prefix,LastName,Gender,Street,HouseNumber ,Zipcode,City,PhoneNumber,MobileNumber,EmailAddress) SELECT Initials,Prefix,LastName,Gender,Street,HouseNumber ,Zipcode,City,PhoneNumber,MobileNumber,EmailAddress FROM [Azure_server].[Teletraan IV].[dbo].[AzureData] WHERE InsertDate BETWEEN '2008-08-22 00:00:00.000' AND '2009-02-06 00:00:00.000' ROLLBACK TRAN --COMMIT TRAN
Now all rows have been added through our linked server into our local database. 
If this blog saved your ass or just made your day, please let me know 🙂
