Quick ways to test OLE DB Connection String

The following method has proven useful for me. It's super quick and practical and doesn't require PowerShell:

• Open up Notepad and create an empty text file, then click File -> click Save -> and save it with the File name: TestConnection.udl to your desktop.
• Go to your desktop and double-click on the TestConnection.udl file you just created and the Data Link Properties box will popup.
• Select the Provider tab and Find the provider that you want to connect with and click Next >>.
• Now from the Connection tab, select or enter your source/ server name -> then enter information to log on to server -> and select the database on the server.
• Click Test Connection and click OK to save the file. If errors occur during testing of your connection string, you will get a popup box with the error message.

---

Walkthrough (same as above but with images)

Open up Notepad and create an empty text file, then click File -> click Save -and save it with the File name: TestConnection.udl to your desktop:

Go to your desktop and double-click on the TestConnection.udl file you just created and the Data Link Properties box will popup.

Select the Provider tab and Find the provider that you want to connect with and click Next >>.

Now from the Connection tab, select or enter your source/ server name -> then enter information to log on to server -> and select the database on the server.

Click Test Connection and click OK to save the file. If errors occur during testing of your connection string, you will get a popup box with the error message.

Source

If the client has PowerShell installed (a given if they are running Windows 7 or Windows Server 2008 R2), then you can execute these commands from a PowerShell console window:

$conn = New-Object System.Data.OleDb.OleDbConnection
$conn.ConnectionString = "Provider=Search.CollatorDSO" # whatever you are testing
$conn.Open()
$conn.Close()

SQL Server Native Connection string testing in PowerShell

This method works in Powershell for testing an SQL Server Native connection string (the type that might work with a SQL Server database and be used in a web.config file). Note that there's no Provider=xxxx at the start of this connection string.

First: Start → Run → PowerShell

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source=(local)\SQLExpress;Initial Catalog=MyDatabase;Persist Security Info=True;User ID=MyDBUser;Password=IShouldBeUsingIntegratedSecurity!;MultipleActiveResultSets=True;User Instance=False"

# If no error occurs here, then connection was successful.
$conn.Open();
$conn.Close();

SqlConnection Class documentation.

Kudos to @Chuck Heatherly, on whose example this is based.

Note: I'm fully aware that this question is for OLEDB, however having come here looking for a means of doing this for a SQL Native connection, this might be of use to others who arrive here with the same goal.