Monday, 24 September 2012

Add SQL Azure as Linked Server

Just a quick note on how to set SQL Azure on your local SQL Server as Linked Server.

EXEC sp_addlinkedserver
@server='LinkServerName',
@srvproduct='',
@provider='sqlncli',
@datasrc='ServerName.database.windows.net',
@location='',
@provstr='',
@catalog='DatabaseName'

EXEC sp_addlinkedsrvlogin
@rmtsrvname='LinkServerName',
@useself='false',
@rmtuser='username@ServerName.database.windows.net',
@rmtpassword='Password'

EXEC sp_serveroption 'LinkServerName', 'rpc out', true;

I added user@ServerName because I kept on getting error from SQL Azure.

Server name cannot be determined.  It must appear as the first segment of the server's dns name (servername.database.windows.net).  Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername).  In addition, if both formats are used, the server names must match.



4 comments:

  1. Thanks for this, it helped me out too.

    ReplyDelete
  2. Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here Thank you. Your blog was very helpful and efficient For Me,Thanks for Sharing the information Regards..!!..Azure Online Training Bangalore

    ReplyDelete