July 10, 2003 at 11:54 am
Hi there,
I'm wondering whats the best way to handle a couple of different situations.
We are using Crystal Reports to pull info from a SQL2K database. We also have a full web-based ASP application that connects to a SQL2K database for data entry, etc.
So like most things when you're not sure what to do we've been using the sa account for authorization (gasp, groan! admitting you have a problem is the first step).
Before we release this all primetime I KNOW this practice needs to change.
If anyone has had any experience with the Crystal, ie knowing what it requires the info would be appreciated.
The web-based app is pretty much straight data entry/manipulation with one 'system' level proc that will automatically backup and restore a database.
I dont know if I 'should' create create a serperate account for both reporting and the application and more importantly what level of privs should be assigned.
Regards,
Chris
July 10, 2003 at 7:39 pm
Hi there
I dont know a lot about it, but may be able to help. We here use crystal reports, we use the RDC API and not the enterprise api (which costs evener big$). Anyhow, the crew connects to the database via a DSN, we programmatically control the DSN via this piece of code:
Public Sub SetDataSource(lType)
If CInt(lType) = CInt(DATASOURCE_SQLDIRECT) Then
DataSourceServer= "163.232.xx.xxx"
DataSourceDatabase= "mydb"
DataSourceUser= "mydbuser"
DataSourceUserPassword= "mydbuserpassword"
End If
If CInt(lType) = CInt(DATASOURCE_ODBC) Then
DataSourceServer= "MYDB_ASP_DSN" 'Name of ODBC DSN
DataSourceDatabase= "MYDB"
DataSourceUser= "mydbuser"
DataSourceUserPassword= "mydbuserpassword"
End If
Dim Table
For Each Table In Session("oRpt").Database.Tables
Table.SetLogonInfo CStr(DataSourceServer), CStr(DataSourceDatabase), CStr(DataSourceUser), CStr(DataSourceUserPassword)
Next
End Sub
The DSN is the best way to go to ensure portalbility of your reports between servers. The DB user is hooked to a role I called "app_crystal_reporting".
We have a whole generic framework for ASP based crystal reporting if your interested. Ping me offline to get more help.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 10, 2003 at 9:41 pm
Hey there 🙂
Thats cool, its pretty much what we're doing now... Maybe I didn't phrase the question right. Currently we're using the SA account for all of the mydbuser, which I'm thinking it bad policy.
What I need to know is what privs should I assign to a SQL user to access data thru crystal and thru ASP. I want them to be able to do what they need to do but I dont want some local MIS person in a town hall to walk thru our ASP code and find the SA password sitting there since we dont give them that kind of access to our server at their location.
Thanks,
Chris
July 10, 2003 at 9:59 pm
Hi there
Your problem is that you want to know what objects to grant the crystal user access to id image and avoid doing a code walkthough (by this other person)? I would consider:
a) consider opting for "db_datareader" for the new user account
d) run profiler whilst hitting reports and see what is accessed
take care if you call stored procs, as ive had issues with db_datareader and stored proc execution access.
With a) its easy, create the new user, and if its table/views only you cant go wrong and is a simply fix. One thing with SA is that is has access to all databases, consider this when creating the new user account, do you do cross database queries? if so, you need to bugger around with giving the user access on these other dbs as well.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply