Test OLE DB connectivity to SQL Server by using a UDL file

Artikel
06/20/2023

Applies to: SQL Server

Note

Before you start troubleshooting, check the prerequisites and go through the checklist.

This article describes how to create a Universal Data Link (UDL) file and use different providers to test the connection to a SQL Server instance through the file.

Create a UDL file

Note

A UDL file enables you to test Object Linking and Embedding Database (OLE DB) providers connectivity to any backend database independent of a full application. Saving a UDL file generates a well-formed connection string, which you can use to help build an application's connection string or check how to set different properties. To get the string, open the file in Notepad.

To create a UDL file to test your OLE DB provider, follow these steps:

To show file extensions in File Explorer, follow these steps:
1. Select Start, and enter File Explorer Options.
2. Select the View tab, uncheck the Hide extensions for known file types option, and then select OK.
Navigate to the folder where you want to create a UDL file. For example, c:\temp.

Create a new text file (such as sqlconn.txt), and then rename the extension from .txt to .udl. (Select Yes to the warning message about changing the file name extension.)

You can also use the following PowerShell script to create sqlconn.udl in your %temp% folder.

clear
$ServerName = "(local)"
$UDL_String = "[oledb]`r`n; Everything after this line is an OLE DB initstring`r`nProvider=MSOLEDBSQL.1;Integrated Security=SSPI;Persist Security Info=False;User ID=`"`";Initial Catalog=`"`";Data Source=" + $ServerName + ";Initial File Name=`"`";Server SPN=`"`";Authentication=`"`";Access Token=`"`""
Set-Content -Path ($env:temp + "\sqlconn.udl") -Value $UDL_String -Encoding Unicode

#open the UDL
Invoke-Expression ($env:temp + "\sqlconn.udl")

Test the connection by using the SQL Server OLE DB provider

Microsoft OLE DB Provider for SQL Server (SQLOLEDB) is the most common provider. It's built into Windows and can connect to any version of SQL Server that's not configured to require TLS 1.2 channel bindings.

Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) is the latest SQL Server OLE DB provider. The provider has all the new features, such as TLS 1.2, MultiSubnetFailover, and Azure authentication options. We recommend this provider for newer SQL Server databases.

To test the connection by using the SQL Server OLE DB provider, follow these steps:

Open the .udl file.
Select the Provider tab, select the SQL Server OLE DB provider that you use in your application, and then select Next.
On the Connection tab, specify the network protocol, the fully qualified domain name (FQDN), and the port number under Select or enter a server name. For example, tcp:SQLProd01.contoso.com,1433.

Note

This way of entering the server name avoids some common issues (such as the SQL Server Browser service issues) that could interfere with a connection.
Enter other properties on the Connection tab.

Note

Most connection tests don't require you to enter a database name.
Select Test Connection.

You can also select other tabs and explore other driver settings. When the connection test finishes, select OK to save the connection string to a file.

Test connection by using Microsoft OLE DB Provider for ODBC Drivers

To test Open Database Connectivity (ODBC) drivers by using Microsoft OLE DB Provider for ODBC Drivers, follow these steps:

Open the .udl file.
Select Provider > Microsoft OLE DB Provider for ODBC Drivers > Next.
On the Connection tab, you can use one of the following methods to specify the source of data, and then enter other properties.
- Enter a value in the Use data source name field.
- Enter a DSN-less connection string like Driver={ODBC Driver 17 for SQL Server};Server=SQLProd01;Database=Northwind;Trusted_Connection=Yes.
Select Test Connection.

Test 32-bit providers on 64-bit machines

To test the connection of 32-bit providers in 64-bit operating systems, follow these steps:

In the Command Prompt window, run the following command to open the 32-bit Command Prompt:
```
%windir%\SysWoW64\cmd.exe
```
Run the following command to open the UDL file:
```
c:\temp\test.udl
```
If you see Microsoft Jet 4.0 OLE DB Provider on the Provider tab, that means you successfully loaded the 32-bit dialog and can now select the 32-bit provider to test the connection.

Tips to troubleshoot connection issues

You can use the following methods to check why the connection fails.

Change the provider.
Change the protocol. For example, tcp:, np:, or lpc:.
Test the connection with or without the full domain suffix or with just the IP address.
Remove the port number and use the instance name to test the SQL Server Browser service.

For each of the above methods, if one combination works and another fails, it could give a clue as to the problem. For example, when you use the second method, if the lpc: works and the tcp: doesn't, try to enable the TCP protocol in SQL Server Configuration Manager.