Remote servers

How can you talk to another server on your network or across the internet?

There a lot of ways to skin a cat, and there are many ways of getting your through to the database you need to talk to.

Have on hand:

• A personal computer you can play with (SQL Server Express?)
  
• A windows login that is godlike (that can map to another computer). Password, etc.
  

Permissions

Practice on a personal computer you can play with -- SQL Server Express installed.
Then try it on a server on a network. If you gradually get the mapping of permissions down, you will understand how it works.

Think of a linked server as a rope bridge. The same permissions must be duplicated to the other server.
Windows permissions is a bit more tricky than that of a SQL login. You will find lots of discussions and many people relenting to SQL login permissions (I own my issue on that account!).

Try a script

What you need to do to make this work:

'[domain\[username]' : replace the text in the below script '[domain\[username]' with your own type of windows login.
this windows user name must match permissions exactly on the target server

'mylinkedserver' - you probably don't have a server called 'mylinkedserver', so find the name of the server/instance of your database you want to link to)..

EXEC master.dbo.sp_addlinkedserver @server = N'mylinkedserver', @srvproduct=N'SQL Server' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'mylinkedserver',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'mylinkedserver',@useself=N'True',@locallogin=N'[domain]\[username]',@rmtuser=NULL,@rmtpassword=NULL GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'rpc', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'connect timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'mylinkedserver', @optname=N'use remote collation', @optvalue=N'true'

 
Troubleshooting: test the connection:



If you fall into any kinds of errors (I made a fake server to emulate an error):



Then, click the advanced technical error



Select the Message areas to get an error number and google the error number or the phrase; you will find some good some bad advice; stick to Microsoft or reputable websites.




 
If you have no errors, then you will call a table from the other server:
Select * from mylinkedserver.ReportServer.dbo.Catalog


 
Related articles (linked servers between 2005 and 2008)

Calling a linked server (microsoft's site)

Use this helpful documentation when scripting out linked servers!