Re: 'Domain\LocalServer$' is not a valid user
- From: "Beppe" <Beppe@xxxxxxxxxxxxxxxxx>
- Date: Fri, 3 Feb 2006 20:07:05 +0100
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@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:eEvvVKKKGHA.3152@xxxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxx>
| References: <eTBSM3cJGHA.2896@xxxxxxxxxxxxxxxxxxxx>
<q778f$iJGHA.3944@xxxxxxxxxxxxxxxxxxxxx>
<uIl0KykJGHA.1088@xxxxxxxxxxxxxxxxxxxx>
<Zyrey5wJGHA.224@xxxxxxxxxxxxxxxxxxxxx>
| 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@xxxxxxxxxxxxxxxxxxxx>
| 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@xxxxxxxxxxxxxxxxxxxx> wrote in message
| news:Zyrey5wJGHA.224@xxxxxxxxxxxxxxxxxxxxxxxx
| > 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/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>
| > | References: <eTBSM3cJGHA.2896@xxxxxxxxxxxxxxxxxxxx>
| > <q778f$iJGHA.3944@xxxxxxxxxxxxxxxxxxxxx>
| > | 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@xxxxxxxxxxxxxxxxxxxx>
| > | 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_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
| > | > |
| > | > | --
| > | > |
| > | > |
| > | > |
| > | >
| > |
| > |
| > |
| >
|
|
|
.
- Follow-Ups:
- Re: 'Domain\LocalServer$' is not a valid user
- From: Wei Lu
- Re: 'Domain\LocalServer$' is not a valid user
- References:
- Re: 'Domain\LocalServer$' is not a valid user
- From: Justin Shen[MSFT]
- Re: 'Domain\LocalServer$' is not a valid user
- From: Beppe
- Re: 'Domain\LocalServer$' is not a valid user
- From: Justin Shen[MSFT]
- Re: 'Domain\LocalServer$' is not a valid user
- Prev by Date: Re: Restrict user from default tables (access given by public role)
- Next by Date: Re: SQLServer 2005 connection / user configuration issues
- Previous by thread: Re: 'Domain\LocalServer$' is not a valid user
- Next by thread: Re: 'Domain\LocalServer$' is not a valid user
- Index(es):
Relevant Pages
|
