How to set up security to SQL 2005 Reporting Service?

I have Sql 2005 server (SSAS, SSRS, Database)installed on my workstation running on Windows XP Pro. SP2 for development only. However I need to migrate the reports to a "Production" enviroment next month which is set up with two servers running on Windows 2003 Server, one will serve as Data Server with Sql 2005 server and SSAS installed on it and the other will serve as Report Server with Sql 2005 Reporting Service. My question is how to set up the IIS on the Report Server to allow users from the domain to access the Report Manager (the reporting Service is intend to set up as an "Intranet" tool for the users ie No Internet access to the reports). A "dumpy" user will be set up at the Report Server for the data connection to the remote Sql 2005 Server. Also how to implement the security on the individual folder in the Report Manager. Currently I have folders set up for the department and I want to set up security only allow people from those depts to access their folder/subfolders. How to set up security on folder and subfolders in Report Manager? Can I set the security on my Windows XP machine to test it before I roll it out to the Production servers? Thanks.

here is a microsoft link that talks about role based security in reporting services...

http://msdn2.microsoft.com/en-us/library/ms156014.aspx

hope this helps...good luck!

how to set up a cube for pivot table service in Excel?

background: sql2k and analysis service
I know how to create a cube in analysis service, but how to view the
cube in the Pivot Table services in Excel?
when I go to Data|PivotTable and PivotChart report...|External Data
Source|
Get Data | OLAP cube, then browse to the server, but I don't see any of
the cubes being created.
I'm using Standard SQL2k. Is this a sql version issue or should i
create cube differently?
thank youHave you set permissions on the cube to allow access from the user running
Excel?
Under Database Roles in Analysis manager.
Standard version includes analysis services, with EE having increased
features in some areas, so I do not think that is your problem.
Mike John
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1107205552.456050.61110@.f14g2000cwb.googlegroups.com...
> background: sql2k and analysis service
> I know how to create a cube in analysis service, but how to view the
> cube in the Pivot Table services in Excel?
> when I go to Data|PivotTable and PivotChart report...|External Data
> Source|
> Get Data | OLAP cube, then browse to the server, but I don't see any of
> the cubes being created.
> I'm using Standard SQL2k. Is this a sql version issue or should i
> create cube differently?
> thank you
>|||i'm very lost...:(
i created a cube on the analysis service then add my nt accout to the
All Users database role. (enforced on client).
i then go thru the steps in Excell, Data|PivotTable and PivotChart
report...|External Data Source|
Get Data | OLAP cube, but then where shoudl browse to from there?
i check the book online about the local cube. it was not helping. i
still dont' know how to create a local cube.|||Steve, it is difficult to follow your description, but Local cube has
nothing to do with connecting to an analysis services cube.
After you select olap cube you should be able to select thser server and
database that contains the cube.
Mike John
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1107212127.396318.211160@.f14g2000cwb.googlegroups.com...
> i'm very lost...:(
> i created a cube on the analysis service then add my nt accout to the
> All Users database role. (enforced on client).
> i then go thru the steps in Excell, Data|PivotTable and PivotChart
> report...|External Data Source|
> Get Data | OLAP cube, but then where shoudl browse to from there?
> i check the book online about the local cube. it was not helping. i
> still dont' know how to create a local cube.
>|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?

How to set the location of a linkedserver?

Hey folks,
I have a WinNT4 machine with the indexing service setup on it. It works.
Also I have a W2k machine running SQL Server 2000. I'd like to add the
indexing service on the WinNT4 machine as a linkedserver to the SQL2000 DB
on the W2k machine.
If the DB as well as indexing service are on the same machine I use this:
sp_addlinkedserver CVDOC, 'Index Server', 'MSIDXS', 'CV'
From reading online I understand that as a 5th parameter you can give the
storedproc a location for the linkedserver however I have not been able to
make this work.
What should that location parameter look like? UNC? Web?
On the NT4 server my documents to index are in C:\somefolder\CV\
The catalog name is CV and points to this location.
Any help would be very much appreciated.
Thanks for reading sofar,
Vincent.
From what I understand the catalogs and services are completely different,
so you can't query a NT 4.0 IS server or its catalog from Win2k.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
<koopman@.furore.com.removethis> wrote in message
news:eLi5QiUzEHA.1264@.TK2MSFTNGP10.phx.gbl...
> Hey folks,
> I have a WinNT4 machine with the indexing service setup on it. It works.
> Also I have a W2k machine running SQL Server 2000. I'd like to add the
> indexing service on the WinNT4 machine as a linkedserver to the SQL2000 DB
> on the W2k machine.
> If the DB as well as indexing service are on the same machine I use this:
> sp_addlinkedserver CVDOC, 'Index Server', 'MSIDXS', 'CV'
> From reading online I understand that as a 5th parameter you can give the
> storedproc a location for the linkedserver however I have not been able to
> make this work.
> What should that location parameter look like? UNC? Web?
> On the NT4 server my documents to index are in C:\somefolder\CV\
> The catalog name is CV and points to this location.
> Any help would be very much appreciated.
> Thanks for reading sofar,
> Vincent.
>
|||And what if I was using another w2k server instead of NT4?
What makes you think the catalogs and services are different? Something I
wrote or is that a fact because I use an NT4 server? I don't get this part
of your message...
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eP6d5aXzEHA.2788@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> From what I understand the catalogs and services are completely different,
> so you can't query a NT 4.0 IS server or its catalog from Win2k.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
>
> <koopman@.furore.com.removethis> wrote in message
> news:eLi5QiUzEHA.1264@.TK2MSFTNGP10.phx.gbl...
DB[vbcol=seagreen]
this:[vbcol=seagreen]
the[vbcol=seagreen]
to
>
|||Win2k to Win2k will work fine. Win2k to NT 4 or vice versa won't as the IS
server, the catalogs, and most importantly the providers are all different.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"news.micosoft.com" <koopman@.furore.com.removethis> wrote in message
news:eMAvcFYzEHA.2316@.TK2MSFTNGP15.phx.gbl...
> And what if I was using another w2k server instead of NT4?
> What makes you think the catalogs and services are different? Something I
> wrote or is that a fact because I use an NT4 server? I don't get this part
> of your message...
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eP6d5aXzEHA.2788@.TK2MSFTNGP15.phx.gbl...
> DB
> this:
> the
> to
>

How to set is_receive_enabled and is_enqueue_enabled from 1 to 0.

Hi All,

I used service broker activation in my receive queue (SQL 2005 SP1). However, it seems the stored procedure does not work after the receive queue get message. I saw the following error in SQL Error log.

"The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'"

Then I checked the queue in sys.service_queues and noticed is_receive_enabled and is_enqueue_enabled is 0 but not 1 (is_activation_enabled is 1, which is normal). I believe this is the cause of my service broker activation issue.

According to this link:

http://www.eggheadcafe.com/aspnet_answers/SQLServerservicebroker/May2006/post26788966.asp

I tried to use ALTER QUEUE ... WITH STATUS = ON command to set the above value back to 1, it does not work the value persists 0. Any idea?

Thanks in advance.

Michael

ALTER QUEUE [YourQueue] WITH STATUS = ON will enable the queue and both is_receive_enabled and is_enqueue_enabled will be set to 1. However, if your stored proc rolls back a RECEIVE 5 times consecutively, the poisoned message detector will automatically disable the queue. You should see error messages in the ERRORLOG if your stored proc is throwing an exception or not committing the RECEIVE correctly.

Rushi

|||

Thanks, Rushi. You are correct the receive queue is disabled and I also found 5 errors before the queue is disabled in the SQL ERRORLOG as below:

--

2006-12-28 20:39:47.93 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:47.93 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:47.95 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:47.96 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:47.98 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:48.01 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:39:50.85 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:39:55.85 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:40:00.85 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:40:05.85 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:40:08.09 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'

Can you let me know:

a. Is the 5 times a built-in value or the value I can set it? Is it the option MAX_QUEUE_READERS = 5 while I set the activation?

b. Since the ReceiveQ is disabled, how can I enable it back? I tried "ALTER QUEUE [YourQueue] WITH STATUS = ON" before, but the ReceiveQ still is disabled. Is it normal? Can I set it back?

Michael

|||

a. The maximum number of times RECEIVE can be consecutively rolled-back without setting of the poisoned message detection cannot be configured. It is hard-coded to '5'. It is also not related to the MAX_QUEUE_READERS which controls max number of concurrent activated tasks.

b. The ATLER QUEUE should re-enable your queue. Of course, if there are pending messages it will also start activation and since you have a bug in your stored proc, the stored proc will rollback 5 times and disable it back.

Rushi

|||

yeah.. that's should my probme. Thanks a lot.

How to service data for multiple customers

Hello All,

I do not know if this is the proper group to ask my question. Please do
direct me to the proper place.

I have a database server and would like to store data for multiple
groups of customers. The data is not shared amongst the groups but are
shared only amongst individuals composing a group.

One solution would be to lump all data from all groups into a single
instance of the database server accompanied with a tag that would
identify to which group each piece of information belongs.

Is there a better method to achieve what I want to do. Would my
solution present any difficulties in the future in terms of performance,
maintenance, scalability.

Are there other solutions that may be available to address my needs.

Any suggestions would be greatly appreciated.

ThanksCREATE TABLE CustomerGrps
(grp_nbr INTEGER NOT NULL, -- a code for the CHAID analysis??
customer_id ...,
..
PRIMARY KEY (grp_nbr, customer_id),
...)'|||Thanks for the input. I was looking towards a solution more from a DBA's
perspective rather than a programmer's perspective.

For example. In order to service multiple groups of customers. Would it be
better to have one sqlserver instance to service the need of all groups of
customers or would it be more advantageous to install multiple instances of
sqlserver, one instance for each group of customers.

thanks

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1121912144.832540.98870@.g49g2000cwa.googlegro ups.com...
> CREATE TABLE CustomerGrps
> (grp_nbr INTEGER NOT NULL, -- a code for the CHAID analysis??
> customer_id ...,
> ..
> PRIMARY KEY (grp_nbr, customer_id),
> ..)'|||If all the customers will have similar admin/support demands and expect
the same service levels then fewer servers/instances will likely mean
less work from a DBA perspective. In that case, the main considerations
will probably be around performance/scalability and any security
concerns the customer has about sharing the server with others.
Multiple instances on the same server don't offer anything much on the
scalability front but design the application such that it can support
multiple servers in case you need that capability in the future.

--
David Portas
SQL Server MVP
--