Hi -

When using linked SQL Server definitions, and using sp_addlinkedsrvlogin, is
there any way to designate the remote user as a Windows Domain login?

Given that OHIO is the Domain and OHIO\Joe is in the System Administrator
role on the remote server;
sp_addlinkedsrvlogin
@rmtsrvname = 'AKRON'
,@useself = FALSE
,@locallogin = NULL
,@rmtuser = 'OHIO\Joe'
-- ,@rmtpassword =

The above call will execute the link login definition, but fail the link
with [Login failed for user 'OHIO\Joe'].

For that matter, is there any way to do that in Enterprise Mgr.? Using
"Impersonate" on Local Login 'OHIO\Joe', and not using any @rmtuser or
@rmypassword will execute the link login definition, but fail the link with
[OLE/DB provider returned message: Invalid connection string attribute].

How can I access the linked SQL Server using a specific Domain account?

Thanks,
John

John,
Are attempting to link to a SQL Server or to Analysis Services? What
versions (of each).
We link, within the same domain, using specific remote windows security
contexts. You can too! And the GUI method works fine.

Give us some more details (above) and we will walk you through it.

dlr

[quoted text, click to view]

Dennis -

We are linking SQL Server 2000 SP3a to SQL Server 2000 SP3a within the same
Domain ('OHIO').

The link is defined as:

exec sp_addlinkedserver @server = 'LNKAKRON' ,
@srvproduct = '' ,
@provider = 'SQLOLEDB' ,
@datasrc = 'AKRON' ,
@catalog = 'master'

Currently, the linked server login is defined as:

exec sp_addlinkedsrvlogin
@rmtsrvname = 'LNKAKRON' ,
@useself = FALSE ,
@locallogin = NULL ,
@rmtuser = 'DBA' , -- a SQL Server login account on the remote server
@rmtpassword = 'dba'

This login definition allows any NT user in a NT group that is granted
access to the local server's database to use the linked definition as the
remote SQL login account 'DBA'.

What we're after is a login definition that allows any NT user in a NT group
that is granted access to the local server to use the linked definition as a
specific Windows Domain login such as 'OHIO\Joe', where 'OHIO\Joe' has been
granted access to the remote server's database.

The reason we want this is because we do not want to grant remote server
object-level permissions to the specific Windows domain account logged in to
the local server. We already have a Windows Domain account that has the
appropriate privileges on every SQL Server, and we wish to use that account
as our remote login.

If this cannot be done, it is easy enough to create a specific SQL Server
login account to be used in every remote server. But, dang it, it seems
that even with Windows pushing so hard to use integrated security, it does
not let us use a specific Windows login for our remote server login.

If you've got a way to do that, please share it with us. We eventually use
only SP calls, because of our necessity to script all setup and change
procedures.

Thanks in advance,
John


[quoted text, click to view]