Re: 'Domain\LocalServer$' is not a valid user
- From: "Beppe" <Beppe@xxxxxxxxxxxxxxxxx>
- Date: Tue, 31 Jan 2006 10:56:01 +0100
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_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@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:q778f$iJGHA.3944@xxxxxxxxxxxxxxxxxxxxxxxx
> 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/technicalsupport/supportoverview/40010469
>
> Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
>
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
> =====================================================
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
>
>
> --------------------
> | From: "Beppe" <Beppe@xxxxxxxxxxxxxxxxx>
> | 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@xxxxxxxxxxxxxxxxxxxx>
> | 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
> |
> | --
> |
> |
> |
>
.
- References:
- 'Domain\LocalServer$' is not a valid user
- From: Beppe
- RE: 'Domain\LocalServer$' is not a valid user
- From: Justin Shen[MSFT]
- 'Domain\LocalServer$' is not a valid user
- Prev by Date: RE: 'Domain\LocalServer$' is not a valid user
- Next by Date: Re: MYSQL Question
- Previous by thread: RE: 'Domain\LocalServer$' is not a valid user
- Next by thread: Server LogIn's without db roles
- Index(es):
Relevant Pages
|
|
