Connection to a SQL DB

[Former Member]

Good Afternoon,

First time poster so bear with me - I have a, I'm sure basic query, but for love nor money I can not find the solution. Apologies in advance if this has been raised somewhere else:

I have Crystal Reports 13 running as a desktop application and a datasource in the form of a SQL Server DB in a different environment.

My questions are:

• As I need to request a firewall change, what port number does Crystal use to query a DB? Is this the standard 1433?
• On the DB I am querying, what privileges do I need? DBO, read & write? Just Read?
• I have watched several videos with the connections, however some indicate the connection should be through the ODBC Data Source Systems DSN first, whilst others point to the connection wizard in Crystal first.

Any information would be great!

Many Thanks,

James

[former_member183750]

First thing to realize about connecting to MS SQL Server is that the version of the client is very, very important:

If OLE DB use:

MS SQL 2005 - OLE DB Provider

MS SQL 2008 - SQL Native 10

MS SQL 2012 - SQL Native 11

MS SQL 2013 - SQL Native 11

If ODBC use:

MS SQL 2005 - SQL Native

MS SQL 2008 - SQL Native 10

MS SQL 2012 - SQL Native 11

MS SQL 2013 - SQL Native 11

As far as port - what ever the ODBC / OLE DB driver (client) can read, CR will be able to read. E.g.; if you can establish the connection in ODBC or UDL, then you can establish a connection to CR. As far as permissions, read should be fine as CR never, ever write to any database.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[former_member11696]

To expand on this...

CR does not have a DB client installed, we assume the Client has been installed and configured and is working. So if any Client software can connect then CR should be able to also.

The user logging to run reports requires permission on Owner/read/write because the users will need to create temp tables to store the data server side. CR does NOT require permission to create Temp tables, just the user.

CR fully supports DB security, if the user does not have rights to access a database/table then that user will not be able to run the report, or create it off those tables.

Often Report Designers will create a "CR User" and add that user account to the DB Server.

Another way is to use Trusted Authentication ( option in the DB Wizard ). Security is all managed by the DBA.

Don

PS - Technically CR does not require write permissions but some people do create Commands that write data and other things in which case the user needs permissions to do this.

If you look at Profilers you will see it's CR Designer accessing the Database.