Here is what I learned:
- Don't use the GUI on the Linked Database node SSMS-- it will lead you places you don't need to go.
- 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.
- Make sure you are connected to the master database when you run the sp_addlinkedserver, sp_addlinkedsrvlogin, and sp_dropserver stored procedures.
- When you run sp_addlinkedserver:
- Use 'sqlncli' as the provider.
- Use both the server parameter (What you are calling it on the client) AND the datasrc parameter (the actual name of the source database).
- srvproduct must be an empty string ('').
- You can't use windows logins (SQL Authentication only)
Here is some example code which I hope leads you in the right direction.