Database Roles And Connections

[Former Member]

I am using Visual Studio C# and a SQL Server database.  I am using database application roles for security.  I am trying to allow a user who is restricted to just viewing reports to run a dynamically created Crystal Report from a form.  The application at logon sets the database application role for the user.  The role has permission to execute a stored procedure and select on a table.  The problem is that when the user clicks on the menu item to run the report, I get an error when it tries to add a field to the report.  If I logon as myself, the report is produced.  My privileges on the database are owner of the database (schema).

I believe what is happening is that the Crystal Report makes a new connection to the database as the user logged on, which is different from the original database connection made through the logon form.  Since the user has only permission to connect, I set the database application role right after logon which allows that described in the first paragraph.  But when the report is selected from the menu, a new connection is made for the user.  Since the application role is not set for this connection, the report fails.  It seems to me that the solution would be for Crystal Reports to use the same connection that was established through the logon form.  I do not see how in the API how to do this.  The connection API looks for a server name, a database name, and a parameter which is set to true or false of which I don't recall what it is as I don't have the code in front of me at the moment.  I would prefer to use my SQL Connection which I established at logon but it is a SqlConnection Class.  I don't see a way to do this.

If this is not possible, is there a way to execute the sp_setapprole procedure from within Crystal Reports?

Any help is appreciated.  You can reference my code here:

http://scn.sap.com/thread/3699339

Martin

[Former Member · Accepted solution]

This is in reply to my original post.  I have solved the problem by creating a "reporting" user id in the SQL Server database which only has permission to connect to the database and to execute the stored procedure which returns the result set for the Crystal Report.  Then in my Visual C# code I use the Crystal SDK to change the userid and password for the report to the "reporting" user id.  All of the individual users in the database with the exception of myself, only have permission to connect to the database.  In my application right after logging on, I set an application role for the user.  This application logon role has execute permission on two stored procedures.  One is used to validate the user's username and password which are stored in an application database table. The password is stored in the database table encrypted.  The second stored procedure gets the user's application role and encrypted password for that role from an application table which contains this information.  The application roles are used to determine what privileges each user has in the database.  By extension, they determine what the user can do through the application, such as running reports, data entry, etc.  I then execute the stored procedure to validate the username and password from my code.  If the username is found in the database table and it's password matches, I then let the user through, otherwise I send them an error message and they remain on the log on form. 

After their username and password has been validated, I execute in my code the stored procedure to get the application role that has been assigned to that user as well as the encrypted password for that application role.  I then unset the application role that was set at first.  I decrypt the password for the new role and set that application role.  This role is what controls what the user can do in the database.

Now the user can click on the menu item in the application to run a given report.  As of now I just have one report.  This report is built by the application using the Crystal Reports SDK.  I had to use the SDK as the report has a variable number of columns determined at run time.  Since the Crystal Report makes a separate connection to the database, I use the SDK to change the connection information for the Crystal Report.  I use the username and password for the "reporting" user id which I mentioned in the first paragraph above.  I also set integrated security to false for this connection.  The Crystal Report then connects to the database as the "reporting" user and returns the report.  As I create new reports, I shall give the "reporting" user permission to either execute stored procedures to return data for these new reports or permission to select on the underlying database tables that are needed for the report.

By the way, as soon as the Crystal Report is closed, the database connection for that Crystal Report is closed.

Martin

[former_member11696 · Accepted solution]

Hi Martin,

Great you figured out a work flow that works for your security model... CR does support DB security but we do not handle it in any way, we don't save the password anywhere, that would introduce a hole. Doing it all Server side is the definite way to go...

Nice description on how it works also for others to see one way to handle this...

And yes, as soon as the report object is closed CR does a DB disconnect, another potential hole if we kept it open.

Don

[DellSC]

The only way I can think of to do this would be for the stored proc to run as part of your application, which would put the data into a DataSet.  You would then connect the report to the DataSet instead of to the database.

-Dell

[former_member11696]

Have you tried using a Command Object that also calls the SP?

CR will not piggy back on an existing Db Connection, we make our own. One for each subreport also.

As long as the Dataset is small this should work...

Don

[Former Member]

I prefer not to use a DataSet if I don't have to.  I have found that if I grant execute on the stored procedure to the individual user, the report shows the data.  However I do not want to have to grant execute on every stored procedure for every report to every user whom I deploy the application to.  This is too much maintenance.  Which is why I prefer to use database application roles.  I can just grant execute to every stored procedure (or select on tables) for every report to just the database application role.  Then I can set the application role to the user in my C# application.

[Former Member]

Don,

I am using a Command to execute the SP from within the Crystal Report.  However the user does not have permission to execute the SP.  After the user logs on to the database using the application's log on form, I set the database application role.  The role has permission to execute the SP.

I am now thinking of creating a database logon trigger.  The logon trigger will check the userid of the user logging on to the database by interrogating the application's user table and application's role table.  It will retrieve the database application role and encrypted password for the user, decrypt the encrypted password, and set the database application role.  This role has execute permission on the SP.  The trigger will also do nothing if the userid of the user connecting to the database is mine or another "system admin" type of userid, so that we preserve our permissions to the database.  I just have to make sure that I can decrypt the role's password using the same encryption algorithm which I use in the C# code, in the SQL Server trigger's code.

So what should happen is that when the user clicks the report menu to run the Crystal Report from within the application, the Crystal Report will logon to the database using integrated security.  The database logon trigger will fire and set the application role which gives permission to execute the SP.  The Crystal Report should then be displayed.  Hopefully the sequence of events is 1) Crystal Report logs on to SQL Server, 2) trigger sets the application role, and finally 3) Crystal executes the SP to bring back the data.

Martin

[former_member11696]

Hi Martin,

Once the report starts running you don't have an option to interrupt the DB connection, all this has to be set before you hit the refresh button. All events and data being returned is after the data is retrieved from the data source.

So what you are attempting to do may work because it's all done server side.

It is a good idea though to add this ability to our SDK and CR, if you want add your request in Idea Place. Dev may someday look into it...

Don

[Former Member]

Don,

I have given this solution more thought. With the logon trigger, EVERY log on to the database will fire the trigger.  I have several SSIS packages which have database connection managers.  These packages do various processing against the database, such as loading data, calculations, etc.  I'm afraid that every connection manager when connecting to the database will fire the trigger and possible cause a performance hit.  So I decided to explore another option outlined below.

I am thinking now of using the Crystal SDK to change the report's connection to NOT use Integrated Security and to instead use SQL Server Security by supplying the special userid which I created to just run reports ("Reporter"), it's password, the server name, and the Initial Catalog which is the database name.  Something like this:

boReportDocument.DataSourceConnections[0].SetConnection(GlobalVariables.server, "Retail_Incentive_Plan", "Reporter", "Password");

instead of:

boReportDocument.DataSourceConnections[0].SetConnection(GlobalVariables.server, "Retail_Incentive_Plan", true);

However this is not working.  I get the following error message from within my application when clicking on the report menu item to run the report:

"BranchRankingReport could not be loaded"

This error is produced by me when the form on which I placed the Crystal Report Viewer encounters an error when executing the .Show() function, that is, when the form which contains the CR tries to display it.

How do I use the CR SDK to change the logon parameters including user name and password at run time?  If these parameters can not be changed at run time, then how do I use the SDK to delete the connection and create a new one with the new log on parameters, all at run time?

Martin

[Former Member]

Don,

I have figured out how to change the Crystal Report's logon parameters including user name and password at run time.  Apparently one has to set each table's LogonInfo 's ConnectionInfo in the report document rather than to just change the ConnectionInfo for the ReportClientDocument's DatabaseController's Database's Tables[x].  So all that I needed is the following code:

	CrystalDecisions.Shared.TableLogOnInfo myLogOn;
	myLogOn = new TableLogOnInfo();
	foreach (CrystalDecisions.CrystalReports.Engine.Table myTable in boReportDocument.Database.Tables)
	{
	myLogOn = myTable.LogOnInfo;
	myLogOn.ConnectionInfo.UserID = GlobalVariables.crystal_report_userid;
	myLogOn.ConnectionInfo.Password = GlobalVariables.crystal_report_password;
	myLogOn.ConnectionInfo.ServerName = GlobalVariables.server;
	myLogOn.ConnectionInfo.IntegratedSecurity = false;
	myLogOn.ConnectionInfo.DatabaseName = "Retail_Incentive_Plan";
	myTable.ApplyLogOnInfo(myLogOn);
	}

I created a special user in the SQL Server database called something like "Reporter" which only has permission to connect to the "Retail_Incentive_Plan" database and to execute the stored procedure which returns the data for the Crystal Report.

As you can see from my code, I store the special userid and it's password in the GlobalVariables class.

Martin