RE: Remote Servers - Could not connect to server

From: Bill Hollinshead [MSFT] (billhol_at_online.microsoft.com)
Date: 05/01/03 

Date: Thu, 01 May 2003 09:01:08 GMT

Hi Mike,

You may want to inspect sp_helpserver upon SQLSTG and SQL2, If SELECT
@@servername doesn't match the machine name (upon either box) then make it
so <g> via sp_dropserver (if needed) and sp_addserver<server name>, LOCAL.

My rather simple sp_helpremotelogin returns the following result set:
{
server local_user_name remote_user_name
options
---------------------- ---------------------- ----------------------
---------
HOTRATS distributor_admin distributor_admin
HOTRATS distributor_admin sa
}
note how it maps a specific local_user_name to a specific remote_user_name,
and that the trusted option isn't set (in my report).

You should consider sp_helpremotelogin 'SQLSTG' on SQL2, and run
sp_remoteoption upon SQL2 to ensure SQL2 accepts trusted connections.

Also ensure the T-SQL steps in
http://msdn.microsoft.com/library/en-us/adminsql/ad_1_server_4i9f.asp were
followed. Also ensure the RPC and RPC Out options are enabled (via
sp_serveroption) upon both SQLSTG and SQL2 (it appears they have been set,
but worth checking). Personally, I would allow linked server access (I
think it is clearer that the old RPC and sp_remote% options) by enabling
'data access' via sp_serveroption at SQL2. If you look at the description
@useself argument at
http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_adda_6e26.asp, you
will see that security account delegation is required, and yet I'm afraid
an NT 4.0 domain won't support delegation (W2K does), thus you will need to
explicitly map a login using sp_addlinkedsrvlogin.

If no solution to this point: If SELECT USER returns 'DBO' (when connected
to SQLSTG and to SQL2) then your login in a sysadmin. However, if you are
logged into NT as a Local Administrator (via a domain admin account) on
either box, and yet (unexpectedly) a SELECT USER does *not* return DBO (on
either SQLSTG or SQL2), then perhaps someone had followed
http://support.microsoft.com/?id=263712. Another possibility might be that
the LOGONSERVER environment variable (C:\>SET) is not pointing to the
expected Domain Controller (i.e., NT is using cached credentials, and the
system event log should mention this just after NT was last restarted), and
the box needs to authenticate (likely to be SQL2) with a DC.

Sorry about the scattered/unfocussed <g> approach (above); I am a little
confused by that error message <g>. That error message, sp_addremotelogin,
and sp_helpremotelogin support the execution of remote system stored
procedure calls (this is SQL Server 6.5 functionality) such as "EXEC
server_name.database_name.ower_name.stored_procedure_name" as opposed to
supporting the use of linked server syntax such as "SELECT * FROM
server_name.database_name.ower_name.stored_procedure_name" (not available
in SQL Server 6.5 and per
http://msdn.microsoft.com/library/en-us/architec/8_ar_sa2_14dh.asp). I
believe the SELECT syntax should return a different error message (and this
would be troubleshot using sp_helplinkedserverlogin).

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Relevant Pages

  • Re: Remote Servers - Could not connect to server
    ... Disable SQL2 as Subscriber in Publisher/Distributor properties. ... Enable SQL2 as SQLSTG subscriber. ... It didn't add SQL2 as a remote server this time, ... Bill, I really appreciate your help in this. ...
    (microsoft.public.sqlserver.security)
  • Multi Instance Cluster Question
    ... Its SQL 2000 EE on ... Windows Server 2003 EE ... one ..in my case SQL2.. ... a default instance was named as SQL1. ...
    (microsoft.public.sqlserver.clustering)
  • Re: General SQL Clustering Question
    ... Senior SQL Infrastructure Consultant ... Microsoft SQL Server MVP ... following was setup when SQL1 was the active node and was different when SQL2 ... shouldn't be any difference between the nodes either because the cluster ...
    (microsoft.public.sqlserver.clustering)
  • Remote Server issue...
    ... production server to my reporting server. ... I have replication from sql1 to sql2. ... I noticed that there is already a remote server setup on ...
    (microsoft.public.sqlserver.security)
  • Remote Servers - Could not connect to server
    ... I log in on the SQLSTG as mydomain\admin which is a windows administrator of all the 3 SQL and system admin. ... SQL2 has been added to Remote Servers by the replication wizard and of course I can't add SQL2 as Link Server. ...
    (microsoft.public.sqlserver.security)