I received an Email from a desperate customer this weekend. They are on the last few months out of a legacy system and had to improve security to the database by requiring a password for running Crystal reports. That  isn’t normally a problem because Crystal will open the User/PW dialogue.  But they run their reports through the legacy application. When this application tries to run a report that requires a password dialogue, the report viewer crashes.  I am told that they can’t use integrated authentication and the also can’t rewrite the legacy application.  So they were looking for a way to store the passwords in the report so that there wasn’t a password dialogue.  I have been asked this before  and thought there was a way to store the password in the connection, so I did some research on their options:

1) No, there is no way that an RPT will store a password.  This is has always been true of Crystal, and it is by design.

2) It appears that in the past you could create an ODBC DSN that stored passwords. This is no longer allowed for security reasons.  To be sure I created a file DSN and specified the user, password and database.  At runtime the login dialogue showed the stored user and database, but ignored the password.  Most of my customers people solve this problem with integrated authentication, where your DB login is tied to your network login.

Actually, this was the first time I have created a File DSN so I learned something new.  A File DSN is a small text file with the extension DSN.  You write it in any text editor. It has the same information that you use when you create a system DSN.  The difference is that a normal DSN’s info is stored in the registry, while a file DSN is kept in a text file.  It can therefore be copied from one PC to another or stored on a central server to be read by many different workstations.  It reminds me of a UDL (see below)

3) If you use OLEDB instead of ODBC you can create a UDL file.  This is a small text file that has the extension UDL.  You create it as a new text file and change the extension. When you right-click on a UDL file you see a “properties”  option that opens a special dialogue where you can enter connection information. Using a UDL file is one option for this customer to get up and running.  This is because a UDL can remember the password for the database, unlike a File DSN.  But it isn’t an ideal solution because the password is stored in plain text.

4) A last resort option is for this customer to deploy a client based viewer. The down side is that they would have to run the reports outside of the application.  With this option they can either have the user enter the password, or they can use a viewer that can encrypt and remember the password.

This customer chose to start with a UDL, because it got them up and running immediately.  If they later decide it is too much of a risk to store the passwords in the file they will move to a viewer.

Related Links:
DSNs and UDLs
Creating a File DSN
Creating a UDL
Microsoft on creating UDL files