Drill down slow: February 2012

Sunday, February 26, 2012

Dont Insert duplicate rows

Hi, I need to insert rows into table1 from table2 and table3 but I don't want to insert repeated combinations of col2, col3. So, table1 has the primary key col2, col3.

This the table1:

create table table1(
col1 int not null,
col2 int not null,
col3 int not null,
constraint PK_table1 primary key (col2, col3)
)

This is my "insert" code:

INSERT INTO table1
SELECT table2.col1,table2.col2, table3.col3
FROM table2, table3
WHERE table2.col1 = table3.col1

Wich conditions shoud i add to this code?

Thanks.

fmilano.If Col2 and Col3 is controlled by Primary key then it will be taken care and if any data is added will be errored.|||To be erroed is what I want to avoid. So, I want to filter this rows by columns col2 and col3.

Don't have any Server to choose from the Server name list

Hi
I have just installed SQL Server 2005 Enterprise Edition. I have Microsoft
Windows 2000. When I start SQL Server Management Studio and have to choose a
server from the Server name list, the list is empty. I have no clue what to
do?
Please Help me!!!
Explain carefully
FiaType the name of the server in the Server Name field. If
it's an named instance, you would need to type
ServerName\InstanceName
-Sue
On Thu, 07 Sep 2006 11:04:03 GMT, "fiaolle"
<fiaolle@.telia.com> wrote:

>Hi
>I have just installed SQL Server 2005 Enterprise Edition. I have Microsoft
>Windows 2000. When I start SQL Server Management Studio and have to choose
a
>server from the Server name list, the list is empty. I have no clue what to
>do?
>Please Help me!!!
>Explain carefully
>Fia
>

Don't get Service Broker to Work

Hi,

I'm not able to get Service Broker to work. I've created the following sample and would excpect to get some data from "PreisanfrageQueue" or "PreisanfrageRequestorQueue". But both they are emtpy.

What do I do wrong?

Regards,

Manfred

create message type Preisanfrage
validation = well_formed_xml;

create message type PreisanfrageAntwort
validation = well_formed_xml;

create contract PreisanfrageContract
(
Preisanfrage sent by initiator,
PreisanfrageAntwort sent by target
);

create queue PreisanfrageRequestorQueue with
status=on;

create queue PreisanfrageQueue;

create service PreisanfrageRequestorService
on queue PreisanfrageRequestorQueue ( PreisanfrageContract );

create service PreisanfrageService
on queue PreisanfrageQueue (PreisanfrageContract );

create table debug_table;
create table debug_table (id int primary key identity(1,1), msg varchar(100));

create procedure PreisanfrageAction
as
declare @.conversation uniqueidentifier
declare @.msg nvarchar(max)
declare @.msgType nvarchar(256)
declare @.answer xml;

insert into debug_table(msg) values('1');

;receive top(1)
@.conversation = conversation_handle,
@.msg = message_body,
@.msgType = message_type_name
from PreisanfrageQueue;

insert into debug_table(msg) values('2');

-- Preisanfrage bearbeiten

set @.answer = '<preis>1</preis>';
;send on conversation @.conversation
message type PreisanfrageAntwort (@.answer);

end conversation @.conversation;

insert into debug_table(msg) values('3');

alter queue PreisanfrageQueue
with
status=on,
activation (
status=on,
PROCEDURE_NAME = PreisanfrageAction,
max_queue_readers = 100,
EXECUTE AS OWNER
);

-- Dialog starten

declare @.conversation uniqueidentifier;

begin dialog conversation @.conversation
from service [PreisanfrageRequestorService]
to service 'PreisanfrageService'
on contract [PreisanfrageContract];

declare @.request xml;

set @.request = '<?xml version="1.0" encoding="UTF-8"?><Preisanfrage xmlns="4711101'">http://www.xyz.at/samples/Preisanfrage"><KundenId>4711</KundenId><ProduktId>10</ProduktId><Anzahl>1</Anzahl></Preisanfrage>';

;send on conversation @.conversation
message type Preisanfrage ( @.request );

receive * from PreisanfrageQueue;

receive * from PreisanfrageRequestorQueue;

select * from debug_table

1. Please double check you have created the master key in this database.

create master key

encryption by password='asdfasdf'

2. query the system table to check the message’s transmission status.

select * from sys.transmission_queue

3. Don’t use

receive * from PreisanfrageQueue;

receive * from PreisanfrageRequestorQueue;

To check the queue's messenger, because this will remove the message from

the queue and but the conversation open

|||

Hi,

thanks - it was the missing master key.

Did I get it right, that thouse activation procedures are polling the queue ?
If yes - is there a way to use some kind of notification so that the activation procedure is just called every time when a message is enqueued ?

Best whishes,
Manfred

|||You did it correctly, but your messages are getting taken off as soon as you put them on there, through your receive * statements. You should try to receive one at a time and do something with those records.

Dont get component choices during installation. Installation finishes without db en iis re

Hallo,
I've tried several times installing RS on a particular machine. The
installation finishes without error messages and without installing the RS
components.
During RS installation on a member server (Win2003, SQL2000 Std SP3a, RS
2000 Std) i dont get the usual Server Components choices and so on. The
usual warning about Visual Studio apperas. But then as i click "Next" in the
warning box the installation just continues on. The box "To continue please
click next" appears but i dont get a chance to click next as the
installation just goes on "automatically". The installation rolls on and on
but after it says it finished successfully the IIS mappings and the RS DBs
arent there.
In this newsgroup I found someone (Johan - post: RS Installation fail
without message) with the same issue, although MS (Brad Syputa) took a look
at it, it did not seem resolved.
Does anybody know what to do about this?
Mike van DuijnDon't know for sure if this is your problem but we had the same issue
when we were trying to install RS on a couple development laptops.
Turned out, we *thought* we were running SQL Server developer on those
laptops. We were actually running SQL Server Personal. If there's any
disagreement between the versions, it won't show you the server
components.
Confirm your SQL Version and the version of your RS CD. My guess is
that you are trying to install Reporting Services Developer on SQL
Server Standard or something like that.
Hope this helps,
Tim
mike wrote:
> Hallo,
> I've tried several times installing RS on a particular machine. The
> installation finishes without error messages and without installing the RS
> components.
> During RS installation on a member server (Win2003, SQL2000 Std SP3a, RS
> 2000 Std) i dont get the usual Server Components choices and so on. The
> usual warning about Visual Studio apperas. But then as i click "Next" in the
> warning box the installation just continues on. The box "To continue please
> click next" appears but i dont get a chance to click next as the
> installation just goes on "automatically". The installation rolls on and on
> but after it says it finished successfully the IIS mappings and the RS DBs
> arent there.
> In this newsgroup I found someone (Johan - post: RS Installation fail
> without message) with the same issue, although MS (Brad Syputa) took a look
> at it, it did not seem resolved.
> Does anybody know what to do about this?
> Mike van Duijn

don''t find Management Studio after installed SQL 2008 CTP JULY.

My OS is WinXP

I don't find Management Studio after installed SQL 2008 CTP JULY.

Why?

Thanks!

bill

I now it sounds like a super obvious question, but did you select to install Management Studio? Did you hit any installation errors?

Take a look at C:\Program Files\microsoft sql server\100\Setup Bootstrap\LOG\Summary.txt to see if there were any installation errors.

Cheers,

Dan

|||

Bill,

Since we haven't heard back from you in ~10 days I'm going to close this as answered. If you're still encountering problems with Management Studio feel free to post a new thread.

Cheers,

Dan

|||

Same problem I am facing. As per comment I have checked with log file and content are as below:

Microsoft SQL Server 2008 10.0.1049.14
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Sun Sep 23 13:07:25 2007

So please help me resolve this issue.

don''t find Management Studio after installed SQL 2008 CTP JULY.

My OS is WinXP

I don't find Management Studio after installed SQL 2008 CTP JULY.

Why?

Thanks!

bill

I now it sounds like a super obvious question, but did you select to install Management Studio? Did you hit any installation errors?

Take a look at C:\Program Files\microsoft sql server\100\Setup Bootstrap\LOG\Summary.txt to see if there were any installation errors.

Cheers,

Dan

|||

Bill,

Since we haven't heard back from you in ~10 days I'm going to close this as answered. If you're still encountering problems with Management Studio feel free to post a new thread.

Cheers,

Dan

|||

Same problem I am facing. As per comment I have checked with log file and content are as below:

Microsoft SQL Server 2008 10.0.1049.14
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Sun Sep 23 13:07:25 2007

So please help me resolve this issue.

don''t find Management Studio after installed SQL 2008 CTP JULY.

My OS is WinXP

I don't find Management Studio after installed SQL 2008 CTP JULY.

Why?

Thanks!

bill

I now it sounds like a super obvious question, but did you select to install Management Studio? Did you hit any installation errors?

Take a look at C:\Program Files\microsoft sql server\100\Setup Bootstrap\LOG\Summary.txt to see if there were any installation errors.

Cheers,

Dan

|||

Bill,

Since we haven't heard back from you in ~10 days I'm going to close this as answered. If you're still encountering problems with Management Studio feel free to post a new thread.

Cheers,

Dan

|||

Same problem I am facing. As per comment I have checked with log file and content are as below:

Microsoft SQL Server 2008 10.0.1049.14
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Sun Sep 23 13:07:25 2007

So please help me resolve this issue.

Don't email report if no results from query

With an automated report (scheduling setup in report manager) is there a way to prevent the sending of an email if the report query has no results?Any ideas anyone?|||Sure. Rather than just creating a schedule, you'll need to do a data driven subscription which gets fired ON a schedule.

The data driven subscription will query another SQL Table which you will need to create and populate with data like who to send the report to (email), what report renderer to use (HTML? PDF? Excel?), what parameter values should be sent to the report for filtering purposes (someField = "SomeValue"), etc. You'll add one row per report which should get sent out.

When you create a data driven sub, the wizard will ask you WHERE it should get the info above from...you normally supply a basic SELECT statement that would select info from "your" table...In your scenario, you'd make the query a bit more complicated...Instead of JUST returning info from "your" table, you would also add some additional logic to check and see if the REPORT will actually return records. Here's a really, really ugly version of what I'm talkig about:

SELECT email,extensionType, myParameterValue from someTableICreated

WHERE exists(select * from a query that could be close to the one my report might use in it's data source..but this one should be quicker and return no rows if the report returns no rows )

Make sense?|||

FYI--This still e-mails a blank report if there are no records in the query, so I don't know what I'm missing.

Brian J. Matuschak
v-brmatu@.microsoft.com

Don't email report if no results from query

SELECT email,extensionType, myParameterValue from someTableICreated

WHERE exists(select * from a query that could be close to the one my report might use in it's data source..but this one should be quicker and return no rows if the report returns no rows )

Make sense?|||

FYI--This still e-mails a blank report if there are no records in the query, so I don't know what I'm missing.

Brian J. Matuschak
v-brmatu@.microsoft.com

Dont display the field if it contains a zero value

I'm working on a report for accounting. Usually debit and credit prob. I don't want to display the field if it contains zero. It creates a gap on my reports and want to avoid it.
Anyone who can help?
Thanks.Using CR 8.5 and RDC, each field has a setting called 'SuppressIfZero' and one called 'SuppressIfBlank'. As the name implies, setting the 'SuppressIfZero' to True will suppress the field if the value is Zero, setting the 'SuppressIfBlank' to True will suppress the field if it's blank.|||I did what you said but I still get a gap between my text. How can I eliminate it?

Thanks.|||Try to put the field in a separate section (If possible), and Suppress section if that value is blank.|||Hi

try suppressing the field or use a formula

if (fieldname) = "0" then "No Data"

I was working on the same thing in crystal reports 9 and got up to this finally.

c ya

Dont Display dbo. in Object Explorer

Is there a way to stop SQL Server Management Studio (2005) from showing "dbo." on everything in the object explorer.

I'd love to turn that off on a database I'm working on as, for example, if you have to find tblMember, you'd have to type d-b-o-.-t-b-l-m before you can actually start jumping to the table you're interested in - whereas in Enterprise Manager (2000) you'd only have to type t-b-l-m if you get my meaning.

Hi there,

There no such option as to hide the owner of the datatable. Actually this is good because the owner is a very strong security feature SQL Server provides. You may want to have in mind who is the owner especially if you are going to host your application in a shared environment where all tables will be owned by your Sql user (which will definitely not be sa).
Sorry for bringing the bad news,
Cheers,

Andreas Botsikas

Dont display address if its not there!

hi there, i have a invoice template that when printed has a box for the users address (which there always will be) and a box for their delivery address if they are having the items delivered. However sometimes they dont have things delivered and so the record in the delivery table does not exist. In this case it will throw an error, how can i avoid this. the code im using i have posted below

string sql ="SELECT [del_address], [del_post_code], [del_date], [del_time] From tbl_del WHERE order_ID = " + intOrderID;

//This creates a sql command which executes the sql statement.

SqlCommand sqlCmd =newSqlCommand(sql, myConn);

myConn.Open();

SqlDataReader dr = sqlCmd.ExecuteReader();

//This reads the first result from the sqlReader

dr.Read();

try

{

//string strDel_Address = dr["del_address"].ToString();

if (Convert.ToString(dr["del_charge"].ToString()) !=null)

{

//delivery items

lblDelAddy.Text = dr["del_address"].ToString();

lblDelPCode.Text = dr["del_post_code"].ToString();

Instead of "dr.Read();" use "while (dr.Read())" If there is no record it wont even try to fill the items

while (dr.Read()){//Do something}

|||

ah thank you, the problem though is that there is this line

if (Convert.ToString(dr["del_charge"].ToString()) !=null)

and several lines like it below which rely on it bringing something back, how could i change it so if it dosnt exist almost to not do anything?

|||

When you usewhile (dr.Read()) if there are no records it will not go into that block of code. This will solve your problem, give it a try.

Don't delete data from linkedserver table .

when our system upgrade to sql server 2005 ,and create a linked server to localhost database , the script of create linkedserver is :

/****** Object: LinkedServer [localhost_boston] Script Date: 05/07/2006 18:37:15 ******/
EXEC master.dbo.sp_addlinkedserver @.server = N'localhost_boston',@.srvproduct='', @.provider=N'SQLNCLI', @.datasrc=N'localhost', @.provstr=N'UID=sa;PWD=007;', @.catalog=N'boston'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'collation compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'data access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'dist', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'pub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'rpc', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'rpc out', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'sub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'connect timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'collation name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'lazy schema validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'query timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'use remote collation', @.optvalue=N'true'

when I run the sql on local server :

delete a from localhost_boston.boston.dbo.Spot as a,
bica.tmpspot as b where a.spotid=b.spotid

it display 7 rows have deleted ,but when I run:

select * from localhost_boston.boston.dbo.Spot as a
join bica. tmpspot as b on a.spotid=b.spotid

find the 7 rows have not delete,I check the localhost_boston.boston.dbo.Spot

table ,and find 7 rows have deleted that it is not a.spotid=b.spotid ,why ?

If I run :

delete from localhost_boston.boston.dbo.Spot where spotid=28147

and the row can deleted .why ?

anyone can talk me how to do I can !

Thanks.

Your delete syntax is improper. You should use a subquery to constrain your deleted rows, not a join.

Code Snippet

delete localhost_boston.boston.dbo.Spot where spotid in (select spotid from bica.tmpspot)

Joins create a result set and using a generic join syntax you can get unexpected result sets. Always test your joins in a select statement 1st to make sure you are getting the results you expect.

Don't delete data from linkedserver table .

when our system upgrade to sql server 2005 ,and create a linked server to localhost database , the script of create linkedserver is :

when I run the sql on local server :

delete a from localhost_boston.boston.dbo.Spot as a,
bica.tmpspot as b where a.spotid=b.spotid

it display 7 rows have deleted ,but when I run:

select * from localhost_boston.boston.dbo.Spot as a
join bica. tmpspot as b on a.spotid=b.spotid

find the 7 rows have not delete,I check the localhost_boston.boston.dbo.Spot

table ,and find 7 rows have deleted that it is not a.spotid=b.spotid ,why ?

If I run :

delete from localhost_boston.boston.dbo.Spot where spotid=28147

and the row can deleted .why ?

anyone can talk me how to do I can !

Thanks.

Your delete syntax is improper. You should use a subquery to constrain your deleted rows, not a join.

Code Snippet

delete localhost_boston.boston.dbo.Spot where spotid in (select spotid from bica.tmpspot)

Joins create a result set and using a generic join syntax you can get unexpected result sets. Always test your joins in a select statement 1st to make sure you are getting the results you expect.

Dont backup if Database hasnt changed

I do weekly full backups of my SQL databases via a scheduled T-SQL job.
I noticed that I have some static databases that dont normally change,
so I dont want to back it up if it has not changed, but when it does,
then I want a backup.
Is there something in the master table, as example, that I can check
prior to running the backup that will indicate any changes?
An example is the Northwind database. I could exclude it from the
backup, but then I would not back it up if it where to change. Again
this is an example, I would not need to modify Northwind.
Thanks in advance for any ideas; they usually give me ideas to problems
yet to come...
Rob CamardaI don't think there's a generic way to tell if a database has changed
or not (assuming that you mean when data in a user table has changed).
You could run a trace which looks for INSERT/UPDATE/DELETE statements,
log the trace in a table, and then check the contents of the table in a
custom backup job, but that doesn't seem very practical.

If your concern is to reduce disk space used for backups, you could use
differential backups for your 'static' databases, and only do the full
backup once a month, or whatever interval is appropriate. But if your
concern is to simplify administration, and given how cheap disks are
relative to a DBA's time, I would consider just adding another disk and
continuing with the same backup job for all databases.

If this isn't helpful, you might want to give some more details about
your environment, especially about how big the databases are, how often
you expect updates, and what you're trying to achieve (eg. save disk
space).

Simon|||I use SQLsafe to backup and compress my data, which in turn is saved to
my backup server. My tape is HP's Surestore 6/6000 library. I have
5540GB tapes in the library.
Monday through Saturday I perform a differential backup and a full
backup on Sunday. My plan is to keep a 1 month worth of data on the
shared disk (890GB currently, upgrading to 10 300GB raid5 next year).
Once the full backup is a month old, I move it to the library and
delete the files from the disk once the backup is successful. I wont
backup the diffs to tape and will delete the prior weeks diff backups
once the full backup is complete.
This is my current plan, it will change as I get useful input and see
how it works in practice.
My full backup of some tables are not currently large; the largest
5.3GB after compression. However, I tend to look forward, and forsee
more demand on the backup server, so why backup data that hasnt
changed? The library will be backing up 3 Linux servers, 16 windows
servers, 5 SQL and 3 Sun machines with more to come. So, I wish to
maximize the storage on the Library by keeping unnecessary data off the
library.
Again, there may not be a practical solution to my question, but often
I find I learn something unrelated that may help me with a future
problem.|||Hi

If you don't back up the database(s) then the last good backup may fall off
the tape cycle. If you chose to do this then restoring the database(s) will
you having to search more tapes for the relevant backups.

If the backups don't fit onto a single tape then you may wish to use an
autochanger (if you aren't already!). It might be possible that you could
use a server that can stage the backups on disc before putting to tape at a
different time. You may also want to consider separating database backups
from other types of backups to speed up the time needed to recover.

You may want to remove the sample databases like northwind and pubs from
your live systems, they are re-creatable from the scripts which are
downloadable if necessary.

If databases are not updated or not updated in an ad-hoc way, then you may
wish to make them read-only and use a different backup cycle for them.

John

"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1126799621.306011.34090@.o13g2000cwo.googlegro ups.com...
>I use SQLsafe to backup and compress my data, which in turn is saved to
> my backup server. My tape is HP's Surestore 6/6000 library. I have
> 5540GB tapes in the library.
> Monday through Saturday I perform a differential backup and a full
> backup on Sunday. My plan is to keep a 1 month worth of data on the
> shared disk (890GB currently, upgrading to 10 300GB raid5 next year).
> Once the full backup is a month old, I move it to the library and
> delete the files from the disk once the backup is successful. I wont
> backup the diffs to tape and will delete the prior weeks diff backups
> once the full backup is complete.
> This is my current plan, it will change as I get useful input and see
> how it works in practice.
> My full backup of some tables are not currently large; the largest
> 5.3GB after compression. However, I tend to look forward, and forsee
> more demand on the backup server, so why backup data that hasnt
> changed? The library will be backing up 3 Linux servers, 16 windows
> servers, 5 SQL and 3 Sun machines with more to come. So, I wish to
> maximize the storage on the Library by keeping unnecessary data off the
> library.
> Again, there may not be a practical solution to my question, but often
> I find I learn something unrelated that may help me with a future
> problem.

Don't Auto Run with default parameters

Is there a way to not have a report run when all of the default parameters
are set?
We would like to give the users some defaults so they don't have to type as
much when they run a report.Unfortunately there is not. There have been many request for this feature.
If you can wait for Yukon then you could use the viewer control which you
could give this behavior.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alec Hardy" <AlecHardy@.discussions.microsoft.com> wrote in message
news:B3C08BBF-2D07-4941-A986-BA89E122E346@.microsoft.com...
> Is there a way to not have a report run when all of the default parameters
> are set?
> We would like to give the users some defaults so they don't have to type
> as
> much when they run a report.

don't allow duplicates problem

I have MSDE with the server tools from sql server 2000 enterprise edition. I
am having a problem with one of my tables. The table is set up with two
fields Class and Student. I want there to be only one student of the same
name in each class. If i use the primary key it will just allow only one
student of the same name in the entire table and wouldn't look at the class
that the student is taking. Is there an easy way around this?
I found a solution, I created a dual primary key. I highlighted both items
and then turned on the primary key and it does exactly what I wanted it to
except the maximum size is 900bytes for a primary key so I had to adjust the
length of the fields a bit.
"Benjamin" wrote:

> I have MSDE with the server tools from sql server 2000 enterprise edition. I
> am having a problem with one of my tables. The table is set up with two
> fields Class and Student. I want there to be only one student of the same
> name in each class. If i use the primary key it will just allow only one
> student of the same name in the entire table and wouldn't look at the class
> that the student is taking. Is there an easy way around this?

Donno how to work on MSDE?

Hi,

I downloaded and installed MSDE successfully on my comp, but since i have this practice of creating my data base, tables.. using the enterprise manager, i donno how exactly to do the same in. MSDE.

I am new to MSDE, so plz someone explain me to create the database and the tables in MSDE.

and another question is that.. i have created 2 instances of the same.. but donno what these instances exactly are ?

can anyone plz explain me all these..?

Try downloading The ASP Enterprise Manager, which is a web based toolwhich allows you to do many Enterprise Manager type tasks in MSDEwithout SQLEM.
http://www.aspenterprisemanager.com/
Jason

don't find Management Studio after installed SQL 2008 CTP JULY.

My OS is WinXP

I don't find Management Studio after installed SQL 2008 CTP JULY.

Why?

Thanks!

bill

I now it sounds like a super obvious question, but did you select to install Management Studio? Did you hit any installation errors?

Take a look at C:\Program Files\microsoft sql server\100\Setup Bootstrap\LOG\Summary.txt to see if there were any installation errors.

Cheers,

Dan

|||

Bill,

Since we haven't heard back from you in ~10 days I'm going to close this as answered. If you're still encountering problems with Management Studio feel free to post a new thread.

Cheers,

Dan

|||

Same problem I am facing. As per comment I have checked with log file and content are as below:

Microsoft SQL Server 2008 10.0.1049.14
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Sun Sep 23 13:07:25 2007

So please help me resolve this issue.

Don

Hi every one,
I learn a lot reading your comments,
Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
performance or create locks ?
I mean : While UPDATE STATISTICS is runnning, will it
affect performance ?
Thanks !
DonYes it will have an impact. Whether this will be noticeable to users are
not is another question...
Regarding locking, SQL Server takes two types of locks:
"Sch-S: Schema Stability Lock
--
This lock ensures that a schema element, such as a table or index, will
not be dropped while any session holds a schema stability lock on the
schema element.
Sch-M-UPD-STATS: Schema Modification Lock
---
This is a non-blocking lock that is used by the system to ensure that
only one automatic UPDATE STATISTICS process is run against a table at
any given point in time. The sp_lock stored procedure will report this
lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Above information found here:
INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work
http://support.microsoft.com/kb/q195565/
They should not block usual DML queries (insert, update, delete), but
may block DDL queries (drop table, alter table, alter database, etc).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
PERFORMANCE DURING UPDATING STATS wrote:
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don|||"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
It will have an effect on performance, in that it uses system resources.
How much of an effect is dependant on your server's horsepower, the size of
the table you're updating statistics for, etc... It will, like any other
read operation, create shared locks, but I have not seen it create exclusive
locks. So blocking should not be an issue.|||"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OtavF8dtEHA.2072@.tk2msftngp13.phx.gbl...
> any given point in time. The sp_lock stored procedure will report this
> lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Mark,
I've never seen the SCH-M lock; I always get only SCH-S when I use
UPDATE STATISTICS on my systems. Is there an option that controls which
lock types are taken?|||Adam,
No, not that I'm aware of.
Adam Machanic wrote:
> I've never seen the SCH-M lock; I always get only SCH-S when I use
> UPDATE STATISTICS on my systems. Is there an option that controls which
> lock types are taken?
>|||UPDATE STATISTICS doesn't use locks, but it will affect performance, because
data pages are randomly accessed from the table to create the statistics.
Most likely not all of these pages are in memory and have to be read from
disk.
Jacco Schalkwijk
SQL Server MVP
"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don

Don

Hi every one,
I learn a lot reading your comments,
Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
performance or create locks ?
I mean : While UPDATE STATISTICS is runnning, will it
affect performance ?
Thanks !
Don
Yes it will have an impact. Whether this will be noticeable to users are
not is another question...
Regarding locking, SQL Server takes two types of locks:
"Sch-S: Schema Stability Lock
This lock ensures that a schema element, such as a table or index, will
not be dropped while any session holds a schema stability lock on the
schema element.
Sch-M-UPD-STATS: Schema Modification Lock
This is a non-blocking lock that is used by the system to ensure that
only one automatic UPDATE STATISTICS process is run against a table at
any given point in time. The sp_lock stored procedure will report this
lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Above information found here:
INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work
http://support.microsoft.com/kb/q195565/
They should not block usual DML queries (insert, update, delete), but
may block DDL queries (drop table, alter table, alter database, etc).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
PERFORMANCE DURING UPDATING STATS wrote:
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don
|||"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
It will have an effect on performance, in that it uses system resources.
How much of an effect is dependant on your server's horsepower, the size of
the table you're updating statistics for, etc... It will, like any other
read operation, create shared locks, but I have not seen it create exclusive
locks. So blocking should not be an issue.
|||"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OtavF8dtEHA.2072@.tk2msftngp13.phx.gbl...
> any given point in time. The sp_lock stored procedure will report this
> lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Mark,
I've never seen the SCH-M lock; I always get only SCH-S when I use
UPDATE STATISTICS on my systems. Is there an option that controls which
lock types are taken?
|||Adam,
No, not that I'm aware of.
Adam Machanic wrote:
> I've never seen the SCH-M lock; I always get only SCH-S when I use
> UPDATE STATISTICS on my systems. Is there an option that controls which
> lock types are taken?
>
|||UPDATE STATISTICS doesn't use locks, but it will affect performance, because
data pages are randomly accessed from the table to create the statistics.
Most likely not all of these pages are in memory and have to be read from
disk.
Jacco Schalkwijk
SQL Server MVP
"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don

Don

Hi every one,
I learn a lot reading your comments,
Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
performance or create locks ?
I mean : While UPDATE STATISTICS is runnning, will it
affect performance ?
Thanks !
DonYes it will have an impact. Whether this will be noticeable to users are
not is another question...
Regarding locking, SQL Server takes two types of locks:
"Sch-S: Schema Stability Lock
--
This lock ensures that a schema element, such as a table or index, will
not be dropped while any session holds a schema stability lock on the
schema element.
Sch-M-UPD-STATS: Schema Modification Lock
---
This is a non-blocking lock that is used by the system to ensure that
only one automatic UPDATE STATISTICS process is run against a table at
any given point in time. The sp_lock stored procedure will report this
lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Above information found here:
INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work
http://support.microsoft.com/kb/q195565/
They should not block usual DML queries (insert, update, delete), but
may block DDL queries (drop table, alter table, alter database, etc).
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
PERFORMANCE DURING UPDATING STATS wrote:
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don|||"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
It will have an effect on performance, in that it uses system resources.
How much of an effect is dependant on your server's horsepower, the size of
the table you're updating statistics for, etc... It will, like any other
read operation, create shared locks, but I have not seen it create exclusive
locks. So blocking should not be an issue.|||"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OtavF8dtEHA.2072@.tk2msftngp13.phx.gbl...
> any given point in time. The sp_lock stored procedure will report this
> lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Mark,
I've never seen the SCH-M lock; I always get only SCH-S when I use
UPDATE STATISTICS on my systems. Is there an option that controls which
lock types are taken?|||Adam,
No, not that I'm aware of.
Adam Machanic wrote:
> I've never seen the SCH-M lock; I always get only SCH-S when I use
> UPDATE STATISTICS on my systems. Is there an option that controls which
> lock types are taken?
>|||UPDATE STATISTICS doesn't use locks, but it will affect performance, because
data pages are randomly accessed from the table to create the statistics.
Most likely not all of these pages are in memory and have to be read from
disk.
--
Jacco Schalkwijk
SQL Server MVP
"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don

Domino VBA

Hi,
I am trying to read data from a domino databases (located in our LAN
not on my local machine!) from my access project. I pass a code to the
function checkCodeInDomino to be verified on Domino. On my local
machine everything went fine, but suddenly there came up following
problem:

I was coding and testing with the code below, and I have never been
asked for my lotus password, now when starting my function, there comes
a box up (dont know why this changed?) where I have to enter my lotus
password, when entering my notes password, my function works fine.
But I have to distribute my application to other clients and users, and
it doesnt make sense to use my password.
So I received a public account from our domino admin, for all of us.
When using "DomSession.Initialize (publicLotusPassword)" there are no
errors.
But the problem is, that that's not a real solution for my
appliacation, because each user has to enter his own password, I dont
want that,
but entering a hard coded password doesnt work, because the password
that is asked is referring to
"c:\lotus\notes\data\wk\$userName.ID"

Is there any way to use "DomSession.Initialize" in some kind of a
"generic" way, so that any other user can use the function
"checkCodeInDomino"?
Please remember that the domino database is always on a remote machine
and
"'DomSession.InitializeUsingNotesUserName($Admin, $Pass)"
does not work!

Here is my code...

Function checkCodeInDomino(checkCode As Long)
On Error GoTo Err_Quit_Click

Dim DomDir As NotesDatabase
Dim DomContacts As NotesView
Dim DomDoc As NotesDocument
Dim StrName As String

Dim DomSession As NotesSession
Dim serverName As String
Dim databaseFile As String

Set DomSession = CreateObject("Lotus.NotesSession")
serverName = "myServerName"
databaseFile = "myFile"

DomSession.Initialize

Set DomDir = DomSession.GetDatabase(serverName, databaseFile)
Set DomContacts = DomDir.GetView("myView")
Set DomDoc = DomContacts.GetFirstDocument

While Not (DomDoc Is Nothing)

If DomDoc.GetItemValue("Dealer_Code")(0) = checkCode Then
checkCodeInDomino = True
Exit Function
End If
Set DomDoc = DomContacts.GetNextDocument(DomDoc)
Wend

checkCodeInDomino = False

Exit_Quit_Click:
Exit Function

Err_Quit_Click:
MsgBox Err.Description
Resume Exit_Quit_Click

End Function

Thank you for your attention!

Peter NeumaierI think you'll have more luck asking in a newsgroup related to Notes or
Domino, as opposed to a database group.

Have you tried looking for documentation at http://www.lotus.com?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Peter Neumaier" <Peter.Neumaier@.gmail.com> wrote in message
news:1112392663.538812.206890@.g14g2000cwa.googlegr oups.com...
> Hi,
> I am trying to read data from a domino databases (located in our LAN
> not on my local machine!) from my access project. I pass a code to the
> function checkCodeInDomino to be verified on Domino. On my local
> machine everything went fine, but suddenly there came up following
> problem:
> I was coding and testing with the code below, and I have never been
> asked for my lotus password, now when starting my function, there comes
> a box up (dont know why this changed?) where I have to enter my lotus
> password, when entering my notes password, my function works fine.
> But I have to distribute my application to other clients and users, and
> it doesnt make sense to use my password.
> So I received a public account from our domino admin, for all of us.
> When using "DomSession.Initialize (publicLotusPassword)" there are no
> errors.
> But the problem is, that that's not a real solution for my
> appliacation, because each user has to enter his own password, I dont
> want that,
> but entering a hard coded password doesnt work, because the password
> that is asked is referring to
> "c:\lotus\notes\data\wk\$userName.ID"
> Is there any way to use "DomSession.Initialize" in some kind of a
> "generic" way, so that any other user can use the function
> "checkCodeInDomino"?
> Please remember that the domino database is always on a remote machine
> and
> "'DomSession.InitializeUsingNotesUserName($Admin, $Pass)"
> does not work!
> Here is my code...
> Function checkCodeInDomino(checkCode As Long)
> On Error GoTo Err_Quit_Click
> Dim DomDir As NotesDatabase
> Dim DomContacts As NotesView
> Dim DomDoc As NotesDocument
> Dim StrName As String
> Dim DomSession As NotesSession
> Dim serverName As String
> Dim databaseFile As String
> Set DomSession = CreateObject("Lotus.NotesSession")
> serverName = "myServerName"
> databaseFile = "myFile"
> DomSession.Initialize
> Set DomDir = DomSession.GetDatabase(serverName, databaseFile)
> Set DomContacts = DomDir.GetView("myView")
> Set DomDoc = DomContacts.GetFirstDocument
> While Not (DomDoc Is Nothing)
> If DomDoc.GetItemValue("Dealer_Code")(0) = checkCode Then
> checkCodeInDomino = True
> Exit Function
> End If
> Set DomDoc = DomContacts.GetNextDocument(DomDoc)
> Wend
> checkCodeInDomino = False
> Exit_Quit_Click:
> Exit Function
> Err_Quit_Click:
> MsgBox Err.Description
> Resume Exit_Quit_Click
> End Function
> Thank you for your attention!
> Peter Neumaier|||Hi Douglas,

thank you for your answer, but I think that my problem is more VBA and
ACCES than it is to domino databases.

So the only problem I have to solve is, how to use a default password
when accessing the domino database ...

For sure I found documentation on lotus.com, but there isnt anything
regarding my problem

"Douglas J. Steele" <NOSPAM_djsteele@.NOSPAM_canada.com> wrote in message news:<0cCdnUdLW-09ddDfRVn-vA@.rogers.com>...
> I think you'll have more luck asking in a newsgroup related to Notes or
> Domino, as opposed to a database group.
> Have you tried looking for documentation at http://www.lotus.com?
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
> "Peter Neumaier" <Peter.Neumaier@.gmail.com> wrote in message
> news:1112392663.538812.206890@.g14g2000cwa.googlegr oups.com...
> > Hi,
> > I am trying to read data from a domino databases (located in our LAN
> > not on my local machine!) from my access project. I pass a code to the
> > function checkCodeInDomino to be verified on Domino. On my local
> > machine everything went fine, but suddenly there came up following
> > problem:
> > I was coding and testing with the code below, and I have never been
> > asked for my lotus password, now when starting my function, there comes
> > a box up (dont know why this changed?) where I have to enter my lotus
> > password, when entering my notes password, my function works fine.
> > But I have to distribute my application to other clients and users, and
> > it doesnt make sense to use my password.
> > So I received a public account from our domino admin, for all of us.
> > When using "DomSession.Initialize (publicLotusPassword)" there are no
> > errors.
> > But the problem is, that that's not a real solution for my
> > appliacation, because each user has to enter his own password, I dont
> > want that,
> > but entering a hard coded password doesnt work, because the password
> > that is asked is referring to
> > "c:\lotus\notes\data\wk\$userName.ID"
> > Is there any way to use "DomSession.Initialize" in some kind of a
> > "generic" way, so that any other user can use the function
> > "checkCodeInDomino"?
> > Please remember that the domino database is always on a remote machine
> > and
> > "'DomSession.InitializeUsingNotesUserName($Admin, $Pass)"
> > does not work!
> > Here is my code...
> > Function checkCodeInDomino(checkCode As Long)
> > On Error GoTo Err_Quit_Click
> > Dim DomDir As NotesDatabase
> > Dim DomContacts As NotesView
> > Dim DomDoc As NotesDocument
> > Dim StrName As String
> > Dim DomSession As NotesSession
> > Dim serverName As String
> > Dim databaseFile As String
> > Set DomSession = CreateObject("Lotus.NotesSession")
> > serverName = "myServerName"
> > databaseFile = "myFile"
> > DomSession.Initialize
> > Set DomDir = DomSession.GetDatabase(serverName, databaseFile)
> > Set DomContacts = DomDir.GetView("myView")
> > Set DomDoc = DomContacts.GetFirstDocument
> > While Not (DomDoc Is Nothing)
> > If DomDoc.GetItemValue("Dealer_Code")(0) = checkCode Then
> > checkCodeInDomino = True
> > Exit Function
> > End If
> > Set DomDoc = DomContacts.GetNextDocument(DomDoc)
> > Wend
> > checkCodeInDomino = False
> > Exit_Quit_Click:
> > Exit Function
> > Err_Quit_Click:
> > MsgBox Err.Description
> > Resume Exit_Quit_Click
> > End Function
> > Thank you for your attention!
> > Peter Neumaier|||While I recognize that you're experiencing the problem using automation from
VBA, you're still issuing commands to directly to Domino. I would think that
people more familiar with the Domino programming model would be better able
to help than people familiar with VBA.

YMMV, though.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Peter Neumaier" <Peter.Neumaier@.gmail.com> wrote in message
news:98284637.0504020824.4b7a20c9@.posting.google.c om...
> Hi Douglas,
> thank you for your answer, but I think that my problem is more VBA and
> ACCES than it is to domino databases.
> So the only problem I have to solve is, how to use a default password
> when accessing the domino database ...
> For sure I found documentation on lotus.com, but there isnt anything
> regarding my problem
>
> "Douglas J. Steele" <NOSPAM_djsteele@.NOSPAM_canada.com> wrote in message
> news:<0cCdnUdLW-09ddDfRVn-vA@.rogers.com>...
>> I think you'll have more luck asking in a newsgroup related to Notes or
>> Domino, as opposed to a database group.
>>
>> Have you tried looking for documentation at http://www.lotus.com?
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>>
>> "Peter Neumaier" <Peter.Neumaier@.gmail.com> wrote in message
>> news:1112392663.538812.206890@.g14g2000cwa.googlegr oups.com...
>> > Hi,
>> > I am trying to read data from a domino databases (located in our LAN
>> > not on my local machine!) from my access project. I pass a code to the
>> > function checkCodeInDomino to be verified on Domino. On my local
>> > machine everything went fine, but suddenly there came up following
>> > problem:
>>> > I was coding and testing with the code below, and I have never been
>> > asked for my lotus password, now when starting my function, there comes
>> > a box up (dont know why this changed?) where I have to enter my lotus
>> > password, when entering my notes password, my function works fine.
>> > But I have to distribute my application to other clients and users, and
>> > it doesnt make sense to use my password.
>> > So I received a public account from our domino admin, for all of us.
>> > When using "DomSession.Initialize (publicLotusPassword)" there are no
>> > errors.
>> > But the problem is, that that's not a real solution for my
>> > appliacation, because each user has to enter his own password, I dont
>> > want that,
>> > but entering a hard coded password doesnt work, because the password
>> > that is asked is referring to
>> > "c:\lotus\notes\data\wk\$userName.ID"
>>> > Is there any way to use "DomSession.Initialize" in some kind of a
>> > "generic" way, so that any other user can use the function
>> > "checkCodeInDomino"?
>> > Please remember that the domino database is always on a remote machine
>> > and
>> > "'DomSession.InitializeUsingNotesUserName($Admin, $Pass)"
>> > does not work!
>>> > Here is my code...
>>> > Function checkCodeInDomino(checkCode As Long)
>> > On Error GoTo Err_Quit_Click
>>> > Dim DomDir As NotesDatabase
>> > Dim DomContacts As NotesView
>> > Dim DomDoc As NotesDocument
>> > Dim StrName As String
>>> > Dim DomSession As NotesSession
>> > Dim serverName As String
>> > Dim databaseFile As String
>>> > Set DomSession = CreateObject("Lotus.NotesSession")
>> > serverName = "myServerName"
>> > databaseFile = "myFile"
>>> > DomSession.Initialize
>>> > Set DomDir = DomSession.GetDatabase(serverName, databaseFile)
>> > Set DomContacts = DomDir.GetView("myView")
>> > Set DomDoc = DomContacts.GetFirstDocument
>>> > While Not (DomDoc Is Nothing)
>>> > If DomDoc.GetItemValue("Dealer_Code")(0) = checkCode Then
>> > checkCodeInDomino = True
>> > Exit Function
>> > End If
>> > Set DomDoc = DomContacts.GetNextDocument(DomDoc)
>> > Wend
>>> > checkCodeInDomino = False
>>> > Exit_Quit_Click:
>> > Exit Function
>>> > Err_Quit_Click:
>> > MsgBox Err.Description
>> > Resume Exit_Quit_Click
>>> > End Function
>>> > Thank you for your attention!
>>> > Peter Neumaier
>

DomainUserAccount?

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

Domains in SQL Server?

Hello all,
I've worked mostly with FirebirdSQL and Firebird has a nifty little
feature called "domains" which are basically pre-defined field types
which can included native field type (int, varchar, etc), constraints,
etc. Instead of defining an Int field with a default value of zero
that must be between 1 and 100000 everytime you need that kind of
field, you can just create that domain and assign that domain as a
field/column type when created a table.
This is a time saver as well as making data types standardized in my
database.
Is there an equivelant in SQL Server?
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."Yes, User Defined Data Types.
Have a look in SQL Books On Line for sp_addtype
You will need to bind a rule to your UDDT with the syntax "CREATE RULE" to
fully implement what you're after
HTH. Ryan
"Lee" <luv2program2000@.yahoo.com> wrote in message
news:Ox7G4Y4GGHA.532@.TK2MSFTNGP15.phx.gbl...
>
> Hello all,
> I've worked mostly with FirebirdSQL and Firebird has a nifty little
> feature called "domains" which are basically pre-defined field types
> which can included native field type (int, varchar, etc), constraints,
> etc. Instead of defining an Int field with a default value of zero
> that must be between 1 and 100000 everytime you need that kind of
> field, you can just create that domain and assign that domain as a
> field/column type when created a table.
> This is a time saver as well as making data types standardized in my
> database.
> Is there an equivelant in SQL Server?
>
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."|||Ryan enlightened me by writing:

> Yes, User Defined Data Types.
> Have a look in SQL Books On Line for sp_addtype
> You will need to bind a rule to your UDDT with the syntax "CREATE
> RULE" to fully implement what you're after
Excellent.
Thanks Ryan
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."|||But you should be aware that Default and Rule objects (they you tie to user
defined datatypes, or
"alias datatypes" as they are called in 2005) are deprecated in 2005. This m
eans that Default and
Rule objects will be removed in some future version of SQL Server (in favor
for Constraints).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lee" <luv2program2000@.yahoo.com> wrote in message news:%23YmZyu4GGHA.216@.TK2MSFTNGP15.phx.g
bl...
> Ryan enlightened me by writing:
>
> Excellent.
> Thanks Ryan
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."

Domains in SQL Server?

Hello all,
I've worked mostly with FirebirdSQL and Firebird has a nifty little
feature called "domains" which are basically pre-defined field types
which can included native field type (int, varchar, etc), constraints,
etc. Instead of defining an Int field with a default value of zero
that must be between 1 and 100000 everytime you need that kind of
field, you can just create that domain and assign that domain as a
field/column type when created a table.
This is a time saver as well as making data types standardized in my
database.
Is there an equivelant in SQL Server?
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."
Yes, User Defined Data Types.
Have a look in SQL Books On Line for sp_addtype
You will need to bind a rule to your UDDT with the syntax "CREATE RULE" to
fully implement what you're after
HTH. Ryan
"Lee" <luv2program2000@.yahoo.com> wrote in message
news:Ox7G4Y4GGHA.532@.TK2MSFTNGP15.phx.gbl...
>
> Hello all,
> I've worked mostly with FirebirdSQL and Firebird has a nifty little
> feature called "domains" which are basically pre-defined field types
> which can included native field type (int, varchar, etc), constraints,
> etc. Instead of defining an Int field with a default value of zero
> that must be between 1 and 100000 everytime you need that kind of
> field, you can just create that domain and assign that domain as a
> field/column type when created a table.
> This is a time saver as well as making data types standardized in my
> database.
> Is there an equivelant in SQL Server?
>
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."
|||Ryan enlightened me by writing:

> Yes, User Defined Data Types.
> Have a look in SQL Books On Line for sp_addtype
> You will need to bind a rule to your UDDT with the syntax "CREATE
> RULE" to fully implement what you're after
Excellent.
Thanks Ryan
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."
|||But you should be aware that Default and Rule objects (they you tie to user defined datatypes, or
"alias datatypes" as they are called in 2005) are deprecated in 2005. This means that Default and
Rule objects will be removed in some future version of SQL Server (in favor for Constraints).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lee" <luv2program2000@.yahoo.com> wrote in message news:%23YmZyu4GGHA.216@.TK2MSFTNGP15.phx.gbl...
> Ryan enlightened me by writing:
>
> Excellent.
> Thanks Ryan
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."

Domains in SQL Server?

Hello all,
I've worked mostly with FirebirdSQL and Firebird has a nifty little
feature called "domains" which are basically pre-defined field types
which can included native field type (int, varchar, etc), constraints,
etc. Instead of defining an Int field with a default value of zero
that must be between 1 and 100000 everytime you need that kind of
field, you can just create that domain and assign that domain as a
field/column type when created a table.
This is a time saver as well as making data types standardized in my
database.
Is there an equivelant in SQL Server?
--
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."Yes, User Defined Data Types.
Have a look in SQL Books On Line for sp_addtype
You will need to bind a rule to your UDDT with the syntax "CREATE RULE" to
fully implement what you're after
--
HTH. Ryan
"Lee" <luv2program2000@.yahoo.com> wrote in message
news:Ox7G4Y4GGHA.532@.TK2MSFTNGP15.phx.gbl...
>
> Hello all,
> I've worked mostly with FirebirdSQL and Firebird has a nifty little
> feature called "domains" which are basically pre-defined field types
> which can included native field type (int, varchar, etc), constraints,
> etc. Instead of defining an Int field with a default value of zero
> that must be between 1 and 100000 everytime you need that kind of
> field, you can just create that domain and assign that domain as a
> field/column type when created a table.
> This is a time saver as well as making data types standardized in my
> database.
> Is there an equivelant in SQL Server?
>
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."|||Ryan enlightened me by writing:
> Yes, User Defined Data Types.
> Have a look in SQL Books On Line for sp_addtype
> You will need to bind a rule to your UDDT with the syntax "CREATE
> RULE" to fully implement what you're after
Excellent.
Thanks Ryan
--
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."|||But you should be aware that Default and Rule objects (they you tie to user defined datatypes, or
"alias datatypes" as they are called in 2005) are deprecated in 2005. This means that Default and
Rule objects will be removed in some future version of SQL Server (in favor for Constraints).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lee" <luv2program2000@.yahoo.com> wrote in message news:%23YmZyu4GGHA.216@.TK2MSFTNGP15.phx.gbl...
> Ryan enlightened me by writing:
>> Yes, User Defined Data Types.
>> Have a look in SQL Books On Line for sp_addtype
>> You will need to bind a rule to your UDDT with the syntax "CREATE
>> RULE" to fully implement what you're after
> Excellent.
> Thanks Ryan
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."

domains and workgroups

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

Domains

What is the best way to move Views and Stored Procedures from Dev to Prod?
Is DTS a good option. Thankshi niles,
Go to Enterprise manager. Right click on the database. click on "all
tasks" . click on "generate sql script". click on "show all". Click on "all
views" and "all stored procedures", this will generate a script file with
DDL commands. You can run this file on the destination database/server
through query analyzer.
Vishal Parkar
vgparkar@.yahoo.co.in|||Thanks, works beautifully!

Domains

What is the best way to move Views and Stored Procedures from Dev to Prod? Is DTS a good option. Thanks
hi niles,
Go to Enterprise manager. Right click on the database. click on "all
tasks" . click on "generate sql script". click on "show all". Click on "all
views" and "all stored procedures", this will generate a script file with
DDL commands. You can run this file on the destination database/server
through query analyzer.
Vishal Parkar
vgparkar@.yahoo.co.in
|||Thanks, works beautifully!

domains

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

Domains

What is the best way to move Views and Stored Procedures from Dev to Prod? Is DTS a good option. Thankshi niles,
Go to Enterprise manager. Right click on the database. click on "all
tasks" . click on "generate sql script". click on "show all". Click on "all
views" and "all stored procedures", this will generate a script file with
DDL commands. You can run this file on the destination database/server
through query analyzer.
--
Vishal Parkar
vgparkar@.yahoo.co.in

domain\user cannot log on SQL server 2005

Hi All,

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

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

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

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

C:\>

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

Thanks for your help.

Damien

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

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

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

|||

Thanks LY.J for your answer.

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

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

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

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

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

Thanks
Damien

|||

Has the account CMVAL\damien expired?

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

If so, change the password, and logoff/logon

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

|||

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

Thanks
Laurentiu

DOMAIN\REMOTE_MACHINE_NAME$ account

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

'Domain\LocalServer$' is not a valid user

I have a cross-databases SQL application on SQL 2000 servers SP4 (db_A on
localServer, db_B on remoteServer) on Win2003
on same domain.
Linked server is defined as "Be made by the login's current security
context".
The login account is member of a Windows group that is dbowner of both
databases.
A stored proc on db_A that just contains the SQL statement:
begin
INSERT INTO
[remoteServer].[db_B].[Domain\Wingroup].[remoteTable] (A.&#
91;ITEM], A.[DESC])
SELECT DISTINCT A.ITEM_ID, A.ITEM_DESC
FROM [localServer].[db_A].[dbo].[ENTITIES] A,
[localServer].[db_B].[dbo].[ITEM_VIEW] B
WHERE A.ITEM_ID = B.ITEM_ID
end
ITEM_VIEW is a view on tables dbo-owned on remoteServer (only the target
remote table is owned by Domain\Wingroup)
Why
- if I exec the stored proc. without the a BEGIN TRANSACTION it works
- if I do the same within a transaction:
SET XACT_ABORT ON
BEGIN TRANSACTION
exec sp_A
COMMIT
I receive the error:
Server user 'Domain\LocalServer$' is not a valid user in database 'db_A'.
I receive the error also if I log MS-Query Anyalzer by sa account instead of
Windows Authetntication.
The problem is solved only if I create a local TEMP table in place of the
ITEM_VIEW, but from the application point of view is not acceptable.
Additionally, the two servers are trusted for delegation (Kerberos) and the
problem is independant of how I set "Allow ownership chain" on two databases
Could it be a MSDTC problem?
Any suggestion is really appreciated,
Thanks in advance
BeppeHi Beppe,
This is Justin from Microsoft. Welcome to MSDN managed NewsGroup.
As I understand, the db_B in the select part of the query is on the
localserver. Is this right? I created some test tables and view on my
machine and it works fine on my side.
If you specify the Linked server to use a predefined security context such
as SA, will you still encounter the same problem? If you still encounter
the same problem, please let me know the exact error message so that I
could better understand your issue.
If you have any question, please feel free to let me know.
Thanks & Regards,
Justin Shen
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Beppe" <Beppe@.newsgroups.nospam>
| Subject: 'Domain\LocalServer$' is not a valid user
| Date: Mon, 30 Jan 2006 19:48:41 +0100
| Lines: 49
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <eTBSM3cJGHA.2896@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.security
| NNTP-Posting-Host: txt1.txt.tno.it 138.66.77.70
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.security:26298
| X-Tomcat-NG: microsoft.public.sqlserver.security
|
| I have a cross-databases SQL application on SQL 2000 servers SP4 (db_A on
| localServer, db_B on remoteServer) on Win2003
| on same domain.
| Linked server is defined as "Be made by the login's current security
| context".
| The login account is member of a Windows group that is dbowner of both
| databases.
| A stored proc on db_A that just contains the SQL statement:
|
| begin
| INSERT INTO
| [remoteServer].[db_B].[Domain\Wingroup].[remoteTable] (A
.[ITEM],
A.[DESC])
| SELECT DISTINCT A.ITEM_ID, A.ITEM_DESC
| FROM [localServer].[db_A].[dbo].[ENTITIES] A,
| [localServer].[db_B].[dbo].[ITEM_VIEW] B
| WHERE A.ITEM_ID = B.ITEM_ID
| end
|
| ITEM_VIEW is a view on tables dbo-owned on remoteServer (only the target
| remote table is owned by Domain\Wingroup)
|
| Why
| - if I exec the stored proc. without the a BEGIN TRANSACTION it works
| - if I do the same within a transaction:
|
| SET XACT_ABORT ON
| BEGIN TRANSACTION
| exec sp_A
| COMMIT
|
| I receive the error:
|
| Server user 'Domain\LocalServer$' is not a valid user in database 'db_A'.
|
| I receive the error also if I log MS-Query Anyalzer by sa account instead
of
| Windows Authetntication.
| The problem is solved only if I create a local TEMP table in place of the
| ITEM_VIEW, but from the application point of view is not acceptable.
| Additionally, the two servers are trusted for delegation (Kerberos) and
the
| problem is independant of how I set "Allow ownership chain" on two
databases
| Could it be a MSDTC problem?
|
| Any suggestion is really appreciated,
| Thanks in advance
| Beppe
|
| --
|
|
||||Hi Justin,
thanks a lot for your quick response

>As I understand, the db_B in the select part of the query is on the
>localserver. Is this right?
Yes, you are right. Actually it was db_A and not db_B. I have verified that
the problem arises
also without the stored proc. I retype here the query:
SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT INTO [RemoteServer].[db_B].[TEST_DOMAIN\TSGROUP].[Tar
getTable]
(B.[DP_SKU], B.[ITEM_ID], B.[START_ITEM_ID], B.[COLOR_ID], B
.[SIZE_ID],
B.[CONFIG_ID], B.[CAT_CODE], B.[REGION_ID])
SELECT DISTINCT A.B_ENTITY_IDEN, A.B_ENTITY_IDEN, A.B_ENTITY_IDEN, '', '',
'', A.CAT_CODE, 'DAT'
FROM [LocalServer].[db_A].[dbo].[BASE_ENTITIES] A,
[LocalServer].[db_A].[dbo].[ITEM_VIEW] B
WHERE B_ENTITY_IDEN = ITW_ITEM_ID
COMMIT
Running it by MS-Query Analyzer, the error is:
Server: Msg 916, Level 14, State 1, Line 3
Server user 'TEST-DOMAIN\TEST1$' is not a valid user in database
'DB_BEPPE'..
TEST-DOMAIN\TEST1 is the computer name of the LocalServer
DB_BEPPE is the remote Database (=db_B)
No problem without the transaction.

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

Sunday, February 26, 2012

Dont Insert duplicate rows

Don't have any Server to choose from the Server name list

Don't get Service Broker to Work

Dont get component choices during installation. Installation finishes without db en iis re

don''t find Management Studio after installed SQL 2008 CTP JULY.

don''t find Management Studio after installed SQL 2008 CTP JULY.

don''t find Management Studio after installed SQL 2008 CTP JULY.

Don't email report if no results from query

Don't email report if no results from query

Dont display the field if it contains a zero value

Dont Display dbo. in Object Explorer

Dont display address if its not there!

Don't delete data from linkedserver table .

Don't delete data from linkedserver table .

Dont backup if Database hasnt changed

Don't Auto Run with default parameters

don't allow duplicates problem

Donno how to work on MSDE?

don't find Management Studio after installed SQL 2008 CTP JULY.

Don

Don

Don

Domino VBA

DomainUserAccount?

Domains in SQL Server?

Domains in SQL Server?

Domains in SQL Server?

domains and workgroups

Domains

Domains

domains

Domains

domain\user cannot log on SQL server 2005

DOMAIN\REMOTE_MACHINE_NAME$ account

'Domain\LocalServer$' is not a valid user

Drill down slow

Blog Archive

About Me