Bastion Host running SQL server 2000

I'm experimenting with a bastion host running SQL server. I have
implemented the SQL server bastion inf from the W2K3 security guide and i
have an issue logging in with a member of the local administrators group. I
guess the lookup of group membership is somehow disabled. Does anyone know
the setting that allows this?
Thank youI'm not sure what you mean by:
"I guess the lookup of group membership is somehow disabled. Does anyone
know
the setting that allows this?"
The account that starts the MSSQLServer doen't need to be an admin account.
What account are you logging into the machine with?
What NT groups does it belong to?
Are you referring to Group Membership contained in Logins?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks for your reply,
The local admin on the server (W2K3) does not have an SQL login individually
but does have one due to it's membership of the BUILTIN\ADMINISTRATORS
group. Loging in to the server does not grant me access to SQL server (EM,
QA). When I log in with a local account created as the service account for
SQL server and that is a member of the BUILTIN\ADMINISTRATORS and also has
an individual mamping to an SQL server login works just fine. So somehow
windows can't figure out and tell SQL server that the local admin account is
a member of the BUILTIN\ADMINISTRATORS group. The only problem I have with
this reasoning is that if Windows can't tell SQL server that, how come I
knows it's the local admin for the machine? Purely on the SID so that it
doesn't even bother looking up the group memberships ...? The error I get
is as follows:
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL
erver connection."
The user is 'null', so that means that Windows could not pass the
authentication through and the failure is occurring in windows not SQL
Server.
So my gues is that their is an issue with the Security Acount Mananger
Service or another security related service or a registry setting in the
.inf file of the W2K3 security guide. I've noticed that some user right
assignments that are neccessary for SQL server to work had to be enabled
(log on as a service etc ...) but I'm still looking for a solution for this
one. Funny thing is, we normally would remove the BUILTIN\ADMINISTRATORS
from SQL server but I first want to figure out the reason of this behaviour.
Best regards
Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:ty$5a2nvDHA.2332@.cpmsftngxa07.phx.gbl...

quote:

---

> I'm not sure what you mean by:
> "I guess the lookup of group membership is somehow disabled. Does anyone
> know
> the setting that allows this?"
> The account that starts the MSSQLServer doen't need to be an admin

---

account.

quote:

---

> What account are you logging into the machine with?
> What NT groups does it belong to?
> Are you referring to Group Membership contained in Logins?
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

---

|||Hi John,
///
-from prev thread-
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL
erver connection."
The user is 'null', so that means that Windows could not pass the
authentication through and the failure is occurring in windows not SQL
Server.
///
Exactly. What I would check first to see if the client has any aliases
defined. You can review this using
the Client Network Utility or check the local registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Client\ConnectTo
You should have an entry that says; DSQUERY: REG_SZ: DBNETLIB
If there are any others, remove them and retest.
If that still doesn't work, try forcing a Named Pipe connection, by
creating one alias specifying Named Pipes.
Hope this helps
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Basic Server Roles (security) question

I want to add a consulting firm of ours to have full access to a
database on my SQL 2000 SP3 server (so DBOWNER is ok with me).
But I also need them to be able to schedule a job against that database
AND create various DB maintenance plans. I've scoured the BOL but
nothing I can find talks about this kind of granularity in security.
Is there a role that will allow this?
Many thanks.cy
Are you member of sysadmin server role as well?
"cy" <cyrus.kapadia@.us.pm.com> wrote in message
news:1105036907.387257.286720@.f14g2000cwb.googlegroups.com...
> I want to add a consulting firm of ours to have full access to a
> database on my SQL 2000 SP3 server (so DBOWNER is ok with me).
>
> But I also need them to be able to schedule a job against that database
> AND create various DB maintenance plans. I've scoured the BOL but
> nothing I can find talks about this kind of granularity in security.
> Is there a role that will allow this?
>
> Many thanks.
>|||Yes, I am top dog on the server - I have all keys to all kingdoms. But
they aren't, of course.
Cy.
Uri Dimant wrote:[vbcol=seagreen]
> cy
> Are you member of sysadmin server role as well?
>
> "cy" <cyrus.kapadia@.us.pm.com> wrote in message
> news:1105036907.387257.286720@.f14g2000cwb.googlegroups.com...
database[vbcol=seagreen]
security.[vbcol=seagreen]

Basic Security Steps

Hi Folks,
Even I know all the different anserwers for the questions,
still I wanted the best one for this query. As I was being
asked in an interview recently...
Q1: What would be the TOP TEN Steps You would do to Secure
SQL Server 2000 Database.
Thanks
Dan !They may have thought of that question from this page -
http://www.microsoft.com/technet/tr...
t/sql2ksrg.asp
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:220701c3e084$eb6d7570$a501280a@.phx.gbl...

quote:

---

> Hi Folks,
> Even I know all the different anserwers for the questions,
> still I wanted the best one for this query. As I was being
> asked in an interview recently...
> Q1: What would be the TOP TEN Steps You would do to Secure
> SQL Server 2000 Database.
> Thanks
> Dan !

---

Basic security setup

Hi there
Our company has just started using RS and I would like to
know how to set up basic security.
Our setup:
We have RS installed on one of our SQL servers. Our
Analysis department creates reports on their PC's (with
Reporing Services & Visual Studio.net installed on their
local PC). They will then deploy the reports to our RS
server and the Marketing department will access reports
through the RS web interface.
What I am struggling with:
I cannot figure out how to let users see reports without
giving them adminitrator rights on the RS server. I have
tried giving them access to the reportserver database -
this gives them access to the reportserver webpage, but
they cannot see any reports (I gave them access to
specific reports in RS manager).
I'm sure it's something stupid that I'm not doing, but
cannot figure out what it is. Could someone please help
me.
Thanks,
TheresaIf you are a 'Content Manager' on the report server (by default all local
admins are content managers) you can set permission on RS objects. Go to
the object in question, on the property tab there is a security sub tab
which will allow you to give users access rights. You most likely want to
use the Browser role but you will have to investigate to see what fits your
needs.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Theresa dP" <theresa@.discussions.microsoft.com> wrote in message
news:43b401c49035$39843a90$a301280a@.phx.gbl...
> Hi there
> Our company has just started using RS and I would like to
> know how to set up basic security.
> Our setup:
> We have RS installed on one of our SQL servers. Our
> Analysis department creates reports on their PC's (with
> Reporing Services & Visual Studio.net installed on their
> local PC). They will then deploy the reports to our RS
> server and the Marketing department will access reports
> through the RS web interface.
> What I am struggling with:
> I cannot figure out how to let users see reports without
> giving them adminitrator rights on the RS server. I have
> tried giving them access to the reportserver database -
> this gives them access to the reportserver webpage, but
> they cannot see any reports (I gave them access to
> specific reports in RS manager).
> I'm sure it's something stupid that I'm not doing, but
> cannot figure out what it is. Could someone please help
> me.
> Thanks,
> Theresa

Basic security questions

Hi,

I am new to SQL 2005, can someone give me some details instructions about how to do below two tasks:

1. All my developers are in a window domain user group, I need to grant dbo privileges to that domain group so then can do the their development work. The rule is all objects they create need to be owned by dbo not by there ID.( I can’t do it because I got “ The “Deafult_Schema clause cannot be used with a windows group”)
2. Same as above but this time they only need select permission on tables nothing else.

Many thanks.

PC

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=678115&SiteID=1 and the thread referenced there.

Thanks
Laurentiu

Basic Security Question for remote app

I'm designing a C# winforms app that will be used by 500 customers/companies. The customers will use the app to edit data for their company on the remote SQL Server. What's the best way to manage security in terms of logins/passwords? Should each user have a different login? (probably not) Or should the application have it's own login/password that is encrypted and the user never knows what the login/password is?

Also, is it necessary to use Web Services for this application? Any other thoughts/comments are very welcome.

If there are 500 DIFFERENT companies, would you want a user for companyA to be able to change information for CompanyB?

If not, then using a common login/permission set is NOT a good idea and individual logins seem necessary. It seems like the question then becomes "How to mangage a large number of logins/permissions?"

Perhaps more details are would be useful to fully understand your scenario...

|||Here's some more detail:

Company Table
CompanyId
CompanyName

ProductTable
ProductId
CompanyId
ProductName
Price

Then, we ship out applications to all the different companies.
The application should know what their CompanyId is. In the C# application, there will be a grid that displays all of the Products for their CompanyId and allows the user to update those products (for example, change the Price for a given product).

At this point, I'm just kind of at a loss for the best way to design this in terms of SQL Server Security and logins/passwords, etc. Hope this is not too vague.
|||

This will prove to be an interesting challenge. I can already imagine some interprising soul for a company 'exploring' the products for other companies -and who knows, even changing prices...

The 'safest' way to control data access (keeping each user to his/her own company) is by knowing what user is attempting data access on each query execution. Without a company specific login (or user specific login), you will have a difficult (perhaps even impossible) time keeping users out of each others data.

Yes, on the surface, the application can appear to take care of it. But are you willing to 'guarantee' all companies that their data is safe from prying eyes and safe from accidental or intentional alteration.

The database 'should' know, with certainity, who (user or company) is accessing data. I think that you have no option but to assign individual logins to each company/user (all users for a company 'could' use the same login).

You will need a process to manage Logins, adding users, resetting lost passwords, disabling or removing users. And a way to map a login to a CompanyID. Once someone logins in, the CompanyID value is supplied to be used in WHERE criteria. I wouldn't accept a CompanyID from the application.

Personally, from the data professional perspective, I wouldn't trust my career to expecting the application to control all access.

Basic Security

I am trying to understand the basic security that RS uses.
During install, a userid is used to install/create the databases. That
userid is then tied to the basic services, like scheduling etc...
Is this correct?
Are all services using that first-supplied ID?
After that when users run a report with a connection that has the
credentials stored, are those the credentials used for the database
access?
I am currently using RS and have 2 shared connections (one to live dB
and one to Test dB), both with stored credentials. Is is safe to assume
that any report activity using that connection will show that userid?
I just want to make sure I can track down problems when they arise.
Thanks.The credentials you supply when you install RS are completely seperate from
the connections you use in your reports. They are only used so that the
report server can talk to the reportserver (metadata) database. A report
that is using stored credentials will use those credentials independent of
the setup credentials.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Geddes" <john_g@.alamode.com> wrote in message
news:eX2q$NR7EHA.4040@.TK2MSFTNGP14.phx.gbl...
>I am trying to understand the basic security that RS uses.
> During install, a userid is used to install/create the databases. That
> userid is then tied to the basic services, like scheduling etc...
> Is this correct?
> Are all services using that first-supplied ID?
> After that when users run a report with a connection that has the
> credentials stored, are those the credentials used for the database
> access?
>
> I am currently using RS and have 2 shared connections (one to live dB and
> one to Test dB), both with stored credentials. Is is safe to assume that
> any report activity using that connection will show that userid?
> I just want to make sure I can track down problems when they arise.
> Thanks.
>