IUSR Role in SQL Server Database

Is it a security risk to have the IUSR account set up as a dbo, or is it
better to give the IUSR account selected authority? I use the IUSR account
to access SQL Server for ASP web pages. The connection string uses
IUSR_<machine> with no password, but the password is stored within IIS.
Hi
Bad idea to give that user DBO. If a hacker gets though your web pages, he
can do anything to your database.
Always give users explicit permission (never to tables, but to stored
procedures), and give the least permissions you can.
Regards
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/
"WC Justice" <BJustice@.wcje.com> wrote in message
news:%W2qe.73996$6k7.73701@.bignews4.bellsouth.net. ..
> Is it a security risk to have the IUSR account set up as a dbo, or is it
> better to give the IUSR account selected authority? I use the IUSR
> account to access SQL Server for ASP web pages. The connection string
> uses IUSR_<machine> with no password, but the password is stored within
> IIS.
>

IUSR Role in SQL Server Database

Is it a security risk to have the IUSR account set up as a dbo, or is it
better to give the IUSR account selected authority? I use the IUSR account
to access SQL Server for ASP web pages. The connection string uses
IUSR_<machine> with no password, but the password is stored within IIS.Hi
Bad idea to give that user DBO. If a hacker gets though your web pages, he
can do anything to your database.
Always give users explicit permission (never to tables, but to stored
procedures), and give the least permissions you can.
Regards
--
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/
"WC Justice" <BJustice@.wcje.com> wrote in message
news:%W2qe.73996$6k7.73701@.bignews4.bellsouth.net...
> Is it a security risk to have the IUSR account set up as a dbo, or is it
> better to give the IUSR account selected authority? I use the IUSR
> account to access SQL Server for ASP web pages. The connection string
> uses IUSR_<machine> with no password, but the password is stored within
> IIS.
>

IUSER Security Risk?

We have an old ASP application that authenticates to a SQL Server 2000 database using the IUSER account.

We are exploring the possibility of moving it to a DMZ so users can access from home. The IUSER account is being used for all database activity for this application. Is there any reason to be concerned about using IUSER as opposed to sql authentication for this tool, or any other security risks I should be wary of. Mind you there may be some sensitive information contained in the database. Thanks in advance.

I would recommend either application roles or some other security method, such as a middle-tier or at least SQL Server security to access the database rather than this user. For one thing, you can't track actions to an individual this way.

This is a good reference for what you are trying to do:

http://www.windowsecurity.com/articles/Secure_Architecture_SQL_Web_Server.html

IUSER Security Risk?

We have an old ASP application that authenticates to a SQL Server 2000 database using the IUSER account.

We are exploring the possibility of moving it to a DMZ so users can access from home. The IUSER account is being used for all database activity for this application. Is there any reason to be concerned about using IUSER as opposed to sql authentication for this tool, or any other security risks I should be wary of. Mind you there may be some sensitive information contained in the database. Thanks in advance.

I would recommend either application roles or some other security method, such as a middle-tier or at least SQL Server security to access the database rather than this user. For one thing, you can't track actions to an individual this way.

This is a good reference for what you are trying to do:

http://www.windowsecurity.com/articles/Secure_Architecture_SQL_Web_Server.html

sql

Item-Level Security for Reports

Hello,

Can somebody explain the process for implementing Item-Level security for reports? My requirement is as follows:

I have some reports pertaining to a specific department, where in I deployed them to a specific folder on Reporting Services. Now I need to give exclusive read access to those reports for the users in that department. They should not be able to create any folders / new reports etc.

The steps I have followed:

1. Deployed the reports from VS 2005 to a specific folder called "TheirReports" and in this I placed the Data source also as a seperate folder.

2. I clicked on Site Settings in Report Manager and then clicked on Configure Item-Level role definitions.

3. Clicked on New Role and gave a name as ReportsBrowser and checked the options --> "View Data sources / View folders / View reports / View resources"

4. Now I went back to the specific folder and clicked on Properties -> Security -> New Role Assignment.

5. Added the necessary users binding them to ReportsBroser role.

When I'm checking from the user machine, they are able to create new folders etc.

Appreciate your ideas.

Thanks

Nobody has dealt so far with Item-Level security for reports.sql

Item vs. System Level Security

I am trying to deploy RS in a corporate environment. I have folders set up at
different organizational levels. I would like to allow certain users only
within those organization levels to create linked reports and deploy them
within their respective organizational levels. I thought that by granting
those users full item-level security they could achieve this. Unfortunately,
when I tried this it did not work.
1. Which task allows users to view report properties and create linked
reports? I'm assuming this is a system-level task.
2. Is there any way that I can assign this task to a role which restricts
the user to only exercising that authority only within a certain folder. I
obviously don't want to give the user the ability to create linked reports
site wide but only in the respective organizational folder.
3. Is there anyway to combine system-level and item-level tasks into a
single role or to customize security configurations?
Any help would be greatly appreciated.
Thanks,
TedCreating linked reports is an item level task. In report manager go to the
site settings tab and then the 'Configure item-level roledefinitions' link.
From here you can create roles as you see fit. One of the tasks you can
assign is 'Create Linked reports'. You can not combine system and
item-level task, but then again you shouldn't need to.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ted" <Ted@.discussions.microsoft.com> wrote in message
news:287C4AA6-65EC-411A-9E93-4C1CDA463AE2@.microsoft.com...
> I am trying to deploy RS in a corporate environment. I have folders set up
at
> different organizational levels. I would like to allow certain users only
> within those organization levels to create linked reports and deploy them
> within their respective organizational levels. I thought that by granting
> those users full item-level security they could achieve this.
Unfortunately,
> when I tried this it did not work.
> 1. Which task allows users to view report properties and create linked
> reports? I'm assuming this is a system-level task.
> 2. Is there any way that I can assign this task to a role which restricts
> the user to only exercising that authority only within a certain folder. I
> obviously don't want to give the user the ability to create linked reports
> site wide but only in the respective organizational folder.
> 3. Is there anyway to combine system-level and item-level tasks into a
> single role or to customize security configurations?
> Any help would be greatly appreciated.
> Thanks,
> Ted

It seems a bug of SQL Server 2005.

I do the following steps:

1:Use Manage Studio login the server with Integrated security.

2:Create a dabase named testdb;

3:Create a SQL Server login named amber ,and set it to be dbowner of testdb;

4:Create a SQL Server login named guxiaobo ,set it's default databse to be testbd,and in the testdb databse map login guxiaobo to user guxiaobo;

5:Close Manage Studio and reopen it ,this time use login amber log to the server.

6:In database testbd create a databse role role1 owned by dbo;

7:In database testdb create a schema schema1 owned by dbo;

8:Set user guxiaobo to be member of role1,and set guxiaobo's default schema to be schema1;

9:In the schema properties-schema1 dialog choose permissions,in the users or roles listview I add role1 to it.in the explicit permissions for role1 listbox,I choose select /update/insert/delete/view definition grant checkboxes.and apply the selects.

10:In the schema1 schema I create a table:create table a(a int ,b varchar(10));

11:In the dabase role properties-role1 dialog choose securables panel ,int securable listview I add the schema1.a table,and in explicit permissions for schema1.a I choose all for grant ,then apply the selects.

12:Now I use login guxiaobo to log into dabase and issue "select * from a",but got a error msg saying guxiaobo has not enough permission to select form table a.

Does anyone has found anything I missed?

And in step 11 ,I choose all the columns of table a for all permissions.|||Anybody helps ?

Issues with privileges

I am having trouble with providing the minimum security to a user. After issuing the following:

GRANT EXECUTE ON SCHEMA :: DBO TO skillsnetuser;

I test the permissions with

exec as login = 'skillsnetuser'

exec prcElmtList 1, 1, 102268

revert;

and receive this message

Msg 229, Level 14, State 5, Line 2

SELECT permission denied on object 'Org', database 'SNAccess_Dev', schema 'dbo'.

The principal that owns the dbo schema is dbo and is the principle for all procedures and tables in that schema.

What can I do to shed some light on what is causing this access problem?

Are the objects 'Org' and the procedure prcElmList defined in the same database ?|||Yes, Org is a table that prcElmList accesses|||

My first guess is that ownership chaining is broken for some reason. Can you please include the portion of the SP body that access the table? We can take a look to it and try to determine why ownership chaining is broken in this case.

I would also like to suggest migrating to one of the other mechanisms we have available on SQL Server 2005 that allow controlled access to resources via a module (i.e. SP) execution:

* EXECUTE AS – For more information you can visit Context Switching in BOL (http://msdn2.microsoft.com/en-us/library/ms188268.aspx)

* module signing: Module signing in BOL (http://msdn2.microsoft.com/en-us/library/ms345102.aspx)

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

IF NOT EXISTS (SELECT

o.OrgID

FROM

Org o

WHERE

o.OrgID = @.piOrgID

AND o.Active = 1

AND o.BeginDate < GetDate()

AND (o.EndDate > GetDate() OR o.EndDate IS NULL) )

BEGIN

RAISERROR ('The passed OrgID is invalid or inactive.' ,17 ,1)

RETURN -2 -- OrgID not valid or active

END -- IF NOT EXISTS

|||

I tried a similar scenario, and it seems to work for me, and as your sample code doen’t involve any dynamic SQL I am not sure what I may be missing. Can you give us some more information?

I would like to ask you to run the following query:

SELECT name, principal_id, schema_id

FROM sys.objects WHERE

name LIKE 'prcElmtList'

OR name LIKE 'Org'

I would also like to ask you if you are using a database in backwards compatibility mode (and if so, what is the compatibility level you are using) and if you are using any options for the stored procedure declaration (such as NOT FOR REPLICATION, EXECUTE AS, etc.), and if there are more objects involved in the chain other than the SP and the table.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

SELECT name, principal_id, schema_id

FROM sys.objects WHERE

name LIKE 'prcElmtList'

OR name LIKE 'Org'

--Results

name principal_id schema_id

Org NULL 1

prcElmtList NULL 1

--For the procedure generation script

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

WITH RECOMPILE

--General Properties of the procedure showned by the GUI

Execute as caller

Schema dbo

System object False

ANSI NULLS True

Encrypted False

For replication False

Quoted identifier True

Recompile True

--Database Options showned by the GUI

Compatibility Level SQL Server 2005 (90)

All Miscellaneous Settings are False except Parameterization which is Simple

|||

At this junction I have added with execute as owner to the procedures,

this provides the functionality I was expecting to be present....

|||

Sorry for the late response, I have been trying to repro why ownership chaining didn’t worked for this particular scenario, I tried creating a small repro similar to the one you described, but so far I have no luck (my repro test is able to access the table as expected).

If you consider it is a bug in the product, I would recommend following the instructions described on the “Tips on using SQL Server Security forums” page to report it (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=286374&SiteID=1). We will probably need more details than the ones we have right now to continue the investigation.

I am glad you were able to find a solution for the problem. Please, let us know if you have any further questions or feedback; we will greatly appreciate your feedback on the EXECUTE AS feature.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Issue Resolved - Interaction between SQL Server and Access

I'm implementing row-level security in a SQL Server database that uses Microsoft Access for the front end. I'm using a UDF (a view behaves the same way) to restrict access to specific rows of a base table based on membership in a role. According to the reading I've done, if the base table has DENY ALL permissions for the role, and the UDF has GRANT ALL, members of the role should be able to update records in the base table via the UDF, without having direct access to the base table. However, I find that unless I grant appropriate permissions on the base table, the user is unable to update the table via the UDF.

Is this expected behavior? Nothing I've read suggests I should have to grant permissions on the columns of the base table.

Yes, that is expected behavior.

Permissions in SQL Server have three values: GRANT, DENY, or 'unsaid'.

If you have been GRANTed permission for a table, obviously you have permission.

If your permission is 'unsaid', then you 'may' still have permission due to permission having been granted to another role that includes you.

But IF you have been explicited DENY(ied), that 'trumps' all.

Think of it this way.

Children have a knack of knowing how to 'scope out' their parents. Perhaps a son wants to go out with friends. He may approach Mom and 'feel her out' to find out if she 'might' say yes WITHOUT directly asking her. He knows that if he asks her and she says 'No', his plans are shot because he cannot then go and ask Dad (DENY). So he will attempt to find out if it is 'safe' to ask her. If it seems safe, he will ask and he's 'home free' (GRANT).

However, if he feels that she would probably say No, then without having asked, he is now free to ask Dad. So Mom was 'unsaid', if permission can be had by another route, it will work for him.

So anytime permission is an explicit DENY, there is no route around it.

Often, in a strong SQL Server security model, TABLE permissions are left 'unsaid', and access is GRANTed through VIEWS, Functions, and Stored Procedures. Users are also added to the db_DenyDataReader and db_DenyDataWriter roles to prohibit them having direct table access.

|||

Thanks for responding.

Ah, yes, that's what I thought. But if I leave the permissions on the base table "unsaid", and grant all on the UDF, Access tells me that the recordset is not updatable (maybe because it can't "see" the PK column?). So I'm back to having to grant permissions in the base table, which is unacceptable.

I read through the good whitepaper on row-level security by Rask, Rubin and Neumann. The architecture they propose is great, but overkill for what I need to do. Nevertheless, I set up a test using their methodology: DENY ALL on the base table, GRANT ALL on a view, and put an INSTEAD OF trigger on the view to verify appropriate access and perform an update. But because Access thinks the recordset isn't updatable, the trigger never fires. If I grant SELECT permissions to just the PK column of the base table, Access thinks the recordset is updatable, but I still can't get the trigger to fire because Access wants at least SELECT permissions on the other base table columns before it will even try to perform the update.

|||

I would suggest the following topics from BOL:

· CREATE VIEW (http://msdn2.microsoft.com/en-us/library/ms187956.aspx), got to the section Updatable Views

· Modifying Data Through a View (http://msdn2.microsoft.com/en-us/library/ms180800.aspx)

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

In addition to Raul's suggestions, I offer the following insight.

Access will allow you to UPDATE or DELETE a row without the table having a primary key (or some unique identifier.)

SQL Server does NOT allow UPDATES or DELETES unless there is a unambiguous way to be certain what row is being addressed. If the VIEW does not include a PK, or unique identifier, it would not be updatable.

|||Thanks for the references, I'll read through them and see where they lead. I note that these are from SQL Server 2005 BOL, and the server that must host the application I'm working with is SQL Server 2000 SP4. I'm just wondering whether you know whether the support for updatable views changed between SQL 2000 and 2005?|||

As far as I understand, updatable views should be supported in SQL Server 2000 SP4, but I am not 100% sure if all the documentation in the links I included may apply to SQL Server 2000 SP4 as well.

I would recommend trying to find the same topic in BOL fro SQL Server 2000 and giving it a try; if you have any further questions please let us know, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks to both you and Arnie for responding to this inquiry.

The problem turns out to have been an interaction between SQL Server and Access. In order for Access to update a SQL Server view, the view must be declared the WITH VIEW_METADATA option. If designing the view in Access, this is accomplished by checking the view option "Update using view rules" on the properties page. Once I did this, I was able to DENY ALL on the base table and GRANT ALL on the view, and the view was updatable via Access with appropriate security.

I ran a SQL Profiler trace to see what Access was sending to SQL Server when the option was properly set--it showed that the UPDATE statement Access generated was against the view, not the base tables. Without using the VIEW_METADATA option, Access does not consider the recordset updatable (hence my original question), so it doesn't generate an UPDATE statement. So I couldn't run a trace to see what happens in that case. I expect that if I could, the UPDATE would be going against the base table rather than the view.

Again, thanks for your help.

Issue in Custom Security Extension

Hi,

The custom security extension generates the following error, when try to access Report Manager. The specifications are as follows.
1. Its 64-bit SQL Server (Reporting Services) and installed as default instance.
2. The Report Server is accessible programmatically. I can publish, view reports without problems, but Report Manager gives an error.
3. Another instance (named instance) of Reporting Services with the same custom security extension on same server is working fine. Report Manager opens up without problems.
4. Both the instances working fine since 3 installation and configuration (3 months back). Now, only the Report Manager of the default instance is not openeing up.
ReportServerWebApp__08_22_2006_08_52_20.log
-
w3wp!ui!1!8/22/2006-08:52:56:: i INFO: Overwriting existing cookie
w3wp!ui!1!8/22/2006-08:52:57:: e ERROR: The report server is not responding. Verify that the report server is running and can be accessed from this computer.
w3wp!ui!1!8/22/2006-08:52:57:: e ERROR: HTTP status code --> 500
-Details--
Microsoft.ReportingServices.UI.Global+RSWebServiceWrapper+CantCommunicateWithReportServerException: The report server is not responding. Verify that the report server is running and can be accessed from this computer.

at Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods()

at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname)

at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()

at Microsoft.ReportingServices.UI.Global.SecureAllAPI()

at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level)

at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args)

at System.EventHandler.Invoke(Object sender, EventArgs e)

at System.Web.UI.Control.OnInit(EventArgs e)

at System.Web.UI.Page.OnInit(EventArgs e)

at System.Web.UI.Control.InitRecursive(Control namingContainer)

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
w3wp!ui!1!8/22/2006-08:52:57:: i INFO: Overwriting existing cookie
w3wp!ui!1!8/22/2006-08:52:58:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)

The service is running fine. Any suggestions/pointers would be greatly appreciated.

THanks in advance

Is there anyway to trace and find the error. Its URGENT!!! Can anyone suggest ?|||

Does anyone has the solution of above problem ? It's urgent.

|||

The error indicates report manager has problem talking to report server soap endpoint. Please check RSWebApplication.config under report manager to make sure either ReportServerUrl or ReportServerVirtualDirectory contains the correct value.

If you think you have the right value in config file, you can install a http tracer between report manager and report server so you can really see the traffic between them.

Issue in Custom Security Extension

Hi,

The custom security extension generates the following error, when try to access Report Manager. The specifications are as follows.
1. Its 64-bit SQL Server (Reporting Services) and installed as default instance.
2. The Report Server is accessible programmatically. I can publish, view reports without problems, but Report Manager gives an error.
3. Another instance (named instance) of Reporting Services with the same custom security extension on same server is working fine. Report Manager opens up without problems.
4. Both the instances working fine since 3 installation and configuration (3 months back). Now, only the Report Manager of the default instance is not openeing up.
ReportServerWebApp__08_22_2006_08_52_20.log
-
w3wp!ui!1!8/22/2006-08:52:56:: i INFO: Overwriting existing cookie
w3wp!ui!1!8/22/2006-08:52:57:: e ERROR: The report server is not responding. Verify that the report server is running and can be accessed from this computer.
w3wp!ui!1!8/22/2006-08:52:57:: e ERROR: HTTP status code --> 500
-Details--
Microsoft.ReportingServices.UI.Global+RSWebServiceWrapper+CantCommunicateWithReportServerException: The report server is not responding. Verify that the report server is running and can be accessed from this computer.

at Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods()

at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname)

at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()

at Microsoft.ReportingServices.UI.Global.SecureAllAPI()

at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level)

at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args)

at System.EventHandler.Invoke(Object sender, EventArgs e)

at System.Web.UI.Control.OnInit(EventArgs e)

at System.Web.UI.Page.OnInit(EventArgs e)

at System.Web.UI.Control.InitRecursive(Control namingContainer)

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
w3wp!ui!1!8/22/2006-08:52:57:: i INFO: Overwriting existing cookie
w3wp!ui!1!8/22/2006-08:52:58:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)

The service is running fine. Any suggestions/pointers would be greatly appreciated.

THanks in advance

Is there anyway to trace and find the error. Its URGENT!!! Can anyone suggest ?|||

Does anyone has the solution of above problem ? It's urgent.

|||

The error indicates report manager has problem talking to report server soap endpoint. Please check RSWebApplication.config under report manager to make sure either ReportServerUrl or ReportServerVirtualDirectory contains the correct value.

If you think you have the right value in config file, you can install a http tracer between report manager and report server so you can really see the traffic between them.