Can I use SQL Server security with reporting services?

I'm using SQL Server 2000 SP3 Reporting Services SP2
Currently my reports are configured to use a shared data source reference.
The credentials for the data source are configured to use the same specified
user name and password, which is a login I created for the purpose of
generating the reports.
Is there a way to pass a SQL Server account user name and password to the
data source instead of using a shared account?
I've tried creating a Data Source Credentials object, but it didn't work:
dim dsc as new DataSourceCredentials
dsc.DataSourceName = "dsn"
dsc.UserName = "unm"
dsc.Password = "pwd"
...
result = rs.Render (...,dsc,...)
...
Unfortunately, I am prevented from using integrated security.
The report renders fine using the data source reference pre-configured with
the shared username and password.
Thanks!
BillHere is what I do and I think is the best thing to do. Create a readonly SQL
Server user account. Use that for your shared datasource. Limit your access
to reports using the built in security model (based on roles). If a user has
rights to run the report they they see the data. They don't need to use
their SQL Server login to do so. This will prevent you from having to manage
SQL Server accounts as much. And, it is better for performance.
By using a single, read only account it means that all database access is
happening through that account which allows connection pooling to do its
magic. Connection pooling only works if the connection string is identical.
Establishing connections is expensive. If you go the route you are
suggesting (which is not possible out of the box, you would have to write
your own forms based authentication and integrate it) then every time a new
user requests a report there will be a new connection being established to
the server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BillE" <belgie@.datamti.com> wrote in message
news:ercHCCmCHHA.3924@.TK2MSFTNGP02.phx.gbl...
> I'm using SQL Server 2000 SP3 Reporting Services SP2
> Currently my reports are configured to use a shared data source reference.
> The credentials for the data source are configured to use the same
> specified user name and password, which is a login I created for the
> purpose of generating the reports.
> Is there a way to pass a SQL Server account user name and password to the
> data source instead of using a shared account?
> I've tried creating a Data Source Credentials object, but it didn't work:
> dim dsc as new DataSourceCredentials
> dsc.DataSourceName = "dsn"
> dsc.UserName = "unm"
> dsc.Password = "pwd"
> ...
> result = rs.Render (...,dsc,...)
> ...
> Unfortunately, I am prevented from using integrated security.
> The report renders fine using the data source reference pre-configured
> with the shared username and password.
> Thanks!
> Bill
>
>|||Thanks for your response. This is a grey area for me, and I'm having some
trouble with it.
In the model I'm using, the user selects parameters in a web forms
interface, which builds a URL and submits it to the report server. The
report is rendered in the browser.
If the user bookmarks the report, it can be retrieved at any time from the
report server by opening the bookmark. No login is necessary, since the
report uses the shared login.
I don't see a security context to prevent a user without the appropriate
role membership from accessing a report. Perhaps my entire model is flawed.
Thanks
Bill
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OhCo0bmCHHA.4892@.TK2MSFTNGP04.phx.gbl...
> Here is what I do and I think is the best thing to do. Create a readonly
> SQL Server user account. Use that for your shared datasource. Limit your
> access to reports using the built in security model (based on roles). If a
> user has rights to run the report they they see the data. They don't need
> to use their SQL Server login to do so. This will prevent you from having
> to manage SQL Server accounts as much. And, it is better for performance.
> By using a single, read only account it means that all database access is
> happening through that account which allows connection pooling to do its
> magic. Connection pooling only works if the connection string is
> identical. Establishing connections is expensive. If you go the route you
> are suggesting (which is not possible out of the box, you would have to
> write your own forms based authentication and integrate it) then every
> time a new user requests a report there will be a new connection being
> established to the server.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "BillE" <belgie@.datamti.com> wrote in message
> news:ercHCCmCHHA.3924@.TK2MSFTNGP02.phx.gbl...
>> I'm using SQL Server 2000 SP3 Reporting Services SP2
>> Currently my reports are configured to use a shared data source
>> reference.
>> The credentials for the data source are configured to use the same
>> specified user name and password, which is a login I created for the
>> purpose of generating the reports.
>> Is there a way to pass a SQL Server account user name and password to the
>> data source instead of using a shared account?
>> I've tried creating a Data Source Credentials object, but it didn't work:
>> dim dsc as new DataSourceCredentials
>> dsc.DataSourceName = "dsn"
>> dsc.UserName = "unm"
>> dsc.Password = "pwd"
>> ...
>> result = rs.Render (...,dsc,...)
>> ...
>> Unfortunately, I am prevented from using integrated security.
>> The report renders fine using the data source reference pre-configured
>> with the shared username and password.
>> Thanks!
>> Bill
>>
>|||RS always has a security context. Being able to have a report rendered is
different from the security used to actually get the data. For the report to
be rendered RS has to know who the user is. It then uses its role based
security to decide whether to provide the service. Then it renders the
report using the security specified for getting the data. Two levels of
security. Rights to run/render the report, rights to get the data. You have
been thinking on the latter when really the issue is more the former.
You have three choices.
1. Create your own authentication. This allows you to have the user log to
your app using the SQL Server login and have that be the login to your app.
You have to integrate this into Reporting Services.
2. Use integrated security where the windows credentials are used to
authenticate into RS. Then integrated security is use for the data source of
the reports as well.
3. Use integrated security where the windows credentials are used to
authenticate into RS. Then, use a fixed SQL Server account (one or more) in
the credentials tab of shared data source.
I do and recommend #3. #1 is non-trivial. #2 is lower performing and in your
case you say you can't use.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BillE" <belgie@.datamti.com> wrote in message
news:uEuGchnCHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Thanks for your response. This is a grey area for me, and I'm having some
> trouble with it.
> In the model I'm using, the user selects parameters in a web forms
> interface, which builds a URL and submits it to the report server. The
> report is rendered in the browser.
> If the user bookmarks the report, it can be retrieved at any time from the
> report server by opening the bookmark. No login is necessary, since the
> report uses the shared login.
> I don't see a security context to prevent a user without the appropriate
> role membership from accessing a report. Perhaps my entire model is
> flawed.
> Thanks
> Bill
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OhCo0bmCHHA.4892@.TK2MSFTNGP04.phx.gbl...
>> Here is what I do and I think is the best thing to do. Create a readonly
>> SQL Server user account. Use that for your shared datasource. Limit your
>> access to reports using the built in security model (based on roles). If
>> a user has rights to run the report they they see the data. They don't
>> need to use their SQL Server login to do so. This will prevent you from
>> having to manage SQL Server accounts as much. And, it is better for
>> performance.
>> By using a single, read only account it means that all database access is
>> happening through that account which allows connection pooling to do its
>> magic. Connection pooling only works if the connection string is
>> identical. Establishing connections is expensive. If you go the route you
>> are suggesting (which is not possible out of the box, you would have to
>> write your own forms based authentication and integrate it) then every
>> time a new user requests a report there will be a new connection being
>> established to the server.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "BillE" <belgie@.datamti.com> wrote in message
>> news:ercHCCmCHHA.3924@.TK2MSFTNGP02.phx.gbl...
>> I'm using SQL Server 2000 SP3 Reporting Services SP2
>> Currently my reports are configured to use a shared data source
>> reference.
>> The credentials for the data source are configured to use the same
>> specified user name and password, which is a login I created for the
>> purpose of generating the reports.
>> Is there a way to pass a SQL Server account user name and password to
>> the data source instead of using a shared account?
>> I've tried creating a Data Source Credentials object, but it didn't
>> work:
>> dim dsc as new DataSourceCredentials
>> dsc.DataSourceName = "dsn"
>> dsc.UserName = "unm"
>> dsc.Password = "pwd"
>> ...
>> result = rs.Render (...,dsc,...)
>> ...
>> Unfortunately, I am prevented from using integrated security.
>> The report renders fine using the data source reference pre-configured
>> with the shared username and password.
>> Thanks!
>> Bill
>>
>>
>|||This client has users sharing computers, and they don't want to log off and
log on each time they switch users. However, one user may have access to
some reports which another user doesn't.
I'll have to pursue option #1, or (preferably) convince the client to come
to his senses.
Thanks for your help, I have a clearer understanding of the situation now.
Bill
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%232RmlDoCHHA.4256@.TK2MSFTNGP04.phx.gbl...
> RS always has a security context. Being able to have a report rendered is
> different from the security used to actually get the data. For the report
> to be rendered RS has to know who the user is. It then uses its role based
> security to decide whether to provide the service. Then it renders the
> report using the security specified for getting the data. Two levels of
> security. Rights to run/render the report, rights to get the data. You
> have been thinking on the latter when really the issue is more the former.
> You have three choices.
> 1. Create your own authentication. This allows you to have the user log to
> your app using the SQL Server login and have that be the login to your
> app. You have to integrate this into Reporting Services.
> 2. Use integrated security where the windows credentials are used to
> authenticate into RS. Then integrated security is use for the data source
> of the reports as well.
> 3. Use integrated security where the windows credentials are used to
> authenticate into RS. Then, use a fixed SQL Server account (one or more)
> in the credentials tab of shared data source.
> I do and recommend #3. #1 is non-trivial. #2 is lower performing and in
> your case you say you can't use.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "BillE" <belgie@.datamti.com> wrote in message
> news:uEuGchnCHHA.3228@.TK2MSFTNGP03.phx.gbl...
>> Thanks for your response. This is a grey area for me, and I'm having
>> some trouble with it.
>> In the model I'm using, the user selects parameters in a web forms
>> interface, which builds a URL and submits it to the report server. The
>> report is rendered in the browser.
>> If the user bookmarks the report, it can be retrieved at any time from
>> the report server by opening the bookmark. No login is necessary, since
>> the report uses the shared login.
>> I don't see a security context to prevent a user without the appropriate
>> role membership from accessing a report. Perhaps my entire model is
>> flawed.
>> Thanks
>> Bill
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OhCo0bmCHHA.4892@.TK2MSFTNGP04.phx.gbl...
>> Here is what I do and I think is the best thing to do. Create a readonly
>> SQL Server user account. Use that for your shared datasource. Limit your
>> access to reports using the built in security model (based on roles). If
>> a user has rights to run the report they they see the data. They don't
>> need to use their SQL Server login to do so. This will prevent you from
>> having to manage SQL Server accounts as much. And, it is better for
>> performance.
>> By using a single, read only account it means that all database access
>> is happening through that account which allows connection pooling to do
>> its magic. Connection pooling only works if the connection string is
>> identical. Establishing connections is expensive. If you go the route
>> you are suggesting (which is not possible out of the box, you would have
>> to write your own forms based authentication and integrate it) then
>> every time a new user requests a report there will be a new connection
>> being established to the server.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "BillE" <belgie@.datamti.com> wrote in message
>> news:ercHCCmCHHA.3924@.TK2MSFTNGP02.phx.gbl...
>> I'm using SQL Server 2000 SP3 Reporting Services SP2
>> Currently my reports are configured to use a shared data source
>> reference.
>> The credentials for the data source are configured to use the same
>> specified user name and password, which is a login I created for the
>> purpose of generating the reports.
>> Is there a way to pass a SQL Server account user name and password to
>> the data source instead of using a shared account?
>> I've tried creating a Data Source Credentials object, but it didn't
>> work:
>> dim dsc as new DataSourceCredentials
>> dsc.DataSourceName = "dsn"
>> dsc.UserName = "unm"
>> dsc.Password = "pwd"
>> ...
>> result = rs.Render (...,dsc,...)
>> ...
>> Unfortunately, I am prevented from using integrated security.
>> The report renders fine using the data source reference pre-configured
>> with the shared username and password.
>> Thanks!
>> Bill
>>
>>
>>
>