TN WW211 Testing SQL Server Connectivity Problems Using a Microsoft UDL

Description

This article from InSource shows how to test SQL Server Connectivity Problems Using a Microsoft UDL

• Author: Peter Farrell
• Published: 06/13/2018
• Applies to: SQL Server all versions upto and including 2018

Details

If you suspect that a Wonderware client application might have failed with a database/datasource connectivity problem, the first thing to try is to look at is the Wonderware logger for evidence of a cause. However, if you do not find information in the logger that indicates a root cause, there is a quick and simple way to test the connection using a Microsoft Universal Data Link file.

It can be quite fiddly to determine if your user account has permission sufficient to connect to the data source, or if your username and password are correct, if your issue is with a client side failure, server side, etc. A quick test using a Universal Data Link (UDL) file can help accelerate your troubleshooting of the problem.

Following are the steps necessary to create and test your SQL connection issue using a Microsoft Universal Data Link file.

1. Get to your Windows desktop and then right click anywhere on an empty space. A pane will appear from which you can select "New". A second pane will display from which you should choose “Text Document.”
   
2. An icon representing the new text document will now appear on your desktop with an extension of "txt." 
   
   Rename the text document to anything you like, but the file extension must be "UDL."
   
   This is IMPORTANT or you will not be able to move onto the next step. When you hit enter to apply the new filename and extension of UDL, you will be presented with a popup warning;
   
   “If you change a file name extension, the file might become unusable. Are you sure you want to change it?”
   
   Click “Yes”
    
3. The icon on your desktop will change to look like the icon below.
   
    
4. Double click on the new UDL icon on your desktop and a Data Link Properties window should appear.
   
   
   The Data Link Properties dialog will have four tabs;
   Provider
   Connection
   Advanced
   All
    
5. The Data Link Properties always open on the Connection tab. Click on the Provider tab. For most Wonderware related testing, you will usually need to select the “Microsoft OLE DB Provider for SQL Server” data source.
   
6. Click on the “Connection” tab.
   
   You should be able to click the drop down arrow in section 1 to browse to the SQL Server for which you want to perform your test. If your server does not appear, this might indicate that the SQL Server to which you are attempting to connect is not running, or is otherwise unreachable.
   
   In section 2 choose the account type to which you are trying to connect, and if you are not using Windows NT integrated security, enter the password for your SQL Server security account.
   
   A common mistake is to type a Windows/Domain user like "mydomain\myuser". This will not work. You can only type a SQL Server Username like "sa" etc. If you select the "Use Windows NT Integrated security" radio button, then the Username and Password text boxes will be disabled and the credential that you used to log on to the machine will be used while connecting to SQL Server.
   
   Important - You should ensure that your SQL Server is in "mixed" mode to use a SQL Server username (this is usually the case if SQL Server was installed for a Wonderware Historian or Galaxy).
    
7. You can now test the connection by clicking the “Test Connection” button. If the connection works, you will see a popup pane reporting “Test connection succeeded.”
   
   If the connection fails, you will see an error level popup reporting, “Test connection failed because of an error in initializing provider. Login failed for user ‘[user name]’.”
   
   You should go back and fix the username and/or password and try to test the connection again. If you are unable to get a connection to work using your account and password, you or your IT/DBA may be able to resolve the issue through Microsoft SQL Server Management console by correcting your user account and/or password.
    
8. If the connection succeeds, you should be able to click the drop-down arrow in section 3 to enumerate the databases created on the SQL Server to which you have connected. If you don’t see the correct database, then you may be connecting to the wrong SQL Server machine.