Share via

  • Article

Tip: Configure Remote Server Connections

Connections from other servers are handled differently than user connections. You can determine whether or not servers can connect to this server, how long it takes for remote queries to time out, and if distributed transactions are used. To configure remote connections, do the following:
1. In the Server Properties dialog box, go to the Connections page.

2. To allow servers to connect to this server, select the option Allow Remote Connections To This Server. Remote servers can then log on to the server to execute stored procedures remotely. You must stop and then start the SQL Server instance to apply the change if you select this option.
Beware: Remote procedure call (RPC) connections are allowed by default. If you change this behavior, remote servers cannot log on to SQL Server. This setting change keeps SQL Server secure from remote server access.

3. By default, queries executed by remote servers time out in 600 seconds. To change this behavior, type a time-out value in the Remote Query Timeout box on the Connections page. Time-out values are set in seconds, and the acceptable range of values is from 0 through 2,147,483,647. A value of 0 means that there is no query time-out for remote server connections.

4. Stored procedures and queries executed on the server can be handled as distributed transactions by using Distributed Transaction Coordinator (DTC). If you want to execute procedures this way, select the Require Distributed Transactions For Server-To-Server Communication check box. If you change this option, you must stop and then start the SQL Server instance.

5. Click OK.

These options can also be set with sp_configure. The related Transact-SQL statements are:

  • exec sp_configure "remote access", <0 or 1>
  • exec sp_configure "remote query timeout", <number of seconds>
  • exec sp_configure "remote proc trans", <0 or 1>

Note: A value of 0 turns a remote server connection option off, and a value of 1 turns an option on.

From the Microsoft Press book Microsoft SQL Server 2008 Administrator's Pocket Consultant by William R. Stanek.

Looking for More Tips?

For more SQL Server Tips, visit the TechNet Magazine SQL Server Tips page.

For more Tips on other products, visit the TechNet Magazine Tips index.