Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Linked server

Author  Topic 

pravin14u
Posting Yak Master

246 Posts

Posted - 2004-08-12 : 10:30:11
hi


the options for "linked" and "remote" servers are under the "security" in enterprise manager...why?how do both(remote and linked server) relate to security?

the above one is a very basic doubt that i have and i am sorry if my question is wrong by any means
thank you

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-12 : 10:34:27
Once again.....Books Online to the rescue:


Configuring Remote Servers
A remote server configuration allows a client connected to one instance of Microsoft® SQL Server™ to execute a stored procedure on another instance of SQL Server without establishing another connection. The server to which the client is connected accepts the client request and sends the request to the remote server on behalf of the client. The remote server processes the request and returns any results to the original server, which in turn passes those results to the client.

If you want to set up a server configuration in order to execute stored procedures on another server and do not have existing remote server configurations, use linked servers instead of remote servers. Both stored procedures and distributed queries are allowed against linked servers; however, only stored procedures are allowed against remote servers.



Note Support for remote servers is provided for backward compatibility only. New applications that must execute stored procedures against remote instances of SQL Server should use linked servers instead.


Remote Server Details
Remote servers are set up in pairs. To set up a pair of remote servers, configure both servers to recognize each other as remote servers. Then, verify that configuration options are set properly for both servers so that each instance of SQL Server allows remote users to execute procedure calls. Check the configuration options in the Server Properties dialog box on both the local and the remote servers.

In most cases, you should not need to set configuration options for remote servers; the defaults set on both local and remote computers by SQL Server Setup allow for remote server connections.

For remote server access to work, the remote access configuration option, which controls logins from remote servers, must be set to 1 (the default setting) on both the local and remote computers. If the setting for either server's remote access option has been changed, you must reset the option (for one or both servers) back to 1 to allow remote access. This can be accomplished through either SQL Server Enterprise Manager or the Transact-SQL sp_configure statement.

From the local server, you can disable a remote server configuration to prevent user access to that server.

To set up a remote server

Transact-SQL


How to set up a remote server to allow the use of remote stored procedures (Transact-SQL)
To set up a remote server to allow the use of remote stored procedures

Run the following code on the first server running Microsoft® SQL Server™:
EXEC sp_addlinkedserver ServerName1, N'SQL Server'
EXEC sp_addlinkedserver ServerName2
EXEC sp_configure 'remote access', 1
RECONFIGURE
GO

Stop and restart the first SQL Server.


Run the following code on the second SQL Server. Make sure you are logging in using SQL Server Authentication.
-- The example shows how to set up access for a login 'sa'
-- from ServerName1 on ServerName2.
EXEC sp_addlinkedserver ServerName2, local
EXEC sp_addlinkedserver ServerName1
EXEC sp_configure 'remote access', 1
RECONFIGURE
GO
-- Assumes that the login 'sa' in ServerName2 and ServerName1
-- have the same password.
EXEC sp_addremotelogin ServerName1, sa, sa
GO

Stop and restart the second SQL Server.


Using the sa login, you can now execute a stored procedure on the second SQL Server from the first SQL Server.

See Also

sp_addremotelogin

sp_configure

sp_addlinkedserver

RECONFIGURE


SQL-DMO

To check and set remote server configuration options

Enterprise Manager

How to check and set remote server configuration options (Enterprise Manager)
To check and set remote server configuration options

Expand a server group, and then right-click a server.


Click Properties, and then click the Connections tab.


Under Remote server connections, review and, if appropriate, change the values for the following configuration options:
Allow other SQL Servers to connect remotely to this SQL Server using RPC.


Query time-out (sec, 0 = unlimited)
This option specifies the number of seconds to wait before returning from processing a query. A value of 0 will allow an infinite wait. The default is 0.

Enforce distributed transactions (MTS).
Changing a configuration option requires that you stop and restart the server. If you changed a configuration option, then proceed to Step 4. If not, skip to Step 6.

Right-click the server, and then click Stop.


After the server has stopped, right-click the server, and then click Start.


Repeat Steps 1 through 5 on the other server of the remote server pair.


SQL-DMO

To disable a remote server setup

Enterprise Manager

How to disable a remote server setup (Enterprise Manager)
To disable a remote server setup

Expand a server group, and then expand a server.


Expand Security, and then click Remote Servers to list the remote servers defined on the selected server.


Right-click the remote server to disable, and then click Delete.


Transact-SQL


How to disable a remote server setup (Transact-SQL)
To disable a remote server setup

Run the following code on the second server running Microsoft® SQL Server™.
EXEC sp_remoteoption ServerName1,sa, sa, trusted, false
EXEC sp_dropremotelogin ServerName1, sa, sa
RECONFIGURE
GO

EXEC sp_configure 'remote access', 0
EXEC sp_dropserver ServerName1
EXEC sp_dropserver ServerName2
RECONFIGURE
GO

Stop and restart the second SQL Server.


Run the following code on the first SQL Server:
EXEC sp_configure 'remote access', 0
EXEC sp_dropserver ServerName2
EXEC sp_dropserver ServerName1
RECONFIGURE
GO

Stop and restart the first SQL Server.

See Also

sp_configure

sp_remoteoption

sp_dropremotelogin

RECONFIGURE

sp_dropserver


SQL-DMO


See Also

Configuring Linked Servers

RECONFIGURE

remote access Option

sp_configure







To summarize, you just shouldn't use remote servers. It's there for legacy.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

pravin14u
Posting Yak Master

246 Posts

Posted - 2004-08-12 : 11:23:08
hi

thank you for ur reply sir.

but my basic questionof why these 2 things should come under "security" is unanswered
i wud be thanful if anyone can answer me reg this
thank you

Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-12 : 11:57:46
It relates to the security of multi-server communication. ??? This area defines a communication channel between two SQL Servers. I suppose you could have a "communication" tab or something, but who really cares?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
   

- Advertisement -