All processes have slowed to a crawl - 1 user running a
simple query in ARC brings all app's residing on this
machine to a crawl. Machine resourses are abundant and
available. I know these are VERY sparse details... I'll
provide whatever info I can if someone can let us know
something. ThanxThis is a multi-part message in MIME format.
--=_NextPart_000_04C0_01C3CE1E.3D63B070
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Some suggestions:
1. Run DBCC OPENTRAN for your database. See what SPID has the longest
running txn. Run DBCC INPUTBUFFER on the SPID.
2. Run sp_lock and see who is putting up the most locks.
3. Based on the above, consider killing the offending SPID.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:07aa01c3ce47$4cc7aca0$a001280a@.phx.gbl...
All processes have slowed to a crawl - 1 user running a
simple query in ARC brings all app's residing on this
machine to a crawl. Machine resourses are abundant and
available. I know these are VERY sparse details... I'll
provide whatever info I can if someone can let us know
something. Thanx
--=_NextPart_000_04C0_01C3CE1E.3D63B070
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Some suggestions:
1. Run DBCC OPENTRAN =for your database. See what SPID has the longest running txn. Run =DBCC INPUTBUFFER on the SPID.
2. Run sp_lock and =see who is putting up the most locks.
3. Based on =the above, consider killing the offending SPID.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"David" wrote in message news:07aa01c3ce47$4c=c7aca0$a001280a@.phx.gbl...All processes have slowed to a crawl - 1 user running a simple query in =ARC brings all app's residing on this machine to a crawl. Machine resourses are abundant and available. I know these are VERY =sparse details... I'll provide whatever info I can if someone can let us =know something. Thanx
--=_NextPart_000_04C0_01C3CE1E.3D63B070--|||What are your server specs? Can you run other operations on the server
quickly(ie. is it SQL or the whole box)?
Other things to list --
mem usage
Free pages
IO
CPU usage
Cost of the query in question
We have had random slow and stoppage of performance on SQL2K.
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:07aa01c3ce47$4cc7aca0$a001280a@.phx.gbl...
> All processes have slowed to a crawl - 1 user running a
> simple query in ARC brings all app's residing on this
> machine to a crawl. Machine resourses are abundant and
> available. I know these are VERY sparse details... I'll
> provide whatever info I can if someone can let us know
> something. Thanx|||1gb RAM
4gb harddrive available (about 40%)
2 - 933 processors
ALL application are affected. Task Mgr show both 933
processors at 100 capacity. All seem fine until we run a
simple SQL query on an ARC database. THAT qry soaks up
100% RAM.
>--Original Message--
>What are your server specs? Can you run other operations
on the server
>quickly(ie. is it SQL or the whole box)?
>Other things to list --
>mem usage
>Free pages
>IO
>CPU usage
>Cost of the query in question
>We have had random slow and stoppage of performance on
SQL2K.
>"David" <anonymous@.discussions.microsoft.com> wrote in
message
>news:07aa01c3ce47$4cc7aca0$a001280a@.phx.gbl...
>> All processes have slowed to a crawl - 1 user running a
>> simple query in ARC brings all app's residing on this
>> machine to a crawl. Machine resourses are abundant and
>> available. I know these are VERY sparse details... I'll
>> provide whatever info I can if someone can let us know
>> something. Thanx
>
>.
>|||What is the cost of the query? Can you run --
SET SHOWPLAN_ALL ON
GO
<QUERY IN QUESTION>
and post the output.
Also there is a SQL bug on SQL2K that will create mem leaks when jumping
between DBs in a query, is that happening? What version of SQL and SP are
you on?
"DAvid" <anonymous@.discussions.microsoft.com> wrote in message
news:02f501c3ce4c$25d52d70$a301280a@.phx.gbl...
> 1gb RAM
> 4gb harddrive available (about 40%)
> 2 - 933 processors
> ALL application are affected. Task Mgr show both 933
> processors at 100 capacity. All seem fine until we run a
> simple SQL query on an ARC database. THAT qry soaks up
> 100% RAM.
>
> >--Original Message--
> >What are your server specs? Can you run other operations
> on the server
> >quickly(ie. is it SQL or the whole box)?
> >
> >Other things to list --
> >mem usage
> >Free pages
> >IO
> >CPU usage
> >Cost of the query in question
> >
> >We have had random slow and stoppage of performance on
> SQL2K.
> >
> >"David" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:07aa01c3ce47$4cc7aca0$a001280a@.phx.gbl...
> >> All processes have slowed to a crawl - 1 user running a
> >> simple query in ARC brings all app's residing on this
> >> machine to a crawl. Machine resourses are abundant and
> >> available. I know these are VERY sparse details... I'll
> >> provide whatever info I can if someone can let us know
> >> something. Thanx
> >
> >
> >.
> >|||Please elaborate on this "bug"., KB articles, PSS cases, thanks.
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Kevin Brooks" <jeepnreb@.yahoo.com> wrote in message
news:uL9#ZMlzDHA.2456@.TK2MSFTNGP10.phx.gbl...
> What is the cost of the query? Can you run --
> SET SHOWPLAN_ALL ON
> GO
> <QUERY IN QUESTION>
> and post the output.
> Also there is a SQL bug on SQL2K that will create mem leaks when jumping
> between DBs in a query, is that happening? What version of SQL and SP are
> you on?
>
> "DAvid" <anonymous@.discussions.microsoft.com> wrote in message
> news:02f501c3ce4c$25d52d70$a301280a@.phx.gbl...
> > 1gb RAM
> > 4gb harddrive available (about 40%)
> > 2 - 933 processors
> >
> > ALL application are affected. Task Mgr show both 933
> > processors at 100 capacity. All seem fine until we run a
> > simple SQL query on an ARC database. THAT qry soaks up
> > 100% RAM.
> >
> >
> > >--Original Message--
> > >What are your server specs? Can you run other operations
> > on the server
> > >quickly(ie. is it SQL or the whole box)?
> > >
> > >Other things to list --
> > >mem usage
> > >Free pages
> > >IO
> > >CPU usage
> > >Cost of the query in question
> > >
> > >We have had random slow and stoppage of performance on
> > SQL2K.
> > >
> > >"David" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > >news:07aa01c3ce47$4cc7aca0$a001280a@.phx.gbl...
> > >> All processes have slowed to a crawl - 1 user running a
> > >> simple query in ARC brings all app's residing on this
> > >> machine to a crawl. Machine resourses are abundant and
> > >> available. I know these are VERY sparse details... I'll
> > >> provide whatever info I can if someone can let us know
> > >> something. Thanx
> > >
> > >
> > >.
> > >
>|||Will have to respond tomorrow - office is closed for the
day
>--Original Message--
>What is the cost of the query? Can you run --
>SET SHOWPLAN_ALL ON
>GO
><QUERY IN QUESTION>
>and post the output.
>Also there is a SQL bug on SQL2K that will create mem
leaks when jumping
>between DBs in a query, is that happening? What version
of SQL and SP are
>you on?
>
>"DAvid" <anonymous@.discussions.microsoft.com> wrote in
message
>news:02f501c3ce4c$25d52d70$a301280a@.phx.gbl...
>> 1gb RAM
>> 4gb harddrive available (about 40%)
>> 2 - 933 processors
>> ALL application are affected. Task Mgr show both 933
>> processors at 100 capacity. All seem fine until we run
a
>> simple SQL query on an ARC database. THAT qry soaks up
>> 100% RAM.
>>
>> >--Original Message--
>> >What are your server specs? Can you run other
operations
>> on the server
>> >quickly(ie. is it SQL or the whole box)?
>> >
>> >Other things to list --
>> >mem usage
>> >Free pages
>> >IO
>> >CPU usage
>> >Cost of the query in question
>> >
>> >We have had random slow and stoppage of performance on
>> SQL2K.
>> >
>> >"David" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:07aa01c3ce47$4cc7aca0$a001280a@.phx.gbl...
>> >> All processes have slowed to a crawl - 1 user
running a
>> >> simple query in ARC brings all app's residing on this
>> >> machine to a crawl. Machine resourses are abundant
and
>> >> available. I know these are VERY sparse details...
I'll
>> >> provide whatever info I can if someone can let us
know
>> >> something. Thanx
>> >
>> >
>> >.
>> >
>
>.
>|||It was a bug that hit us a few years back. We did not open a PSS case, but
you can search KB for it(if they acknowledge it). The client apps where
made in PB and a SQL2K(Compaq) server gold release. We no longer see this
problem and are on SP3a/SP2. It was a sporatic and not a serious outage at
the time, it sounded familar to is problem though.
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:uKFCBJmzDHA.1760@.TK2MSFTNGP10.phx.gbl...
> Please elaborate on this "bug"., KB articles, PSS cases, thanks.
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Kevin Brooks" <jeepnreb@.yahoo.com> wrote in message
> news:uL9#ZMlzDHA.2456@.TK2MSFTNGP10.phx.gbl...
> > What is the cost of the query? Can you run --
> >
> > SET SHOWPLAN_ALL ON
> > GO
> > <QUERY IN QUESTION>
> >
> > and post the output.
> >
> > Also there is a SQL bug on SQL2K that will create mem leaks when jumping
> > between DBs in a query, is that happening? What version of SQL and SP
are
> > you on?
> >
> >
> > "DAvid" <anonymous@.discussions.microsoft.com> wrote in message
> > news:02f501c3ce4c$25d52d70$a301280a@.phx.gbl...
> > > 1gb RAM
> > > 4gb harddrive available (about 40%)
> > > 2 - 933 processors
> > >
> > > ALL application are affected. Task Mgr show both 933
> > > processors at 100 capacity. All seem fine until we run a
> > > simple SQL query on an ARC database. THAT qry soaks up
> > > 100% RAM.
> > >
> > >
> > > >--Original Message--
> > > >What are your server specs? Can you run other operations
> > > on the server
> > > >quickly(ie. is it SQL or the whole box)?
> > > >
> > > >Other things to list --
> > > >mem usage
> > > >Free pages
> > > >IO
> > > >CPU usage
> > > >Cost of the query in question
> > > >
> > > >We have had random slow and stoppage of performance on
> > > SQL2K.
> > > >
> > > >"David" <anonymous@.discussions.microsoft.com> wrote in
> > > message
> > > >news:07aa01c3ce47$4cc7aca0$a001280a@.phx.gbl...
> > > >> All processes have slowed to a crawl - 1 user running a
> > > >> simple query in ARC brings all app's residing on this
> > > >> machine to a crawl. Machine resourses are abundant and
> > > >> available. I know these are VERY sparse details... I'll
> > > >> provide whatever info I can if someone can let us know
> > > >> something. Thanx
> > > >
> > > >
> > > >.
> > > >
> >
> >
>|||Problem turned out not to be SQL Server generated. Was
instead a problem with SDE reconciliation...sorry!
david
>--Original Message--
>All processes have slowed to a crawl - 1 user running a
>simple query in ARC brings all app's residing on this
>machine to a crawl. Machine resourses are abundant and
>available. I know these are VERY sparse details... I'll
>provide whatever info I can if someone can let us know
>something. Thanx
>.
>
Showing posts with label user. Show all posts
Showing posts with label user. Show all posts
Thursday, March 29, 2012
Drag columns around?
Hello,
I am a complete newbie to CR and am evaluating CR-XI. I have a requirement to create user-customizable reports - i.e. reports where the user can move columns around to get a layout he/she likes for printing. For example, if there were 5 fields in the report:
Column1 Column2 Column3 Column4 Column5
and the user wanted to print the report with the field order:-
Column1 Column2 Column5 Column3 Column4
is it possible for the user to be able to drag the columns around? Being an amateur, could someone please list the steps I need to go thru to achieve this? Is this possible programatically - via VB6/VB.Net and if so could someone direct/show me some detailed code for this?
Apart from what I described above, the bigger requirement is to create reports at run-time from scratch. I need to create an application that will accept SQL queries from the user, parse it, run it, and display the resultant recordset as a CR. Could someone please direct/show me some code on how to do it - I am a beginner and so will need as much help as possibe.
I use Delphi 7/.Net but am pretty sure I will be able to convert VB to Delphi.
I very much appreciate any help I can get.
Thanks,
VrijeshSee if you find solution at the suppoet section of this site
www.BusinessObjects.comsql
I am a complete newbie to CR and am evaluating CR-XI. I have a requirement to create user-customizable reports - i.e. reports where the user can move columns around to get a layout he/she likes for printing. For example, if there were 5 fields in the report:
Column1 Column2 Column3 Column4 Column5
and the user wanted to print the report with the field order:-
Column1 Column2 Column5 Column3 Column4
is it possible for the user to be able to drag the columns around? Being an amateur, could someone please list the steps I need to go thru to achieve this? Is this possible programatically - via VB6/VB.Net and if so could someone direct/show me some detailed code for this?
Apart from what I described above, the bigger requirement is to create reports at run-time from scratch. I need to create an application that will accept SQL queries from the user, parse it, run it, and display the resultant recordset as a CR. Could someone please direct/show me some code on how to do it - I am a beginner and so will need as much help as possibe.
I use Delphi 7/.Net but am pretty sure I will be able to convert VB to Delphi.
I very much appreciate any help I can get.
Thanks,
VrijeshSee if you find solution at the suppoet section of this site
www.BusinessObjects.comsql
Tuesday, March 27, 2012
DR - Restore Master
I am doing a DR test and I am running the command sqlservr.exe -c -m to put
the server in single user mode before I restore the Master database. My ques
tion - How long does this command normally run?
Thanks,
JessThe command prompt window will sit there, it is running the sql server =
process, so it stays until you shut it down (either by CTRL + C, or a =
shutdown command from osql or Query analyser)
Mike John
"Jess" <anonymous@.discussions.microsoft.com> wrote in message =
news:65EAAC26-7D01-4B7A-8357-A88735A63000@.microsoft.com...
> I am doing a DR test and I am running the command sqlservr.exe -c -m =
to put the server in single user mode before I restore the Master =
database. My question - How long does this command normally run?=20
>=20
> Thanks,
> Jess|||sqlservr.exe is the service. When you run it with a switch in a command
window it will keep running as long as SQL Server is running in single user
mode.
Christian Smith
"Jess" <anonymous@.discussions.microsoft.com> wrote in message
news:65EAAC26-7D01-4B7A-8357-A88735A63000@.microsoft.com...
> I am doing a DR test and I am running the command sqlservr.exe -c -m to
put the server in single user mode before I restore the Master database. My
question - How long does this command normally run?
> Thanks,
> Jess|||Great, thanks guys! I logged into QA to see if it was in single user mode an
d it was but not sure if there was any way the command prompt would give any
notification that it was in single user mode. My sits there for awhile afte
r Starting up database 'msd
b'.
the server in single user mode before I restore the Master database. My ques
tion - How long does this command normally run?
Thanks,
JessThe command prompt window will sit there, it is running the sql server =
process, so it stays until you shut it down (either by CTRL + C, or a =
shutdown command from osql or Query analyser)
Mike John
"Jess" <anonymous@.discussions.microsoft.com> wrote in message =
news:65EAAC26-7D01-4B7A-8357-A88735A63000@.microsoft.com...
> I am doing a DR test and I am running the command sqlservr.exe -c -m =
to put the server in single user mode before I restore the Master =
database. My question - How long does this command normally run?=20
>=20
> Thanks,
> Jess|||sqlservr.exe is the service. When you run it with a switch in a command
window it will keep running as long as SQL Server is running in single user
mode.
Christian Smith
"Jess" <anonymous@.discussions.microsoft.com> wrote in message
news:65EAAC26-7D01-4B7A-8357-A88735A63000@.microsoft.com...
> I am doing a DR test and I am running the command sqlservr.exe -c -m to
put the server in single user mode before I restore the Master database. My
question - How long does this command normally run?
> Thanks,
> Jess|||Great, thanks guys! I logged into QA to see if it was in single user mode an
d it was but not sure if there was any way the command prompt would give any
notification that it was in single user mode. My sits there for awhile afte
r Starting up database 'msd
b'.
DR - Master db restore
I am doing a DR test and running the command sqlservr.exe -c -m to get the s
erver in single user mode. How long does this command normally run?
Thanks,
JessI am assuming you are running this in a command window. If so, it will run
until you hit Ctrl-C and answer yes to the prompt. When you start SQL
server this way it is starting as an application.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
As soon you run sqlservr.exe -c -m from command prompt the sql server starts
in Single user mode. One user can login to SQL server and perform all the
activities. After that you can close the sqlservr.exe which is running from
command prompt using CNTL and C key.
Alternately,
You can start the SQL server in Single user mode using
COntrol panel -- Admin tools -- Services , Select SQL server Services and in
parameters you can put -m and click the start button. This will also start
the SQL server in single user mode. Once the actitivty is completed you can
just restart the service to make it Multiuser.
Thanks
Hari
MCDBA
"Jess" <anonymous@.discussions.microsoft.com> wrote in message
news:3FAD59F2-DA04-490D-B131-EE5D92F6AB9C@.microsoft.com...
> I am doing a DR test and running the command sqlservr.exe -c -m to get the
server in single user mode. How long does this command normally run?
> Thanks,
> Jess|||When you run this from the command prompt, it should take a few seconds to
come up and while it is coming up, it will be writing things to the console
( command prompt), the last message shoujld be 'recovery complete' I think,
and then it stops writing...
The system is not hung.. the server is simply up and running. The server is
running as long as the command window is open... Go ahead and use Query
Analyzer or whatever to do your work, Then Ctrl-C on the command prompt
window to bring SQL back down.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jess" <anonymous@.discussions.microsoft.com> wrote in message
news:3FAD59F2-DA04-490D-B131-EE5D92F6AB9C@.microsoft.com...
> I am doing a DR test and running the command sqlservr.exe -c -m to get the
server in single user mode. How long does this command normally run?
> Thanks,
> Jess|||Thanks everyone!sql
erver in single user mode. How long does this command normally run?
Thanks,
JessI am assuming you are running this in a command window. If so, it will run
until you hit Ctrl-C and answer yes to the prompt. When you start SQL
server this way it is starting as an application.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
As soon you run sqlservr.exe -c -m from command prompt the sql server starts
in Single user mode. One user can login to SQL server and perform all the
activities. After that you can close the sqlservr.exe which is running from
command prompt using CNTL and C key.
Alternately,
You can start the SQL server in Single user mode using
COntrol panel -- Admin tools -- Services , Select SQL server Services and in
parameters you can put -m and click the start button. This will also start
the SQL server in single user mode. Once the actitivty is completed you can
just restart the service to make it Multiuser.
Thanks
Hari
MCDBA
"Jess" <anonymous@.discussions.microsoft.com> wrote in message
news:3FAD59F2-DA04-490D-B131-EE5D92F6AB9C@.microsoft.com...
> I am doing a DR test and running the command sqlservr.exe -c -m to get the
server in single user mode. How long does this command normally run?
> Thanks,
> Jess|||When you run this from the command prompt, it should take a few seconds to
come up and while it is coming up, it will be writing things to the console
( command prompt), the last message shoujld be 'recovery complete' I think,
and then it stops writing...
The system is not hung.. the server is simply up and running. The server is
running as long as the command window is open... Go ahead and use Query
Analyzer or whatever to do your work, Then Ctrl-C on the command prompt
window to bring SQL back down.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jess" <anonymous@.discussions.microsoft.com> wrote in message
news:3FAD59F2-DA04-490D-B131-EE5D92F6AB9C@.microsoft.com...
> I am doing a DR test and running the command sqlservr.exe -c -m to get the
server in single user mode. How long does this command normally run?
> Thanks,
> Jess|||Thanks everyone!sql
DR - Master db restore
I am doing a DR test and running the command sqlservr.exe -c -m to get the server in single user mode. How long does this command normally run
Thanks
JessI am assuming you are running this in a command window. If so, it will run
until you hit Ctrl-C and answer yes to the prompt. When you start SQL
server this way it is starting as an application.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
As soon you run sqlservr.exe -c -m from command prompt the sql server starts
in Single user mode. One user can login to SQL server and perform all the
activities. After that you can close the sqlservr.exe which is running from
command prompt using CNTL and C key.
Alternately,
You can start the SQL server in Single user mode using
COntrol panel -- Admin tools -- Services , Select SQL server Services and in
parameters you can put -m and click the start button. This will also start
the SQL server in single user mode. Once the actitivty is completed you can
just restart the service to make it Multiuser.
Thanks
Hari
MCDBA
"Jess" <anonymous@.discussions.microsoft.com> wrote in message
news:3FAD59F2-DA04-490D-B131-EE5D92F6AB9C@.microsoft.com...
> I am doing a DR test and running the command sqlservr.exe -c -m to get the
server in single user mode. How long does this command normally run?
> Thanks,
> Jess|||When you run this from the command prompt, it should take a few seconds to
come up and while it is coming up, it will be writing things to the console
( command prompt), the last message shoujld be 'recovery complete' I think,
and then it stops writing...
The system is not hung.. the server is simply up and running. The server is
running as long as the command window is open... Go ahead and use Query
Analyzer or whatever to do your work, Then Ctrl-C on the command prompt
window to bring SQL back down.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jess" <anonymous@.discussions.microsoft.com> wrote in message
news:3FAD59F2-DA04-490D-B131-EE5D92F6AB9C@.microsoft.com...
> I am doing a DR test and running the command sqlservr.exe -c -m to get the
server in single user mode. How long does this command normally run?
> Thanks,
> Jess|||Thanks everyone!
Thanks
JessI am assuming you are running this in a command window. If so, it will run
until you hit Ctrl-C and answer yes to the prompt. When you start SQL
server this way it is starting as an application.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
As soon you run sqlservr.exe -c -m from command prompt the sql server starts
in Single user mode. One user can login to SQL server and perform all the
activities. After that you can close the sqlservr.exe which is running from
command prompt using CNTL and C key.
Alternately,
You can start the SQL server in Single user mode using
COntrol panel -- Admin tools -- Services , Select SQL server Services and in
parameters you can put -m and click the start button. This will also start
the SQL server in single user mode. Once the actitivty is completed you can
just restart the service to make it Multiuser.
Thanks
Hari
MCDBA
"Jess" <anonymous@.discussions.microsoft.com> wrote in message
news:3FAD59F2-DA04-490D-B131-EE5D92F6AB9C@.microsoft.com...
> I am doing a DR test and running the command sqlservr.exe -c -m to get the
server in single user mode. How long does this command normally run?
> Thanks,
> Jess|||When you run this from the command prompt, it should take a few seconds to
come up and while it is coming up, it will be writing things to the console
( command prompt), the last message shoujld be 'recovery complete' I think,
and then it stops writing...
The system is not hung.. the server is simply up and running. The server is
running as long as the command window is open... Go ahead and use Query
Analyzer or whatever to do your work, Then Ctrl-C on the command prompt
window to bring SQL back down.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jess" <anonymous@.discussions.microsoft.com> wrote in message
news:3FAD59F2-DA04-490D-B131-EE5D92F6AB9C@.microsoft.com...
> I am doing a DR test and running the command sqlservr.exe -c -m to get the
server in single user mode. How long does this command normally run?
> Thanks,
> Jess|||Thanks everyone!
Sunday, March 25, 2012
download pdf
I was wondering if there is any online user reference/manual for
transact sql (SQL Server) in PDF format? Download links? I know about
online help but I would like to have something locally.
Thanks.If you have sql server installed , then this has BOL installed automatically
transact sql (SQL Server) in PDF format? Download links? I know about
online help but I would like to have something locally.
Thanks.If you have sql server installed , then this has BOL installed automatically
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
<othellomy@.yahoo.comwrote in message
news:1173785538.156525.107820@.h3g2000cwc.googlegro ups.com...
Quote:
Originally Posted by
>I was wondering if there is any online user reference/manual for
transact sql (SQL Server) in PDF format? Download links? I know about
online help but I would like to have something locally.
Thanks.
>
Thursday, March 22, 2012
download excel file
I've uploaded a excel file and i want to force the user to download it
and not display it inline. where can i change the MIME settings of the
report manager?
ThanksHello Owen,
I would like to know this issue a little bit clear.
Do you mean when you click the excel file, you could just open it in the IE?
You could force the IE show the Open & Save dialogbox to enable client user
to download the Excel file.
1. Open a windows explorer.
2. In the Tools -> Folder Options -> File Types -> Find the XLS extension.
3. Click the Advanced button. On the Edit File Type dialogbox, please check
the Confirm open after download and uncheck the Browse in same window.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi
Thanks for the help, but I want to make the report manager only allow
the download of Excel files and not opening of them inline in IE.
Wei Lu [MSFT] wrote:
> Hello Owen,
> I would like to know this issue a little bit clear.
> Do you mean when you click the excel file, you could just open it in the IE?
> You could force the IE show the Open & Save dialogbox to enable client user
> to download the Excel file.
> 1. Open a windows explorer.
> 2. In the Tools -> Folder Options -> File Types -> Find the XLS extension.
> 3. Click the Advanced button. On the Edit File Type dialogbox, please check
> the Confirm open after download and uncheck the Browse in same window.
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Owen,
Unfortunately, you could not do this directly in Reporting Manager.
All the request was handled by the reporting services and we could not
modify it directly.
I would like to know why you want to upload a Excel file to the report
server. Since the report server is used to generate the report, we do not
suggest to upload other file expect the report RDL file to the report
server.
Sincerely,
Wei Lu
Microsoft Online Community 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
I want to distribute a Excelsheet with Olap so power users can customize
their reports.
regardes
Wei Lu [MSFT] wrote:
> Hello Owen,
> Unfortunately, you could not do this directly in Reporting Manager.
> All the request was handled by the reporting services and we could not
> modify it directly.
> I would like to know why you want to upload a Excel file to the report
> server. Since the report server is used to generate the report, we do not
> suggest to upload other file expect the report RDL file to the report
> server.
>
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>|||Hello Owen,
Would you please let me know some details of how you make the power user
customize the report?
Do you mean you use the Excel as the datasource?
Sincerely,
Wei Lu
Microsoft Online Community 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.|||Hello
No Excel has a OLAP-Connection and can be used without reporting
services. I want to change the mime-type so the browser thinks it's a
octead-stream that only leaves the option to save the file and not open
it inline.
Wei Lu [MSFT] wrote:
> Hello Owen,
> Would you please let me know some details of how you make the power user
> customize the report?
> Do you mean you use the Excel as the datasource?
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>|||Hello Owen,
Unfortunately, you could not change the mime-type in the report manager
directly.
You need to force the client to change their client setting so that they
will download the excel file only instead of open it in the browser.
When the client get the Open & Save dialogbox, uncheck the Always ask
before opening this type of file and then click the Save to Save it to the
local.
Then, next time when you open the excel file in the Report manager, you
will get the file open directly in local.
Sincerely,
Wei Lu
Microsoft Online Community 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.|||Hello
Its not me with the problem of saving instead of opening the xls but the
end-user. i want to eliminante a potential mistake of the end-user.
Thats why i wont to adjust the mime-type of the xls. I dont care if i
have to set it in a db-table or config file. I just need to know where
the setting is.
Thanks
Wei Lu [MSFT] wrote:
> Hello Owen,
> Unfortunately, you could not change the mime-type in the report manager
> directly.
> You need to force the client to change their client setting so that they
> will download the excel file only instead of open it in the browser.
> When the client get the Open & Save dialogbox, uncheck the Always ask
> before opening this type of file and then click the Save to Save it to the
> local.
> Then, next time when you open the excel file in the Report manager, you
> will get the file open directly in local.
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>|||Hello Owen,
After consulting the IIS engineer, you could add a MIME type in the IIS
virtual directory.
1. Run "inetmgr" in the start menu.
2. Expand the Web Sites, find the ReportServer virtual directory.
Right-click it and click the Properties.
3. Click the HTTP Headers Tab, then click the MIME Types button and click
New Button.
4. In the Dialogbox, type the .XLS in the entension and octead-stream in
the MIME type section. Click OK.
5. Click OK and then restart the IIS.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community 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
Thanks for trying but if it was so easy i would of succeded myself. The
problem is that i cant change the default mime-types. i added the
mime-type you suggested but it didn't ovverride the existing. Have you
got any other suggestion. Sorry for all the work i'm creating with this
ping-pong request.
Thanks,
Wei Lu [MSFT] wrote:
> Hello Owen,
> After consulting the IIS engineer, you could add a MIME type in the IIS
> virtual directory.
> 1. Run "inetmgr" in the start menu.
> 2. Expand the Web Sites, find the ReportServer virtual directory.
> Right-click it and click the Properties.
> 3. Click the HTTP Headers Tab, then click the MIME Types button and click
> New Button.
> 4. In the Dialogbox, type the .XLS in the entension and octead-stream in
> the MIME type section. Click OK.
> 5. Click OK and then restart the IIS.
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>|||Hello Owen,
Would you please test this on other site?
Also, you may try to use the application/octstream for the MIME type.
Sincerely,
Wei Lu
Microsoft Online Community 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 ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community 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.
and not display it inline. where can i change the MIME settings of the
report manager?
ThanksHello Owen,
I would like to know this issue a little bit clear.
Do you mean when you click the excel file, you could just open it in the IE?
You could force the IE show the Open & Save dialogbox to enable client user
to download the Excel file.
1. Open a windows explorer.
2. In the Tools -> Folder Options -> File Types -> Find the XLS extension.
3. Click the Advanced button. On the Edit File Type dialogbox, please check
the Confirm open after download and uncheck the Browse in same window.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi
Thanks for the help, but I want to make the report manager only allow
the download of Excel files and not opening of them inline in IE.
Wei Lu [MSFT] wrote:
> Hello Owen,
> I would like to know this issue a little bit clear.
> Do you mean when you click the excel file, you could just open it in the IE?
> You could force the IE show the Open & Save dialogbox to enable client user
> to download the Excel file.
> 1. Open a windows explorer.
> 2. In the Tools -> Folder Options -> File Types -> Find the XLS extension.
> 3. Click the Advanced button. On the Edit File Type dialogbox, please check
> the Confirm open after download and uncheck the Browse in same window.
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Owen,
Unfortunately, you could not do this directly in Reporting Manager.
All the request was handled by the reporting services and we could not
modify it directly.
I would like to know why you want to upload a Excel file to the report
server. Since the report server is used to generate the report, we do not
suggest to upload other file expect the report RDL file to the report
server.
Sincerely,
Wei Lu
Microsoft Online Community 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
I want to distribute a Excelsheet with Olap so power users can customize
their reports.
regardes
Wei Lu [MSFT] wrote:
> Hello Owen,
> Unfortunately, you could not do this directly in Reporting Manager.
> All the request was handled by the reporting services and we could not
> modify it directly.
> I would like to know why you want to upload a Excel file to the report
> server. Since the report server is used to generate the report, we do not
> suggest to upload other file expect the report RDL file to the report
> server.
>
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>|||Hello Owen,
Would you please let me know some details of how you make the power user
customize the report?
Do you mean you use the Excel as the datasource?
Sincerely,
Wei Lu
Microsoft Online Community 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.|||Hello
No Excel has a OLAP-Connection and can be used without reporting
services. I want to change the mime-type so the browser thinks it's a
octead-stream that only leaves the option to save the file and not open
it inline.
Wei Lu [MSFT] wrote:
> Hello Owen,
> Would you please let me know some details of how you make the power user
> customize the report?
> Do you mean you use the Excel as the datasource?
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>|||Hello Owen,
Unfortunately, you could not change the mime-type in the report manager
directly.
You need to force the client to change their client setting so that they
will download the excel file only instead of open it in the browser.
When the client get the Open & Save dialogbox, uncheck the Always ask
before opening this type of file and then click the Save to Save it to the
local.
Then, next time when you open the excel file in the Report manager, you
will get the file open directly in local.
Sincerely,
Wei Lu
Microsoft Online Community 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.|||Hello
Its not me with the problem of saving instead of opening the xls but the
end-user. i want to eliminante a potential mistake of the end-user.
Thats why i wont to adjust the mime-type of the xls. I dont care if i
have to set it in a db-table or config file. I just need to know where
the setting is.
Thanks
Wei Lu [MSFT] wrote:
> Hello Owen,
> Unfortunately, you could not change the mime-type in the report manager
> directly.
> You need to force the client to change their client setting so that they
> will download the excel file only instead of open it in the browser.
> When the client get the Open & Save dialogbox, uncheck the Always ask
> before opening this type of file and then click the Save to Save it to the
> local.
> Then, next time when you open the excel file in the Report manager, you
> will get the file open directly in local.
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>|||Hello Owen,
After consulting the IIS engineer, you could add a MIME type in the IIS
virtual directory.
1. Run "inetmgr" in the start menu.
2. Expand the Web Sites, find the ReportServer virtual directory.
Right-click it and click the Properties.
3. Click the HTTP Headers Tab, then click the MIME Types button and click
New Button.
4. In the Dialogbox, type the .XLS in the entension and octead-stream in
the MIME type section. Click OK.
5. Click OK and then restart the IIS.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community 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
Thanks for trying but if it was so easy i would of succeded myself. The
problem is that i cant change the default mime-types. i added the
mime-type you suggested but it didn't ovverride the existing. Have you
got any other suggestion. Sorry for all the work i'm creating with this
ping-pong request.
Thanks,
Wei Lu [MSFT] wrote:
> Hello Owen,
> After consulting the IIS engineer, you could add a MIME type in the IIS
> virtual directory.
> 1. Run "inetmgr" in the start menu.
> 2. Expand the Web Sites, find the ReportServer virtual directory.
> Right-click it and click the Properties.
> 3. Click the HTTP Headers Tab, then click the MIME Types button and click
> New Button.
> 4. In the Dialogbox, type the .XLS in the entension and octead-stream in
> the MIME type section. Click OK.
> 5. Click OK and then restart the IIS.
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>|||Hello Owen,
Would you please test this on other site?
Also, you may try to use the application/octstream for the MIME type.
Sincerely,
Wei Lu
Microsoft Online Community 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 ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community 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.
Download Documents From Database
Does anyone have a code snippet in VBScript for how allow a user to save (or open) a document that has been stored in a database?
I have documents that are stored in an image column in a database. I have the code I need to upload documents from a user's browser. I'm not having any luck, however, figuring out how to let the user view or download the document that has been stored.
Regards,
Hugh ScottI'm no developer but found interesting code snippets from Planet SC (http://www.planet-source-code.com/).
HTH|||Sorry,
I had the code snippet right under my nose. This is VBScript:
<%
Set Conn = GetConnection
Set adoRS = Server.CreateObject("ADODB.Recordset")
'Open dynamic recordset, table Upload
adoRS.Open "SELECT * FROM tbl_Documents WHERE DocumentID = 1", Conn, 2, 2
sFileName = adoRS("SourceFileName")
sContentType = adoRS("ContentType")
sDataSize = adoRS("DataSize")
' clear the buffer
Response.Buffer = True
Response.Clear
' send the headers to the users browser
Response.AddHeader "Content-Disposition", "attachment; filename=" & sFileName
' Response.AddHeader "Content-Length", sDataSize
Response.Charset = "UTF-8"
Response.ContentType = scontentType
' output the file to the browser
Response.BinaryWrite adoRS("Data")
Response.Flush
' tidy up
s.Close
Set s = Nothing
Function GetConnection()
dim Conn
Set Conn = CreateObject("ADODB.Connection")
Conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=iEIC;Data Source=MyServer"
Conn.Open
set GetConnection = Conn
end function
%>
Originally posted by hmscott
Does anyone have a code snippet in VBScript for how allow a user to save (or open) a document that has been stored in a database?
I have documents that are stored in an image column in a database. I have the code I need to upload documents from a user's browser. I'm not having any luck, however, figuring out how to let the user view or download the document that has been stored.
Regards,
Hugh Scott
I have documents that are stored in an image column in a database. I have the code I need to upload documents from a user's browser. I'm not having any luck, however, figuring out how to let the user view or download the document that has been stored.
Regards,
Hugh ScottI'm no developer but found interesting code snippets from Planet SC (http://www.planet-source-code.com/).
HTH|||Sorry,
I had the code snippet right under my nose. This is VBScript:
<%
Set Conn = GetConnection
Set adoRS = Server.CreateObject("ADODB.Recordset")
'Open dynamic recordset, table Upload
adoRS.Open "SELECT * FROM tbl_Documents WHERE DocumentID = 1", Conn, 2, 2
sFileName = adoRS("SourceFileName")
sContentType = adoRS("ContentType")
sDataSize = adoRS("DataSize")
' clear the buffer
Response.Buffer = True
Response.Clear
' send the headers to the users browser
Response.AddHeader "Content-Disposition", "attachment; filename=" & sFileName
' Response.AddHeader "Content-Length", sDataSize
Response.Charset = "UTF-8"
Response.ContentType = scontentType
' output the file to the browser
Response.BinaryWrite adoRS("Data")
Response.Flush
' tidy up
s.Close
Set s = Nothing
Function GetConnection()
dim Conn
Set Conn = CreateObject("ADODB.Connection")
Conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=iEIC;Data Source=MyServer"
Conn.Open
set GetConnection = Conn
end function
%>
Originally posted by hmscott
Does anyone have a code snippet in VBScript for how allow a user to save (or open) a document that has been stored in a database?
I have documents that are stored in an image column in a database. I have the code I need to upload documents from a user's browser. I'm not having any luck, however, figuring out how to let the user view or download the document that has been stored.
Regards,
Hugh Scott
Download database to local and use
I have SQL database hosted by my ISP. Every now and again we log on and create new tables using user XXX1. After getting a backup of the database, I have restored it on my local machine. When running the application on local, I get an error because there is a new user in database called XXX1.
I would like to change the user from XXX1 to dbo on my local machine for all tables, stored procedures and views. How do I do this easily?
Thanks in advance!
Dave
Hi,
http://groups.google.de/group/microsoft.public.sqlserver.programming/browse_frm/thread/f1625d70fb765701
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Monday, March 19, 2012
Doubt about connections to sql server.
Hi, i have some applications that use a sql server user to connect to
databases. Sometimes i Check the las batch time in enterprise manager to
see if the connection is active, and the date is expressed like this:
"01/01/1900 12:00 am"
Do somebody know why this happen?
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
See if this helps:
http://support.microsoft.com/default...b;en-us;306625
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:OF18nL3dFHA.3808@.TK2MSFTNGP14.phx.gbl...
Hi, i have some applications that use a sql server user to connect to
databases. Sometimes i Check the las batch time in enterprise manager to
see if the connection is active, and the date is expressed like this:
"01/01/1900 12:00 am"
Do somebody know why this happen?
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
databases. Sometimes i Check the las batch time in enterprise manager to
see if the connection is active, and the date is expressed like this:
"01/01/1900 12:00 am"
Do somebody know why this happen?
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
See if this helps:
http://support.microsoft.com/default...b;en-us;306625
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:OF18nL3dFHA.3808@.TK2MSFTNGP14.phx.gbl...
Hi, i have some applications that use a sql server user to connect to
databases. Sometimes i Check the las batch time in enterprise manager to
see if the connection is active, and the date is expressed like this:
"01/01/1900 12:00 am"
Do somebody know why this happen?
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
Doubt about connections to sql server.
Hi, i have some applications that use a sql server user to connect to
databases. Sometimes i Check the las batch time in enterprise manager to
see if the connection is active, and the date is expressed like this:
"01/01/1900 12:00 am"
Do somebody know why this happen'
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***See if this helps:
http://support.microsoft.com/defaul...kb;en-us;306625
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:OF18nL3dFHA.3808@.TK2MSFTNGP14.phx.gbl...
Hi, i have some applications that use a sql server user to connect to
databases. Sometimes i Check the las batch time in enterprise manager to
see if the connection is active, and the date is expressed like this:
"01/01/1900 12:00 am"
Do somebody know why this happen'
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
databases. Sometimes i Check the las batch time in enterprise manager to
see if the connection is active, and the date is expressed like this:
"01/01/1900 12:00 am"
Do somebody know why this happen'
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***See if this helps:
http://support.microsoft.com/defaul...kb;en-us;306625
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:OF18nL3dFHA.3808@.TK2MSFTNGP14.phx.gbl...
Hi, i have some applications that use a sql server user to connect to
databases. Sometimes i Check the las batch time in enterprise manager to
see if the connection is active, and the date is expressed like this:
"01/01/1900 12:00 am"
Do somebody know why this happen'
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
Sunday, March 11, 2012
double-hop authentication for SSRS and ASP
hey
I work in a SSRS environment; I also do some web dev.. and we keep on
having authentication problems getting a token to pass from one user to
the webserver; and then to the database server.
this is called 'double-hop' authentication.. right?
I'm of the understanding that I need to check 'trust for delegation'
and run SetSpn on the ServiceAccount for my database server for it to
be the recipient of a double-hop token.
Isn't that correct?
Ok.. now let's fast forward.. We got a bunch of SSRS Servers; and for
most of these; these guys put the SSRS Server on the same machine as
the database server.
Then they go to copy the report from the localserver to another; and
they bitch about how it doesn't have permissions.. it shows up and says
'user Null' or whatever-- it just sounds to me like a simple double-hop
problem.
I've tried to explain this to a half dozen clients over the years--
about the web authentication thing.. .because everywhere I go; people
use SQL authentication or they put the DB Server on the same machine as
the webserver.. or they make you launch the SSAS processing job from
the local machine.. instead of being able to connect from my desktop to
a SSAS machine to a 3rd machine- the db server.
It just seems to me like 99% of the companies out there dont know how
to do double-hop authentication.
And I just swear to god... everyone i tell this to theyre like 'well
were not a kerberos shop so we dont need to do that'
Does anyone have any REAL CLEAR guidelines for when we should use the
double-hop 'trust for delegation' and 'setspn' concept?
a) we DONT have kerberos
b) we have a half dozen servers and these guys put SSRS on the main db
server
(I assume it's because they dont speak double-hop)
c) at one of my last jobs; we had to terminal into the local db server
to do anything because we didn't have double-hop.. seemed like we were
throwing the baby out with the bathwater; giving people perms to the
terminal services on the db server; it just seems like overkill
d) I want to be able to go from DesktopA and hit ServerA and from
ServerA use a linked server to ServerB. That spells 'i need
double-hop' right?
thanks guys
-Susieyes I must do this also
my friend said that this was possible
susiedba@.hotmail.com wrote:
> hey
> I work in a SSRS environment; I also do some web dev.. and we keep on
> having authentication problems getting a token to pass from one user to
> the webserver; and then to the database server.
> this is called 'double-hop' authentication.. right?
> I'm of the understanding that I need to check 'trust for delegation'
> and run SetSpn on the ServiceAccount for my database server for it to
> be the recipient of a double-hop token.
> Isn't that correct?
> Ok.. now let's fast forward.. We got a bunch of SSRS Servers; and for
> most of these; these guys put the SSRS Server on the same machine as
> the database server.
> Then they go to copy the report from the localserver to another; and
> they bitch about how it doesn't have permissions.. it shows up and says
> 'user Null' or whatever-- it just sounds to me like a simple double-hop
> problem.
> I've tried to explain this to a half dozen clients over the years--
> about the web authentication thing.. .because everywhere I go; people
> use SQL authentication or they put the DB Server on the same machine as
> the webserver.. or they make you launch the SSAS processing job from
> the local machine.. instead of being able to connect from my desktop to
> a SSAS machine to a 3rd machine- the db server.
> It just seems to me like 99% of the companies out there dont know how
> to do double-hop authentication.
>
> And I just swear to god... everyone i tell this to theyre like 'well
> were not a kerberos shop so we dont need to do that'
> Does anyone have any REAL CLEAR guidelines for when we should use the
> double-hop 'trust for delegation' and 'setspn' concept?
> a) we DONT have kerberos
> b) we have a half dozen servers and these guys put SSRS on the main db
> server
> (I assume it's because they dont speak double-hop)
> c) at one of my last jobs; we had to terminal into the local db server
> to do anything because we didn't have double-hop.. seemed like we were
> throwing the baby out with the bathwater; giving people perms to the
> terminal services on the db server; it just seems like overkill
> d) I want to be able to go from DesktopA and hit ServerA and from
> ServerA use a linked server to ServerB. That spells 'i need
> double-hop' right?
> thanks guys
> -Susie|||I'm not an expert - but:
Yes - this is the classic double-hop scenario. I live with it everyday.
A lot depends on the Windows versions of the servers:
Windows 2000 - this requires Kerberos or the use of network service
accounts used between servers (or if the back-end service is SQL - SQL
logins).
Windows 2003 - you can use the Protocol Transition features to delegate
credentials without Kerberos.
See:
http://technet2.microsoft.com/WindowsServer/en/library/dac7ecea-7c82-43c0-847b-3a1a81454cfe1033.mspx|||Sleepy
can you please tell more? my english is poor
I have had security problems with Services of Reporting; I can see
'user is null' even though we are using NT Authentication.
Tom
SleepyLab wrote:
> I'm not an expert - but:
> Yes - this is the classic double-hop scenario. I live with it everyday.
> A lot depends on the Windows versions of the servers:
> Windows 2000 - this requires Kerberos or the use of network service
> accounts used between servers (or if the back-end service is SQL - SQL
> logins).
> Windows 2003 - you can use the Protocol Transition features to delegate
> credentials without Kerberos.
> See:
> http://technet2.microsoft.com/WindowsServer/en/library/dac7ecea-7c82-43c0-847b-3a1a81454cfe1033.mspx|||Difficult to help without more details, but if your using Windows 2000
servers and NTLM only, you will need to create a service account for
the Front End server to communicate with the Back End.
In a typical Web Server (A) talks to Application Server (B) that talks
to SQL Server (C) scenario...
1) Web Site (A) uses NTLM authentication and ASP(.NET) code to
impersonate <user>.
2) App Server B serves use NTLM authentication to grant access to
<user>, but is not allowed to pass these credentials to C. So either:
a) the DCOM or .NET services on B are configured to run under a
service account <_service1> which has access rights on the SQL Server
(C) and necessary Databases objects
or
b) the services on B use SQL authentication to login and access (C).
Note: If you use this approach, and you need to audit for security; you
should log accesses to C on server B - so that you know who really was
accessing C (All C sees is the same user, all the time).
If your using Windows Server 2003, you can use delagation as explained
in the links I provided earlier.
punjab_tom@.hotmail.com wrote:
> Sleepy
> can you please tell more? my english is poor
> I have had security problems with Services of Reporting; I can see
> 'user is null' even though we are using NT Authentication.
> Tom
>|||Which account is the Application Pool your Report Server virtual directories
running under?
If that runs as network service, you might want to try running it as the
domain user you've set up.
Or try using Integrated Security instead of basic authentication.
Kaisa M. Lindahl Lervik
"dawidg" <dawidg@.discussions.microsoft.com> wrote in message
news:8AB36908-AB90-4F46-B185-5F2E21A85E9D@.microsoft.com...
> Hi,
> I have a very similar problem. I've been through a lot of articles, but
> something is still missing.
> This is my setup:
> All servers are Windows Server 2003.
> IE6 connects to IIS using basic authentication. In ASP.NET code, using
> Win32
> LogonUser, I impersonate a domain user and then make a call to the
> database
> server. For some reason the impersonated user is not delegated to SQL
> server.
> This is how I configured everything so far:
> Created a domain account for the aspnet worker process (edited
> machine.config on IIS server and edited <processmodel>:
> <processModel enable="true" userName="domain\ASPNETuser" password="xxx"/>
> The ASPNETuser is set to be Trusted for Delegation on the domain
> controller.
> The IIS server is set to be Trusted for Delegation on the domain
> controller.
> The users that are to be impersonated are NOT set as sensitive on the
> domain
> controller.
> The connection string to SQL looks like:"server=sqlserver;initial
> catalog=dbname;integrated security=SSPI".
> When connecting to the database, I get "Login failed for user 'NT
> AUTHORITY\ANONYMOUS LOGON'".
> What am I missing? How do I know the Kerberos is working.
>
> "SleepyLab" wrote:
>> Difficult to help without more details, but if your using Windows 2000
>> servers and NTLM only, you will need to create a service account for
>> the Front End server to communicate with the Back End.
>> In a typical Web Server (A) talks to Application Server (B) that talks
>> to SQL Server (C) scenario...
>> 1) Web Site (A) uses NTLM authentication and ASP(.NET) code to
>> impersonate <user>.
>> 2) App Server B serves use NTLM authentication to grant access to
>> <user>, but is not allowed to pass these credentials to C. So either:
>> a) the DCOM or .NET services on B are configured to run under a
>> service account <_service1> which has access rights on the SQL Server
>> (C) and necessary Databases objects
>> or
>> b) the services on B use SQL authentication to login and access (C).
>> Note: If you use this approach, and you need to audit for security; you
>> should log accesses to C on server B - so that you know who really was
>> accessing C (All C sees is the same user, all the time).
>> If your using Windows Server 2003, you can use delagation as explained
>> in the links I provided earlier.
>>
>> punjab_tom@.hotmail.com wrote:
>> > Sleepy
>> >
>> > can you please tell more? my english is poor
>> > I have had security problems with Services of Reporting; I can see
>> > 'user is null' even though we are using NT Authentication.
>> >
>> > Tom
>> >
>>
I work in a SSRS environment; I also do some web dev.. and we keep on
having authentication problems getting a token to pass from one user to
the webserver; and then to the database server.
this is called 'double-hop' authentication.. right?
I'm of the understanding that I need to check 'trust for delegation'
and run SetSpn on the ServiceAccount for my database server for it to
be the recipient of a double-hop token.
Isn't that correct?
Ok.. now let's fast forward.. We got a bunch of SSRS Servers; and for
most of these; these guys put the SSRS Server on the same machine as
the database server.
Then they go to copy the report from the localserver to another; and
they bitch about how it doesn't have permissions.. it shows up and says
'user Null' or whatever-- it just sounds to me like a simple double-hop
problem.
I've tried to explain this to a half dozen clients over the years--
about the web authentication thing.. .because everywhere I go; people
use SQL authentication or they put the DB Server on the same machine as
the webserver.. or they make you launch the SSAS processing job from
the local machine.. instead of being able to connect from my desktop to
a SSAS machine to a 3rd machine- the db server.
It just seems to me like 99% of the companies out there dont know how
to do double-hop authentication.
And I just swear to god... everyone i tell this to theyre like 'well
were not a kerberos shop so we dont need to do that'
Does anyone have any REAL CLEAR guidelines for when we should use the
double-hop 'trust for delegation' and 'setspn' concept?
a) we DONT have kerberos
b) we have a half dozen servers and these guys put SSRS on the main db
server
(I assume it's because they dont speak double-hop)
c) at one of my last jobs; we had to terminal into the local db server
to do anything because we didn't have double-hop.. seemed like we were
throwing the baby out with the bathwater; giving people perms to the
terminal services on the db server; it just seems like overkill
d) I want to be able to go from DesktopA and hit ServerA and from
ServerA use a linked server to ServerB. That spells 'i need
double-hop' right?
thanks guys
-Susieyes I must do this also
my friend said that this was possible
susiedba@.hotmail.com wrote:
> hey
> I work in a SSRS environment; I also do some web dev.. and we keep on
> having authentication problems getting a token to pass from one user to
> the webserver; and then to the database server.
> this is called 'double-hop' authentication.. right?
> I'm of the understanding that I need to check 'trust for delegation'
> and run SetSpn on the ServiceAccount for my database server for it to
> be the recipient of a double-hop token.
> Isn't that correct?
> Ok.. now let's fast forward.. We got a bunch of SSRS Servers; and for
> most of these; these guys put the SSRS Server on the same machine as
> the database server.
> Then they go to copy the report from the localserver to another; and
> they bitch about how it doesn't have permissions.. it shows up and says
> 'user Null' or whatever-- it just sounds to me like a simple double-hop
> problem.
> I've tried to explain this to a half dozen clients over the years--
> about the web authentication thing.. .because everywhere I go; people
> use SQL authentication or they put the DB Server on the same machine as
> the webserver.. or they make you launch the SSAS processing job from
> the local machine.. instead of being able to connect from my desktop to
> a SSAS machine to a 3rd machine- the db server.
> It just seems to me like 99% of the companies out there dont know how
> to do double-hop authentication.
>
> And I just swear to god... everyone i tell this to theyre like 'well
> were not a kerberos shop so we dont need to do that'
> Does anyone have any REAL CLEAR guidelines for when we should use the
> double-hop 'trust for delegation' and 'setspn' concept?
> a) we DONT have kerberos
> b) we have a half dozen servers and these guys put SSRS on the main db
> server
> (I assume it's because they dont speak double-hop)
> c) at one of my last jobs; we had to terminal into the local db server
> to do anything because we didn't have double-hop.. seemed like we were
> throwing the baby out with the bathwater; giving people perms to the
> terminal services on the db server; it just seems like overkill
> d) I want to be able to go from DesktopA and hit ServerA and from
> ServerA use a linked server to ServerB. That spells 'i need
> double-hop' right?
> thanks guys
> -Susie|||I'm not an expert - but:
Yes - this is the classic double-hop scenario. I live with it everyday.
A lot depends on the Windows versions of the servers:
Windows 2000 - this requires Kerberos or the use of network service
accounts used between servers (or if the back-end service is SQL - SQL
logins).
Windows 2003 - you can use the Protocol Transition features to delegate
credentials without Kerberos.
See:
http://technet2.microsoft.com/WindowsServer/en/library/dac7ecea-7c82-43c0-847b-3a1a81454cfe1033.mspx|||Sleepy
can you please tell more? my english is poor
I have had security problems with Services of Reporting; I can see
'user is null' even though we are using NT Authentication.
Tom
SleepyLab wrote:
> I'm not an expert - but:
> Yes - this is the classic double-hop scenario. I live with it everyday.
> A lot depends on the Windows versions of the servers:
> Windows 2000 - this requires Kerberos or the use of network service
> accounts used between servers (or if the back-end service is SQL - SQL
> logins).
> Windows 2003 - you can use the Protocol Transition features to delegate
> credentials without Kerberos.
> See:
> http://technet2.microsoft.com/WindowsServer/en/library/dac7ecea-7c82-43c0-847b-3a1a81454cfe1033.mspx|||Difficult to help without more details, but if your using Windows 2000
servers and NTLM only, you will need to create a service account for
the Front End server to communicate with the Back End.
In a typical Web Server (A) talks to Application Server (B) that talks
to SQL Server (C) scenario...
1) Web Site (A) uses NTLM authentication and ASP(.NET) code to
impersonate <user>.
2) App Server B serves use NTLM authentication to grant access to
<user>, but is not allowed to pass these credentials to C. So either:
a) the DCOM or .NET services on B are configured to run under a
service account <_service1> which has access rights on the SQL Server
(C) and necessary Databases objects
or
b) the services on B use SQL authentication to login and access (C).
Note: If you use this approach, and you need to audit for security; you
should log accesses to C on server B - so that you know who really was
accessing C (All C sees is the same user, all the time).
If your using Windows Server 2003, you can use delagation as explained
in the links I provided earlier.
punjab_tom@.hotmail.com wrote:
> Sleepy
> can you please tell more? my english is poor
> I have had security problems with Services of Reporting; I can see
> 'user is null' even though we are using NT Authentication.
> Tom
>|||Which account is the Application Pool your Report Server virtual directories
running under?
If that runs as network service, you might want to try running it as the
domain user you've set up.
Or try using Integrated Security instead of basic authentication.
Kaisa M. Lindahl Lervik
"dawidg" <dawidg@.discussions.microsoft.com> wrote in message
news:8AB36908-AB90-4F46-B185-5F2E21A85E9D@.microsoft.com...
> Hi,
> I have a very similar problem. I've been through a lot of articles, but
> something is still missing.
> This is my setup:
> All servers are Windows Server 2003.
> IE6 connects to IIS using basic authentication. In ASP.NET code, using
> Win32
> LogonUser, I impersonate a domain user and then make a call to the
> database
> server. For some reason the impersonated user is not delegated to SQL
> server.
> This is how I configured everything so far:
> Created a domain account for the aspnet worker process (edited
> machine.config on IIS server and edited <processmodel>:
> <processModel enable="true" userName="domain\ASPNETuser" password="xxx"/>
> The ASPNETuser is set to be Trusted for Delegation on the domain
> controller.
> The IIS server is set to be Trusted for Delegation on the domain
> controller.
> The users that are to be impersonated are NOT set as sensitive on the
> domain
> controller.
> The connection string to SQL looks like:"server=sqlserver;initial
> catalog=dbname;integrated security=SSPI".
> When connecting to the database, I get "Login failed for user 'NT
> AUTHORITY\ANONYMOUS LOGON'".
> What am I missing? How do I know the Kerberos is working.
>
> "SleepyLab" wrote:
>> Difficult to help without more details, but if your using Windows 2000
>> servers and NTLM only, you will need to create a service account for
>> the Front End server to communicate with the Back End.
>> In a typical Web Server (A) talks to Application Server (B) that talks
>> to SQL Server (C) scenario...
>> 1) Web Site (A) uses NTLM authentication and ASP(.NET) code to
>> impersonate <user>.
>> 2) App Server B serves use NTLM authentication to grant access to
>> <user>, but is not allowed to pass these credentials to C. So either:
>> a) the DCOM or .NET services on B are configured to run under a
>> service account <_service1> which has access rights on the SQL Server
>> (C) and necessary Databases objects
>> or
>> b) the services on B use SQL authentication to login and access (C).
>> Note: If you use this approach, and you need to audit for security; you
>> should log accesses to C on server B - so that you know who really was
>> accessing C (All C sees is the same user, all the time).
>> If your using Windows Server 2003, you can use delagation as explained
>> in the links I provided earlier.
>>
>> punjab_tom@.hotmail.com wrote:
>> > Sleepy
>> >
>> > can you please tell more? my english is poor
>> > I have had security problems with Services of Reporting; I can see
>> > 'user is null' even though we are using NT Authentication.
>> >
>> > Tom
>> >
>>
Wednesday, March 7, 2012
Don't run report straight away
When accessing a report via a url, is it possible to wait until the user
clicks the 'View Report' button, rather than running the report straight away
with whatever default parameters have been set?
FredNo. If all the parameters have defaults then the report will run. The only
thing you can do is to leave one parameter without a default.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:1333BECB-C529-4F38-8DF7-AEEBC57842FE@.microsoft.com...
> When accessing a report via a url, is it possible to wait until the user
> clicks the 'View Report' button, rather than running the report straight
away
> with whatever default parameters have been set?
>
> Fred
clicks the 'View Report' button, rather than running the report straight away
with whatever default parameters have been set?
FredNo. If all the parameters have defaults then the report will run. The only
thing you can do is to leave one parameter without a default.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:1333BECB-C529-4F38-8DF7-AEEBC57842FE@.microsoft.com...
> When accessing a report via a url, is it possible to wait until the user
> clicks the 'View Report' button, rather than running the report straight
away
> with whatever default parameters have been set?
>
> Fred
dont repeat the same fruilt a user ate...
This may get brought up again and again, but I don't know how to deal with it by heart yet... in fact I mean to say, I don't know how to deal with it.
With the given data set how do you only return one of the 2 rows where a given user ate the same fruit?
apples greg
peach greg
lemon greg
apples greg
peach bruce
peach bruce
lemon bruce
apples bruce
pear paul
peach paul
plumb paul
plumb paul
apples barb
peach barb
lemon barb
pear barb
What T-SQL is needed to produce the following dataset?
apples greg
peach greg
lemon greg
peach bruce
lemon bruce
apples bruce
pear paul
peach paul
plumb paul
apples barb
peach barb
lemon barb
pear barb
SELECT DISTINCT fruit,user
FROM ...
ORDER BY user,fruit
don't prompt user for parameters error
I am trying to run a report in RS using url parameters. I want to prompt the
user for two parameters, but I want to hide the other six parameters (the
values are set in the url and the user should not be able to change them).
When I am in RS in Properties and Parameters I can set each parameter to
prompt user or not prompt user. If I set any of them to not prompt user
(uncheck the checkbox) I get the following error:
The report parameter 'nodeID' is read-only and cannot be modified.
(rsReadOnlyReportParameter)
This error shows for the one parameter that is selected not to prompt, or
for the first one in the list if I set them all to not prompt.
What am I doing wrong? Is there some other way to hide these parameters?
Thank you for your help!
Sharlynleave the prompt in VS.net, deploy and change the properties in the report
manager. check "prompt user" but leave the string blank.
"SharinDenver" wrote:
> I am trying to run a report in RS using url parameters. I want to prompt the
> user for two parameters, but I want to hide the other six parameters (the
> values are set in the url and the user should not be able to change them).
> When I am in RS in Properties and Parameters I can set each parameter to
> prompt user or not prompt user. If I set any of them to not prompt user
> (uncheck the checkbox) I get the following error:
> The report parameter 'nodeID' is read-only and cannot be modified.
> (rsReadOnlyReportParameter)
> This error shows for the one parameter that is selected not to prompt, or
> for the first one in the list if I set them all to not prompt.
> What am I doing wrong? Is there some other way to hide these parameters?
> Thank you for your help!
> Sharlyn
>
user for two parameters, but I want to hide the other six parameters (the
values are set in the url and the user should not be able to change them).
When I am in RS in Properties and Parameters I can set each parameter to
prompt user or not prompt user. If I set any of them to not prompt user
(uncheck the checkbox) I get the following error:
The report parameter 'nodeID' is read-only and cannot be modified.
(rsReadOnlyReportParameter)
This error shows for the one parameter that is selected not to prompt, or
for the first one in the list if I set them all to not prompt.
What am I doing wrong? Is there some other way to hide these parameters?
Thank you for your help!
Sharlynleave the prompt in VS.net, deploy and change the properties in the report
manager. check "prompt user" but leave the string blank.
"SharinDenver" wrote:
> I am trying to run a report in RS using url parameters. I want to prompt the
> user for two parameters, but I want to hide the other six parameters (the
> values are set in the url and the user should not be able to change them).
> When I am in RS in Properties and Parameters I can set each parameter to
> prompt user or not prompt user. If I set any of them to not prompt user
> (uncheck the checkbox) I get the following error:
> The report parameter 'nodeID' is read-only and cannot be modified.
> (rsReadOnlyReportParameter)
> This error shows for the one parameter that is selected not to prompt, or
> for the first one in the list if I set them all to not prompt.
> What am I doing wrong? Is there some other way to hide these parameters?
> Thank you for your help!
> Sharlyn
>
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
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?
>
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?
>
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\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.
>
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.
>
Subscribe to:
Posts (Atom)