sp_addlinkedsrvlogin problems

[John Beatty]

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

[Dennis Redfield]

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

"John Beatty" <jo...@bigfootsoftware.com> wrote in message
news:%23EmCmnn...@TK2MSFTNGP10.phx.gbl...

[John Beatty]

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

"Dennis Redfield" <dennis....@acadia-ins.com> wrote in message
news:%23xZdR9v...@TK2MSFTNGP12.phx.gbl...

[SQLVarad]

The best way to do this is using SQL Server Enterprise
Manager and expand the "Security" folder for that server.
Then Right click the "Linked Server" and select "New
Linked server".

Then in "General Tab", Enter the Remote SQL Server name
and then select the SQL Server (radio button).

Then click the Security tab and select the last
option "Made using the Security Context" . There you can
enter the remote login and password.

This whole thing can be done using the sp_addlinkedserver.

FYI:
----
A default mapping between all logins on the local server
and remote logins on the linked server is automatically
created by executing sp_addlinkedserver. The default
mapping states that SQL Server uses the local login's user
credentials when connecting to the linked server on behalf
of the login (equivalent to executing sp_addlinkedsrvlogin
with @useself set to true for the linked server). Use
sp_addlinkedsrvlogin only to change the default mapping or
to add new mappings for specific local logins. To delete
the default mapping or any other mapping, use
sp_droplinkedsrvlogin.

Hope this helps.

-SQLVarad (MCDBA-1999,MCSE-1999)

>.
>

[Prakash - Savvysoft technologies]

USE master
GO
-- To use named parameters: Add linked server in the source (Local machine - eg: MachineName or LocalSeverLoginName)

sp_addlinkedserver
@server = N'LnkSrv_RemoteServer_TEST',
@srvproduct=N'', -- Leave it blank when its not 'SQL Server'
@provider=N'SQLNCLI', -- Note: provider 'MSDASQL' is not working
@datasrc=N'RemoteServer',
@provstr=N'UID=sa;PWD=sa;'
--,@catalog = N'MYDATABASE' eg: pubs
GO

/*
Note:
1. provider 'MSDASQL' is not working
2. To check provider name use the folling query in the destination server
Select Provider From sys.servers
*/
----------------------------------------------------------------------------------------------------------
-- Optional
--EXEC sp_addlinkedsrvlogin 'LnkSrv_RemoteServer_TEST', 'true' -- (self is true) -- for LocalSeverLoginName
--GO

-- Remote login
sp_addlinkedsrvlogin
@rmtsrvname = 'LnkSrv_RemoteServer_TEST',
@useself = 'False',
@rmtuser = 'sa',
@rmtpassword = 'sa'
GO

-- OR
/*
IF the above add linked server login failed then try in the Linked Server (LnkSrv_RemoteServer_TEST) Property
Select -> Security - > 'For a login not defined in the list above, Connection will:'

Choose - > Be made using this security context
SET Remote login: sa
With password: sa
*/
----------------------------------------------------------------------------------------------------------

-- Test server connection
declare @srvr nvarchar(128), @retval int;
set @srvr = 'LnkSrv_RemoteServer_TEST';
begin try
exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
set @retval = sign(@@error);
end catch;
if @retval <> 0
raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );

-- OR

BEGIN TRY
EXEC sp_testlinkedserver N'LnkSrv_RemoteServer_TEST';
END TRY
BEGIN CATCH
PRINT 'Linked Server not available';
RETURN;
END CATCH
----------------------------------------------------------------------------------------------------------

-- Get access linked server database
SET xact_abort ON
GO

BEGIN TRANSACTION
SELECT * FROM LnkSrv_RemoteServer_TEST.DBName.dbo.tblName
COMMIT TRAN
GO

-- OR
SELECT * FROM OPENQUERY(LnkSrv_RemoteServer_TEST, 'SELECT * FROM DBName.dbo.tblName')
GO

-- OR
SELECT * FROM OPENQUERY(LnkSrv_RemoteServer_TEST, 'SELECT * FROM sys.databases Order by name')
GO
----------------------------------------------------------------------------------------------------------

From http://www.developmentnow.com/g/112_2003_10_0_0_406499/sp-addlinkedsrvlogin-problems.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/