Chris McKenzie's Blog

I have implemented row-based security via checking whether a user is a member of a role that has permission to see certain rows that are flagged as secure. The implementation consists of checking if the user is a member of the appropriate role inside the stored procedure that serves the data via the IS_MEMBER() function.  If the user is a member of the secure role, then the procedure returns the appropriate rows.  If not, they can still use the same procedure to see rows that were not secured.

After installing a beta of the software at the client's site, I found a strange problem.  None of the users that were assigned the appropriate permissions could get into the secure data.  I checked and double-checked and triple-checked the permissions to make sure that they were a member of the appropriate role. No problems there. Then I noticed that my DBA had set everyone up as a db_owner to start with. I started doing some testing with the IS_MEMBER() function and found this:

If user is made a member of dbo or db_owner, then IS_MEMBER always returns 0 for any role other than dbo, regardless of whether the user is a member of that role or not.

Example Script:

SETUSER ‘MyDboUser’

PRINT 'The Current User is: ' + CAST(CURRENT_USER as varchar)

DECLARE @i int

SET @i = IS_MEMBER(‘MyCustomRole’)

IF @i = 0 BEGIN

    PRINT 'The User is not a member of the role.'

END

ELSE IF @i = 1 BEGIN

    PRINT 'The User is a member of the role.'

END

ELSE IF @i IS NULL BEGIN

    PRINT 'The role was not found'

END

exec sp_addrolemember @rolename= ‘MyCustomRole’, @membername= ‘MyDboUser’

SET @i = IS_MEMBER('JUA')

IF @i = 0 BEGIN

    PRINT 'The User is not a member of the role.'

END

ELSE IF @i = 1 BEGIN

    PRINT 'The User is a member of the role.'

END

ELSE IF @i IS NULL BEGIN

    PRINT 'The role was not found'

END

Output:

The Current User is: dbo

The User is not a member of the role.

‘MyDboUser’ added to role 'MyCustomRole'.

The User is not a member of the role.

This is very strange to me.  I would think if IS_MEMBER was going to return a default value for dbo’s, it would be that they were a member of every role—the way this is set up now defies my understanding.  Perhaps you know why IS_MEMBER was designed this way—if so, please enlighten the rest of us! J

The solution to this problem is simple--either grant db_owner access to the secure rows (bad solution) or finish the script to assign role-based security correctly, abandoning the db_owner for basic users (good solution).   I still say the behaviour of the IS_MEMBER() function is strange in this case though.  I thought about this some more, and came up with a--

Question: What happens when the user is a member of a role that is a parent of a child role?

Suppose I have a role called Parent, and a role called Child.  I create a user ‘MyUser’, and add him to the Child Role.  Then I add the Parent role to the Child role, granting the parent role all permissions of the child, plus additional permissions.  Now, I call SETUSER ‘MyUser’. Finally, I call IS_MEMBER(‘Parent’) and IS_MEMBER(‘Child’). 

SELECT IS_MEMBER(‘Parent’) – returns 0

SELECT IS_MEMBER(‘Child’) –returns 1

Now, I change the role assignments so that MyUser is a member of Parent, but not Child.

SELECT IS_MEMBER(‘Parent’) –returns 1

SELECT IS_MEMBER(‘Child’) –returns 1

As luck would have it, my first actual field-install of SQL Server Reporting Services was on a Windows 2000 Server SP4 Domain Controller.  After installation, I got an error stating that the ReportServer could not be initialized.  It took most of the day--but the solution is actually in the ReadMe file.  Below is the excerpt from the ReadMe file that is relevant, along with the WorkAround proposed in the related Knowledge Base Article.

1.8. Reporting Services on a domain controller requires manual configuration after setupOn Windows 2003 server, no manual configuration is necessary in order for Reporting Services to install and run properly on a computer that is also a domain controller. On Windows 2000 server, Reporting Services installs properly on a domain controller, but is not activated. Do the following either before or after running setup in order to properly configure Reporting Services to run on a domain controller:

Grant Impersonate Privilege to the IWAM_<machine> account. For more information, see the Knowledge Base Article "IWAM Account Is Not Granted the Impersonate Privilege for ASP.NET 1.1 on a Windows 2000 Domain Controller with SP4" (KB 824308).

On both Windows 2000 and Windows 2003, if you are using a Windows account to connect to the report server database, the Windows user must be granted the privilege to log on locally to the domain controller on which the report server is running, even if the report server database is on a different computer. Domain users are not granted this permission by default. If any of the above steps are performed after setup is completed, run rsactivate.exe to activate your installation of Reporting Services.

Hopefully, this will save someone else from wasting a day trying to resolve this problem.

Happy Reporting!

http://www.chriswetherell.com/elf/Default.asp

My elvish name is Valandil Anwamanë, what's yours?

Recently I had a problem with a required field validator and a DropDownList in a composite server control I had created. I'm trying to implement a Self-Validating input control that changes its implementation based on the datatype specified by the developer.  Numbers, strings, and datetimes are rendered as a textbox field for user entry.  Booleans are optionally rendered as a radiobuttonlist or CheckBox.  Lists are rendered in a DropDownList.  In my parlance, these are referred to as the Value controls.  In addition, the ValidatedInputControl renders a label and uses RequiredField and CompareValidators to check the output.  I had to develop a custom RegEx to validate datetimes since the standard CompareValidator can only validate dates. Finally, if the developer specifies that null values are allowed, a checkbox will be rendered beside the Value control.  Checking the box disables the Value Control, making its value null. Again, the rendering is determined dynamically by the datatype of the control.

Since the RequiredFieldValidator is returning Not Valid to the Page during page validation if I'm rendering a dropdownlist, I had a serious problem. I thought of not including the RequiredFieldValidtor if I'm using a dropdownlist, but that would actually create a lot of special code in the case where displaying a list. I've decided to abstract the ValidatedInputControl into component pieces.  I'll create a ValidatedTextBox control that accepts Numeric, String, and datetime values, a ValidatedBoolean Control that optionally renders a radiobuttonlist or checkbox, and a ValidatedListControl which will basically be a dropdownlist, a label, and an isnull checkbox if necessary (the checkbox isn't necessary if the dropdownlist contains a value that is already null.

Finally, I'll modify the ValidatedInputControl so that it renders one of the above child controls based on the datattype.

Side Note:

When developing a composite control that changes the type of child control rendered, I've found it necessary to set Me.ChildControlsCreated = False anytime a property that affects the type of Value control I render is set.  For example, changing the datatype from Integer to Boolean requires removing the textbox from the controls collection, and replacing it with a RadioButtonList.  Setting Me.ChildControlsCreated = False ensures that CreateChildControls is called.  This also means that I can't use the child control's viewstate to store properties relevant to it, such as style or whatnot. I have to store all properties related to the childcontrols in the local viewstate, and apply them during RenderContents. I haven't seen any examples of this type of control yet, so if there's demand for it, I may write an article demonstrating how to create one.

Of course, if someone knows a better approach to the problem of a dynamically rendered composite control, I'll be glad to hear it!

I figured this one out finally.

One of the commenters on this blog suggested that the problem was caused by the fact that I was using the <asp:listitem> tag inside my <sdp:validatedinputcontrol> tag.  I modified the control so that it used an ArrayList of values instead, and had identical results, so the “active schema does not support element '<asp:listitem'” angle appears to be a dead-end.

However, during testing, I found a property of the Page class that I hadn't noticed before (silly me!)--Page.Validators.  This is a collection of all the Validator controls loaded on the page.  I tested each one, and found that my RequiredFieldValidator was triggering as Not Valid.

Hmmm.  A little investigation into the order of events.

Button Click:

Call to Page.IsValid from the Button Click event:

Page.IsValid references the child validator controls, which calls Control.EnsureChildControls, which in turn calls CreateChildControls.

In CreateChildControls, all of the child controls are created, but none of their properties are set.  This includes the RequiredFieldValidator.Enabled property.  Control returns to the Page.IsValid call at this point.  Since the developer has specified a list of values for the user to choose from, my control renders a dropdown list.  Apparently, the RequiredFieldValidator is unable to evaluate the dropdownlist, so it returns false. The Required Field Validator doesn't work the way I expected it to with the DropDownList.  My assumption was that it meant that an item in the dropdownlist has to be selected before postback.  According to this article however, you have to specify an initial value.  The RequiredFieldValidator tests if the initial value and the new value are different.

YAY! Mystery solved.  Now for what to do about it.

I'll either have to disable the RequiredFieldValidator in CreateChildControls if and only if the entry control is going to be a dropdownlist, or I'll have to roll my own RequiredFieldValidator that can handle a dropdownlist.

First, let me say that I love the Visual Studio SQL Editor. I love being able to browse, edit, and execute SQL Server objects straight from the IDE.  There are only a few things that keep the VS IDE from being my primary SQL tool.

• Lack of a results “grid.“  Sometimes the query results never show up in the command window.  Besides, the grid view is easier for me to deal with in most cases.  I like being able to resize columns (it would be nice to be able to toggle their visibility on and off too). The SQL Editor tab should break down into 3 tabs just like Query analyzer does--SQL, Messages, and Results.
• Reformatting of my views.  I'd love to have the option of right-clicking the views in the Server Explorer, and see the script for them, instead of have them automatically pushed into the Query Designer.  The Query Designer reformats my SQL, which I hate.  Of course, if the Query Designer could be made use a syntax-highlighted, custom formatted version of SQL in its SQL pane, then I wouldn't mind so much.
• No intellisense for SQL objects.  Some tools such as SQLBuddy are trying to implement this feature, but to my knowledge, none have really succeeded.
• The ability to execute a stored procedure without actually committing them to the database. Perhaps we could have two options instead of Save: 1) Save as script file, Commit to database.  If Save as Script file is chosen, then the IDE should add the script file to the current project.  Scripting it out is a pain, especially if you have many parameters. Perhaps if the stored procedure hasn't been committed to the database yet, the IDE could perform the scripting out behind the scenes. Let the IDE DECLARE and set variable values, and execute the SQL, but hide that process from the developer.  Here's an example of what I mean:

ALTER PROCEDURE dbo.dummy

(

@Parameter int   ---passed in a 5

)

AS

SELECT * FROM MyTable WHERE index = @Parameter

The IDE could run the above as:

DECLARE @Parameter int

SET @Parameter = 5 --whatever value was passed in

SELECT * FROM MyTable WHERE index = @Parameter

Don't let this list of annoyances imply that I'm not pleased with Visual Studio to date--I am. I'm just pointing out where I think it could be stronger.  I'm trying to focus on the time-consuming tasks that I do repeatedly.

Thanks for listening.

As (a very small) some of you may know, I have been developing an dynamic UI rendering control for SQL Server Reporting Services in ASP .NET.  Given a report, the control will obtain a list of report parameters, and dynamically render UI and validation elements based on datatype and other properties of Report Parameters.  This control basically functions like a table, where each cell contains all the UI bits for each parameter.  Each collection of UI bits (a Value webcontrol, a Required Field validator, a Datatype validator, and a Set-Value-To-Null checkbox) is itself wrapped up into a self-contained server control that loads itself from a parameter.  It's called RSValidatedInputControl.  RSValidatedInputControl inherits from another server control I created called ValidatedInputControl.

For the most part, this system of objects works beautifully.  However, I've noticed something very strange. 

If ValidatedInputControl has a list of valid values, it renders a dropdownlist as its primary value control. Since it's a dropdownlist, required field and datatype validation are unnecessary, so those two controls are disabled.  The ValidValues list is stored as a ListItemCollection internal to the control.  The aspx markup is as follows:

 <sdp:validatedinputcontrol id=ValidatedInputControl1 runat="server" CanBeNull="False">
  <asp:ListItem Value="Text1">Text1</asp:ListItem>
  <asp:ListItem Value="Text2">Text2</asp:ListItem>
 </sdp:validatedinputcontrol>

I have a page containing only this control and a button.  When I click the button, I test Page.IsValid to determine if the validation has passed. Since all the validators are disabled by default, this should return “True.”  It doesn't.  I checked the validator controls--they are disabled.  They are also marked as valid.  Why would the page be marked as invalid?  I've even stepped through the built-in javascript provided with the validator controls. 25 points to the first person with the correct answer (that was an obvious bid to encourage others to help me answer this question : ) ).

The only thing that I can find that might be the start of an explanation is this. When I look at the aspx markup in HTML view, I get a tooltip to the effect that the page parser doesn't like the <asp: listitem> tags.  “The active schema does not support the element '<asp:listitem>'”.

Whaddya' think?

I'm in the middle of debugging and enhancing a group of Reports using SQL Server Reporting Services.

Overall I like the product a lot. It's got a very slick designer.  The one-touch deployment is outstanding.  It isn't lacking for much in the way of features. I still think the reports out to have a managed event-driven code-behind, but I'm able to work around that in most cases.  The webservice API is fairly easy to use. I was able to use it to implement my own custom navigation and parameter UI fairly rapidly.

There is one major drawback that should be mentioned though. You cannot use SQL Server Security to access it. It relies on Integrated Windows Security. In the Enterprise Edition you can implement a custom Security extension, which will allow you to basically use any kind of security you can code.  This is not available in the Standard version, however.

This creates a problem for me because the app I am working on is a LAN app that uses SQL Server Security. This means that each user has to be set up in a SQL Server role AND their Windows Account name as be set up in the same role--argh!  As if security wasn't complex enough!  Since our app is a web app, it also means that if a user to to another machine, logs into our app using their SQL Server Credentials, they still view reports in Reporting Services as if they were the logged into the Windows Account.

Am I the only one that finds it strange that an alleged “bolt-on“ to SQL Server can't use SQL Server Security? Does Analysis Services have the same security model?

Again, overall I think this product is outstanding, especially for a first effort. I just question the security model.

UPDATE

Roman writes: “What you have to remember is that RS is a combination of SQL Server and IIS components. SQL Server supports both Windows and SQL Authentication but IIS supports only Windows Authentication. If you wanted to avoid administering dual groups, you would have to switch from Standard to Windows in your other application and set up RS to use Windows Authentication in report data sources.”

You know, I get that.  I really do. I guess I expected to be able to allow anonymous access to the Report Manager via IIS, and have the Report Manager query the user for SQL Server credentials when they access the reports.  Since Microsoft has a graduated licensing model for Report Access, I guess that wasn't a viable solution for them.