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 permissions for a user, who need to work with SQL Server Agent

I am looking for a good idea or best practice for setting up a security plan which allows users to execute a specific job, owned by the SQL Server Agent Service Account.

I tried to use the msdb SQLAgentOperatorRole, but unfortunately I recognized, that every local job could be executed.

I would like to have only one job executed by the user and all others should be visible, but not executable.

What is the best practice for this request? Can Proxies and credentials be helpful and if yes, how do I have to use them?

In other words: what is the best approach for this request (end user should see and start specific jobs in the SQL Server Agent).

Thanks in advance

Norbert

Owner can execute job as well.|||

Thanks for the hint, but I do not want to have this user as an owner. The owner should be another account. This user (developer) should only have the capability to execute jobs we (DBA) provided to him. He should not see ALL jobs nor should he execute ALL jobs. I would like to set permissions in a way that he can start/stop/enable/disable ONE specific job.

Is there an easy way to realize it? Or maybe a more complicated way?

Regards

Norbert

How to set up a login that should execute a specific SQL Server Agent job

I am looking for a good idea or best practice for setting up a security plan which allows users to execute a specific job, owned by the SQL Server Agent Service Account.

I tried to use the msdb SQLAgentOperatorRole, but unfortunately I recognized, that every local job could be executed.

I would like to have only one job executed by the user and all others should be visible, but not executable.

What is the best practice for this request? Can Proxies and credentials be helpful and if yes, how do I have to use them?

In other words: what is the best approach for this request (end user should see and start specific jobs in the SQL Server Agent).

Thanks in advance

Norbert

Has no one an idea?

Regards

Norbert

|||

I hope, that someone xould give me some hints if it is possible to realize the scenario or not.

Regards

Norbert

How to set up a login that should execute a specific SQL Server Agent job

I am looking for a good idea or best practice for setting up a security plan which allows users to execute a specific job, owned by the SQL Server Agent Service Account.

I tried to use the msdb SQLAgentOperatorRole, but unfortunately I recognized, that every local job could be executed.

I would like to have only one job executed by the user and all others should be visible, but not executable.

What is the best practice for this request? Can Proxies and credentials be helpful and if yes, how do I have to use them?

In other words: what is the best approach for this request (end user should see and start specific jobs in the SQL Server Agent).

Thanks in advance

Norbert

Has no one an idea?

Regards

Norbert

|||

I hope, that someone xould give me some hints if it is possible to realize the scenario or not.

Regards

Norbert

How to set this up?

I need to set up the security on the Reporting Services such that user can
view the reports ONLY through a ReportViewer control on a asp.net web page
ie not allow to navigate all other folders or areas in the Report Manager.
Reporting services is installed on a stand alone server (not belong to any
domain, no AD set up) running on Windows 2003 server. Currently I configured
the "Report Manager" and "ReportServer" web site to allow annonyous access.
However some users know how to bypass the web application and directly enter
the IP address/reports of the stand alone server to view all the reports.
The asp.net page has built-in security to filter out the reports listed on
the page based on the user login and password. This web application is also
hosted at the stand alone server and is connected to a remote Sql server.
The web application is using the sql security.I have a similar problem and cannot find a solution provided by Microsoft.
However, I decidee to solve it by a trick. I develop an ASP.net web
application and the application have some report printing function. Whenever
a report is printed, a random key will be inserted into a database table
like this:
Key User
KE#n-asdfjk-ad33klj-kasjd WBush
The key and user are also passed in the query string. When the report is
run, the key and user will be checked again in the stored procedure to
verify that this user can print this report.
I am not sure whether this is a good solution, but I think that's what we
can do before Microsoft can provide a better solution.
"Paul" <paul_mak@.hotmail.com> wrote in message
news:eWZLuvhdGHA.2456@.TK2MSFTNGP04.phx.gbl...
> I need to set up the security on the Reporting Services such that user can
> view the reports ONLY through a ReportViewer control on a asp.net web page
> ie not allow to navigate all other folders or areas in the Report Manager.
> Reporting services is installed on a stand alone server (not belong to any
> domain, no AD set up) running on Windows 2003 server. Currently I
configured
> the "Report Manager" and "ReportServer" web site to allow annonyous
access.
> However some users know how to bypass the web application and directly
enter
> the IP address/reports of the stand alone server to view all the reports.
> The asp.net page has built-in security to filter out the reports listed on
> the page based on the user login and password. This web application is
also
> hosted at the stand alone server and is connected to a remote Sql server.
> The web application is using the sql security.
>|||Hi Paul.
The only way I know of is to have your web site's app pool use an identity
that is the only identity that is allowed to hit the reports.
For example, you might set up a service account called "WebApp1SvcAcct".
give that account and administrators group privileges in reporting services,
but not the rest of your users. This might be only half the solution. It's
been a while since we've had to deal with this.
-Tim
"Paul" <paul_mak@.hotmail.com> wrote in message
news:eWZLuvhdGHA.2456@.TK2MSFTNGP04.phx.gbl...
>I need to set up the security on the Reporting Services such that user can
>view the reports ONLY through a ReportViewer control on a asp.net web page
>ie not allow to navigate all other folders or areas in the Report Manager.
>Reporting services is installed on a stand alone server (not belong to any
>domain, no AD set up) running on Windows 2003 server. Currently I
>configured the "Report Manager" and "ReportServer" web site to allow
>annonyous access. However some users know how to bypass the web application
>and directly enter the IP address/reports of the stand alone server to view
>all the reports. The asp.net page has built-in security to filter out the
>reports listed on the page based on the user login and password. This web
>application is also hosted at the stand alone server and is connected to a
>remote Sql server. The web application is using the sql security.
>

how to set permission for a database

hi,

I need to do a security setup in SQL server 2000. the scenario is as follows:

In a team, only one person(Team Leader) should access the particular DB (through Enterprise Manager and rest of the persons in the Team(developers) should access through code.

How to do this.

Thanks and Regards

Sridhar, not sure what you mean by "through code"... executing code in an app? or maintaining proc/trigger code on a database? You're trying to restirct WHAT groups can do, not necesarily thru what tool? (because if a developer has permission to update tables, they can get in thru EM)... In SQL 2005 SP2, you can put a SERVER TRIGGER on LOGONS and probably not allow certain logons in thru certain application names that way.. What type of access are you talking about? Bruce|||

In SQL server 2005 we introduced a new feature that hopefully will help to solve your scenario: digital signatures in SQL Modules.

The general idea is to allow certain permissions only when the call comes directly from a signed module (SP, user defined function, etc.). I strongly recommend reading the following resources:

· BOL

o Understanding Execution Context:http://msdn2.microsoft.com/en-us/library/ms187096.aspx

o Module Signing: http://msdn2.microsoft.com/en-us/library/ms345102.aspx

o ADD SIGNATURE: http://msdn2.microsoft.com/en-us/library/ms181700.aspx

· Laurentiu Cristofor’s Blog:

o http://blogs.msdn.com/lcris/archive/2005/06/15/429631.aspx

o http://blogs.msdn.com/lcris/archive/tags/SQL+Server+-+cryptography/default.aspx?p=2

· Raul Garcia’s blog:

o http://blogs.msdn.com/raulga/archive/tags/SQL+Server+Signatures/default.aspx

Feel free to ask us any question or feedback on this topic. We will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

hi bruce,

1. In my setup there are some developers for a project accessing the database through th EM and once the same once the same project implemented in production level, it should not get accessed through the EM (either they may only view the tables not edit/delete). how to do this?

2. What permission should be given to database so that throgh the application it can be edited/deleted/updated and not through the EM.

|||

I strongly recommend reading the following thread. Here we discuss in detail about application security, and from your last post, I think it is something similar to what you are asking for:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=775364&SiteID=1

Please let us know if this information was useful.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine