Test a Connection via a UDL File

What is a UDL File For?

A UDL file (Universal Data Link) allows you to test OLE DB Provider connectivity to any backend database independent of a full application. For connectivity to SQL Server, it may be useful to identify SQL Browser issues, enabled/disabled communication protocols, or a firewall/antivirus blocking access to a port. Saving a UDL file generates a well-formed connection string, which you can see if you open the file in Notepad. You can use it to help build an application’s connection string or check how to set various properties.

Where Do I Get it From?

No external components are required. It is a feature of the OLE DB infrastructure in Windows.

Creating a UDL File

To create a UDL file, right click in any folder (or the Desktop), select 'New', and then 'Text document'. Rename the document to the desired name, and change the file extension to .UDL; for example, TEST.UDL. As soon as you rename it, the text file icon should change to the UDL icon.

Note: Be sure the Windows Explorer setting Hide extensions for known file types is disabled or File Name Extensions is checked in Windows Explorer's View menu.

Choosing a Provider

There are a number of Providers for SQL Server. Click on the Providers tab to select a Provider. The most common Provider selected is the Microsoft OLE DB Provider for SQL Server (Provider=SQLOLEDB). This is the SQL 2000 OLE DB Provider that comes on every version of Windows. It can connect to any version of SQL Server (2000-2019) that is not configured to require TLS 1.2 channel bindings. This is good for a basic connection test.

Once selected, you can click the Next and set the connection string properties. A good test will specify the network protocol (tcp: - always use lower-case letters), the fully qualified server name, and the port number (,1433 in this case). Fill in the authentication block and click Test Connection. Most connection tests do not require you to enter a database name, though you can enter one if you want.

This way of entering the server name avoids some common issues that could interfere with a connection, such as a SQL Alias, incorrect domain suffixes if entering just the NETBIOS name, and SQL Browser service problems if connecting to a named instance (we are using the port number instead of the instance name).

The second dialog above uses the latest SQL Server OLE DB Provider, the Microsoft OLE DB Driver for SQL Server (Provider=MSOLEDBSQL). Note the difference in names is Provider (old) vs. Driver (new), even though both are OLE DB Providers. The new OLE DB Provider has all the new features, such as TLS 1.2, MultiSubnetFailover, and Azure authentication options, and is the recommended Provider for newer SQL Server databases.

You can also select the other tabs and explore other driver settings. When you are finished testing the connection, click OK to save the connection string to the file. Open the file in Notepad and you have a valid connection string.

Testing ODBC Drivers

You can also test ODBC Drivers by selecting the Microsoft OLE DB Provider for ODBC Drivers. Enter the Data Source Name (DSN) and complete the remaining properties.

You can also enter a DSN-less connection string, e.g.

Driver={ODBC Driver 17 for SQL Server};Server=SQLProd01;Database=Northwind;Trusted_Connection=Yes

Test 32-bit Providers on 64-bit Machines

Run the 32-bit command-line: c:\windows\syswow64\cmd.exe
Open UDL file: start c:\temp\test.udl

If you see the Microsoft Jet 4.0 OLE DB Provider, you are successful in loading the 32-bit dialog.

Connection Permutations

You can try different permutations to explore why a connection might be failing. Here are some things you can try:

1. Change the Provider: SQLOLEDB, SQLNCLI11, MSOLEDBSQL
2. Change the protocol: tcp:, np:, lpc:. This latter is for connections to a local SQL Server only.
3. Try connecting with and without the full domain suffix or just using the IP address.
4. Try removing the port number and using the instance name to test the SQL Browser and see if it is working.

If one combination works and another fails, it could give a clue as to the problem, e.g. if lpc: works and tcp: does not, try enabling TCP in the SQL Server Configuration Manager.

References

https://docs.microsoft.com/en-us/sql/connect/oledb/help-topics/data-link-pages?view=sql-server-ver15