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.

Lookupcube and Parameter not working together

Please help me
I have the following MDx that works perfect, but I need now to attach a
parameter, but it would work. please help
With
Member Measures.[Sales Current Year Target] as
'LookupCube("Sales vs SalesBudget","( [Measures].[Current Year
Target],[DistrictGeo].[District Id].["+[DistrictIsManaged].CurrentMember.Name
+"]," + [Time].CurrentMember.UniqueName + ")")'
SELECT NON EMPTY { Measures.[Sales Current Year Target] } ON COLUMNS ,
{NONEMPTYCROSSJOIN(
{ [DistrictIsManaged].[District Id].[Dallas]},
[Territory].[Territory Desc].members)} on
ROWS
FROM [Sales vs RepBudget]
where (" & Parameters!pTime.Value & ")"You need to prefix your mdx with =" and end with ". And the whole mdx
statement needs to be in on one line. It will break by itself, but you can
check it out by copying the whole statement to Notepad, turn off word wrap
and see that it's all on one line.
Also, you might have to escape the quotes "s in your statement. You do this
by adding more quotes.
"
Visual Basic Language Specification
2.4.4 String Literals
A string literal is a sequence of zero or more Unicode characters beginning
and ending with an ASCII double-quote character, a Unicode left double-quote
character, or a Unicode right double-quote character. Within a string, a
sequence of two double-quote characters is an escape sequence representing a
double quote in the string."
Try adding an extra " infront of you "s.
Kaisa M. Lindahl
"Tomas" <Tomas@.discussions.microsoft.com> wrote in message
news:E32EB005-182D-4315-A873-FF6C6606AFCB@.microsoft.com...
> Please help me
> I have the following MDx that works perfect, but I need now to attach a
> parameter, but it would work. please help
> With
> Member Measures.[Sales Current Year Target] as
> 'LookupCube("Sales vs SalesBudget","( [Measures].[Current Year
> Target],[DistrictGeo].[District
> Id].["+[DistrictIsManaged].CurrentMember.Name
> +"]," + [Time].CurrentMember.UniqueName + ")")'
> SELECT NON EMPTY { Measures.[Sales Current Year Target] } ON COLUMNS ,
> {NONEMPTYCROSSJOIN(
> { [DistrictIsManaged].[District Id].[Dallas]},
> [Territory].[Territory Desc].members)} on
> ROWS
> FROM [Sales vs RepBudget]
> where (" & Parameters!pTime.Value & ")"
>

Looking for some SQL Advice

I'm working on a project to create a Report. Currently, I have an
Excel spreadsheet with a macro running that creates the report that I
want to move to SQL reporting.
There are 2 tables. One Table has 2 sets of price ranges, with a
"StartPrice", "EndPrice", and a "Type" to select between the 2 set of
ranges.
One of the ranges is like:
Type1 $0.00 $49.00
Type1 $49.01 $79.00
Type1 $79.01 $99.00
Type1 $99.01 $129.00
Type1 $129.01 $149.00
Type1 $149.01 $179.00
Type1 $179.01 $199.00
Type1 $199.01 $249.00
Type1 $249.01 $299.00
Type1 $299.01 $349.00
Type1 $349.01 $399.00
Type1 $399.01 $499.00
Type1 $499.01 $599.00
Type1 $599.01 $699.00
The other table has sales data with a qty, sellingprice, Actualcost
that I calculate the Total Qty, Total Cost, Total SellingPrice, and
Profit, for each of the price ranges.
On the report its like:
Range Qty Price Cost Profit
$299.01 - $349.00 1 $349.00 $142.28 $206.72
$349.01 - $399.00 1 $362.38 $180.93 $181.45
$399.01 - $499.00
To achieve this output, I loop thru each of the Price ranges, pluging
the Start and End into a Select to select the single record for the
Report.
I was wondering if there was a way to simply by using only one SQL
command.
Any ideas?
BartYes, you can join the sales data to the range data where the Total Cost is between the Start Price and the End Price.
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||Could you show me an example?
Using my rough field names would be fine.
Bart
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:O5icBq7YEHA.3692@.TK2MSFTNGP09.phx.gbl...
> Yes, you can join the sales data to the range data where the Total Cost is
between the Start Price and the End Price.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Looking for some general feedback on working with SQL, SSIS and SAP

This is less of a specific question and more of a request for for some advice as to possibilities and directions. Here's the current situation. My company is using SAP for its purchasing, inventory, etc. This system is pretty much opaque to me - it's managed by another group within the company, and changes to it go through a complicated approval process. At the same time, the majority of our users, internal and external, are looking at this same data through a more accesible and more user friendly collection of web applications - done in classic ASP, up through ASP.NET 1.1 and 2 - and stored in an assortment of MS-SQL 2000 databases. Data is exchanged between SQL and SAP via DTS packages, some nightly, some run more frequently.

There's some issues here - data is never quite synchronized between the two sides, sometimes the same data must be updated twice, leading to possible data integrity issues, etc. Given that, we're going to be moving to SQL 2005 within the next year or so. From everything I've understood, within that context, there are vastly better ways of dealing with out situation than the way we're currently doing it.

So what I'm looking for is just a general impression of what can be done, with SSIS and SAP. Any approaches that might prove more fruitful, an y pitfalls to watch out for, that sort of thing.

How are you pulling data from SAP to SQL using DTS?

With SQL 2005, we have a preview version of the SAP .NET Data Provider for use within SSIS. You can check it out http://msdn2.microsoft.com/en-us/library/ms141761(SQL.90).aspx

SSIS will also be supported by the upcoming Biztalk R2 Adapter pack - which has adapters for SAP, Oracle & Siebel.

Go to https://connect.microsoft.com/ and look for this adapter pack - you can try out Beta2 around end-July.

|||

Currently we're not exactly pulling data. We're exporting data from SAP to a collection of flat files. Then we have a number of DTS packages that run - most nightly, one every 4 hours - and import the data into our database. Which seems damn clumsy.

Thanks for pointing me towards the .NET Data Provider, which I had a vague notion of, and to the Biztalk beta, which is something I hadn't heard of. I'll have plenty of things to research.

Looking for Solution to Remote Data Entry

I'm looking for different solution people have implemented to solve this
problem.
Problem: People need to work remotely. While working remotely they want to
gather information. This information might be survey info, or inspection
info, etc. This information is to be stored remotely on a handheld, or
laptop device. After they get back to the office/homeo office they want to
upload the information to our corporate database, which is SQL Server 2000.
This upload process should be very easy, like a like of a button, or done
automatically when the devices is docked into the corporate network. This
solution has to be supported over the Internet, and intranet.
What I'm looking for is what technologies and processes have people put in
places to implement a solution to handle the problem I stated above.
If you have more questions about what I am asking for please let me know.Replication is the MS SQL Server technology for this.
Quentin
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
> I'm looking for different solution people have implemented to solve this
> problem.
> Problem: People need to work remotely. While working remotely they want
> to
> gather information. This information might be survey info, or inspection
> info, etc. This information is to be stored remotely on a handheld, or
> laptop device. After they get back to the office/homeo office they want
> to
> upload the information to our corporate database, which is SQL Server
> 2000.
> This upload process should be very easy, like a like of a button, or done
> automatically when the devices is docked into the corporate network. This
> solution has to be supported over the Internet, and intranet.
> What I'm looking for is what technologies and processes have people put in
> places to implement a solution to handle the problem I stated above.
> If you have more questions about what I am asking for please let me know.
>|||Are you saying you would use replication from a CE or Laptop device back to
your corporate database?
What other technologies might be available. I'm looking for all the
different technologies so I can determine what might be best and worst
methods.
"Quentin Ran" wrote:
> Replication is the MS SQL Server technology for this.
> Quentin
>
> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
> news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
> > I'm looking for different solution people have implemented to solve this
> > problem.
> >
> > Problem: People need to work remotely. While working remotely they want
> > to
> > gather information. This information might be survey info, or inspection
> > info, etc. This information is to be stored remotely on a handheld, or
> > laptop device. After they get back to the office/homeo office they want
> > to
> > upload the information to our corporate database, which is SQL Server
> > 2000.
> > This upload process should be very easy, like a like of a button, or done
> > automatically when the devices is docked into the corporate network. This
> > solution has to be supported over the Internet, and intranet.
> >
> > What I'm looking for is what technologies and processes have people put in
> > places to implement a solution to handle the problem I stated above.
> >
> > If you have more questions about what I am asking for please let me know.
> >
> >
>
>|||> Are you saying you would use replication from a CE or Laptop device back
> to
> your corporate database?
Yes that's what I am saying.
> What other technologies might be available. I'm looking for all the
> different technologies so I can determine what might be best and worst
> methods.
I am a SQL Server guy, not too much into other applications. One thing I
see can be useful is message queueing.
> "Quentin Ran" wrote:
>> Replication is the MS SQL Server technology for this.
>> Quentin
>>
>> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
>> news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
>> > I'm looking for different solution people have implemented to solve
>> > this
>> > problem.
>> >
>> > Problem: People need to work remotely. While working remotely they
>> > want
>> > to
>> > gather information. This information might be survey info, or
>> > inspection
>> > info, etc. This information is to be stored remotely on a handheld, or
>> > laptop device. After they get back to the office/homeo office they
>> > want
>> > to
>> > upload the information to our corporate database, which is SQL Server
>> > 2000.
>> > This upload process should be very easy, like a like of a button, or
>> > done
>> > automatically when the devices is docked into the corporate network.
>> > This
>> > solution has to be supported over the Internet, and intranet.
>> >
>> > What I'm looking for is what technologies and processes have people put
>> > in
>> > places to implement a solution to handle the problem I stated above.
>> >
>> > If you have more questions about what I am asking for please let me
>> > know.
>> >
>> >
>>

Looking for Solution to Remote Data Entry

I'm looking for different solution people have implemented to solve this
problem.
Problem: People need to work remotely. While working remotely they want to
gather information. This information might be survey info, or inspection
info, etc. This information is to be stored remotely on a handheld, or
laptop device. After they get back to the office/homeo office they want to
upload the information to our corporate database, which is SQL Server 2000.
This upload process should be very easy, like a like of a button, or done
automatically when the devices is docked into the corporate network. This
solution has to be supported over the Internet, and intranet.
What I'm looking for is what technologies and processes have people put in
places to implement a solution to handle the problem I stated above.
If you have more questions about what I am asking for please let me know.
Replication is the MS SQL Server technology for this.
Quentin
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
> I'm looking for different solution people have implemented to solve this
> problem.
> Problem: People need to work remotely. While working remotely they want
> to
> gather information. This information might be survey info, or inspection
> info, etc. This information is to be stored remotely on a handheld, or
> laptop device. After they get back to the office/homeo office they want
> to
> upload the information to our corporate database, which is SQL Server
> 2000.
> This upload process should be very easy, like a like of a button, or done
> automatically when the devices is docked into the corporate network. This
> solution has to be supported over the Internet, and intranet.
> What I'm looking for is what technologies and processes have people put in
> places to implement a solution to handle the problem I stated above.
> If you have more questions about what I am asking for please let me know.
>
|||Are you saying you would use replication from a CE or Laptop device back to
your corporate database?
What other technologies might be available. I'm looking for all the
different technologies so I can determine what might be best and worst
methods.
"Quentin Ran" wrote:

> Replication is the MS SQL Server technology for this.
> Quentin
>
> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
> news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
>
>
|||> Are you saying you would use replication from a CE or Laptop device back
> to
> your corporate database?
Yes that's what I am saying.

> What other technologies might be available. I'm looking for all the
> different technologies so I can determine what might be best and worst
> methods.
I am a SQL Server guy, not too much into other applications. One thing I
see can be useful is message queueing.
[vbcol=seagreen]
> "Quentin Ran" wrote:

Looking for Solution to Remote Data Entry

I'm looking for different solution people have implemented to solve this
problem.
Problem: People need to work remotely. While working remotely they want to
gather information. This information might be survey info, or inspection
info, etc. This information is to be stored remotely on a handheld, or
laptop device. After they get back to the office/homeo office they want to
upload the information to our corporate database, which is SQL Server 2000.
This upload process should be very easy, like a like of a button, or done
automatically when the devices is docked into the corporate network. This
solution has to be supported over the Internet, and intranet.
What I'm looking for is what technologies and processes have people put in
places to implement a solution to handle the problem I stated above.
If you have more questions about what I am asking for please let me know.Replication is the MS SQL Server technology for this.
Quentin
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
> I'm looking for different solution people have implemented to solve this
> problem.
> Problem: People need to work remotely. While working remotely they want
> to
> gather information. This information might be survey info, or inspection
> info, etc. This information is to be stored remotely on a handheld, or
> laptop device. After they get back to the office/homeo office they want
> to
> upload the information to our corporate database, which is SQL Server
> 2000.
> This upload process should be very easy, like a like of a button, or done
> automatically when the devices is docked into the corporate network. This
> solution has to be supported over the Internet, and intranet.
> What I'm looking for is what technologies and processes have people put in
> places to implement a solution to handle the problem I stated above.
> If you have more questions about what I am asking for please let me know.
>|||Are you saying you would use replication from a CE or Laptop device back to
your corporate database?
What other technologies might be available. I'm looking for all the
different technologies so I can determine what might be best and worst
methods.
"Quentin Ran" wrote:

> Replication is the MS SQL Server technology for this.
> Quentin
>
> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
> news:C353A7E9-0D0E-4DA4-85E8-6EE8F6D754AD@.microsoft.com...
>
>|||> Are you saying you would use replication from a CE or Laptop device back
> to
> your corporate database?
Yes that's what I am saying.

> What other technologies might be available. I'm looking for all the
> different technologies so I can determine what might be best and worst
> methods.
I am a SQL Server guy, not too much into other applications. One thing I
see can be useful is message queueing.
[vbcol=seagreen]
> "Quentin Ran" wrote:
>

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