First Time setting up a Connection String 1

[weigoldk]

I'd like to make it easy for my users to run reports that are stored on a file share. I can set up an ODBC connection as a System DSN and it works fine. I was hoping to store that connection information right in the report. Is this possible? We don't have a crystal server. Crystal reports is installed on the PCs and the database itself is SQL (on SQL Server 2008).

Here's my System DSN info and it works great (yet, I don't want to HAVE to use it)
Name: mydatabasename
server: my-app-server
I use SQL server authentication
check box in connect SQL server to obtain default settings for the additional configuration options
Login ID: reportuser
Password: password
Change database to mydatabasename

I tried entering the connection string as this,

Server=192.168.3.54;Database=mydatabasename;User Id=reportuser;Password=password;

but the next page still prompts for a password and then it says Logon failed. Details: Cannot find DSN in connection string.

Any guidance would be very much appreciated.

 

[PeterDimitrov]

The connection string you are using looks like an OLE DB connection. DSN is saved as a flat text file and password will be not included for security reasons. There are 2 ways to connect using ODBC without the user to be prompted for password: use integrated security OR set the password programmatically before to run the report. There is also an option to edit the DSN file and add a line for the password, but this will be accessible for everybody and will basically bypass your security. I am not sure if using 3rd party tools is an option for you. There are some free or inexpensive tools. Ken Hamady has a good list of viewers on his website:

http://kenhamady.com/viewerfeaturematrix.pdf

I don't know how many users do you have , but for about $25 per license you can have a set of network enabled viewers , which could be managed from a central place. You can use also free viewers, but the configuration will take more time ( for example in order to use a report by 20 users you might need to configure it manually on each of the computers)

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[weigoldk]

Thanks for the quick reply.

Sorry, I wasn't clear. The username and password do not need to be kept secret from the users. They have full access via their login to the SQL application. In addition to that, my reportuser is very locked down.

I like the idea of editing the DSN file but I have absolutely no idea where that file exists? We get Crystal Reports for free so I'd like to stick with using that.

 

[PeterDimitrov]

I am not sure why would you prefer open password instead of integrated security, but it is your choice. Search your hard drive for a file named <ODBC name>.dsn
If you cannot find it open ODBC DataSource Administrator and select File DSN, press the button Add and create a new DSN file. By default it will be created in My Documents folder but you can change the destination. Edit the file with Notepad and add line like this one:
PWD=xxxx

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[weigoldk]

Thanks yet again. Maybe I just need some more guidance. I would like to learn about integrated security, but I don't know where to start and don't want to buy another product. Can you point me to a good resource on the topic of integrated security as it relates to Crystal Reports and SQL databases?

 

[PeterDimitrov]

Integrated security is using the credentials of the user who is currently logged in Windows. Obviously when you login to Windows the system will identify you so when you try to connect to the SQLServer it can use the same credentials. You do not need to purchase additional products, just create a login for each of the users in SQLServer. If the users are able to connect and query the data in SQLServer (for example using SSMS) then obviously they have logins and you don't need to do anything.
You can check how to create a login here:

http://msdn.microsoft.com/en-us/library/aa337562.aspx

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[weigoldk]

Thank you for taking the time to make it so clear. I really appreciate it.