Simplifying ADO Database Connection Strings

NeoMatrix Plant Automation Tech Tip

Connecting SCADA systems and other applications to databases have been common for quite some time. SCADA platforms from GE, Rockwell, and Wonderware for instance, require that connection to be made utilizing a connection string. A database connection string is simply a string variable containing all the detail for an application to connect to the database. While there are many different resources which define the format of a connection string, creating one can prove to be frustrating as the syntax needs to be perfect in order to work.

The following steps are a foolproof way to create a connection string utilizing a wizard driven interface right from your Windows desktop utilizing a Microsoft Universal Data Link File (UDL). A UDL file is nothing but a text file which contains all the database information as a connection string in it. Interestingly it’s very easy to setup an UDL file and get the required connection string from that.

How to Create the UDL File and Get the Database Connection String

Step 1: Create the UDL File

  1. Create a blank text file using notepad.
  2. Save it by providing any name with an extension *.UDL. Let’s name it as “conn.UDL”. Before that please ensure the Save as Type should be kept as “All Files”, else the file will be saved as Conn.UDL.txt.

database connection string

Step 2: Configure the UDL File

Now close the blank text file and go to the directory containing the conn.UDL file. You will see that the icon has changed as Windows recognizes the file as a universal data link file.

database connection string

  1. Double click on the file and you can see a window named “Data linked properties”. On the provider tab we can find several different OLE DB providers.
    database connection string
  2. Choose the suitable provider that we want. Let’s select “Microsoft OLE DB provider for SQL Server” as my application connects to the SQL server database.
  3. Then Click “Next>>” button, that will take us to the connection tab. There we need to select or provide a server name authentication information and database. Please note that these properties will be different depending on the database provider you are connecting to. If we provide UserID and Password, then we need to check the “Allow Saving Password check box”. Then select the database from the server that we select.
    database connection string
  4. After the things are completed, click on the “Test Connection” button. We will get a message as Test connection succeeded. Click OK to close the dialog box.

Step 3: Copy the Connection String

Now we are ready with the connection string.

  1. Right click on the conn.UDL file and open the file with Notepad.database connection string
  2. As is stated in the file – everything after the second line is the connection string which can be copied and pasted into your application.