UDL Test – Check Connectivity for SQL Server or Analysis Server (SSAS)

UDL Test: UDL file is a kind of file that will probably help you to test the connection between your application and database. It can be used to test Object Linking and Embedding Database (OLE DB) providers connectivity to any of your backend database independent of any full type of application. Saving a UDL file generates very good and 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.

UDL Test Steps:

In this article we will guide you, how can we check the connectivity of any server. In this page we will see for 3 different of servers and will be listed down below for better convenient. Also, you can refer the document from Microsoft for any confusion. Link: Test OLE DB connectivity to SQL Server by using a UDL file – SQL Server | Microsoft Learn

Create UDL File:

Step 1: Right click on your desktop and open one new text document, as you can see in the below image.

image 51

Step 2: You can give name to the file and also give the file extension name as “.udl” As you can see in the below image.

image 65

Note: If you are not getting this above kind of logo, then you can go to you file explorer and click on view option at the top and check the option “show file name extension”. You can open any file or folder and you will find this option at the top.image 58

Step 3: Once you are done and able to see this logo then you can open it and follow up with the connectivity testing.

SQL Server:

Step 1: Open the file created in the above steps. Select the first tab “Provider” and select the SQL Server option.

image 52

Step 2: Focus the below points:

  1. Give the server’s name or you can just directly click on the drop-down option and list of servers will be listed from your local machine.
  2. Provide the credentials/you can use windows as well.
  3. Select the database you want to make the connectivity and at last click on the test connection.
image 62
Note: You can also use local or IP address of the server with port number to check the connectivity. If you see “PC” is my Machine name and “PCDB” is my SQL Server name.

Step 3: Once you click on test connection, you will get one general kind of error which we can ignore, and you will get Test Connection Succeeded.

image 53
If it is failing then there is some kind of connectivity issue, so need to troubleshoot the issue.

Analysis Services (SSAS):

Step 1: Open the file created in the above steps. Select the first tab “Provider” and select the SQL Server option.

image 63

Step 2: Focus on the points:

  1. Enter the server name of your SQL Server Analysis Services.
  2. Enter your credentials, as you can I am using Windows NT because my SSAS is hosted on this VM. But you can manually give the username and password.
  3. Click on the drop-down menu and you can see the list of models and click on test connection.
image 56

Step 3: Once you click on test connection, you will get one general kind of error which we can ignore and you will get as Test Connection Succeeded.

UDL Test

As you can see after getting the above error our test connection succussed. image 54

Azure Analysis Services (AAS):

Go to your Azure Portal and click on your Instance name and copy the server’s name.

UDL Test for AAS

Step 1: Open the file created in the above steps. Select the first tab “Provider” and select the SQL Server option.

image 60

Step 2: Focus on the points:

  1. Enter the server name of your Azure Analysis Services (AAS).
  2. Enter your credentials, as you can I am NOT using Windows NT because my AAS is hosted on CLOUD. We will manually give the username and password. (Note: MFA must be enabled to test this)
  3. Click on the drop-down menu and you can see the list of models and click on test connection.
UDL Test

Step 3:  Once you click on test connection, you will get one general kind of error which we can ignore, and you will get as Test Connection Succeeded.

image 61

As you can see after getting the above error our test connection succussed. image 55

Conclusion:

UDL is far better application or file which is implemented to check the connectivity issue. Similarly, if you have other kind of data sources such as Snowflake, Synapse, Oracle etc. If you have any other kind of Servers which supports ODBC, you can test the connection. If you have any update or concern, please comment it below.

SQL Bi Links:

  1. Port Number For Analysis Services – Find Ports For SSAS? (kingfishertechtips.in)
  2. SMTP Server In SSRS/PBIRS |Subscribe To Email Problem/Issue (kingfishertechtips.in)
  3. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
  4. Creating Tabular Project & Deploy Model To Analysis Service (kingfishertechtips.in)
  5. Deploy Tabular Project To Azure Analysis Service – SSDT Tool (kingfishertechtips.in)
  6. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
  7. Could Not Load Folder Contents In Portal URLs – SSRSPBIRS (kingfishertechtips.in)
  8. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
  9. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
  10. Capture Fiddler Trace for Debugging the Network Traffic (kingfishertechtips.in)
  11. On-Premises Gateway: Configure/Map SQL DB To Power Bi (kingfishertechtips.in)
  12. Fixed Column/Row Header Make Visible In Any Report (kingfishertechtips.in)
  13. Self-Signed Certificate: Configure SSRS/PBIRS With SSL (kingfishertechtips.in)
  14. Create Local User Account/Local Admin User In Windows 10/11 (kingfishertechtips.in)
  15. Login With AAD/Domain/Microsoft Account To Azure VM’s/RDP (kingfishertechtips.in)
Vickey Rajpoot
Vickey Rajpoot

Hello there! I'm dedicated Microsoft Data & AI Engineer at LTIMindtree, where I thrive technology into actionable insights. Dive into my world by visiting my YouTube channel & Webiste, "Kingfisher Tech Tips".

Articles: 71

Leave a Reply

Your email address will not be published. Required fields are marked *