Drill down slow: domain

Showing posts with label domain. Show all posts

Sunday, February 26, 2012

DomainUserAccount?

Is there any PROC or variable that can tell me what the domain login name is
of the user asking for data from SQL server?
reg.
Kjell BrandesCheck out SUSER_SNAME() in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Kjell Brandes" <kjell@.brandes.tv> wrote in message
news:OQdF9kGMFHA.2748@.TK2MSFTNGP09.phx.gbl...
Is there any PROC or variable that can tell me what the domain login name is
of the user asking for data from SQL server?
reg.
Kjell Brandes

domains and workgroups

Is it possible to add a new user (with Windows authentication) for a user
within a Windows workgroup rather than a Domain? On Workgroup (peer-to-peer)
networks I get "Windows NT user or group 'Workgroup\user' not found. Check
the name again." from sp_grantlogin - the same if I use MachineName\username
instead of workgroup\username.
Can we only use SQL authentication in workgroups?
Hi
SQL Server only supports Domain accounts for Integrated security. Workgroups
are too loosely coupled to guarantee security and correct contexts.
You option is domain or SQL Security.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"quilkin" <quilkin@.discussions.microsoft.com> wrote in message
news:B98997A3-E80A-4796-8234-2FFDE83BEC7C@.microsoft.com...
> Is it possible to add a new user (with Windows authentication) for a user
> within a Windows workgroup rather than a Domain? On Workgroup
(peer-to-peer)
> networks I get "Windows NT user or group 'Workgroup\user' not found. Check
> the name again." from sp_grantlogin - the same if I use
MachineName\username
> instead of workgroup\username.
> Can we only use SQL authentication in workgroups?
>

domains

Hi group,
Reporting service work fine in a server with windows 2000 with out domain,
else with workgroup'
thanks,
MaryMary - reporting services can be installed without a domain.
However, users will still have to be authenticated when they navigate to
your report server. When they do so, they have to use a user account that
exists on that server. Of course, you could turn off authenication and allow
anonymous but I don't recommend that.
Easier using a domain... :)
=-Chris
"Mary" <maria.carrero@.gmail.com> wrote in message
news:ucR1Zvc$GHA.3572@.TK2MSFTNGP02.phx.gbl...
> Hi group,
> Reporting service work fine in a server with windows 2000 with out domain,
> else with workgroup'
> thanks,
> Mary
>

domain\user cannot log on SQL server 2005

Hi All,

I have an issue to connect to SQL 2005. My user is also declared in SQL 2005 as sysadmin.

C:\>ECHO %USERDOMAIN%\%USERNAME%
CMVAL\damien

C:\>sqlcmd -S CM-DB
1> quit

C:\>sqlcmd -S CM-DB -U "CMVAL\damien" -P "ThePassword"
Msg 18456, Level 14, State 1, Server CM-DB, Line 1
Login failed for user 'CMVAL\damien'.

C:\>

Seems that the system reject the domain I've provided. Some of my service failed to log to SQL Server due to this issue.

Thanks for your help.

Damien

By default, sqlcmd uses Windows Authentication. If you are using windows system, then you automatically get authenticated through windows authentication mode by not using -U options.

While if you do use -U, then it means you will be authenticated using Sql login mode, you need to use the sql server logins, not windows logins.

You may refer to http://msdn2.microsoft.com/en-us/library/ms165702.aspx for more information.

|||

Thanks LY.J for your answer.

In fact, my original issue is that I have a lot of this error in the event viewer :

Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 18456
Date: 13/12/2006
Time: 09:42:29
User: CMVAL\damien
Computer: CM-DB
Description:
Login failed for user 'CMVAL\damien'. [CLIENT: 10.194.94.8]

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 18 48 00 00 0e 00 00 00 .H......
0008: 06 00 00 00 43 00 4d 00 ....C.M.
0010: 2d 00 44 00 42 00 00 00 -.D.B...
0018: 07 00 00 00 6d 00 61 00 ....m.a.
0020: 73 00 74 00 65 00 72 00 s.t.e.r.
0028: 00 00 ..

Then I suppose that if I can't connect to the DB with sqlcmd providing -U "CMVAL\damien", my service could not either connect.
But according to what you said, it seems that the issue is elsewhere.

Do you have an idea on how I can investigate on it?

Thanks
Damien

|||

Has the account CMVAL\damien expired?

This happens when you create an account specifically for a service and forget to set the 'does not expire' attribute.

If so, change the password, and logoff/logon

Also I had a case where a user could not be authenticated to SQL because he used a German keyboard which typed 'y' in some applications and 'z' in other applications... :)

|||

Try running a command prompt under the damien credentials, then connect using -E option. Also, have a look at the SQL Server ERRORLOG file and let us know what is the state of the error printed there (it will be different from the one printed by the client tool).

Thanks
Laurentiu

DOMAIN\REMOTE_MACHINE_NAME$ account

does anyone know under what circumstances a SQL Server login from
code/services running on a remote server would be attempted using an account
named DOMAIN\REMOTE_MACHINE_NAME$ where REMOTE_MACHINE_NAME is the machine
where the code is running (and of course failing) ...
specifically i am talking about a call to a WebStore web service that then
makes the call to SQL Server, where the service has been configured to use a
specific domain account (that usually is utilized and works)When the code on the remote machine is running under a local security
context (like LocalSystem, Network Service, Local Service etc) would be the
most common scenario.
Cheers
Ken
Blog: www.adopenstatic.com/cs/blogs/ken/
Web: www.adopenstatic.com
"John Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:uKiQujSRFHA.3544@.TK2MSFTNGP12.phx.gbl...
: does anyone know under what circumstances a SQL Server login from
: code/services running on a remote server would be attempted using an
account
: named DOMAIN\REMOTE_MACHINE_NAME$ where REMOTE_MACHINE_NAME is the machine
: where the code is running (and of course failing) ...
:
: specifically i am talking about a call to a WebStore web service that then
: makes the call to SQL Server, where the service has been configured to use
a
: specific domain account (that usually is utilized and works)
:
:

'Domain\LocalServer$' is not a valid user

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.
>

Domain vs Local

What are gotchas for starting Sql & the agent with a Local system account
versus a system Domain account.Hi

IMO the main reason to use a domain account is permissions (lack of, or too
high) and the ability to assign them to the account.

John
"AHartman" <Hoosbruin@.Kconline.com> wrote in message
news:d6adndqf25WCUj3cRVn-oQ@.kconline.com...
>
> What are gotchas for starting Sql & the agent with a Local system account
> versus a system Domain account.|||AHartman (Hoosbruin@.Kconline.com) writes:
> What are gotchas for starting Sql & the agent with a Local system account
> versus a system Domain account.

One gotcha that people run from time to time, is that they want to access
network shares from SQL Server, for instance for BULK INSERT. This does not
work with Local System. Another gotcha is that SQL debugging from another
machine usually does not work if you are running under local system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <Xns95A8D9056DF1CYazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> AHartman (Hoosbruin@.Kconline.com) writes:
> > What are gotchas for starting Sql & the agent with a Local system account
> > versus a system Domain account.
> One gotcha that people run from time to time, is that they want to access
> network shares from SQL Server, for instance for BULK INSERT. This does not
> work with Local System. Another gotcha is that SQL debugging from another
> machine usually does not work if you are running under local system.

Actually, of you understand NET USE, then you don't have to have the SQL
Server in the domain. If you provide the credentials it works just fine.

In most cases, where there is some form of web interface, even for a
intranet, I always setup the servers without providing Domain User
account access to it. The one exception being an application that uses
Windows Authentication Accounts, instead of a programatic method, to
access data.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)

Domain Users Rights

I have a problem that im using Active Directory in Win2k Server, when domain users logon to workstations they can't open sql server databases, SQL server is local installed on Workstation and operating system is XP.

When i give administrator rights to users the sql server works fine.

Tell me is there is a way to use sql server without giving to the user administrative rights?

thanksYou need to login as the account that the SQL Server is running in on their boxes (set up a SQL Server "account". You then need to go into SQL Server while logged in as this account and add Domain Users as a login in SQL Server and grant permissions. They can then login and do what they need to do.

Domain User privilege for connecting SQL server 2000

Hi,

My server administrator has created a few users as domain user privilege for connecting SQL server from my application. However it faild to connect, and

with Domain Admin privilege, my app is running ok.

I asked admin person to upgrade domain user privilege to connect SQL server,

but he couldn't do this for security reason, and told it should work with domain user.

Is it true for domain user to connect SQL server without any problem?

Do I need to add the user, domain user, into Logins of Security inside SQL server Enterprise Manager?

Or inside users of my database section of SQL server EManager?

Thanks,

dixon

You have to create a login in SQL Server 2000 that corresponds to the domain user. Look at sp_grantlogin:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ga-gz_8dri.asp

Thanks
Laurentiu

domain user or local system account ?

Hello,
My server is part of a W2K domain. What do you advice me as account to
run my SQL*Server, service started with a domain user account or as
local system ?

I need advices from a security point of view.

Thank's in advanceDepends on your requirements...

First, I would personally never recommend running SQL in a production
environment as Local System.

If the SQL proccess will ever need to connect to file shares on other
machines (like to backup data to remote location etc...) then you can either
use a domain account, or use local accounts with matching usernames and
passwords on the SQL server and any machine it may connect to.

If your SQL proccess will never connect to File shares, or similar resources
on the network, then you should run it with a Local User account on the box.

But of course, this is just my opinion.

"Kona" <kona_iron@.yahoo.fr> wrote in message
news:a1de658a.0404191607.75ec6a49@.posting.google.c om...
> Hello,
> My server is part of a W2K domain. What do you advice me as account to
> run my SQL*Server, service started with a domain user account or as
> local system ?
> I need advices from a security point of view.
> Thank's in advance|||Thank you for your opinion.

And I also agree to not use Local System, all the more since this
account as network access into the same W2K domain (that was not the
case under NT).

"GL" <GL@.noSpam.ReplyToNewsgroup.com> wrote in message news:<108ddd45gbs8k69@.news.supernews.com>...
> Depends on your requirements...
> First, I would personally never recommend running SQL in a production
> environment as Local System.
> If the SQL proccess will ever need to connect to file shares on other
> machines (like to backup data to remote location etc...) then you can either
> use a domain account, or use local accounts with matching usernames and
> passwords on the SQL server and any machine it may connect to.
> If your SQL proccess will never connect to File shares, or similar resources
> on the network, then you should run it with a Local User account on the box.
> But of course, this is just my opinion.
> GL
> "Kona" <kona_iron@.yahoo.fr> wrote in message
> news:a1de658a.0404191607.75ec6a49@.posting.google.c om...
> > Hello,
> > My server is part of a W2K domain. What do you advice me as account to
> > run my SQL*Server, service started with a domain user account or as
> > local system ?
> > I need advices from a security point of view.
> > Thank's in advance|||Depends on your requirements...

First, I would personally never recommend running SQL in a production
environment as Local System.

If your SQL proccess will never connect to File shares, or similar resources
on the network, then you should run it with a Local User account on the box.

But of course, this is just my opinion.

And I also agree to not use Local System, all the more since this
account as network access into the same W2K domain (that was not the
case under NT).

Domain User Groups

Hi

Im currently working on a intranet and trying to set up some security. The intranet acesses a SQL server 2000 database. I would like to know if there is a stored procedure(or other way) of returning all the domain groups that a user belongs to when passed the users NT login. I found xp_enumgroups which returns all the groups on the domain and also xp_logininfo which returns the users of a passed domain group. These are usful but i need to just pass the NT username and return all the Domain Groups. Any thoughts, ideas would be great!

CheersI don't know of any way to do this using Microsoft supplied code in SQL Server. The code could certainly be written, but I think it is much easier to do at the client side than on the server.

-PatP

Domain user can't hit SQL Server

Hello,
I have a user on Windows 2000 running an application written in VB.net
that uses a config file with a connection string to hit a SQL 2000 DB
running on a Windows 2000 Server box. The connection string looks
something like this:
Provider=SQLOLEDB;Server=MyServer;Databa
se=MYDB;Persist Security
Info=False;User Id=shipio;
When the user tries to perform a task that requires a DB connection, a
message pops up saying that Domain\UserAccount does not have permissions
to access the SQL server. However, there is a sql server username and
password in the connection string. Why doesn't it user it?
The application works fine when it's installed on Win XP PCs, but it
doesn't work on a win 2000 PC. Does Win2000 have a different need for
connection strings? Or does it by default send the domain account to
the sql server?
Any thoughts are greatly appreciated.
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Connection strings that pass a username are for Standard SQL
Authentication. Not Windows Authentication.
Check the Authentication Mode of SQL. It appears to be set to Windows Auth
only. I you intend on using Standard SQL Security, then your
Server settings for SQL need to be changed to allow both.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||This is actually only a problem on the Windows 2000 pc. The sql server
is set up for windows and sql server authentication. We have 20 other
users on Windows XP where this works fine. It's just the 2000 box that
gets the error.
Does the connection string maybe need some additional properties for it
to work on 2000?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Try connecting from the Windows 2000 client using Query Analyser or OSQL
with a Trusted Connection.
OSQL -SserverNameHere -E -Q"Select @.@.version"
Do you receive the same error?
Try using Standard Security.
OSQL -SserverNameHere -Uuserid -Ppassword -Q"Select @.@.version"
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

domain user belonging to multiple windows group

If I have a domain user DOMAIN\user1 who belongs to multiple window groups say DOMAIN\LookupConfigUsers and DOMAIN\AuditConfigUsers. In sqlserver, I would create two logins - DOMAIN\LookupConfigUsers and DOMAIN\AuditConfigUsers and matching users in the database. Then I grant LookupConfig role to the LookupConfigUsers user and AuditConfig role to the AuditConfigUsers user in the database. When DOMAIN\user1 logs in, will it have both roles? I try to set this up but it does not seem to work. The domain user only picks up one of the role. Am I on the right track? If not, what is the proper way to grant multiple roles to a user when it belongs to multiple groups and each group has different privileges in the database.

Based on the scenario you described, DOMAIN\user should be a member of both roles ( LookupConfig role & AuditConfig role). You can use the following script to take a look to the login and user token on the current connection:

SELECT name, type, usage FROM sys.login_token ORDER BY type, name

SELECT name, type, usage FROM sys.user_token ORDER BY type, name

In the login token you should be able to look at all the server and Windows groups that are part of the token, while on the user token view you should be able to see the database roles and Windows roles that are part of the context.

The usage column will tell you how each row is used, typically it should be “GRANT OR DENY”, which means that row is being used to evaluate for both granted and denied permissions, while a “DENY ONLY” value means that only denied permissions will be honored.

Please let us know the results of this query. If you see both roles, my guess would be that either LookupConfig or AuditConfgi role has an explicit denied permission that is affecting the permission check.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

It works now. It's actually my fault. I had one group defined initially and later when I add the other group, I simply disconnect and reconnect from the Management Studio. It did not pick up the new group. Today, I tried closing the Management Studio and reopen it, and it picks up both groups and roles. Your sql statements above showed me exactly what I expected - domin\user belongs to both LookConfig and AuditConfig Role.

However, I don't understand why it also belongs to the public role?

Also, the login_token output names like -

NT AUTHORITY\Authenticated Users, NT AUTHORITY\INTERACTIVE, NT AUTHORITY\NTLM Authentication, domain\None, \Everyone, etc...

What are these groups?

I am new to sqlserver and windows authentication. Any help would be much appreciated.

|||

No problem, we are happy to help.The public role is a role that everyone in the system belongs to, think of the public as “Everyone”. If you grant any permission to public, everyone will get it. This behavior is automatic and cannot be changed.

Regarding the other Windows groups in the login token. To generate the SQL Server login token we take the token from the Windows client connection and get every group from it. You can double check it in Windows using the whoami tool (i.e. whoami /groups).

I hope this information helps. Let us know if you have any further question, we will be glad to help.