Database Roles And Connections
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:
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.