not a trusted connection

I never had this problem between two WIN2k servers.
I have a new Win2003(XP) server with SQL 2000 and an old Win2k server
with SQL 7. I'm trying to run a query for data from a table on 7 joined
to a table on 2000 inserting into a table on 2000.
Regardless of which server hosts the query I get an error about user
'null' not being a trusted SQL server connection. Same error whether I
run the query in QAnalyser or DTS.
I have admin rights on both servers and both DBMs.
I DON'T have a domained network; windows networking. Since I've done
things between the 7 server and my dev server (W2k/SQL2000) I'm guessing
it's something to do with a stricter W2003 and seperate, but same
username, identities on each local machine.
Can anybody tell me what the next step is?
thx
md
*** Sent via Developersdex http://www.codecomments.com ***Not a lot of information on the error but I'm guess you are
using a linked server that is set up to use the current
users security context. And you are logging into SQL Server
using windows authentication.
It's not clear from which box you are initiating this from.
Even if it was initiated from SQL 2000, sounds like you
would still need to use a SQL login for the linked server.
-Sue
On Tue, 13 Sep 2005 14:58:37 -0700, M D <mardukes@.aol.com>
wrote:

>I never had this problem between two WIN2k servers.
>I have a new Win2003(XP) server with SQL 2000 and an old Win2k server
>with SQL 7. I'm trying to run a query for data from a table on 7 joined
>to a table on 2000 inserting into a table on 2000.
>Regardless of which server hosts the query I get an error about user
>'null' not being a trusted SQL server connection. Same error whether I
>run the query in QAnalyser or DTS.
>I have admin rights on both servers and both DBMs.
>I DON'T have a domained network; windows networking. Since I've done
>things between the 7 server and my dev server (W2k/SQL2000) I'm guessing
>it's something to do with a stricter W2003 and seperate, but same
>username, identities on each local machine.
>Can anybody tell me what the next step is?
>thx
>md
>*** Sent via Developersdex http://www.codecomments.com ***

No transaction is active

I've google'd like a crazy, tried a lot of stuff, but still can't get
it to work:
When run on our old SQL 2000 it worked fine. Now, running SQL 2005 it
doesn't:
An UPDATE-trigger on a table calls a stored procedure that selects
data and send them to a linked server.
1.
When running the stored procedure alone, everything is fine and data
is send to linked server (so there is through-put to linked server).
2.
But when run normally and the SP is run via the trigger we get:
OLE DB provider "SQLNCLI" for linked server "WEBDEV1" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure spVENT_AppUpdVentDeltager, Line
56
The operation could not be performed because OLE DB provider "SQLNCLI"
for linked server "WEBDEV1" was unable to begin a distributed
transaction.
Any ideas?
Regards /SnedkerHave you got MSDTC running? MSDTC would be responsible for managing the
inter-server (ie. distributed) transaction.
*mike hodgson*
http://sqlnerd.blogspot.com
Morten Snedker wrote:

>I've google'd like a crazy, tried a lot of stuff, but still can't get
>it to work:
>When run on our old SQL 2000 it worked fine. Now, running SQL 2005 it
>doesn't:
>An UPDATE-trigger on a table calls a stored procedure that selects
>data and send them to a linked server.
>1.
>When running the stored procedure alone, everything is fine and data
>is send to linked server (so there is through-put to linked server).
>2.
>But when run normally and the SP is run via the trigger we get:
>OLE DB provider "SQLNCLI" for linked server "WEBDEV1" returned message
>"No transaction is active.".
>Msg 7391, Level 16, State 2, Procedure spVENT_AppUpdVentDeltager, Line
>56
>The operation could not be performed because OLE DB provider "SQLNCLI"
>for linked server "WEBDEV1" was unable to begin a distributed
>transaction.
>Any ideas?
>
>Regards /Snedker
>|||Are these two servers running Windows 2003 Server? By default, MSDTC
is locked down, preventing network connections and anonymous sessions.
You need to modify those settings for SQL to be able to use MSDTC.
http://msdn.microsoft.com/library/d...0676ec97786.asp
Morten Snedker wrote:
> I've google'd like a crazy, tried a lot of stuff, but still can't get
> it to work:
> When run on our old SQL 2000 it worked fine. Now, running SQL 2005 it
> doesn't:
> An UPDATE-trigger on a table calls a stored procedure that selects
> data and send them to a linked server.
> 1.
> When running the stored procedure alone, everything is fine and data
> is send to linked server (so there is through-put to linked server).
> 2.
> But when run normally and the SP is run via the trigger we get:
> OLE DB provider "SQLNCLI" for linked server "WEBDEV1" returned message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure spVENT_AppUpdVentDeltager, Line
> 56
> The operation could not be performed because OLE DB provider "SQLNCLI"
> for linked server "WEBDEV1" was unable to begin a distributed
> transaction.
> Any ideas?
>
> Regards /Snedker|||On Fri, 09 Jun 2006 21:48:49 +1000, Mike Hodgson <e1minst3r@.gmail.com>
wrote:
Yes, it is running.
But I suppose the transaction is the same, whether it is launched from
a stored procedure - or a trigger firing the stored procedure?
/Snedker

>Have you got MSDTC running? MSDTC would be responsible for managing the
>inter-server (ie. distributed) transaction.|||On 9 Jun 2006 05:07:56 -0700, "Tracy McKibben"
<tracy.mckibben@.gmail.com> wrote:
As stated previously in this thread, the MSDTC is running.
If it wasn't running I wouldn't be able to run the stored procedure.
That is the essence of the weirdness:
I can insert data to a table on linked server 'WEBEDV1" from the
stored procedure. But when I let the trigger launch the stored
procedure, it doesn't work !?
This is my trigger:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [trgUpdateVentDeltagere]
ON [dbo].[tblVENTTilsagn]
FOR UPDATE
AS
--SET REMOTE_PROC_TRANSACTIONS OFF;
declare @.count int
declare @.journal varchar(50)
declare @.params varchar(512)
-- Find post som er blevet opdateret
select @.count = count(*)
from Inserted i join Deleted d on i.journal =d.journal
if @.count > 0 and (Substring(columns_updated(),1,1) > 0 or
Substring(columns_updated(),2,1) >0)
begin
select @.journal = i.journal from inserted i
exec spVENT_AppUpdVentDeltager @.jnr = @.journal
end
Running spVENT_AppUpdVentDeltager from this trigger fails with error
given in the subject-line.
Running spVENT_AppUpdVentDeltager alone works fine.
>Are these two servers running Windows 2003 Server? By default, MSDTC
>is locked down, preventing network connections and anonymous sessions.
>You need to modify those settings for SQL to be able to use MSDTC.
>http://msdn.microsoft.com/library/d...0676ec97786.asp
>
>Morten Snedker wrote:|||Without knowing what else that sproc does, and based on what you've
given us, I'm still going to suggest that your problem is due to MSDTC
permissions. If your servers are Windows 2003, there are some MSDTC
properties that you must change. If the information provided in the
first link I posted doesn't work, then try this one:
http://support.microsoft.com/defaul...332&Product=sql
Good luck!
Morten Snedker wrote:
> On 9 Jun 2006 05:07:56 -0700, "Tracy McKibben"
> <tracy.mckibben@.gmail.com> wrote:
> As stated previously in this thread, the MSDTC is running.
> If it wasn't running I wouldn't be able to run the stored procedure.
> That is the essence of the weirdness:
> I can insert data to a table on linked server 'WEBEDV1" from the
> stored procedure. But when I let the trigger launch the stored
> procedure, it doesn't work !?
> This is my trigger:
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
> ALTER TRIGGER [trgUpdateVentDeltagere]
> ON [dbo].[tblVENTTilsagn]
> FOR UPDATE
> AS
> --SET REMOTE_PROC_TRANSACTIONS OFF;
> declare @.count int
> declare @.journal varchar(50)
> declare @.params varchar(512)
> -- Find post som er blevet opdateret
> select @.count = count(*)
> from Inserted i join Deleted d on i.journal =d.journal
> if @.count > 0 and (Substring(columns_updated(),1,1) > 0 or
> Substring(columns_updated(),2,1) >0)
> begin
> select @.journal = i.journal from inserted i
> exec spVENT_AppUpdVentDeltager @.jnr = @.journal
> end
> Running spVENT_AppUpdVentDeltager from this trigger fails with error
> given in the subject-line.
> Running spVENT_AppUpdVentDeltager alone works fine.
>|||On 9 Jun 2006 06:24:56 -0700, "Tracy McKibben"
<tracy.mckibben@.gmail.com> wrote:
Thanks for your suggestion and your efforts. However, I've been there
already and MSDTC has been set properly.
The solution for now is moving the stored procedure from the trigger
to a client application. Less than optimal, but it works.
Regards /Snedker

>Without knowing what else that sproc does, and based on what you've
>given us, I'm still going to suggest that your problem is due to MSDTC
>permissions. If your servers are Windows 2003, there are some MSDTC
>properties that you must change. If the information provided in the
>first link I posted doesn't work, then try this one:
>http://support.microsoft.com/defaul...332&Product=sql
>Good luck!