Drawbacks of putting transaction log on system drive

Hi,
I use MS SQL Server 2000 SP3a on MS Windows 2000 Advanced Server (latest).
Would I come across any drawbacks if I put all transaction log files of a
database onto the system (OS installation) drive along with a page file and
maybe the tempdb? Most of the load in my database is due to read operations.
-- Many thanks, Oskar.Oskar wrote:
> Hi,
> I use MS SQL Server 2000 SP3a on MS Windows 2000 Advanced Server (latest).
> Would I come across any drawbacks if I put all transaction log files of a
> database onto the system (OS installation) drive along with a page file an
d
> maybe the tempdb? Most of the load in my database is due to read operation
s.
> -- Many thanks, Oskar.
>
Hi Oskar
There will most likely be a performance hit the more files you put on
the same drive, but if it will be noticable or a problem is hard to say
without knowing your load and application. The best advice it to test it
as good as you can and then make your decicion based on your tests.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Drawbacks of putting transaction log on system drive

Hi,
I use MS SQL Server 2000 SP3a on MS Windows 2000 Advanced Server (latest).
Would I come across any drawbacks if I put all transaction log files of a
database onto the system (OS installation) drive along with a page file and
maybe the tempdb? Most of the load in my database is due to read operations.
-- Many thanks, Oskar.Oskar wrote:
> Hi,
> I use MS SQL Server 2000 SP3a on MS Windows 2000 Advanced Server (latest).
> Would I come across any drawbacks if I put all transaction log files of a
> database onto the system (OS installation) drive along with a page file and
> maybe the tempdb? Most of the load in my database is due to read operations.
> -- Many thanks, Oskar.
>
Hi Oskar
There will most likely be a performance hit the more files you put on
the same drive, but if it will be noticable or a problem is hard to say
without knowing your load and application. The best advice it to test it
as good as you can and then make your decicion based on your tests.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Drag and drop query onto Management Studio

Everytime I drag and drop a query file (.sql) from Windows Explorer onto
Management Studio, I am prompted to login. How do I get drag and drop to use
existing connection?
guy,
Install SP2 I believe. (At least it works for me.)
RLF
"guy" <guy@.hotmail.com> wrote in message
news:OiHKPyJqHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Everytime I drag and drop a query file (.sql) from Windows Explorer onto
> Management Studio, I am prompted to login. How do I get drag and drop to
> use existing connection?
>
|||That"s the ticket. Thank you.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OGnQpwRqHHA.3740@.TK2MSFTNGP02.phx.gbl...
> guy,
> Install SP2 I believe. (At least it works for me.)
> RLF
> "guy" <guy@.hotmail.com> wrote in message
> news:OiHKPyJqHHA.4872@.TK2MSFTNGP03.phx.gbl...
>

Dr Watson error after writing to SQL Server

I have a VB6 program that receives data using socket and save the data to a
SQL Server 2000 database.
It has Windows 2000 SP4 with all hot patches in it. It has 2 gig of RAM.
The program had been running for years with no problem, but for the last 3
days it had died exactly at 3 AM, right after it finished writing the data
to SQL Server.
No error on the screen, but there is Dr.
Watson error (it is in drwtsn32.log).
The error in drwtsn32.log is:
Exception number: c0000005 (access violation) --> this is on this VB
program.
I do not know how to read Dr Watson log, but the "Raw Stack Dump" contains
c:\winnt\system32\comsvcs.dll.
I searched on the internet and found that there was an Access Violation
error with comsvcs.dll, and the fix is to install the latest Windows
patches, which we already did. We also increased the memory from 1 gig RAM
to 2 gig. The program still happens.
There is no scheduled task running at 3 AM.
Does anybody know how to read Dr Watson log ?
How can I fix this problem ?
Thank you very much.
The previous 2 days when the program did not write to the SQL Server
database, the program did not die.
But today, it starts writing to the database again, and it again died at 3
am. The program writes to the database every minute every day without any
problem, except after it finishes writing to the database at 3 am.
I found this article that talks about Access Violation error and SQL Server
(but it is with Windows 2003, and I am using Windows 2000)
http://ask.support.microsoft.com/kb/910471
Symptoms
An access violation occurs in Microsoft Distributed Transaction Coordinator
(MS DTC) when a client that is connected to an MS DTC clustered resource
tries to create an enlistment in MS DTC. For example, this problem may occur
if a computer where Microsoft SQL Server is installed loses synchronization
with an MS DTC server and then tries to enlist a transaction in MS DTC.
Cause
This problem occurs because of two race conditions in the MS DTC code. In
one race condition, the transaction manager (TM) tries to create a trace
before an interface pointer to perform that trace exists. A second race
condition exists when the MS DTC proxy sends TMDown notifications to
enlistments and resource managers.
Could that problem related to my problem, even though for different Windows
version ? What does it mean by "Microsoft SQL Server is installed loses
synchronization with an MS DTC server and then tries to enlist a transaction
in MS DTC" ?
Thanks.
"fniles" <fniles@.pfmail.com> wrote in message
news:uW9dGglNHHA.1872@.TK2MSFTNGP04.phx.gbl...
>I have a VB6 program that receives data using socket and save the data to a
> SQL Server 2000 database.
> It has Windows 2000 SP4 with all hot patches in it. It has 2 gig of RAM.
> The program had been running for years with no problem, but for the last 3
> days it had died exactly at 3 AM, right after it finished writing the data
> to SQL Server.
> No error on the screen, but there is Dr.
> Watson error (it is in drwtsn32.log).
> The error in drwtsn32.log is:
> Exception number: c0000005 (access violation) --> this is on this VB
> program.
> I do not know how to read Dr Watson log, but the "Raw Stack Dump" contains
> c:\winnt\system32\comsvcs.dll.
> I searched on the internet and found that there was an Access Violation
> error with comsvcs.dll, and the fix is to install the latest Windows
> patches, which we already did. We also increased the memory from 1 gig RAM
> to 2 gig. The program still happens.
> There is no scheduled task running at 3 AM.
> Does anybody know how to read Dr Watson log ?
> How can I fix this problem ?
> Thank you very much.
>
>

Dr Watson error after writing to SQL Server

I have a VB6 program that receives data using socket and save the data to a
SQL Server 2000 database.
It has Windows 2000 SP4 with all hot patches in it. It has 2 gig of RAM.
The program had been running for years with no problem, but for the last 3
days it had died exactly at 3 AM, right after it finished writing the data
to SQL Server.
No error on the screen, but there is Dr.
Watson error (it is in drwtsn32.log).
The error in drwtsn32.log is:
Exception number: c0000005 (access violation) --> this is on this VB
program.
I do not know how to read Dr Watson log, but the "Raw Stack Dump" contains
c:\winnt\system32\comsvcs.dll.
I searched on the internet and found that there was an Access Violation
error with comsvcs.dll, and the fix is to install the latest Windows
patches, which we already did. We also increased the memory from 1 gig RAM
to 2 gig. The program still happens.
There is no scheduled task running at 3 AM.
Does anybody know how to read Dr Watson log ?
How can I fix this problem ?
Thank you very much.The previous 2 days when the program did not write to the SQL Server
database, the program did not die.
But today, it starts writing to the database again, and it again died at 3
am. The program writes to the database every minute every day without any
problem, except after it finishes writing to the database at 3 am.
I found this article that talks about Access Violation error and SQL Server
(but it is with Windows 2003, and I am using Windows 2000)
http://ask.support.microsoft.com/kb/910471
Symptoms
An access violation occurs in Microsoft Distributed Transaction Coordinator
(MS DTC) when a client that is connected to an MS DTC clustered resource
tries to create an enlistment in MS DTC. For example, this problem may occur
if a computer where Microsoft SQL Server is installed loses synchronization
with an MS DTC server and then tries to enlist a transaction in MS DTC.
Cause
This problem occurs because of two race conditions in the MS DTC code. In
one race condition, the transaction manager (TM) tries to create a trace
before an interface pointer to perform that trace exists. A second race
condition exists when the MS DTC proxy sends TMDown notifications to
enlistments and resource managers.
Could that problem related to my problem, even though for different Windows
version ? What does it mean by "Microsoft SQL Server is installed loses
synchronization with an MS DTC server and then tries to enlist a transaction
in MS DTC" ?
Thanks.
"fniles" <fniles@.pfmail.com> wrote in message
news:uW9dGglNHHA.1872@.TK2MSFTNGP04.phx.gbl...
>I have a VB6 program that receives data using socket and save the data to a
> SQL Server 2000 database.
> It has Windows 2000 SP4 with all hot patches in it. It has 2 gig of RAM.
> The program had been running for years with no problem, but for the last 3
> days it had died exactly at 3 AM, right after it finished writing the data
> to SQL Server.
> No error on the screen, but there is Dr.
> Watson error (it is in drwtsn32.log).
> The error in drwtsn32.log is:
> Exception number: c0000005 (access violation) --> this is on this VB
> program.
> I do not know how to read Dr Watson log, but the "Raw Stack Dump" contains
> c:\winnt\system32\comsvcs.dll.
> I searched on the internet and found that there was an Access Violation
> error with comsvcs.dll, and the fix is to install the latest Windows
> patches, which we already did. We also increased the memory from 1 gig RAM
> to 2 gig. The program still happens.
> There is no scheduled task running at 3 AM.
> Does anybody know how to read Dr Watson log ?
> How can I fix this problem ?
> Thank you very much.
>
>

Dr Watson error after writing to SQL Server

I have a VB6 program that receives data using socket and save the data to a
SQL Server 2000 database.
It has Windows 2000 SP4 with all hot patches in it. It has 2 gig of RAM.
The program had been running for years with no problem, but for the last 3
days it had died exactly at 3 AM, right after it finished writing the data
to SQL Server.
No error on the screen, but there is Dr.
Watson error (it is in drwtsn32.log).
The error in drwtsn32.log is:
Exception number: c0000005 (access violation) --> this is on this VB
program.
I do not know how to read Dr Watson log, but the "Raw Stack Dump" contains
c:\winnt\system32\comsvcs.dll.
I searched on the internet and found that there was an Access Violation
error with comsvcs.dll, and the fix is to install the latest Windows
patches, which we already did. We also increased the memory from 1 gig RAM
to 2 gig. The program still happens.
There is no scheduled task running at 3 AM.
Does anybody know how to read Dr Watson log ?
How can I fix this problem ?
Thank you very much.The previous 2 days when the program did not write to the SQL Server
database, the program did not die.
But today, it starts writing to the database again, and it again died at 3
am. The program writes to the database every minute every day without any
problem, except after it finishes writing to the database at 3 am.
I found this article that talks about Access Violation error and SQL Server
(but it is with Windows 2003, and I am using Windows 2000)
http://ask.support.microsoft.com/kb/910471
Symptoms
An access violation occurs in Microsoft Distributed Transaction Coordinator
(MS DTC) when a client that is connected to an MS DTC clustered resource
tries to create an enlistment in MS DTC. For example, this problem may occur
if a computer where Microsoft SQL Server is installed loses synchronization
with an MS DTC server and then tries to enlist a transaction in MS DTC.
Cause
This problem occurs because of two race conditions in the MS DTC code. In
one race condition, the transaction manager (TM) tries to create a trace
before an interface pointer to perform that trace exists. A second race
condition exists when the MS DTC proxy sends TMDown notifications to
enlistments and resource managers.
Could that problem related to my problem, even though for different Windows
version ? What does it mean by "Microsoft SQL Server is installed loses
synchronization with an MS DTC server and then tries to enlist a transaction
in MS DTC" ?
Thanks.
"fniles" <fniles@.pfmail.com> wrote in message
news:uW9dGglNHHA.1872@.TK2MSFTNGP04.phx.gbl...
>I have a VB6 program that receives data using socket and save the data to a
> SQL Server 2000 database.
> It has Windows 2000 SP4 with all hot patches in it. It has 2 gig of RAM.
> The program had been running for years with no problem, but for the last 3
> days it had died exactly at 3 AM, right after it finished writing the data
> to SQL Server.
> No error on the screen, but there is Dr.
> Watson error (it is in drwtsn32.log).
> The error in drwtsn32.log is:
> Exception number: c0000005 (access violation) --> this is on this VB
> program.
> I do not know how to read Dr Watson log, but the "Raw Stack Dump" contains
> c:\winnt\system32\comsvcs.dll.
> I searched on the internet and found that there was an Access Violation
> error with comsvcs.dll, and the fix is to install the latest Windows
> patches, which we already did. We also increased the memory from 1 gig RAM
> to 2 gig. The program still happens.
> There is no scheduled task running at 3 AM.
> Does anybody know how to read Dr Watson log ?
> How can I fix this problem ?
> Thank you very much.
>
>

Downloading sql server 2005

Hi!
I am a beginner who downloaded Microsoft SQL server 2005 (for reporting services) to my Windows XP Professional (32-bit systems), but it asks me to install the service packs as well...

Its confusing to know which ones... So Windows XP Service Pack 2 is already installed.
I could not find Windows 2000 Service Pack 4 for my version though.
Downloaded Windows Server 2003 Service Pack 1, which requires Windows Server 2003 as well... true?

Do I also need Asp.net and IIS?
I would be very grateful for some help... to clarify which components needed.

With your setup you need to:

1. Install IIS and .net 2.0

2. Install SQL Server 2005 Developer or Standard edition. The Developer edition has all features of the Enterprise edition and can run on Windows XP.

Downloading RS

Hello,

I have SQL Server Standard installed in one of our Windows Server 2003, where should I request or download real version of Reporting Services? I do not want to install trial version to avoid re-installation in the future.

Thanks,

You need a minimum of SQL Server Developer edition for RS|||Yes, but even more than that - you have to "fill in" a form and MS will send the CD to you for a "full" production version of MSRS!
This is the only way I know of in order to get a full licensed copy of MSRS - and not have to worry about the "trial" version!
I have made numerous post about this exact topic - but the "moderator" keeps rejecting! - but -
Since it is a "lucky" Irish day -
Best Regards

Download Windows server

Hi!
I am confused ...downloaded Microsoft SQL server 2005 (for reporting services) to my Windows 2002 (32-bit systems), but it asks me to install the service packs as well...

So Windows XP Service Pack 2 is already installed.
And I need to download Windows server 2000 or 2003 R2, but where could I find a free trial version?

Do I also need Asp.net and IIS?
I would be very grateful for some help... to clarify which components needed.

Since you're wanting RS, you'll need to install SQL Server Express with Advanced Services. Here's the link:

http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx

This product is supported on XP SP2, so you won't need to do any OS upgrades.

Thanks,
Sam Lester (MSFT)

|||Thank you so much!
I get the following answer though "Your OS does nor support the Service Pack required for this SQL server release."

So my OS is 2002...
How do I check which SP required?
I did not find any .net Framework 2.0 SP.|||I managed to download SQL Server 2005 Express Edition with Advanced

Services SP1, and noticed Reporting services are available but how about "Analysis services" ?
|||

Analysis Services does not ship with any of the Express SKUs. It is part of the other SKUs (Enterprise, Standard, etc). If you want to play around with it, you can download the evaluation version found here:

http://www.microsoft.com/sql/downloads/trial-software.mspx

Thanks,
Sam Lester (MSFT)

Download image datatype to Jpegs or GIFs

Hi,

I want download photos (image datatype) from a SQL-2000 database
into windows based jpeg files (one for each record). Just wondering is
there any way of doing this without using TEXTCOPY?

Michael(mcgrathml@.gmail.com) writes:
> I want download photos (image datatype) from a SQL-2000 database
> into windows based jpeg files (one for each record). Just wondering is
> there any way of doing this without using TEXTCOPY?

There is no other program that comes with SQL 2000 for the task, but you
could write your own. Or search on Google to see if you can find anything.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||mcgrathml@.gmail.com ha scritto:
> Hi,
> I want download photos (image datatype) from a SQL-2000 database
> into windows based jpeg files (one for each record). Just wondering is
> there any way of doing this without using TEXTCOPY?
> Michael
You can write a simple .net program to read/write
images (as byte streams) on a BLOB field
of a SQL Server (or Access) database.

If you are interested I can post you the
(small and simple) code...

Bye
M.A.|||http://www.microsoft.com/technet/pr...art3/c1161.mspx

Madhivanan|||Maury,

Please email/post details of code.

Michael.

Maury wrote:

> mcgrathml@.gmail.com ha scritto:
> > Hi,
> > I want download photos (image datatype) from a SQL-2000 database
> > into windows based jpeg files (one for each record). Just wondering is
> > there any way of doing this without using TEXTCOPY?
> > Michael
> You can write a simple .net program to read/write
> images (as byte streams) on a BLOB field
> of a SQL Server (or Access) database.
> If you are interested I can post you the
> (small and simple) code...
> Bye
> M.A.sql

double-hop authentication for SSRS and ASP

I'm not an expert - but:
Yes - this is the classic double-hop scenario. I live with it everyday.
A lot depends on the Windows versions of the servers:
Windows 2000 - this requires Kerberos or the use of network service
accounts used between servers (or if the back-end service is SQL - SQL
logins).
Windows 2003 - you can use the Protocol Transition features to delegate
credentials without Kerberos.
See:
http://technet2.microsoft.com/WindowsServer/en/library/dac7ecea-7c82-43c0-847b-3a1a81454cfe1033.mspx
Difficult to help without more details, but if your using Windows 2000
servers and NTLM only, you will need to create a service account for
the Front End server to communicate with the Back End.
In a typical Web Server (A) talks to Application Server (B) that talks
to SQL Server (C) scenario...
1) Web Site (A) uses NTLM authentication and ASP(.NET) code to
impersonate <user>.
2) App Server B serves use NTLM authentication to grant access to
<user>, but is not allowed to pass these credentials to C. So either:
a) the DCOM or .NET services on B are configured to run under a
service account <_service1> which has access rights on the SQL Server
(C) and necessary Databases objects
or
b) the services on B use SQL authentication to login and access (C).
Note: If you use this approach, and you need to audit for security; you
should log accesses to C on server B - so that you know who really was
accessing C (All C sees is the same user, all the time).
If your using Windows Server 2003, you can use delagation as explained
in the links I provided earlier.
punjab_tom@.hotmail.com wrote:
> Sleepy
> can you please tell more? my english is poor
> I have had security problems with Services of Reporting; I can see
> 'user is null' even though we are using NT Authentication.
> Tom
>
|||Hi,
I have a very similar problem. I've been through a lot of articles, but
something is still missing.
This is my setup:
All servers are Windows Server 2003.
IE6 connects to IIS using basic authentication. In ASP.NET code, using Win32
LogonUser, I impersonate a domain user and then make a call to the database
server. For some reason the impersonated user is not delegated to SQL server.
This is how I configured everything so far:
Created a domain account for the aspnet worker process (edited
machine.config on IIS server and edited <processmodel>:
<processModel enable="true" userName="domain\ASPNETuser" password="xxx"/>
The ASPNETuser is set to be Trusted for Delegation on the domain controller.
The IIS server is set to be Trusted for Delegation on the domain controller.
The users that are to be impersonated are NOT set as sensitive on the domain
controller.
The connection string to SQL looks like:"server=sqlserver;initial
catalog=dbname;integrated security=SSPI".
When connecting to the database, I get "Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'".
What am I missing? How do I know the Kerberos is working.
"SleepyLab" wrote:

> Difficult to help without more details, but if your using Windows 2000
> servers and NTLM only, you will need to create a service account for
> the Front End server to communicate with the Back End.
> In a typical Web Server (A) talks to Application Server (B) that talks
> to SQL Server (C) scenario...
> 1) Web Site (A) uses NTLM authentication and ASP(.NET) code to
> impersonate <user>.
> 2) App Server B serves use NTLM authentication to grant access to
> <user>, but is not allowed to pass these credentials to C. So either:
> a) the DCOM or .NET services on B are configured to run under a
> service account <_service1> which has access rights on the SQL Server
> (C) and necessary Databases objects
> or
> b) the services on B use SQL authentication to login and access (C).
> Note: If you use this approach, and you need to audit for security; you
> should log accesses to C on server B - so that you know who really was
> accessing C (All C sees is the same user, all the time).
> If your using Windows Server 2003, you can use delagation as explained
> in the links I provided earlier.
>
> punjab_tom@.hotmail.com wrote:
>

domains and workgroups

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

domains

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

Domain user can't hit SQL Server

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

domain user belonging to multiple windows group

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

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

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

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

go

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

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

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

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

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

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

Also, the login_token output names like -

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

What are these groups?

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

|||

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

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

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

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Domain user account

New to SQL Server. Plan to install SQL Server 2005 standard edition on Windows 2k3. After searched a lot of places, still don't understand what exactly "domain user account" is. Could someone explain it to me?
1. Is this a OS account where SQL Server is running?
2. Or, is this an account under domain controller on other machine? Is this an account on DNS srver? How do I create it?
3. Or, is this an account in SQL Server?

Where is this account located? How do I manage it?

TIA.

A domain account in the Windows world is a user account in a shared security database. The current domain system is called Active Directory. It does not only store user accounts but a wide range of data used in a networking system (groups, computers, printers, software packages...). Those entities are stored on domain controllers, i.e. Windows Server with some services implementing the server side of a domain. You can have multiple domain controllers which replicate the domain data like user accounts with each other. Computers and users which are part of the domain can be authenticated by those domain controllers, so they can get authorized to execute tasks on their or other computers in the domain.

So:

1) Typically not, only if your SQL Server is running on a Windows Server which is also domain controller.

2) Yes, the account is from a domain controller, or from the security database of a domain which is hosted on its domain controllers. A DNS server plays no specific role here, except that Active Directory does need a DNS server. You create those account with Start->Programs->Administrative Templates->Active Directory Users And Computers. This management console is installed on domain contollers. You can manually install it from adminpak.msi which exists on Servers under windows\system32.

3) No. You can "import" a domain account to give it login rights and assign database roles.

The nice part about domain accounts is, that you have a centralized place for user authentication, once authenticated on a system that authentication can be used agains other computers of the same domain and be authorized to do things on those computers. In contrast, in a workgroup you would need to log on to every computer indivually, except when you manage to have your user account and password synchronized manually on all machines you want to access.

--
SvenC

|||

Thank you for your response.

I should state my situation more clearly. Three MS 2003 servers. Plan to install sql server 2005 on them. One as principle, one as mirror, and one as witness. Don't have any control on domain controller.

I can see my computers on my MS Windows Network. I guess that I only have computer account on domain controller. Is it true only the administrator on domain controller can add a domain user account? If a domain user account has been added, how do I use this account in my local 2003 server to run sql server services? Where to put domain user name and password? How do I know this domain user is in trusted domains? In order to setup mirroring, should I use this account on all three machines?

My database will only be used to accept client requests from IIS on another machine. If running sql services under domain user account is too much trouble, create same local user account on all the machine and run service under this same user account, will this work for mirroring. Do I need to create certificate?

Just try to find an easy way to setup mirroring. MS BOL is not much helpful.

|||

You can set a domain account as your service account, using the SQL Server Configuration Manager tool. The SQL Server 2005 setup program will also allow you to select a domain account as the service account. For questions on setting up database mirroring, you should however post on the Database Mirroring forum instead of this one.

Thanks
Laurentiu

Domain user account

New to SQL Server. Plan to install SQL Server 2005 standard edition on Windows 2k3. After searched a lot of places, still don't understand what exactly "domain user account" is. Could someone explain it to me?
1. Is this a OS account where SQL Server is running?
2. Or, is this an account under domain controller on other machine? Is this an account on DNS srver? How do I create it?
3. Or, is this an account in SQL Server?

Where is this account located? How do I manage it?

TIA.

A domain account in the Windows world is a user account in a shared security database. The current domain system is called Active Directory. It does not only store user accounts but a wide range of data used in a networking system (groups, computers, printers, software packages...). Those entities are stored on domain controllers, i.e. Windows Server with some services implementing the server side of a domain. You can have multiple domain controllers which replicate the domain data like user accounts with each other. Computers and users which are part of the domain can be authenticated by those domain controllers, so they can get authorized to execute tasks on their or other computers in the domain.

So:

1) Typically not, only if your SQL Server is running on a Windows Server which is also domain controller.

2) Yes, the account is from a domain controller, or from the security database of a domain which is hosted on its domain controllers. A DNS server plays no specific role here, except that Active Directory does need a DNS server. You create those account with Start->Programs->Administrative Templates->Active Directory Users And Computers. This management console is installed on domain contollers. You can manually install it from adminpak.msi which exists on Servers under windows\system32.

3) No. You can "import" a domain account to give it login rights and assign database roles.

The nice part about domain accounts is, that you have a centralized place for user authentication, once authenticated on a system that authentication can be used agains other computers of the same domain and be authorized to do things on those computers. In contrast, in a workgroup you would need to log on to every computer indivually, except when you manage to have your user account and password synchronized manually on all machines you want to access.

--
SvenC

|||

Thank you for your response.

I should state my situation more clearly. Three MS 2003 servers. Plan to install sql server 2005 on them. One as principle, one as mirror, and one as witness. Don't have any control on domain controller.

I can see my computers on my MS Windows Network. I guess that I only have computer account on domain controller. Is it true only the administrator on domain controller can add a domain user account? If a domain user account has been added, how do I use this account in my local 2003 server to run sql server services? Where to put domain user name and password? How do I know this domain user is in trusted domains? In order to setup mirroring, should I use this account on all three machines?

My database will only be used to accept client requests from IIS on another machine. If running sql services under domain user account is too much trouble, create same local user account on all the machine and run service under this same user account, will this work for mirroring. Do I need to create certificate?

Just try to find an easy way to setup mirroring. MS BOL is not much helpful.

|||

You can set a domain account as your service account, using the SQL Server Configuration Manager tool. The SQL Server 2005 setup program will also allow you to select a domain account as the service account. For questions on setting up database mirroring, you should however post on the Database Mirroring forum instead of this one.

Thanks
Laurentiu

Domain Trust - login failed: not a trusted sql connection

(Cross post from newsgroup)

Attempting to implement Windows authentication between trusted domains. . .

I have a domain trust set up between two domains connected via persistent vpn:
REMOTE and HQ

sqlserv is: HQ\SQLSERV (SQL 2000 server)

client machine is: REMOTE\MACHINE1 (SQL 2005 tools)

I sucessfully added user REMOTE\User1 to system admins on HQ\SQLSERV I can login via Remote Desktop from REMOTE\MACHINE1 to HQ\SQLSERV and open SQL2000 Query Analyzer and query databases as REMOTE\User1

When running SQL2005 SQL Server Management Server on REMOTE\MACHINE1 logged on as REMOTE\User1
I cannot connect to HQ\SQLSERV.
I get:

TITLE:
Connect to Server
Cannot connect to HQ\SQLSERV.
ADDITIONAL INFORMATION: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476
BUTTONS: OK

Any ideas? What I am trying to do should be possible. Isn't it?

Check your connection info in SSMS on REMOTE\MACHINE1
Check you have all the required DNS
Try osql -SSQLSERV -E|||

Mulhall wrote:

Check your connection info in SSMS on REMOTE\MACHINE1

fails on both named pipes and tcp/ip

Check you have all the required DNS

It appears the DNS replicates sucessfully as I have set up secondaries on both sides for the other domains.
I can ping from one side to the other.
I can assign rights from one side to the other, etc. . .

Try osql -SSQLSERV -E

same error|||

Ahhh. . .

found the problem. . .

forgot that we had moved all of our workstations into sets of ADS folders, one for general all purpose use where access via network was permitted to all authenticated users and one set that had access permissions specifically defined to only developers.

Had to give access to the remote developers group via group policy.
The easiest thing was -

Add a Local Domain Group on HQ - Remote Developers
Add REMOTE\Developers to HQ\Remote Developers
Add HQ\Remote Developers to 'Allow access from network' on the applicable gpo

Does that make sense?

Well, it works!!!

You know. . .
Somethings M$ does really really well - Active Directory/Policy Management/Name Service Administration, SQL Server!!!

Somethings they choke on - Visual Studio 6 (oh what a dog!)

At any rate, setting up this VPN and Domain trust was rather simple and straight forward!

Domain or Workgroup?

Hi,
In a 2-node SQL Server 2000 Active/Passive cluster running
under Windows 2003 Enterprise, can both computers be
member if a workgroup (i.e. "WORKGROUP") or do they "have"
to be members of a domain?
Thanks.
They HAVE to be domain members. The cluster service requires a common
security context to transfer resources and manage virtual servers.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:396e01c47f0a$cf308300$a501280a@.phx.gbl...
> Hi,
> In a 2-node SQL Server 2000 Active/Passive cluster running
> under Windows 2003 Enterprise, can both computers be
> member if a workgroup (i.e. "WORKGROUP") or do they "have"
> to be members of a domain?
> Thanks.

Domain Groups and Windows Authentication

Hi

We are planning implementation of a currently Sybase db. The users (about 3600) will be i 5 domains and we want single sign-on through trusted connections. We want to use the database roles to define different user access on databases and tables. There will be around 2000 roles. We also want to add the users directly to the database roles without having to grant each user database access.

So I thought that I could add the user groups from all domains and then add each domain user account to specified database roles. Am I right here or what? The Windows authentication will lookup or check the users kerberos ticket during logon process and allow logon.

The documentation here is weak and I assume it's a windows authentication question but wondered if any of you guys had been down the same road.

For creating the groups I have the following options:

Create a domain group and put all the usergroups from the other domains in this group

Add user groups from all other domains directly into the SQL Server.

Any recommendations here?

YOu don′t need to add the single users to the database. If you put the domain groups (which contain the users) to the server and the database principles, SQL Server will take care of doing the authentication.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

HI,

If you are adding your domain groups to your SQL Server and grant them to allow access of DB/objects then Windows Server will take care of logins to SQL Server tooo do not need to do anything separately.