Wednesday, December 30, 2015

Using a Linked Server with Azure

I just spent the better part of an afternoon trying to figure out how to get a linked server to work between a SQL Server 2014 Database and an SQL Azure Database. After a lot of googling and experiments I was finally successful.

Here is what I learned:

  1. Don't use the GUI on the Linked Database node SSMS-- it will lead you places you don't need to go.
  2. The only Microsoft doc I could find with working example code is at https://msdn.microsoft.com/en-us/library/ms190479.aspx, at the very end.
  3. Make sure you are connected to the master database when you run the sp_addlinkedserver, sp_addlinkedsrvlogin, and sp_dropserver stored procedures.
  4. When you run sp_addlinkedserver:
    1. Use 'sqlncli' as the provider.
    2. Use both the server parameter (What you are calling it on the client) AND the datasrc parameter (the actual name of the source database).
    3. srvproduct must be an empty string ('').
  5. You can't use windows logins (SQL Authentication only)


Here is some example code which I hope leads you in the right direction.


--

6 comments:

  1. Perfect!

    Thanks,
    Lee Everest

    ReplyDelete
  2. Awesome post, thank you very much, worked on the first try!

    ReplyDelete
  3. Thank you so much.. I wasted about 45 minutes before I stumbled on this. Worked 1st time as well. Amazing to have something explained simply without a bunch of random text! Thank you!!!

    ReplyDelete
  4. You can leave @catalog blank to add all your azure databases, but I can't open the tables. Do I have to add each catalog individually as linked servers?

    ReplyDelete
    Replies
    1. I looked further into this and I can't see anyplace in any doc where the 'catalog' parameter was left blank. The error that it throws is an error that has indicated an unsupported connectivity feature on the past.

      Delete
  5. I'm not sure, I'll play around with it and see what happens, but I'll bet you can't since linked server capabilities are a bit restrictive.

    ReplyDelete