Losing dimension permissions in security role with deployment wizard

I'm working with an AS database that has security roles configured with dimension and dimension data permissions. If I deploy my BI project using Deployment Wizard with the option to Retain roles and members, the roles and members do remain intact but the cube access in the roles is set to None and the dimension and data dimension permissions are gone.

Is there any way to use Deployment Wizard so that it retains the roles, members and the cube/dimension permissions defined for the role? I don't want to deploy roles and retain members because my roles will be different depending on the target server.

Thanks in advance.

This seems to be the same bug as described in Chris Webb's blog today:

http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!978.entry

>>

Deployment Wizard Bug

The Deployment Wizard is meant to allow you to deploy a project from BIDS but not overwrite certain parts of the associated Analysis Services database. Examples of things you’d not want to overwrite on the server include partitions, connection strings and security settings. However once I started actually using this functionality with my customers over the last few months I came across (or rather was informed of by one of said customers and was able to repro with others) a pretty major bug: when you select the ‘Retain Roles and Members’ option to not overwrite any of the security roles, while it does indeed not overwrite any of the roles it sets cube access in all of them to None and this in turn loses all the dimension security settings defined on the roles. So, be warned… it’s with PSS at the moment but I’m told it probably won’t be fixed in SP2.

>>

|||Yes, I am probably one of the customers Chris references in his blog.

Lose Security options in Report manager

I have installed RS on a machine and have deployed several reports. Now when
I go into Report Manager, there is no security settings on the properties tab
- This means I am not allowed to add/edit roles or any security functions at
all. Under the properties tab, i only see a general settings which lists
modified date, modified by, creation date, and created by information for the
report. The security functions used to be there - how do i get them back?
I used the RS.EXE tool to deploy my reports to the server - Could this have
caused my problem somehow? Any help would be appreciated!If you log on from the local machine as a local machine admin you should
always have permission to set security. It is hard to say why you lost your
security, perhaps somebody else came and removed them? Does you script
attempt to modify security?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"PCL" <PCL@.discussions.microsoft.com> wrote in message
news:BD224DBC-8998-45D9-A85A-4A73097C5D2C@.microsoft.com...
>I have installed RS on a machine and have deployed several reports. Now
>when
> I go into Report Manager, there is no security settings on the properties
> tab
> - This means I am not allowed to add/edit roles or any security functions
> at
> all. Under the properties tab, i only see a general settings which lists
> modified date, modified by, creation date, and created by information for
> the
> report. The security functions used to be there - how do i get them back?
> I used the RS.EXE tool to deploy my reports to the server - Could this
> have
> caused my problem somehow? Any help would be appreciated!|||Even when i loginto the local machine as Administrator, the security tabs are
missing. I dont think the RSS file does anything with security - It is a
modified version of the sample scripts that come with RS - we just used them
to deploy our reports.
"Daniel Reib [MSFT]" wrote:
> If you log on from the local machine as a local machine admin you should
> always have permission to set security. It is hard to say why you lost your
> security, perhaps somebody else came and removed them? Does you script
> attempt to modify security?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "PCL" <PCL@.discussions.microsoft.com> wrote in message
> news:BD224DBC-8998-45D9-A85A-4A73097C5D2C@.microsoft.com...
> >I have installed RS on a machine and have deployed several reports. Now
> >when
> > I go into Report Manager, there is no security settings on the properties
> > tab
> > - This means I am not allowed to add/edit roles or any security functions
> > at
> > all. Under the properties tab, i only see a general settings which lists
> > modified date, modified by, creation date, and created by information for
> > the
> > report. The security functions used to be there - how do i get them back?
> >
> > I used the RS.EXE tool to deploy my reports to the server - Could this
> > have
> > caused my problem somehow? Any help would be appreciated!
>
>|||Well if anyone is interested, I got all the security functionality back by
uninstalling and reinstalling reporting services. I tried rerunning my RSS
script and cannot get it to duplicate the problem i was having - Wierd, but i
guess it is fixed...
"PCL" wrote:
> I have installed RS on a machine and have deployed several reports. Now when
> I go into Report Manager, there is no security settings on the properties tab
> - This means I am not allowed to add/edit roles or any security functions at
> all. Under the properties tab, i only see a general settings which lists
> modified date, modified by, creation date, and created by information for the
> report. The security functions used to be there - how do i get them back?
> I used the RS.EXE tool to deploy my reports to the server - Could this have
> caused my problem somehow? Any help would be appreciated!|||Did you tweak the security on the IIS virtual server? If you use anonymous
authentication, you can't publish since it's not authenticating.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"PCL" <PCL@.discussions.microsoft.com> wrote in message
news:BD224DBC-8998-45D9-A85A-4A73097C5D2C@.microsoft.com...
>I have installed RS on a machine and have deployed several reports. Now
>when
> I go into Report Manager, there is no security settings on the properties
> tab
> - This means I am not allowed to add/edit roles or any security functions
> at
> all. Under the properties tab, i only see a general settings which lists
> modified date, modified by, creation date, and created by information for
> the
> report. The security functions used to be there - how do i get them back?
> I used the RS.EXE tool to deploy my reports to the server - Could this
> have
> caused my problem somehow? Any help would be appreciated!

looking for security guidelines to avoid sql injection risks with xquery

Hi, i hope this forum can help me clarify my security concerns for my scenario:

I want a user to be able to filter sql rows by using xquery on an untyped XML that is stored in a XMLDataType column. My table will have multiple rows/columns, with one column being XML.

I would like to get some guidance on how a user can specify their "filter" condition string using the xquery syntax without harding coding any dependency on the server side (client decides which items they are interested in), and I would like to know the guidelines on how to translate the xquery on the server to avoid any sql injection risks. For instance, perhaps i should double all single quotes in the given xquery string. Also, i am concerned about sql:variables that may be able to access private variables inside a stored proc, has this been an issue?

Thanks!!

All of the issues you bring up are concerns:

-SQL injection
-sql:column
-sql:variable
-very expensive queries can lead to Denial of Service attacks

XQuery is code, so it should be treated similarly to SELECT statements, in general it is difficult to safely execute untrusted queries.

Ways around this would be to:

-Use set of parameterized stored procedures that reasonably cover the likely set of queries users are going to execute.
-Give each user their own database and assume that they have full SELECT permissions on it. (This wont prevent Denial of Service type attacks, but could prevent users' from access other users' data in the event of SQL injection.)
-Use full-text search. The queries wont be as expressive, but it might be good enough to filter rows.

Looking for input on SQL 2005 and IIS Security

I am just looking for feedback and maybe pointers for research regarding securing SQL 2005 for IIS access. I am currently working on a project for building a new retail website and our sysadm guys have some concerns regarding exposing our SQL box to the DMZ via direct connections to the IIS box. Now we have not completely come to a conclusion of saying that it is not possible, but we are very concerned due to recent credit card and customer data problems in the industry.

So far we have mainly talked about just exposing web services(limiting the exposure of data that can be retrieved) on an internal IIS box that would be called allowed to be accessed from only the IIS box in the DMZ using User Accounts and Client SSL Certs. What I am most concerned about is the performance of this design. I would like to try and provide a connection to the SQL box directly for the devs, but I am not sure what the best practice would be for securing this connection through the firewall and also monitoring it in case our DMZ IIS box gets owned.

Any input or direction to resources would be much appreciated. I have read quite a few papers so far and just want to get feedback on architectures and designs.

Thanks in advance.

Options include: using IPSEC (or SSL) to control the connection between the IIS box and SQL Server (and Developers). Only 'approved' IP addresses would be allowed to connect to SQL Server. That protects SQL Server from being directly attacked, but not from attack from the IIS box.

Using IIS's capability to use Application Pools, you can further restrict access to SQL Server through the Application.

Here are some resources that might be useful:

Web Applications -Connect to SQL Server
Configuring an ASP.NET 2.0 Application to Work with Microsoft SQL Server 2000 or SQL Server 2005
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=395
How To: Create a Service Account for an ASP.NET 2.0 Application
http://msdn2.microsoft.com/en-us/library/ms998297.aspx
How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0
http://msdn2.microsoft.com/en-us/library/ms998300.aspx