I have a cross-databases SQL application on SQL 2000 servers SP4 (db_A on
localServer, db_B on remoteServer) on Win2003
on same domain.
Linked server is defined as "Be made by the login's current security
context".
The login account is member of a Windows group that is dbowner of both
databases.
A stored proc on db_A that just contains the SQL statement:
begin
INSERT INTO
[remoteServer].[db_B].[Domain\Wingroup].[remoteTable] (A.&#
91;ITEM], A.[DESC])
SELECT DISTINCT A.ITEM_ID, A.ITEM_DESC
FROM [localServer].[db_A].[dbo].[ENTITIES] A,
[localServer].[db_B].[dbo].[ITEM_VIEW] B
WHERE A.ITEM_ID = B.ITEM_ID
end
ITEM_VIEW is a view on tables dbo-owned on remoteServer (only the target
remote table is owned by Domain\Wingroup)
Why
- if I exec the stored proc. without the a BEGIN TRANSACTION it works
- if I do the same within a transaction:
SET XACT_ABORT ON
BEGIN TRANSACTION
exec sp_A
COMMIT
I receive the error:
Server user 'Domain\LocalServer$' is not a valid user in database 'db_A'.
I receive the error also if I log MS-Query Anyalzer by sa account instead of
Windows Authetntication.
The problem is solved only if I create a local TEMP table in place of the
ITEM_VIEW, but from the application point of view is not acceptable.
Additionally, the two servers are trusted for delegation (Kerberos) and the
problem is independant of how I set "Allow ownership chain" on two databases
Could it be a MSDTC problem?
Any suggestion is really appreciated,
Thanks in advance
BeppeHi Beppe,
This is Justin from Microsoft. Welcome to MSDN managed NewsGroup.
As I understand, the db_B in the select part of the query is on the
localserver. Is this right? I created some test tables and view on my
machine and it works fine on my side.
If you specify the Linked server to use a predefined security context such
as SA, will you still encounter the same problem? If you still encounter
the same problem, please let me know the exact error message so that I
could better understand your issue.
If you have any question, please feel free to let me know.
Thanks & Regards,
Justin Shen
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Beppe" <Beppe@.newsgroups.nospam>
| Subject: 'Domain\LocalServer$' is not a valid user
| Date: Mon, 30 Jan 2006 19:48:41 +0100
| Lines: 49
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <eTBSM3cJGHA.2896@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.security
| NNTP-Posting-Host: txt1.txt.tno.it 138.66.77.70
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.security:26298
| X-Tomcat-NG: microsoft.public.sqlserver.security
|
| I have a cross-databases SQL application on SQL 2000 servers SP4 (db_A on
| localServer, db_B on remoteServer) on Win2003
| on same domain.
| Linked server is defined as "Be made by the login's current security
| context".
| The login account is member of a Windows group that is dbowner of both
| databases.
| A stored proc on db_A that just contains the SQL statement:
|
| begin
| INSERT INTO
| [remoteServer].[db_B].[Domain\Wingroup].[remoteTable] (A
.[ITEM],
A.[DESC])
| SELECT DISTINCT A.ITEM_ID, A.ITEM_DESC
| FROM [localServer].[db_A].[dbo].[ENTITIES] A,
| [localServer].[db_B].[dbo].[ITEM_VIEW] B
| WHERE A.ITEM_ID = B.ITEM_ID
| end
|
| ITEM_VIEW is a view on tables dbo-owned on remoteServer (only the target
| remote table is owned by Domain\Wingroup)
|
| Why
| - if I exec the stored proc. without the a BEGIN TRANSACTION it works
| - if I do the same within a transaction:
|
| SET XACT_ABORT ON
| BEGIN TRANSACTION
| exec sp_A
| COMMIT
|
| I receive the error:
|
| Server user 'Domain\LocalServer$' is not a valid user in database 'db_A'.
|
| I receive the error also if I log MS-Query Anyalzer by sa account instead
of
| Windows Authetntication.
| The problem is solved only if I create a local TEMP table in place of the
| ITEM_VIEW, but from the application point of view is not acceptable.
| Additionally, the two servers are trusted for delegation (Kerberos) and
the
| problem is independant of how I set "Allow ownership chain" on two
databases
| Could it be a MSDTC problem?
|
| Any suggestion is really appreciated,
| Thanks in advance
| Beppe
|
| --
|
|
||||Hi Justin,
thanks a lot for your quick response
>As I understand, the db_B in the select part of the query is on the
>localserver. Is this right?
Yes, you are right. Actually it was db_A and not db_B. I have verified that
the problem arises
also without the stored proc. I retype here the query:
SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT INTO [RemoteServer].[db_B].[TEST_DOMAIN\TSGROUP].[Tar
getTable]
(B.[DP_SKU], B.[ITEM_ID], B.[START_ITEM_ID], B.[COLOR_ID], B
.[SIZE_ID],
B.[CONFIG_ID], B.[CAT_CODE], B.[REGION_ID])
SELECT DISTINCT A.B_ENTITY_IDEN, A.B_ENTITY_IDEN, A.B_ENTITY_IDEN, '', '',
'', A.CAT_CODE, 'DAT'
FROM [LocalServer].[db_A].[dbo].[BASE_ENTITIES] A,
[LocalServer].[db_A].[dbo].[ITEM_VIEW] B
WHERE B_ENTITY_IDEN = ITW_ITEM_ID
COMMIT
Running it by MS-Query Analyzer, the error is:
Server: Msg 916, Level 14, State 1, Line 3
Server user 'TEST-DOMAIN\TEST1$' is not a valid user in database
'DB_BEPPE'..
TEST-DOMAIN\TEST1 is the computer name of the LocalServer
DB_BEPPE is the remote Database (=db_B)
No problem without the transaction.
>If you specify the Linked server to use a predefined security context such
>as SA, will you still encounter the same problem?
If I define the Linked Server as SA in "Be made using the security context"
I have NOT the problem (but it is
not acceptable from security point of view, of course).
Only if I create the login TEST-DOMAIN\TEST1$ on remoteServer and I grant
him R\W on [PRODUCTS] table (see below the ITEM_VIEW
definition) I do NOT get the error (but also this workaround can be a
security hole).
Following additional information:
- the ITEM_VIEW is created in LocalServer:
CREATE VIEW ITEM_VIEW( ITW_ITEM_ID, ITW_ATT_NAME, ITW_ATT_VALUE ) AS Select
IT.[ITEMID], 'Item Group', IT.[ITEMGROUPID]
from
[RemoteServer].[db_B].[dbo].[PRODUCTS] IT
where
IT.[INCLUDE] = 0
- I get the error logging MS-Query Analyzer on LocalServer both as user
member of TEST_DOMAIN\TSGROUP
via Windows Authentication and as SA\pwd.
- I get the error also if I grant TEST_DOMAIN\TSGROUP as SystemAdministrator
of RemoteServer
- the TEST_DOMAIN\TSGROUP is defined as login on both SQL server and is
dbOwner of both databases.
Only the [TargetTable] it's owned by TEST_DOMAIN\TSGROUP on both databas
es.
All other objects are dbo owned.
- Linked Server is defined as SQL Server, "Be made by the login's current
security
context" and Data Access, RPC, RPC out, Use Remote Collation checked ON.
- both SQL server are running as LOCAL SYSTEM
- DTC runs as "NT Authority\Network Service"
Thanks again, Beppe
"Justin Shen[MSFT]" <v-yishen@.online.microsoft.com> wrote in message
news:q778f$iJGHA.3944@.TK2MSFTNGXA02.phx.gbl...
> Hi Beppe,
> This is Justin from Microsoft. Welcome to MSDN managed NewsGroup.
> As I understand, the db_B in the select part of the query is on the
> localserver. Is this right? I created some test tables and view on my
> machine and it works fine on my side.
> If you specify the Linked server to use a predefined security context such
> as SA, will you still encounter the same problem? If you still encounter
> the same problem, please let me know the exact error message so that I
> could better understand your issue.
> If you have any question, please feel free to let me know.
> Thanks & Regards,
> Justin Shen
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...erview/40010469
> Others: https://partner.microsoft.com/US/te...upportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/defaul...rnational.aspx.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
> --
> | From: "Beppe" <Beppe@.newsgroups.nospam>
> | Subject: 'Domain\LocalServer$' is not a valid user
> | Date: Mon, 30 Jan 2006 19:48:41 +0100
> | Lines: 49
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <eTBSM3cJGHA.2896@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.security
> | NNTP-Posting-Host: txt1.txt.tno.it 138.66.77.70
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.security:26298
> | X-Tomcat-NG: microsoft.public.sqlserver.security
> |
> | I have a cross-databases SQL application on SQL 2000 servers SP4 (db_A
> on
> | localServer, db_B on remoteServer) on Win2003
> | on same domain.
> | Linked server is defined as "Be made by the login's current security
> | context".
> | The login account is member of a Windows group that is dbowner of both
> | databases.
> | A stored proc on db_A that just contains the SQL statement:
> |
> | begin
> | INSERT INTO
> | [remoteServer].[db_B].[Domain\Wingroup].[remoteTable]
(A.[ITEM],
> A.[DESC])
> | SELECT DISTINCT A.ITEM_ID, A.ITEM_DESC
> | FROM [localServer].[db_A].[dbo].[ENTITIES] A,
> | [localServer].[db_B].[dbo].[ITEM_VIEW] B
> | WHERE A.ITEM_ID = B.ITEM_ID
> | end
> |
> | ITEM_VIEW is a view on tables dbo-owned on remoteServer (only the target
> | remote table is owned by Domain\Wingroup)
> |
> | Why
> | - if I exec the stored proc. without the a BEGIN TRANSACTION it works
> | - if I do the same within a transaction:
> |
> | SET XACT_ABORT ON
> | BEGIN TRANSACTION
> | exec sp_A
> | COMMIT
> |
> | I receive the error:
> |
> | Server user 'Domain\LocalServer$' is not a valid user in database
> 'db_A'.
> |
> | I receive the error also if I log MS-Query Anyalzer by sa account
> instead
> of
> | Windows Authetntication.
> | The problem is solved only if I create a local TEMP table in place of
> the
> | ITEM_VIEW, but from the application point of view is not acceptable.
> | Additionally, the two servers are trusted for delegation (Kerberos) and
> the
> | problem is independant of how I set "Allow ownership chain" on two
> databases
> | Could it be a MSDTC problem?
> |
> | Any suggestion is really appreciated,
> | Thanks in advance
> | Beppe
> |
> | --
> |
> |
> |
>|||Hi Beppe,
Could you please tell me what is the user TEST-DOMAIN\TEST1$ ? Do you logon
into the machine with the credential of this user?
Actually, the current user need the necessary privilege so that it could
insert into to the remote server. Why do you think adding the account to
the remote server will be a security hole?
Thanks & Regards,
Justin Shen
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "Beppe" <Beppe@.newsgroups.nospam>
| References: <eTBSM3cJGHA.2896@.TK2MSFTNGP09.phx.gbl>
<q778f$iJGHA.3944@.TK2MSFTNGXA02.phx.gbl>
| Subject: Re: 'Domain\LocalServer$' is not a valid user
| Date: Tue, 31 Jan 2006 10:56:01 +0100
| Lines: 207
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <uIl0KykJGHA.1088@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.security
| NNTP-Posting-Host: txt1.txt.tno.it 138.66.77.70
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.security:26304
| X-Tomcat-NG: microsoft.public.sqlserver.security
|
| Hi Justin,
| thanks a lot for your quick response
|
| >As I understand, the db_B in the select part of the query is on the
| >localserver. Is this right?
|
| Yes, you are right. Actually it was db_A and not db_B. I have verified
that
| the problem arises
| also without the stored proc. I retype here the query:
|
| SET XACT_ABORT ON
| BEGIN TRANSACTION
| INSERT INTO [RemoteServer].[db_B].[TEST_DOMAIN\TSGROUP].[T
argetTable]
| (B.[DP_SKU], B.[ITEM_ID], B.[START_ITEM_ID], B.[COLOR_ID]
, B.[SIZE_ID],
| B.[CONFIG_ID], B.[CAT_CODE], B.[REGION_ID])
| SELECT DISTINCT A.B_ENTITY_IDEN, A.B_ENTITY_IDEN, A.B_ENTITY_IDEN, '',
'',
| '', A.CAT_CODE, 'DAT'
| FROM [LocalServer].[db_A].[dbo].[BASE_ENTITIES] A,
| [LocalServer].[db_A].[dbo].[ITEM_VIEW] B
| WHERE B_ENTITY_IDEN = ITW_ITEM_ID
| COMMIT
|
| Running it by MS-Query Analyzer, the error is:
| Server: Msg 916, Level 14, State 1, Line 3
| Server user 'TEST-DOMAIN\TEST1$' is not a valid user in database
| 'DB_BEPPE'..
|
| TEST-DOMAIN\TEST1 is the computer name of the LocalServer
| DB_BEPPE is the remote Database (=db_B)
|
| No problem without the transaction.
|
| >If you specify the Linked server to use a predefined security context
such
| >as SA, will you still encounter the same problem?
|
| If I define the Linked Server as SA in "Be made using the security
context"
| I have NOT the problem (but it is
| not acceptable from security point of view, of course).
|
| Only if I create the login TEST-DOMAIN\TEST1$ on remoteServer and I grant
| him R\W on [PRODUCTS] table (see below the ITEM_VIEW
| definition) I do NOT get the error (but also this workaround can be a
| security hole).
|
| Following additional information:
|
| - the ITEM_VIEW is created in LocalServer:
|
| CREATE VIEW ITEM_VIEW( ITW_ITEM_ID, ITW_ATT_NAME, ITW_ATT_VALUE ) AS
Select
| IT.[ITEMID], 'Item Group', IT.[ITEMGROUPID]
| from
| [RemoteServer].[db_B].[dbo].[PRODUCTS] IT
| where
| IT.[INCLUDE] = 0
|
| - I get the error logging MS-Query Analyzer on LocalServer both as user
| member of TEST_DOMAIN\TSGROUP
| via Windows Authentication and as SA\pwd.
|
| - I get the error also if I grant TEST_DOMAIN\TSGROUP as
SystemAdministrator
| of RemoteServer
|
| - the TEST_DOMAIN\TSGROUP is defined as login on both SQL server and is
| dbOwner of both databases.
| Only the [TargetTable] it's owned by TEST_DOMAIN\TSGROUP on both
databases.
| All other objects are dbo owned.
|
| - Linked Server is defined as SQL Server, "Be made by the login's current
| security
| context" and Data Access, RPC, RPC out, Use Remote Collation checked ON.
|
| - both SQL server are running as LOCAL SYSTEM
|
| - DTC runs as "NT Authority\Network Service"
|
| Thanks again, Beppe
|
|
| "Justin Shen[MSFT]" <v-yishen@.online.microsoft.com> wrote in message
| news:q778f$iJGHA.3944@.TK2MSFTNGXA02.phx.gbl...
| > Hi Beppe,
| >
| > This is Justin from Microsoft. Welcome to MSDN managed NewsGroup.
| >
| > As I understand, the db_B in the select part of the query is on the
| > localserver. Is this right? I created some test tables and view on my
| > machine and it works fine on my side.
| > If you specify the Linked server to use a predefined security context
such
| > as SA, will you still encounter the same problem? If you still encounter
| > the same problem, please let me know the exact error message so that I
| > could better understand your issue.
| >
| > If you have any question, please feel free to let me know.
| >
| > Thanks & Regards,
| >
| > Justin Shen
| >
| > Microsoft Online Partner Support
| >
| > Get Secure! - www.microsoft.com/security
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| >
| > ========================================
=============
| > Business-Critical Phone Support (BCPS) provides you with technical phone
| > support at no charge during critical LAN outages or "business down"
| > situations. This benefit is available 24 hours a day, 7 days a week to
all
| > Microsoft technology partners in the United States and Canada.
| >
| > This and other support options are available here:
| >
| > BCPS:
| >
https://partner.microsoft.com/US/te...erview/40010469
| >
| > Others:
https://partner.microsoft.com/US/te...upportoverview/
| >
| > If you are outside the United States, please visit our International
| > Support page:
| > http://support.microsoft.com/defaul...rnational.aspx.
| > ========================================
=============
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| >
| >
| >
| > --
| > | From: "Beppe" <Beppe@.newsgroups.nospam>
| > | Subject: 'Domain\LocalServer$' is not a valid user
| > | Date: Mon, 30 Jan 2006 19:48:41 +0100
| > | Lines: 49
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| > | X-RFC2646: Format=Flowed; Original
| > | Message-ID: <eTBSM3cJGHA.2896@.TK2MSFTNGP09.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.security
| > | NNTP-Posting-Host: txt1.txt.tno.it 138.66.77.70
| > | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| > | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.security:26298
| > | X-Tomcat-NG: microsoft.public.sqlserver.security
| > |
| > | I have a cross-databases SQL application on SQL 2000 servers SP4
(db_A
| > on
| > | localServer, db_B on remoteServer) on Win2003
| > | on same domain.
| > | Linked server is defined as "Be made by the login's current security
| > | context".
| > | The login account is member of a Windows group that is dbowner of both
| > | databases.
| > | A stored proc on db_A that just contains the SQL statement:
| > |
| > | begin
| > | INSERT INTO
| > | [remoteServer].[db_B].[Domain\Wingroup].[remoteTable]
(A.[ITEM],
| > A.[DESC])
| > | SELECT DISTINCT A.ITEM_ID, A.ITEM_DESC
| > | FROM [localServer].[db_A].[dbo].[ENTITIES] A,
| > | [localServer].[db_B].[dbo].[ITEM_VIEW] B
| > | WHERE A.ITEM_ID = B.ITEM_ID
| > | end
| > |
| > | ITEM_VIEW is a view on tables dbo-owned on remoteServer (only the
target
| > | remote table is owned by Domain\Wingroup)
| > |
| > | Why
| > | - if I exec the stored proc. without the a BEGIN TRANSACTION it works
| > | - if I do the same within a transaction:
| > |
| > | SET XACT_ABORT ON
| > | BEGIN TRANSACTION
| > | exec sp_A
| > | COMMIT
| > |
| > | I receive the error:
| > |
| > | Server user 'Domain\LocalServer$' is not a valid user in database
| > 'db_A'.
| > |
| > | I receive the error also if I log MS-Query Anyalzer by sa account
| > instead
| > of
| > | Windows Authetntication.
| > | The problem is solved only if I create a local TEMP table in place of
| > the
| > | ITEM_VIEW, but from the application point of view is not acceptable.
| > | Additionally, the two servers are trusted for delegation (Kerberos)
and
| > the
| > | problem is independant of how I set "Allow ownership chain" on two
| > databases
| > | Could it be a MSDTC problem?
| > |
| > | Any suggestion is really appreciated,
| > | Thanks in advance
| > | Beppe
| > |
| > | --
| > |
| > |
| > |
| >
|
|
||||Hi Justin
no, TEST-DOMAIN\TEST1 is the local computer name (= localServer) where I run
the query and not the user.
I login the computer TEST-DOMAIN\TEST1 with the user account
TEST_DOMAIN\NICK member of the Windows group TEST_DOMAIN\TSGROUP.
That Windows group is defined as dbowner on both databases.
In order to work around the error I have additionally to define the login
TEST-DOMAIN\TEST1$ (local computer name$) and to grant it as datareader on
remote db.
Actually I don't really know if this solution can represent a potential
security hole (information disclosure?), but surely it's strange and not
regular solution (.. at least for me).
Thanks again,
Beppe
"Justin Shen[MSFT]" <v-yishen@.online.microsoft.com> wrote in message
news:Zyrey5wJGHA.224@.TK2MSFTNGXA02.phx.gbl...
> Hi Beppe,
> Could you please tell me what is the user TEST-DOMAIN\TEST1$ ? Do you
> logon
> into the machine with the credential of this user?
> Actually, the current user need the necessary privilege so that it could
> insert into to the remote server. Why do you think adding the account to
> the remote server will be a security hole?
> Thanks & Regards,
> Justin Shen
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...erview/40010469
> Others: https://partner.microsoft.com/US/te...upportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/defaul...rnational.aspx.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "Beppe" <Beppe@.newsgroups.nospam>
> | References: <eTBSM3cJGHA.2896@.TK2MSFTNGP09.phx.gbl>
> <q778f$iJGHA.3944@.TK2MSFTNGXA02.phx.gbl>
> | Subject: Re: 'Domain\LocalServer$' is not a valid user
> | Date: Tue, 31 Jan 2006 10:56:01 +0100
> | Lines: 207
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <uIl0KykJGHA.1088@.tk2msftngp13.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.security
> | NNTP-Posting-Host: txt1.txt.tno.it 138.66.77.70
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.security:26304
> | X-Tomcat-NG: microsoft.public.sqlserver.security
> |
> | Hi Justin,
> | thanks a lot for your quick response
> |
> | >As I understand, the db_B in the select part of the query is on the
> | >localserver. Is this right?
> |
> | Yes, you are right. Actually it was db_A and not db_B. I have verified
> that
> | the problem arises
> | also without the stored proc. I retype here the query:
> |
> | SET XACT_ABORT ON
> | BEGIN TRANSACTION
> | INSERT INTO [RemoteServer].[db_B].[TEST_DOMAIN\TSGROUP].[
;TargetTable]
> | (B.[DP_SKU], B.[ITEM_ID], B.[START_ITEM_ID], B.[COLOR_I
D], B.[SIZE_ID],
> | B.[CONFIG_ID], B.[CAT_CODE], B.[REGION_ID])
> | SELECT DISTINCT A.B_ENTITY_IDEN, A.B_ENTITY_IDEN, A.B_ENTITY_IDEN, '',
> '',
> | '', A.CAT_CODE, 'DAT'
> | FROM [LocalServer].[db_A].[dbo].[BASE_ENTITIES] A,
> | [LocalServer].[db_A].[dbo].[ITEM_VIEW] B
> | WHERE B_ENTITY_IDEN = ITW_ITEM_ID
> | COMMIT
> |
> | Running it by MS-Query Analyzer, the error is:
> | Server: Msg 916, Level 14, State 1, Line 3
> | Server user 'TEST-DOMAIN\TEST1$' is not a valid user in database
> | 'DB_BEPPE'..
> |
> | TEST-DOMAIN\TEST1 is the computer name of the LocalServer
> | DB_BEPPE is the remote Database (=db_B)
> |
> | No problem without the transaction.
> |
> | >If you specify the Linked server to use a predefined security context
> such
> | >as SA, will you still encounter the same problem?
> |
> | If I define the Linked Server as SA in "Be made using the security
> context"
> | I have NOT the problem (but it is
> | not acceptable from security point of view, of course).
> |
> | Only if I create the login TEST-DOMAIN\TEST1$ on remoteServer and I
> grant
> | him R\W on [PRODUCTS] table (see below the ITEM_VIEW
> | definition) I do NOT get the error (but also this workaround can be a
> | security hole).
> |
> | Following additional information:
> |
> | - the ITEM_VIEW is created in LocalServer:
> |
> | CREATE VIEW ITEM_VIEW( ITW_ITEM_ID, ITW_ATT_NAME, ITW_ATT_VALUE ) AS
> Select
> | IT.[ITEMID], 'Item Group', IT.[ITEMGROUPID]
> | from
> | [RemoteServer].[db_B].[dbo].[PRODUCTS] IT
> | where
> | IT.[INCLUDE] = 0
> |
> | - I get the error logging MS-Query Analyzer on LocalServer both as user
> | member of TEST_DOMAIN\TSGROUP
> | via Windows Authentication and as SA\pwd.
> |
> | - I get the error also if I grant TEST_DOMAIN\TSGROUP as
> SystemAdministrator
> | of RemoteServer
> |
> | - the TEST_DOMAIN\TSGROUP is defined as login on both SQL server and is
> | dbOwner of both databases.
> | Only the [TargetTable] it's owned by TEST_DOMAIN\TSGROUP on both
> databases.
> | All other objects are dbo owned.
> |
> | - Linked Server is defined as SQL Server, "Be made by the login's
> current
> | security
> | context" and Data Access, RPC, RPC out, Use Remote Collation checked ON.
> |
> | - both SQL server are running as LOCAL SYSTEM
> |
> | - DTC runs as "NT Authority\Network Service"
> |
> | Thanks again, Beppe
> |
> |
> | "Justin Shen[MSFT]" <v-yishen@.online.microsoft.com> wrote in message
> | news:q778f$iJGHA.3944@.TK2MSFTNGXA02.phx.gbl...
> | > Hi Beppe,
> | >
> | > This is Justin from Microsoft. Welcome to MSDN managed NewsGroup.
> | >
> | > As I understand, the db_B in the select part of the query is on the
> | > localserver. Is this right? I created some test tables and view on my
> | > machine and it works fine on my side.
> | > If you specify the Linked server to use a predefined security context
> such
> | > as SA, will you still encounter the same problem? If you still
> encounter
> | > the same problem, please let me know the exact error message so that I
> | > could better understand your issue.
> | >
> | > If you have any question, please feel free to let me know.
> | >
> | > Thanks & Regards,
> | >
> | > Justin Shen
> | >
> | > Microsoft Online Partner Support
> | >
> | > Get Secure! - www.microsoft.com/security
> | >
> | > When responding to posts, please "Reply to Group" via your newsreader
> so
> | > that others may learn and benefit from your issue.
> | >
> | > ========================================
=============
> | > Business-Critical Phone Support (BCPS) provides you with technical
> phone
> | > support at no charge during critical LAN outages or "business down"
> | > situations. This benefit is available 24 hours a day, 7 days a week to
> all
> | > Microsoft technology partners in the United States and Canada.
> | >
> | > This and other support options are available here:
> | >
> | > BCPS:
> | >
> https://partner.microsoft.com/US/te...erview/40010469
> | >
> | > Others:
> https://partner.microsoft.com/US/te...upportoverview/
> | >
> | > If you are outside the United States, please visit our International
> | > Support page:
> | > http://support.microsoft.com/defaul...rnational.aspx.
> | > ========================================
=============
> | >
> | > This posting is provided "AS IS" with no warranties, and confers no
> | > rights.
> | >
> | >
> | >
> | >
> | > --
> | > | From: "Beppe" <Beppe@.newsgroups.nospam>
> | > | Subject: 'Domain\LocalServer$' is not a valid user
> | > | Date: Mon, 30 Jan 2006 19:48:41 +0100
> | > | Lines: 49
> | > | X-Priority: 3
> | > | X-MSMail-Priority: Normal
> | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | > | X-RFC2646: Format=Flowed; Original
> | > | Message-ID: <eTBSM3cJGHA.2896@.TK2MSFTNGP09.phx.gbl>
> | > | Newsgroups: microsoft.public.sqlserver.security
> | > | NNTP-Posting-Host: txt1.txt.tno.it 138.66.77.70
> | > | Path:
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | > | Xref: TK2MSFTNGXA02.phx.gbl
> microsoft.public.sqlserver.security:26298
> | > | X-Tomcat-NG: microsoft.public.sqlserver.security
> | > |
> | > | I have a cross-databases SQL application on SQL 2000 servers SP4
> (db_A
> | > on
> | > | localServer, db_B on remoteServer) on Win2003
> | > | on same domain.
> | > | Linked server is defined as "Be made by the login's current security
> | > | context".
> | > | The login account is member of a Windows group that is dbowner of
> both
> | > | databases.
> | > | A stored proc on db_A that just contains the SQL statement:
> | > |
> | > | begin
> | > | INSERT INTO
> | > | [remoteServer].[db_B].[Domain\Wingroup].[remoteTabl
e] (A.[ITEM],
> | > A.[DESC])
> | > | SELECT DISTINCT A.ITEM_ID, A.ITEM_DESC
> | > | FROM [localServer].[db_A].[dbo].[ENTITIES] A,
> | > | [localServer].[db_B].[dbo].[ITEM_VIEW] B
> | > | WHERE A.ITEM_ID = B.ITEM_ID
> | > | end
> | > |
> | > | ITEM_VIEW is a view on tables dbo-owned on remoteServer (only the
> target
> | > | remote table is owned by Domain\Wingroup)
> | > |
> | > | Why
> | > | - if I exec the stored proc. without the a BEGIN TRANSACTION it
> works
> | > | - if I do the same within a transaction:
> | > |
> | > | SET XACT_ABORT ON
> | > | BEGIN TRANSACTION
> | > | exec sp_A
> | > | COMMIT
> | > |
> | > | I receive the error:
> | > |
> | > | Server user 'Domain\LocalServer$' is not a valid user in database
> | > 'db_A'.
> | > |
> | > | I receive the error also if I log MS-Query Anyalzer by sa account
> | > instead
> | > of
> | > | Windows Authetntication.
> | > | The problem is solved only if I create a local TEMP table in place
> of
> | > the
> | > | ITEM_VIEW, but from the application point of view is not acceptable.
> | > | Additionally, the two servers are trusted for delegation (Kerberos)
> and
> | > the
> | > | problem is independant of how I set "Allow ownership chain" on two
> | > databases
> | > | Could it be a MSDTC problem?
> | > |
> | > | Any suggestion is really appreciated,
> | > | Thanks in advance
> | > | Beppe
> | > |
> | > | --
> | > |
> | > |
> | > |
> | >
> |
> |
> |
>|||Yes, it can be a BIG security hole. When a service running under either the
Local System or Network Service account on a machine makes a remote request
it is made as the machine account for that system (domain\<nodename>$). So
opening up your SQL Server to access by that machine account means lots of
things have access to your SQL Server. For example, if you having any
ASP.NET running on TEST1 it (by default) runs under Network Service and you
have now allowed any of it to access your database.
Most likely you installed SQL Server to run under the Local System account
and need to change that.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Beppe" <Beppe@.newsgroups.nospam> wrote in message
news:eYeGBT0JGHA.3696@.TK2MSFTNGP15.phx.gbl...
> Hi Justin
> no, TEST-DOMAIN\TEST1 is the local computer name (= localServer) where I
> run the query and not the user.
> I login the computer TEST-DOMAIN\TEST1 with the user account
> TEST_DOMAIN\NICK member of the Windows group TEST_DOMAIN\TSGROUP.
> That Windows group is defined as dbowner on both databases.
> In order to work around the error I have additionally to define the login
> TEST-DOMAIN\TEST1$ (local computer name$) and to grant it as datareader
> on remote db.
> Actually I don't really know if this solution can represent a potential
> security hole (information disclosure?), but surely it's strange and not
> regular solution (.. at least for me).
> Thanks again,
> Beppe
> "Justin Shen[MSFT]" <v-yishen@.online.microsoft.com> wrote in message
> news:Zyrey5wJGHA.224@.TK2MSFTNGXA02.phx.gbl...
>|||Hi Beppe,
Thanks for your update.
It is quite weird that machine account will get involved in this scenario.
It is possible that the delegation is not enabled in the DC, thus it fails
to use the current user credential to logon to the remote server. Thus, it
needs to use the machine account.
Please capture a Profiler trace on the remote server and run the SQL
command without transaction and let me know it use which user to logon to
the remote server. We could know it from the Profiler trace.
If you have any question, please feel free to let me know.
Regards,
Justin Shen
| From: "Beppe" <Beppe@.newsgroups.nospam>
| References: <eTBSM3cJGHA.2896@.TK2MSFTNGP09.phx.gbl>
<q778f$iJGHA.3944@.TK2MSFTNGXA02.phx.gbl>
<uIl0KykJGHA.1088@.tk2msftngp13.phx.gbl>
<Zyrey5wJGHA.224@.TK2MSFTNGXA02.phx.gbl>
| Subject: Re: 'Domain\LocalServer$' is not a valid user
| Date: Wed, 1 Feb 2006 16:32:43 +0100
| Lines: 315
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <eYeGBT0JGHA.3696@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.security
| NNTP-Posting-Host: txt1.txt.tno.it 138.66.77.70
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.security:26323
| X-Tomcat-NG: microsoft.public.sqlserver.security
|
| Hi Justin
|
| no, TEST-DOMAIN\TEST1 is the local computer name (= localServer) where I
run
| the query and not the user.
| I login the computer TEST-DOMAIN\TEST1 with the user account
| TEST_DOMAIN\NICK member of the Windows group TEST_DOMAIN\TSGROUP.
| That Windows group is defined as dbowner on both databases.
| In order to work around the error I have additionally to define the login
| TEST-DOMAIN\TEST1$ (local computer name$) and to grant it as datareader
on
| remote db.
|
| Actually I don't really know if this solution can represent a potential
| security hole (information disclosure?), but surely it's strange and not
| regular solution (.. at least for me).
|
| Thanks again,
| Beppe
|
| "Justin Shen[MSFT]" <v-yishen@.online.microsoft.com> wrote in message
| news:Zyrey5wJGHA.224@.TK2MSFTNGXA02.phx.gbl...
| > Hi Beppe,
| >
| > Could you please tell me what is the user TEST-DOMAIN\TEST1$ ? Do you
| > logon
| > into the machine with the credential of this user?
| >
| > Actually, the current user need the necessary privilege so that it could
| > insert into to the remote server. Why do you think adding the account to
| > the remote server will be a security hole?
| >
| > Thanks & Regards,
| >
| > Justin Shen
| >
| > Microsoft Online Partner Support
| >
| > Get Secure! - www.microsoft.com/security
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| >
| > ========================================
=============
| > Business-Critical Phone Support (BCPS) provides you with technical phone
| > support at no charge during critical LAN outages or "business down"
| > situations. This benefit is available 24 hours a day, 7 days a week to
all
| > Microsoft technology partners in the United States and Canada.
| >
| > This and other support options are available here:
| >
| > BCPS:
| >
https://partner.microsoft.com/US/te...erview/40010469
| >
| > Others:
https://partner.microsoft.com/US/te...upportoverview/
| >
| > If you are outside the United States, please visit our International
| > Support page:
| > http://support.microsoft.com/defaul...rnational.aspx.
| > ========================================
=============
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| > --
| > | From: "Beppe" <Beppe@.newsgroups.nospam>
| > | References: <eTBSM3cJGHA.2896@.TK2MSFTNGP09.phx.gbl>
| > <q778f$iJGHA.3944@.TK2MSFTNGXA02.phx.gbl>
| > | Subject: Re: 'Domain\LocalServer$' is not a valid user
| > | Date: Tue, 31 Jan 2006 10:56:01 +0100
| > | Lines: 207
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| > | X-RFC2646: Format=Flowed; Original
| > | Message-ID: <uIl0KykJGHA.1088@.tk2msftngp13.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.security
| > | NNTP-Posting-Host: txt1.txt.tno.it 138.66.77.70
| > | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| > | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.security:26304
| > | X-Tomcat-NG: microsoft.public.sqlserver.security
| > |
| > | Hi Justin,
| > | thanks a lot for your quick response
| > |
| > | >As I understand, the db_B in the select part of the query is on the
| > | >localserver. Is this right?
| > |
| > | Yes, you are right. Actually it was db_A and not db_B. I have verified
| > that
| > | the problem arises
| > | also without the stored proc. I retype here the query:
| > |
| > | SET XACT_ABORT ON
| > | BEGIN TRANSACTION
| > | INSERT INTO [RemoteServer].[db_B].[TEST_DOMAIN\TSGROUP].&#
91;TargetTable]
| > | (B.[DP_SKU], B.[ITEM_ID], B.[START_ITEM_ID], B.[COLOR
_ID],
B.[SIZE_ID],
| > | B.[CONFIG_ID], B.[CAT_CODE], B.[REGION_ID])
| > | SELECT DISTINCT A.B_ENTITY_IDEN, A.B_ENTITY_IDEN, A.B_ENTITY_IDEN,
'',
| > '',
| > | '', A.CAT_CODE, 'DAT'
| > | FROM [LocalServer].[db_A].[dbo].[BASE_ENTITIES] A,
| > | [LocalServer].[db_A].[dbo].[ITEM_VIEW] B
| > | WHERE B_ENTITY_IDEN = ITW_ITEM_ID
| > | COMMIT
| > |
| > | Running it by MS-Query Analyzer, the error is:
| > | Server: Msg 916, Level 14, State 1, Line 3
| > | Server user 'TEST-DOMAIN\TEST1$' is not a valid user in database
| > | 'DB_BEPPE'..
| > |
| > | TEST-DOMAIN\TEST1 is the computer name of the LocalServer
| > | DB_BEPPE is the remote Database (=db_B)
| > |
| > | No problem without the transaction.
| > |
| > | >If you specify the Linked server to use a predefined security context
| > such
| > | >as SA, will you still encounter the same problem?
| > |
| > | If I define the Linked Server as SA in "Be made using the security
| > context"
| > | I have NOT the problem (but it is
| > | not acceptable from security point of view, of course).
| > |
| > | Only if I create the login TEST-DOMAIN\TEST1$ on remoteServer and I
| > grant
| > | him R\W on [PRODUCTS] table (see below the ITEM_VIEW
| > | definition) I do NOT get the error (but also this workaround can be a
| > | security hole).
| > |
| > | Following additional information:
| > |
| > | - the ITEM_VIEW is created in LocalServer:
| > |
| > | CREATE VIEW ITEM_VIEW( ITW_ITEM_ID, ITW_ATT_NAME, ITW_ATT_VALUE ) AS
| > Select
| > | IT.[ITEMID], 'Item Group', IT.[ITEMGROUPID]
| > | from
| > | [RemoteServer].[db_B].[dbo].[PRODUCTS] IT
| > | where
| > | IT.[INCLUDE] = 0
| > |
| > | - I get the error logging MS-Query Analyzer on LocalServer both as
user
| > | member of TEST_DOMAIN\TSGROUP
| > | via Windows Authentication and as SA\pwd.
| > |
| > | - I get the error also if I grant TEST_DOMAIN\TSGROUP as
| > SystemAdministrator
| > | of RemoteServer
| > |
| > | - the TEST_DOMAIN\TSGROUP is defined as login on both SQL server and
is
| > | dbOwner of both databases.
| > | Only the [TargetTable] it's owned by TEST_DOMAIN\TSGROUP on both
| > databases.
| > | All other objects are dbo owned.
| > |
| > | - Linked Server is defined as SQL Server, "Be made by the login's
| > current
| > | security
| > | context" and Data Access, RPC, RPC out, Use Remote Collation checked
ON.
| > |
| > | - both SQL server are running as LOCAL SYSTEM
| > |
| > | - DTC runs as "NT Authority\Network Service"
| > |
| > | Thanks again, Beppe
| > |
| > |
| > | "Justin Shen[MSFT]" <v-yishen@.online.microsoft.com> wrote in messa
ge
| > | news:q778f$iJGHA.3944@.TK2MSFTNGXA02.phx.gbl...
| > | > Hi Beppe,
| > | >
| > | > This is Justin from Microsoft. Welcome to MSDN managed NewsGroup.
| > | >
| > | > As I understand, the db_B in the select part of the query is on the
| > | > localserver. Is this right? I created some test tables and view on
my
| > | > machine and it works fine on my side.
| > | > If you specify the Linked server to use a predefined security
context
| > such
| > | > as SA, will you still encounter the same problem? If you still
| > encounter
| > | > the same problem, please let me know the exact error message so
that I
| > | > could better understand your issue.
| > | >
| > | > If you have any question, please feel free to let me know.
| > | >
| > | > Thanks & Regards,
| > | >
| > | > Justin Shen
| > | >
| > | > Microsoft Online Partner Support
| > | >
| > | > Get Secure! - www.microsoft.com/security
| > | >
| > | > When responding to posts, please "Reply to Group" via your
newsreader
| > so
| > | > that others may learn and benefit from your issue.
| > | >
| > | > ========================================
=============
| > | > Business-Critical Phone Support (BCPS) provides you with technical
| > phone
| > | > support at no charge during critical LAN outages or "business down"
| > | > situations. This benefit is available 24 hours a day, 7 days a week
to
| > all
| > | > Microsoft technology partners in the United States and Canada.
| > | >
| > | > This and other support options are available here:
| > | >
| > | > BCPS:
| > | >
| >
https://partner.microsoft.com/US/te...erview/40010469
| > | >
| > | > Others:
| > https://partner.microsoft.com/US/te...upportoverview/
| > | >
| > | > If you are outside the United States, please visit our International
| > | > Support page:
| > | >
http://support.microsoft.com/defaul...rnational.aspx.
| > | > ========================================
=============
| > | >
| > | > This posting is provided "AS IS" with no warranties, and confers no
| > | > rights.
| > | >
| > | >
| > | >
| > | >
| > | > --
| > | > | From: "Beppe" <Beppe@.newsgroups.nospam>
| > | > | Subject: 'Domain\LocalServer$' is not a valid user
| > | > | Date: Mon, 30 Jan 2006 19:48:41 +0100
| > | > | Lines: 49
| > | > | X-Priority: 3
| > | > | X-MSMail-Priority: Normal
| > | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| > | > | X-RFC2646: Format=Flowed; Original
| > | > | Message-ID: <eTBSM3cJGHA.2896@.TK2MSFTNGP09.phx.gbl>
| > | > | Newsgroups: microsoft.public.sqlserver.security
| > | > | NNTP-Posting-Host: txt1.txt.tno.it 138.66.77.70
| > | > | Path:
| > TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| > | > | Xref: TK2MSFTNGXA02.phx.gbl
| > microsoft.public.sqlserver.security:26298
| > | > | X-Tomcat-NG: microsoft.public.sqlserver.security
| > | > |
| > | > | I have a cross-databases SQL application on SQL 2000 servers SP4
| > (db_A
| > | > on
| > | > | localServer, db_B on remoteServer) on Win2003
| > | > | on same domain.
| > | > | Linked server is defined as "Be made by the login's current
security
| > | > | context".
| > | > | The login account is member of a Windows group that is dbowner of
| > both
| > | > | databases.
| > | > | A stored proc on db_A that just contains the SQL statement:
| > | > |
| > | > | begin
| > | > | INSERT INTO
| > | > | [remoteServer].[db_B].[Domain\Wingroup].[remoteTa
ble] (A.[ITEM],
| > | > A.[DESC])
| > | > | SELECT DISTINCT A.ITEM_ID, A.ITEM_DESC
| > | > | FROM [localServer].[db_A].[dbo].[ENTITIES] A,
| > | > | [localServer].[db_B].[dbo].[ITEM_VIEW] B
| > | > | WHERE A.ITEM_ID = B.ITEM_ID
| > | > | end
| > | > |
| > | > | ITEM_VIEW is a view on tables dbo-owned on remoteServer (only the
| > target
| > | > | remote table is owned by Domain\Wingroup)
| > | > |
| > | > | Why
| > | > | - if I exec the stored proc. without the a BEGIN TRANSACTION it
| > works
| > | > | - if I do the same within a transaction:
| > | > |
| > | > | SET XACT_ABORT ON
| > | > | BEGIN TRANSACTION
| > | > | exec sp_A
| > | > | COMMIT
| > | > |
| > | > | I receive the error:
| > | > |
| > | > | Server user 'Domain\LocalServer$' is not a valid user in database
| > | > 'db_A'.
| > | > |
| > | > | I receive the error also if I log MS-Query Anyalzer by sa account
| > | > instead
| > | > of
| > | > | Windows Authetntication.
| > | > | The problem is solved only if I create a local TEMP table in
place
| > of
| > | > the
| > | > | ITEM_VIEW, but from the application point of view is not
acceptable.
| > | > | Additionally, the two servers are trusted for delegation
(Kerberos)
| > and
| > | > the
| > | > | problem is independant of how I set "Allow ownership chain" on two
| > | > databases
| > | > | Could it be a MSDTC problem?
| > | > |
| > | > | Any suggestion is really appreciated,
| > | > | Thanks in advance
| > | > | Beppe
| > | > |
| > | > | --
| > | > |
| > | > |
| > | > |
| > | >
| > |
| > |
| > |
| >
|
|
||||Hi Justin,
I have traced by SQL-profiler on remote computer.
Without BEGIN TRANS, it's always traced the user account (ok).
With BEGIN TRANS:
It starts with the user account then it switches to the computer account as
reported below.
Both the servers' computers are "Kerberos Trusted for delegation for any
service" on AD.
... my suspect is still around a bad behaviour of the DTC. Its Security is
set as Network DTC Access ON,
Allow Inbound and Outbound ON, No mutual authentication, account=NT
authority\Network Service.
Thanks again Justin!
Beppe
On remote server:
______________________________________
LAST Profiler row having user=TEST_DOMAIN\NICK (the user account)
_______________________________________
EventClass=RPC:Completed
declare @.P1 int
set @.P1=180150000
declare @.P2 int
set @.P2=2
declare @.P3 int
set @.P3=4
declare @.P4 int
set @.P4=-1
exec sp_cursoropen @.P1 output, N'select * from
"db_B"."TEST_DOMAIN\TSGROUP"."PRODUCTS"', @.P2 output, @.P3 output, @.P4 output
select @.P1, @.P2, @.P3, @.P4
______________________________________
FIRST Profiler row having user=TEST-DOMAIN\TEST1 (the computer name)
_______________________________________
EventClass=Audit Login
-- network protocol: TCP/IP
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
_______________________________________
SECOND Profiler row with user=TEST-DOMAIN\TEST1 (the computer name)
_______________________________________
EventClass=RPC:Completed
declare @.P1 int
set @.P1=NULL
declare @.P2 bigint
set @.P2=NULL
exec sp_getschemalock @.P1 output, @.P2 output,
N'"AXDB_BEPPE"."dbo"."INVENTTABLE"'
select @.P1, @.P2
Then there's the AUDIT logout and stops.
"Justin Shen[MSFT]" <v-yishen@.online.microsoft.com> wrote in message
news:eEvvVKKKGHA.3152@.TK2MSFTNGXA02.phx.gbl...
> Hi Beppe,
> Thanks for your update.
> It is quite weird that machine account will get involved in this scenario.
> It is possible that the delegation is not enabled in the DC, thus it fails
> to use the current user credential to logon to the remote server. Thus, it
> needs to use the machine account.
> Please capture a Profiler trace on the remote server and run the SQL
> command without transaction and let me know it use which user to logon to
> the remote server. We could know it from the Profiler trace.
> If you have any question, please feel free to let me know.
> Regards,
> Justin Shen
>
> --
> | From: "Beppe" <Beppe@.newsgroups.nospam>
> | References: <eTBSM3cJGHA.2896@.TK2MSFTNGP09.phx.gbl>
> <q778f$iJGHA.3944@.TK2MSFTNGXA02.phx.gbl>
> <uIl0KykJGHA.1088@.tk2msftngp13.phx.gbl>
> <Zyrey5wJGHA.224@.TK2MSFTNGXA02.phx.gbl>
> | Subject: Re: 'Domain\LocalServer$' is not a valid user
> | Date: Wed, 1 Feb 2006 16:32:43 +0100
> | Lines: 315
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <eYeGBT0JGHA.3696@.TK2MSFTNGP15.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.security
> | NNTP-Posting-Host: txt1.txt.tno.it 138.66.77.70
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.security:26323
> | X-Tomcat-NG: microsoft.public.sqlserver.security
> |
> | Hi Justin
> |
> | no, TEST-DOMAIN\TEST1 is the local computer name (= localServer) where I
> run
> | the query and not the user.
> | I login the computer TEST-DOMAIN\TEST1 with the user account
> | TEST_DOMAIN\NICK member of the Windows group TEST_DOMAIN\TSGROUP.
> | That Windows group is defined as dbowner on both databases.
> | In order to work around the error I have additionally to define the
> login
> | TEST-DOMAIN\TEST1$ (local computer name$) and to grant it as datareader
> on
> | remote db.
> |
> | Actually I don't really know if this solution can represent a potential
> | security hole (information disclosure?), but surely it's strange and not
> | regular solution (.. at least for me).
> |
> | Thanks again,
> | Beppe
> |
> | "Justin Shen[MSFT]" <v-yishen@.online.microsoft.com> wrote in message
> | news:Zyrey5wJGHA.224@.TK2MSFTNGXA02.phx.gbl...
> | > Hi Beppe,
> | >
> | > Could you please tell me what is the user TEST-DOMAIN\TEST1$ ? Do you
> | > logon
> | > into the machine with the credential of this user?
> | >
> | > Actually, the current user need the necessary privilege so that it
> could
> | > insert into to the remote server. Why do you think adding the account
> to
> | > the remote server will be a security hole?
> | >
> | > Thanks & Regards,
> | >
> | > Justin Shen
> | >
> | > Microsoft Online Partner Support
> | >
> | > Get Secure! - www.microsoft.com/security
> | >
> | > When responding to posts, please "Reply to Group" via your newsreader
> so
> | > that others may learn and benefit from your issue.
> | >
> | > ========================================
=============
> | > Business-Critical Phone Support (BCPS) provides you with technical
> phone
> | > support at no charge during critical LAN outages or "business down"
> | > situations. This benefit is available 24 hours a day, 7 days a week to
> all
> | > Microsoft technology partners in the United States and Canada.
> | >
> | > This and other support options are available here:
> | >
> | > BCPS:
> | >
> https://partner.microsoft.com/US/te...erview/40010469
> | >
> | > Others:
> https://partner.microsoft.com/US/te...upportoverview/
> | >
> | > If you are outside the United States, please visit our International
> | > Support page:
> | > http://support.microsoft.com/defaul...rnational.aspx.
> | > ========================================
=============
> | >
> | > This posting is provided "AS IS" with no warranties, and confers no
> | > rights.
> | > --
> | > | From: "Beppe" <Beppe@.newsgroups.nospam>
> | > | References: <eTBSM3cJGHA.2896@.TK2MSFTNGP09.phx.gbl>
> | > <q778f$iJGHA.3944@.TK2MSFTNGXA02.phx.gbl>
> | > | Subject: Re: 'Domain\LocalServer$' is not a valid user
> | > | Date: Tue, 31 Jan 2006 10:56:01 +0100
> | > | Lines: 207
> | > | X-Priority: 3
> | > | X-MSMail-Priority: Normal
> | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | > | X-RFC2646: Format=Flowed; Original
> | > | Message-ID: <uIl0KykJGHA.1088@.tk2msftngp13.phx.gbl>
> | > | Newsgroups: microsoft.public.sqlserver.security
> | > | NNTP-Posting-Host: txt1.txt.tno.it 138.66.77.70
> | > | Path:
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
> | > | Xref: TK2MSFTNGXA02.phx.gbl
> microsoft.public.sqlserver.security:26304
> | > | X-Tomcat-NG: microsoft.public.sqlserver.security
> | > |
> | > | Hi Justin,
> | > | thanks a lot for your quick response
> | > |
> | > | >As I understand, the db_B in the select part of the query is on the
> | > | >localserver. Is this right?
> | > |
> | > | Yes, you are right. Actually it was db_A and not db_B. I have
> verified
> | > that
> | > | the problem arises
> | > | also without the stored proc. I retype here the query:
> | > |
> | > | SET XACT_ABORT ON
> | > | BEGIN TRANSACTION
> | > | INSERT INTO
> [RemoteServer].[db_B].[TEST_DOMAIN\TSGROUP].[TargetTable]
> | > | (B.[DP_SKU], B.[ITEM_ID], B.[START_ITEM_ID], B.[COL
OR_ID],
> B.[SIZE_ID],
> | > | B.[CONFIG_ID], B.[CAT_CODE], B.[REGION_ID])
> | > | SELECT DISTINCT A.B_ENTITY_IDEN, A.B_ENTITY_IDEN, A.B_ENTITY_IDEN,
> '',
> | > '',
> | > | '', A.CAT_CODE, 'DAT'
> | > | FROM [LocalServer].[db_A].[dbo].[BASE_ENTITIES] A,
> | > | [LocalServer].[db_A].[dbo].[ITEM_VIEW] B
> | > | WHERE B_ENTITY_IDEN = ITW_ITEM_ID
> | > | COMMIT
> | > |
> | > | Running it by MS-Query Analyzer, the error is:
> | > | Server: Msg 916, Level 14, State 1, Line 3
> | > | Server user 'TEST-DOMAIN\TEST1$' is not a valid user in database
> | > | 'DB_BEPPE'..
> | > |
> | > | TEST-DOMAIN\TEST1 is the computer name of the LocalServer
> | > | DB_BEPPE is the remote Database (=db_B)
> | > |
> | > | No problem without the transaction.
> | > |
> | > | >If you specify the Linked server to use a predefined security
> context
> | > such
> | > | >as SA, will you still encounter the same problem?
> | > |
> | > | If I define the Linked Server as SA in "Be made using the security
> | > context"
> | > | I have NOT the problem (but it is
> | > | not acceptable from security point of view, of course).
> | > |
> | > | Only if I create the login TEST-DOMAIN\TEST1$ on remoteServer and I
> | > grant
> | > | him R\W on [PRODUCTS] table (see below the ITEM_VIEW
> | > | definition) I do NOT get the error (but also this workaround can be
> a
> | > | security hole).
> | > |
> | > | Following additional information:
> | > |
> | > | - the ITEM_VIEW is created in LocalServer:
> | > |
> | > | CREATE VIEW ITEM_VIEW( ITW_ITEM_ID, ITW_ATT_NAME, ITW_ATT_VALUE ) AS
> | > Select
> | > | IT.[ITEMID], 'Item Group', IT.[ITEMGROUPID]
> | > | from
> | > | [RemoteServer].[db_B].[dbo].[PRODUCTS] IT
> | > | where
> | > | IT.[INCLUDE] = 0
> | > |
> | > | - I get the error logging MS-Query Analyzer on LocalServer both as
> user
> | > | member of TEST_DOMAIN\TSGROUP
> | > | via Windows Authentication and as SA\pwd.
> | > |
> | > | - I get the error also if I grant TEST_DOMAIN\TSGROUP as
> | > SystemAdministrator
> | > | of RemoteServer
> | > |
> | > | - the TEST_DOMAIN\TSGROUP is defined as login on both SQL server and
> is
> | > | dbOwner of both databases.
> | > | Only the [TargetTable] it's owned by TEST_DOMAIN\TSGROUP on both
> | > databases.
> | > | All other objects are dbo owned.
> | > |
> | > | - Linked Server is defined as SQL Server, "Be made by the login's
> | > current
> | > | security
> | > | context" and Data Access, RPC, RPC out, Use Remote Collation checked
> ON.
> | > |
> | > | - both SQL server are running as LOCAL SYSTEM
> | > |
> | > | - DTC runs as "NT Authority\Network Service"
> | > |
> | > | Thanks again, Beppe
> | > |
> | > |
> | > | "Justin Shen[MSFT]" <v-yishen@.online.microsoft.com> wrote in mes
sage
> | > | news:q778f$iJGHA.3944@.TK2MSFTNGXA02.phx.gbl...
> | > | > Hi Beppe,
> | > | >
> | > | > This is Justin from Microsoft. Welcome to MSDN managed NewsGroup.
> | > | >
> | > | > As I understand, the db_B in the select part of the query is on
> the
> | > | > localserver. Is this right? I created some test tables and view on
> my
> | > | > machine and it works fine on my side.
> | > | > If you specify the Linked server to use a predefined security
> context
> | > such
> | > | > as SA, will you still encounter the same problem? If you still
> | > encounter
> | > | > the same problem, please let me know the exact error message so
> that I
> | > | > could better understand your issue.
> | > | >
> | > | > If you have any question, please feel free to let me know.
> | > | >
> | > | > Thanks & Regards,
> | > | >
> | > | > Justin Shen
> | > | >
> | > | > Microsoft Online Partner Support
> | > | >
> | > | > Get Secure! - www.microsoft.com/security
> | > | >
> | > | > When responding to posts, please "Reply to Group" via your
> newsreader
> | > so
> | > | > that others may learn and benefit from your issue.
> | > | >
> | > | > ========================================
=============
> | > | > Business-Critical Phone Support (BCPS) provides you with technical
> | > phone
> | > | > support at no charge during critical LAN outages or "business
> down"
> | > | > situations. This benefit is available 24 hours a day, 7 days a
> week
> to
> | > all
> | > | > Microsoft technology partners in the United States and Canada.
> | > | >
> | > | > This and other support options are available here:
> | > | >
> | > | > BCPS:
> | > | >
> | >
> https://partner.microsoft.com/US/te...erview/40010469
> | > | >
> | > | > Others:
> | > https://partner.microsoft.com/US/te...upportoverview/
> | > | >
> | > | > If you are outside the United States, please visit our
> International
> | > | > Support page:
> | > | >
> http://support.microsoft.com/defaul...rnational.aspx.
> | > | > ========================================
=============
> | > | >
> | > | > This posting is provided "AS IS" with no warranties, and confers
> no
> | > | > rights.
> | > | >
> | > | >
> | > | >
> | > | >
> | > | > --
> | > | > | From: "Beppe" <Beppe@.newsgroups.nospam>
> | > | > | Subject: 'Domain\LocalServer$' is not a valid user
> | > | > | Date: Mon, 30 Jan 2006 19:48:41 +0100
> | > | > | Lines: 49
> | > | > | X-Priority: 3
> | > | > | X-MSMail-Priority: Normal
> | > | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | > | > | X-RFC2646: Format=Flowed; Original
> | > | > | Message-ID: <eTBSM3cJGHA.2896@.TK2MSFTNGP09.phx.gbl>
> | > | > | Newsgroups: microsoft.public.sqlserver.security
> | > | > | NNTP-Posting-Host: txt1.txt.tno.it 138.66.77.70
> | > | > | Path:
> | > TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | > | > | Xref: TK2MSFTNGXA02.phx.gbl
> | > microsoft.public.sqlserver.security:26298
> | > | > | X-Tomcat-NG: microsoft.public.sqlserver.security
> | > | > |
> | > | > | I have a cross-databases SQL application on SQL 2000 servers SP4
> | > (db_A
> | > | > on
> | > | > | localServer, db_B on remoteServer) on Win2003
> | > | > | on same domain.
> | > | > | Linked server is defined as "Be made by the login's current
> security
> | > | > | context".
> | > | > | The login account is member of a Windows group that is dbowner
> of
> | > both
> | > | > | databases.
> | > | > | A stored proc on db_A that just contains the SQL statement:
> | > | > |
> | > | > | begin
> | > | > | INSERT INTO
> | > | > | [remoteServer].[db_B].[Domain\Wingroup].[remote
Table]
> (A.[ITEM],
> | > | > A.[DESC])
> | > | > | SELECT DISTINCT A.ITEM_ID, A.ITEM_DESC
> | > | > | FROM [localServer].[db_A].[dbo].[ENTITIES] A,
> | > | > | [localServer].[db_B].[dbo].[ITEM_VIEW] B
> | > | > | WHERE A.ITEM_ID = B.ITEM_ID
> | > | > | end
> | > | > |
> | > | > | ITEM_VIEW is a view on tables dbo-owned on remoteServer (only
> the
> | > target
> | > | > | remote table is owned by Domain\Wingroup)
> | > | > |
> | > | > | Why
> | > | > | - if I exec the stored proc. without the a BEGIN TRANSACTION it
> | > works
> | > | > | - if I do the same within a transaction:
> | > | > |
> | > | > | SET XACT_ABORT ON
> | > | > | BEGIN TRANSACTION
> | > | > | exec sp_A
> | > | > | COMMIT
> | > | > |
> | > | > | I receive the error:
> | > | > |
> | > | > | Server user 'Domain\LocalServer$' is not a valid user in
> database
> | > | > 'db_A'.
> | > | > |
> | > | > | I receive the error also if I log MS-Query Anyalzer by sa
> account
> | > | > instead
> | > | > of
> | > | > | Windows Authetntication.
> | > | > | The problem is solved only if I create a local TEMP table in
> place
> | > of
> | > | > the
> | > | > | ITEM_VIEW, but from the application point of view is not
> acceptable.
> | > | > | Additionally, the two servers are trusted for delegation
> (Kerberos)
> | > and
> | > | > the
> | > | > | problem is independant of how I set "Allow ownership chain" on
> two
> | > | > databases
> | > | > | Could it be a MSDTC problem?
> | > | > |
> | > | > | Any suggestion is really appreciated,
> | > | > | Thanks in advance
> | > | > | Beppe
> | > | > |
> | > | > | --
> | > | > |
> | > | > |
> | > | > |
> | > | >
> | > |
> | > |
> | > |
> | >
> |
> |
> |
>|||Hi Beppe,
Would you please try the following step to solve this problem?
On both machines, open regedt32.exe, go to
HKEY_LOCAL_MACHINE\Software\Microsoft\MS
DTC, add a DWORD named
TurnOffRpcSecurity with the value 1 under this key, then restart MSDTC to
see if it works
If all above still could not resolved issue, what's the detailed Windows
Error message for the failure of running distributed
transaction error?
Further more, did you configure the firewall on both machines?
The following article may be helpful to troubleshoot the DTC issue
How to troubleshoot MS DTC firewall issues
http://support.microsoft.com/kb/306843/
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
- I had already TurnOffRpcSecurity=1 on both machines
- Unfortunately I have no windows error. In Event Viewer on the remote
system I have Security Audit attempts of the LocalComputer
- The firewall service is not installed
The problem is only on that particular query, not on others and only within
a BEGIN TRAN-COMMIT
Thanks again
"Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
news:B0t7CtxKGHA.1240@.TK2MSFTNGXA02.phx.gbl...
> Hi Beppe,
> Would you please try the following step to solve this problem?
> On both machines, open regedt32.exe, go to
> HKEY_LOCAL_MACHINE\Software\Microsoft\MS
DTC, add a DWORD named
> TurnOffRpcSecurity with the value 1 under this key, then restart MSDTC to
> see if it works
> If all above still could not resolved issue, what's the detailed Windows
> Error message for the failure of running distributed
> transaction error?
> Further more, did you configure the firewall on both machines?
> The following article may be helpful to troubleshoot the DTC issue
> How to troubleshoot MS DTC firewall issues
> http://support.microsoft.com/kb/306843/
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
