Dynamic connection string

Our Reporting Services environment uses Oracle as the data source. Based upon the user connecting to the database determines what rows they will see for various tables. How can we dynamically pass the username/password to the connection string? Background: Our users log into Active Directory and are assigned to a group. The AD group name is used to access a control table in Oracle that contains the database username/password for that group’s connection to Oracle. All subsequent connections to Oracle will use the group’s username/password from the control table. We have an ASP.NET application that works like this and stores the connection information in the session state. How can we do something similar with our connection in Reporting Services? Note: Our Oracle Database does not use Windows Integration.

You can create a private (report-level) data source that uses an expression-based connection string as the ExpressionBasedConnection report in this download demonstrates.|||Thank you Teo for your prompt reply. I will take a look at this.|||

Teo,

I looked over the files, but I can't say that the answer stands out at me. Can you tell me specifically which part that I should look at?

|||The report needs to be set up for a private data source and the data source connection string is expression-based. Look at the way the dataset data source is configured.|||

Thanks Teo. I looked into this but it does not solve our unique situation. In our reports, we logged onto our database as one user. We have other companies that need to access our reports now, but do not want them to see the same information. We are attempting to get the user information and log into the database to retrieve the password for the group that that person belongs to. Then we would like to pass back the group user name and password in our connection string so that the person running the report only sees the information that they are allowed to. Another developer wrote a DLL to attempt to do this. It passes back in the dll but does not work when we try to display the connection string on the report. Do you have any samples to make this work or any suggestions on how we might do this?

|||

Why wouldn't this approach work in your situation? Can't you call the DLL from the connection string expression in the same way and get the user-specific connection string?

As a side note, the right way probably to address a similar requirement is to pass User!UserID to your database and let the database implememt row-level security based on the user identity. With this approach you don't have to deal with connection strings at all.

|||Our biggest problem is that we need to take the ID from Active Directory and find the groups that they are assigned to. So far we have been unsuccessful in making this work.