MSSQ server service (widnows service) account configuration --

Hi Folks,
We have some inhouse applications developed using microsoft technologies
like C#.net,asp.net and sql server 2000 and we have third party applicatio
ns
using sql server 2000.
In all our sql servers we configured as below.
we created domain level user accounts(service accounts) like SA_Server1.
we made that SA_server1 as member of local administrator group on the
server where SQLserver2000 software got installed.
we are using SA_server1 account to run mssqlserver service (windows
service).
All our proudction sql server are windows clusters with 2 nodes.
we have mixed mode authentication. And all users in Builtin/Administrators
group are sql users.
Using that domain level service account (SA_Server1) credentials,we can
login to that particualr sql server locally to check everything is working
fine.
Everything is working fine.
Recently Sarbanes-Oxley Audit conducted in our company.
One of the questions they asked is as given below.
1.why Interactive log-in is turned ON for that mssqlservice accounts? (for
SA_Server1) ?
They don't want any one to login to that server locally using that domain
account credentials.
2.Why builtin/administrators are part of sql users?
How can we prevent anyone logging in to that sql server locally using that
service account credentials?
we will be giving that credentials for the 2,3 administrative people only.
But we don't want them also to login to that server locally using service
account credentials.
They should login to that server using their own windows accounts.
How can we turn OFF that interactive log-on?
is it in group policy or local policy or Active directory member profile?
What are the best security practices for sql server 2000 configuration
mainly with service accounts?
Any kind of help is greatly appreciated.
--KumarDeny Logon locally right to the account used to start sql server services is
recommended to prevent someone from using sql server service account to logi
n
to the sql server.
Hope this hepls
"Kumar" wrote:

> Hi Folks,
> We have some inhouse applications developed using microsoft technologies
> like C#.net,asp.net and sql server 2000 and we have third party applicat
ions
> using sql server 2000.
> In all our sql servers we configured as below.
> we created domain level user accounts(service accounts) like SA_Server1.
> we made that SA_server1 as member of local administrator group on the
> server where SQLserver2000 software got installed.
> we are using SA_server1 account to run mssqlserver service (windows
> service).
> All our proudction sql server are windows clusters with 2 nodes.
> we have mixed mode authentication. And all users in Builtin/Administrators
> group are sql users.
> Using that domain level service account (SA_Server1) credentials,we can
> login to that particualr sql server locally to check everything is working
> fine.
> Everything is working fine.
>
> Recently Sarbanes-Oxley Audit conducted in our company.
> One of the questions they asked is as given below.
> 1.why Interactive log-in is turned ON for that mssqlservice accounts? (fo
r
> SA_Server1) ?
> They don't want any one to login to that server locally using that doma
in
> account credentials.
> 2.Why builtin/administrators are part of sql users?
>
> How can we prevent anyone logging in to that sql server locally using that
> service account credentials?
> we will be giving that credentials for the 2,3 administrative people only
.
> But we don't want them also to login to that server locally using service
> account credentials.
> They should login to that server using their own windows accounts.
>
> How can we turn OFF that interactive log-on?
> is it in group policy or local policy or Active directory member profile
?
>
> What are the best security practices for sql server 2000 configuration
> mainly with service accounts?
>
>
> Any kind of help is greatly appreciated.
>
> --Kumar
>

MSSQ server service (widnows service) account configuration --

Hi Folks,
We have some inhouse applications developed using microsoft technologies
like C#.net,asp.net and sql server 2000 and we have third party applications
using sql server 2000.
In all our sql servers we configured as below.
we created domain level user accounts(service accounts) like SA_Server1.
we made that SA_server1 as member of local administrator group on the
server where SQLserver2000 software got installed.
we are using SA_server1 account to run mssqlserver service (windows
service).
All our proudction sql server are windows clusters with 2 nodes.
we have mixed mode authentication. And all users in Builtin/Administrators
group are sql users.
Using that domain level service account (SA_Server1) credentials,we can
login to that particualr sql server locally to check everything is working
fine.
Everything is working fine.
Recently Sarbanes-Oxley Audit conducted in our company.
One of the questions they asked is as given below.
1.why Interactive log-in is turned ON for that mssqlservice accounts? (for
SA_Server1) ?
They don't want any one to login to that server locally using that domain
account credentials.
2.Why builtin/administrators are part of sql users?
How can we prevent anyone logging in to that sql server locally using that
service account credentials?
we will be giving that credentials for the 2,3 administrative people only.
But we don't want them also to login to that server locally using service
account credentials.
They should login to that server using their own windows accounts.
How can we turn OFF that interactive log-on?
is it in group policy or local policy or Active directory member profile?
What are the best security practices for sql server 2000 configuration
mainly with service accounts?
Any kind of help is greatly appreciated.
--KumarDeny Logon locally right to the account used to start sql server services is
recommended to prevent someone from using sql server service account to login
to the sql server.
Hope this hepls
"Kumar" wrote:
> Hi Folks,
> We have some inhouse applications developed using microsoft technologies
> like C#.net,asp.net and sql server 2000 and we have third party applications
> using sql server 2000.
> In all our sql servers we configured as below.
> we created domain level user accounts(service accounts) like SA_Server1.
> we made that SA_server1 as member of local administrator group on the
> server where SQLserver2000 software got installed.
> we are using SA_server1 account to run mssqlserver service (windows
> service).
> All our proudction sql server are windows clusters with 2 nodes.
> we have mixed mode authentication. And all users in Builtin/Administrators
> group are sql users.
> Using that domain level service account (SA_Server1) credentials,we can
> login to that particualr sql server locally to check everything is working
> fine.
> Everything is working fine.
>
> Recently Sarbanes-Oxley Audit conducted in our company.
> One of the questions they asked is as given below.
> 1.why Interactive log-in is turned ON for that mssqlservice accounts? (for
> SA_Server1) ?
> They don't want any one to login to that server locally using that domain
> account credentials.
> 2.Why builtin/administrators are part of sql users?
>
> How can we prevent anyone logging in to that sql server locally using that
> service account credentials?
> we will be giving that credentials for the 2,3 administrative people only.
> But we don't want them also to login to that server locally using service
> account credentials.
> They should login to that server using their own windows accounts.
>
> How can we turn OFF that interactive log-on?
> is it in group policy or local policy or Active directory member profile?
>
> What are the best security practices for sql server 2000 configuration
> mainly with service accounts?
>
>
> Any kind of help is greatly appreciated.
>
> --Kumar
>

msmdpump, how does it work?

I am using AS2000, i have msmdpump setup properly and its working fine, but when i look at the code of msolap.asp i was confused as to how is the mdx query passed to msmdpump.dll as i couldn't see any parameters being passed to msmdpump, i could just find

//no parameter passed here

pump.ReadData

So how does msmdpump know as to which mdx query it has to process?. I badly need to find out which mdx query is the msmdpump processing ,or which dimensions it is querying.

I took a dump of the data being posted to the msolap.asp page from an excel client by writing an isapi filter and writing the data on the SF_NOTIFY_READ_RAW_DATA event,here it is

--


R E Q U E S T = H ; S T A T E = 0 ; T Y P E = b ; N A M E = FoodMart 2000 ; V E R = 2 4 8 ; L A S T = N ; T Y P E = m ; N A M E = Sales; V E R = 4 4 0 ; L A S T = Y ; D V E R = 3 8 8 ; C V E R = 0 ; POST /msolap.asp HTTP/1.1

User-Agent: Light DCube

Host: localhost:8080

Content-Length: 268

Connection: Keep-Alive

Cache-Control: no-cache

Cookie: ASPSESSIONIDQASQBQST=BOFBOCMCIMPOFJEECELBGBJD


R E Q U E S T = H ; S T A T E = 0 ; T Y P E = b ; N A M E =FoodMart 2000 ; V E R = 2 4 8 ; L A S T = N ; T Y P E = m ; N A M E = HR ; V E R = 4 4 1 ; L A S T = Y ; D V E R = 4 0 4 ; C V E R = 0 ; POST /msolap.asp HTTP/1.1

User-Agent: Light DCube

Host: localhost:8080

Content-Length: 252

Connection: Keep-Alive

Cache-Control: no-cache

Cookie: ASPSESSIONIDQASQBQST=BOFBOCMCIMPOFJEECELBGBJD

I couldn't make anything out of it though

I also tried referencing msmdpump.dll to a .net project and i could see functions like

OnStartPage(object piUnk) .

looked to me as if piUnk is a reference to some asp com object interface, but don't know for sure.

Any help is appreceiated

In AS2000, the MDX query is not passed from the client to the server. Client code, i.e. msolap80.dll does all the parsing etc. The communication with server happens on much lower level.|||

Thanks for the reply , so is there anyway to know which mdx query is being passed to the as2000 server to process, or if that's not possible, then is it possible to know which dimension is the client trying to access.

Actually the problem is that we have as2000 server in domain A ,the webserver is in domain B and the users are in domain C.

Now B trusts C, A trusts B. We want to give users in C direct permission to the cubes so we have setup msmdpump. But the problem is that we want the users in C to have access to only a particular value of a dimension ,say dimRestricted, and since A doesn't know the users in C, we can't directly add users in C to the AS2000 client roles. So we have created users in B and have added them to the roles in AS2000 server,one for each value of dimRestricted(the number of entries in dimRestricted is constant and is less then 10).When a client from C tries to access the cubes we check whether he has access to the cube and depending on the dimension of dimRestricted which he is trying to access we impersonate the corresponding user in domain B.

But for that to happen we need to find out the value of dimRestricted dimension the client is trying to access.

Any clues or hints are well appreciated.

|||I suggest that you create a mapping between users in domain C and users in domain B. This way, when user from C is authenticated - you will be able to map him to user in domain B. The mapping will work purely on user per user basis, and won't need to know anything about cubes or dimensions.|||

Thanks for the quick reply,

That's pretty much what we are doing, so what we have is that we have AD groups in C each of which is supposed to have access to a particular value of dimension dimRestricted and a corresponding user in Domain B to which we give access to that value of dimRestricted dimension.

So suppose we have AD group named C1 in C , which is supposed to have access to value "panasonic" in dimension dimRestricted, so there is a corresponding user B1

in B which only has access to panasonic value in dimRestricted.

Similarly we have AD group named C2 in C , which is supposed to have access to value "samsong" in dimension dimRestricted, so there is a corresponding user B2 in B which only has access to samsong value in dimRestricted.

But now there can be a special user who might be both in C1 and C2, the code just checks that if user belongs to C1 then impersonate to B1 ,if C2 then B2. Now if he requests to see Measures for dimension "panasonic" , he would be impersonated to B1 and everything would be fine, but what if he tries to see for "samsong", our code does't knows which dimension he is trying to access and would again impersonate to B1 and he would not get any data whereas he should.

So we need to know which value of dimRestricted he is trying to access.

Thanks for the help.

|||My suggestion was that you have true one-to-one mapping between users in C and in B. If you have user C3 which should belong to two different roles, you need to create corresponding user B3, who will belong to these two roles, and then when you detect C3, you impersonate B3. I.e. you map user to user, not group to user. There is a management overhead with this solution, but this is the only way you can get it working with AS2000.|||

Thanks for the reply,

Yes that's what we are planning to do, and its specifically for this huge number of users that we might have to create in domain B(for which we may not even get permission) that i was looking for finding some way out of finding the dimension that the client is accessing .

Thanks anyways!.

MSDTC error

Hi guys,
Im new to SQL Server development in general...Well, I've been trying to
work on an existing application that uses both ASP and SQL Server that
happened to be raising an error that is logged on the Event Viewer 15
minutes after closing the browser. Here it is:
Event Type: Information
Event Source: MSDTC
Event Category: (3)
Event ID: 4156
Date: 09/08/2005
Time: 12:56:28 PM
User: N/A
Description:
String message: Session idle timeout over, tearing down the session.
As it looks, it seems like a uncleared session state upon exiting the
ASP page. I then tried using Session.Contents.RemoveAll and
Session.Abandon methods but still got no luck on the ASP side...Is there
anyway this Informational Message won't get logged into the Event
Viewer? I dont really know if its ASP or SQL problem! hehe...
Your help will greatly be appreciated. Thanks!
*** Sent via Developersdex http://www.examnotes.net ***Hope this helps.
Dan Guzman
SQL Server MVP
"jules remoreras" <losevilla@.yahoo.com> wrote in message
news:uQYosRRvFHA.2504@.tk2msftngp13.phx.gbl...
> Hi guys,
> Im new to SQL Server development in general...Well, I've been trying to
> work on an existing application that uses both ASP and SQL Server that
> happened to be raising an error that is logged on the Event Viewer 15
> minutes after closing the browser. Here it is:
> Event Type: Information
> Event Source: MSDTC
> Event Category: (3)
> Event ID: 4156
> Date: 09/08/2005
> Time: 12:56:28 PM
> User: N/A
> Description:
> String message: Session idle timeout over, tearing down the session.
> As it looks, it seems like a uncleared session state upon exiting the
> ASP page. I then tried using Session.Contents.RemoveAll and
> Session.Abandon methods but still got no luck on the ASP side...Is there
> anyway this Informational Message won't get logged into the Event
> Viewer? I dont really know if its ASP or SQL problem! hehe...
> Your help will greatly be appreciated. Thanks!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Check out http://support.microsoft.com/defaul...kb;en-us;290334 for
more information on the error. AFAIK, the message can't be suppressed.
Hope this helps.
Dan Guzman
SQL Server MVP
"jules remoreras" <losevilla@.yahoo.com> wrote in message
news:uQYosRRvFHA.2504@.tk2msftngp13.phx.gbl...
> Hi guys,
> Im new to SQL Server development in general...Well, I've been trying to
> work on an existing application that uses both ASP and SQL Server that
> happened to be raising an error that is logged on the Event Viewer 15
> minutes after closing the browser. Here it is:
> Event Type: Information
> Event Source: MSDTC
> Event Category: (3)
> Event ID: 4156
> Date: 09/08/2005
> Time: 12:56:28 PM
> User: N/A
> Description:
> String message: Session idle timeout over, tearing down the session.
> As it looks, it seems like a uncleared session state upon exiting the
> ASP page. I then tried using Session.Contents.RemoveAll and
> Session.Abandon methods but still got no luck on the ASP side...Is there
> anyway this Informational Message won't get logged into the Event
> Viewer? I dont really know if its ASP or SQL problem! hehe...
> Your help will greatly be appreciated. Thanks!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Session is holding an object it should not hold. If it is database related,
it is likely a connection opened when a session starts that is never killed.
It is not necessarily SQL Server related.
I would post in the ASP group:
microsoft.public.inetserver.asp.general
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"jules remoreras" wrote:

> Hi guys,
> Im new to SQL Server development in general...Well, I've been trying to
> work on an existing application that uses both ASP and SQL Server that
> happened to be raising an error that is logged on the Event Viewer 15
> minutes after closing the browser. Here it is:
> Event Type: Information
> Event Source: MSDTC
> Event Category: (3)
> Event ID: 4156
> Date: 09/08/2005
> Time: 12:56:28 PM
> User: N/A
> Description:
> String message: Session idle timeout over, tearing down the session.
> As it looks, it seems like a uncleared session state upon exiting the
> ASP page. I then tried using Session.Contents.RemoveAll and
> Session.Abandon methods but still got no luck on the ASP side...Is there
> anyway this Informational Message won't get logged into the Event
> Viewer? I dont really know if its ASP or SQL problem! hehe...
> Your help will greatly be appreciated. Thanks!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Gee...thanks to both of you (Dan and Cowboy) for the input!!! :) At
least the input/link you posted somehow enlighten my heavily burden
mind. hehehe. Thanks again!
*** Sent via Developersdex http://www.examnotes.net ***