RE: Remote Servers - Could not connect to server
From: Bill Hollinshead [MSFT] (billhol_at_online.microsoft.com)
Date: 05/01/03
- Next message: Lan Lewis-Bevan [MS]: "RE: Integrated Security"
- Previous message: Ted Senior: "Re: Trusted Sql Server Connection"
- In reply to: Mike Mitaritonna: "Remote Servers - Could not connect to server"
- Next in thread: Mike Mitaritonna: "Re: Remote Servers - Could not connect to server"
- Reply: Mike Mitaritonna: "Re: Remote Servers - Could not connect to server"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
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.
- Next message: Lan Lewis-Bevan [MS]: "RE: Integrated Security"
- Previous message: Ted Senior: "Re: Trusted Sql Server Connection"
- In reply to: Mike Mitaritonna: "Remote Servers - Could not connect to server"
- Next in thread: Mike Mitaritonna: "Re: Remote Servers - Could not connect to server"
- Reply: Mike Mitaritonna: "Re: Remote Servers - Could not connect to server"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
