Testing database connectivity by using a Universal Data Link file

After installing a SQL instance, we may need to test the database connectivity from a client host to make sure that everything is working very well, for instance, sometimes Windows Firewall might block access to the service or there might be network issues. Moreover, in the likely event that there was no time to install SQL Server client tools such as SSMS or SQLCMD to carry out the test, you woud be a bit suprised to know that there is a simpler way to do it, that is via a Data Link file. Consequently, in this post I am going to show you how to create and use a Data Link file to test connectivity to a SQL instance. To begin with, you must open Notepad to create an empty .txt file and save it with the .udl extension as you can see in the following picture.

After doing that, you must open the .udl file and you will then see the following window with four tabs. The second tab "Connection" is to fill with the server name (or SQL instance name) and the credentials accordingly. For instance, I am testing the connectivity to a default SQL instance and using Windows Authentication. You must modify that to serve your needs.

In the first tab "Provider" we can choose the Provider to use in the test. By default, it is always "Microsoft OLE DB Provider for SQL Server". It is ideal to test other providers as well. It is worth noting that SQL Native Providers will be listed if SQL Client tools are installed locally in the client host from where the test is done. In the tab "Advanced" it is possible to set the timeout value whereas in the tab "All" we can see a summary of all the settings, and we can also edit the values of some important connection parameters such as "Language", "Connect Timeout", "Packet Size", "Data Source" and "Initial Catalog".

Finally, we just have to click on "Test Connection" in the tab "Connection" to proceed with the test. If the connectivity to the SQL instance is ok, you will see the message "Test connection succeeded", it couldn't have been simpler!

That is all for now. I hope you find this post helpful and practical. Let me know any remarks you ma have. Stay tuned.

Ports and Protocols Used by Microsoft SQL Server

Naturally, I have been asked many times about ports used by SQL Server services and to be honest sometimes I took me some time to reply because there is a great number of ports and protocols and it is not easy to remember them quickly. Not all of us have to learn everything by rote, so thinking about it, I made the decision of sharing the following lists of useful ports and protocols so that you can have them at hand when needed.

Ports and Protocols Used by Microsoft SQL Server 2000
Service / Purpose	Protocol	Port
Analysis Services	TCP	2725
Client connections when "hide server" option enabled	TCP	2433
Clients using Named Pipes over Netbios	TCP	139/445
Microsoft SQL Monitor port	UDP	1434
OLAP Services connections from downlevel clients OLAP Services 7.0	TCP	2393/2394
SQL over TCP **	TCP	1433
Standard URL for a report server (Reporting Services)	TCP	80 HTTP /443 SSL
Ports and Protocols Used by Microsoft SQL Server 2005
Service / Purpose	Protocol	Port
Analysis Services connections via HTTP (default)	TCP	80
Analysis Services connections via HTTPS (default)	TCP	443
Clients using Named Pipes over Netbios	TCP	137/138/139/445
Dedicated Administrator Connection	TCP	1434 by default (local port). But this port is assigned dynamically by SQL Server during startup.
Reporting services on Windows 2003/2008/Vista (default)	TCP	80
Reporting services on Windows XP SP2	TCP	8080
SQL Server 2005 Analysis Services	TCP	2383
SQL Server Browser Service	TCP	2382
SQL Server Integration Services (MSDTSServer)	TCP	135
SQL Server Resolution Protocol	TCP	1434
SQL over TCP (default instance)	TCP	1433
SQL over TCP (named instances)	TCP	1434 / 1954
Ports and Protocols Used by Microsoft SQL Server 2008/2012/2014/2016/2017
Service / Purpose	Protocol	Port
Analysis Services connections via HTTP (default)	TCP	80
Analysis Services connections via HTTPS (default)	TCP	443
Clustering	UDP	135
Clustering	TCP	135 (RPC) / 3343 (Cluster Network Driver) / 445 SMB / 139 NetBIOS / 5000-5099 (RPC) / 8011-8031 (RPC)
Database Mirroring	TCP	There is no default port for this service. Use the following T-SQL statements to identify which ports are in use: SELECT name, port FROM sys.tcp_endpoints.
Dedicated Administrator Connection	TCP	1434 by default (local port). But this port is assigned dynamically by SQL Server during startup.
Filestream	TCP	139 y 445
Microsoft Distributed Transaction Coordinator (MS DTC)	TCP	135
Reporting services Web Services	TCP	80
Reporting Services configured for use through HTTPS	TCP	1433
Service Broker	TCP	4022
SQL Server Analysis Services	TCP	2382 (SQL Server Browser Services for SSAS port)
		2383 (Clusters will listen only on this port)
SQL Server Browser Service (Database Engine)	UDP	1434. Might be required when using named instances.
SQL Server Browser Service	TCP	2382
SQL Server default instance running over an HTTPS endpoint.	TCP	443
SQL Server Instance (Database Engine) running over an HTTP endpoint.	TCP	80 y 443 (SSL)
SQL Server Integration Services	TCP	135 (DCOM)
SQL over TCP (default instance)	TCP	1433
Transact-SQL Debugger	TCP	135
Windows Management Instrumentation	TCP	135 (DCOM)

That is all for now. I hope you find this post useful. Let me know any remarks you may have. Stay tuned.