Call remote proc in TSQL

I have two SQL Servers, both in the same domain. My domain account is
defined as a system administrator on both. On one server I set up a linked
server definition to the other and told it to use "logins current security
context". On the second I created a procedure spHelloWorld in master that
creates a table and stuffs a message into it. I tested the proc on that
server and it works fine. I logged into the first server using Query
Analyser using my domain account. I then execute this :
exec RMTSRVRNAME.master.dbo.spHelloWorld.
I get back this error:
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I f I change the linked server propertyies to always use the sa/password of
the linked server it works fine. What am I missing here? Can't I use
windows authentication with linked servers?
BarryCheck out "Security Account Delegation" in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
<barryfz@.home.com> wrote in message
news:uk2AGGPvEHA.2536@.TK2MSFTNGP11.phx.gbl...
I have two SQL Servers, both in the same domain. My domain account is
defined as a system administrator on both. On one server I set up a linked
server definition to the other and told it to use "logins current security
context". On the second I created a procedure spHelloWorld in master that
creates a table and stuffs a message into it. I tested the proc on that
server and it works fine. I logged into the first server using Query
Analyser using my domain account. I then execute this :
exec RMTSRVRNAME.master.dbo.spHelloWorld.
I get back this error:
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I f I change the linked server propertyies to always use the sa/password of
the linked server it works fine. What am I missing here? Can't I use
windows authentication with linked servers?
Barry|||barry
EXEC sp_serveroption SERVER, 'data access' , 'true'
go
use pubs
go
create procedure proc_BARRY
select * from dbo.authors
go
select *
from OPENQUERY(OPUSPENGUIN,'exec pubs.dbo.proc_BARRY')
go
-- clean-up
drop procedure proc_BARRY
go
<barryfz@.home.com> wrote in message
news:uk2AGGPvEHA.2536@.TK2MSFTNGP11.phx.gbl...
> I have two SQL Servers, both in the same domain. My domain account is
> defined as a system administrator on both. On one server I set up a linked
> server definition to the other and told it to use "logins current security
> context". On the second I created a procedure spHelloWorld in master that
> creates a table and stuffs a message into it. I tested the proc on that
> server and it works fine. I logged into the first server using Query
> Analyser using my domain account. I then execute this :
> exec RMTSRVRNAME.master.dbo.spHelloWorld.
> I get back this error:
> Server: Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I f I change the linked server propertyies to always use the sa/password
of
> the linked server it works fine. What am I missing here? Can't I use
> windows authentication with linked servers?
>
> Barry
>|||Hi Barry ,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. Have you tried
Uri Dimant's statement? We appreciate your patience and look forward to
hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||I have check out the recommendations suggested and found them useful but I
am still having a problem. What I have done now is made my remote server a
linked Server using Enterprise Manager and for now I have set the security
to map to the sa for all requests. The remote server is names Mikey. When I
run this:
select * from mikey.master.dbo.CPIRestoreDB
I get back the rows from the table
But when I run this:
exec mikey.master.dbo.spHelloWorld
I get:: Could not execute procedure on remote server 'mikey' because SQL
Server is not configured for remote access.
Why can't I run a sp'
Barry|||barryfz@.home.com wrote:
> I have check out the recommendations suggested and found them useful
> but I am still having a problem. What I have done now is made my
> remote server a linked Server using Enterprise Manager and for now I
> have set the security to map to the sa for all requests. The remote
> server is names Mikey. When I run this:
> select * from mikey.master.dbo.CPIRestoreDB
> I get back the rows from the table
> But when I run this:
> exec mikey.master.dbo.spHelloWorld
> I get:: Could not execute procedure on remote server 'mikey'
> because SQL Server is not configured for remote access.
> Why can't I run a sp'
> Barry
AFAIK, in the linked server properties, check the box "RPC in", right?|||Hi Barry,
Have you tried Zarko Jovanovic's suggestion? I wanted to post a quick note
to see if you would like additional assistance or information regarding
this particular issue. We appreciate your patience and look forward to
hearing from you!
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!