Re: Remote Servers - Could not connect to server

From: Mike Mitaritonna (mmitaritonna_at_usg.com)
Date: 05/01/03 

Date: Thu, 1 May 2003 13:00:43 -0500

Bill,
First of all I would like to compliment the
microsoft.public.sqlserver.security Newsgroup.
I post my question last night before leaving work and this morning I got
notified that you answered me.
After reading your response and the first link you sent, I had my problem
solved.

Remote Servers are not as good as Link Servers.
So this is what I did:
Drop Subscriptions from SQLSTG.
Disable SQL2 as Subscriber in Publisher/Distributor properties.
At this point I am finally able to run: sp_dropserver 'sql2','droplogins'
Add SQL2 as a Link Server with the "be made by login's current security
context" option.
Enable SQL2 as SQLSTG subscriber.
Note: It didn't add SQL2 as a remote server this time, I guess because it
found it as a link server.
Push new subscription.

Replication worked both ways.
exec or select sql2.dbname.dbo.table worked from sqlstg
exec or select or sqlstg.dbname.dbo.table worked from sql2
using the login's current security context and without having to map logins.

Bill, I really appreciate your help in this.
Thank you.

Mike.

""Bill Hollinshead [MSFT]"" <billhol@online.microsoft.com> wrote in message
news:6WNt5A8DDHA.1120@cpmsftngxa06.phx.gbl...
> 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
    ... You may want to inspect sp_helpserver upon SQLSTG and SQL2, ... I would allow linked server access (I ... procedure calls (this is SQL Server 6.5 functionality) such as "EXEC ...
    (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)