Introduction

This article provides a detailed, step-by-step guide on how to test access to a remote Microsoft SQL Server database using a .UDL (Universal Data Link) file. A UDL file is a simple and effective way to verify if a user can access a specific SQL database on a remote server using their account credentials.

To successfully access the SiteAudit database, the user account must have either db_owner rights or be assigned an appropriate SiteAudit Functional Security Role. If a user is unable to access the database to view or monitor data, verify that the user account has the necessary permissions for reading and writing data.

Prerequisites

Before you begin, ensure the following: 

• A Windows computer. 
• Permissions to access the remote SQL Server. 
• Knowledge of the remote SQL server's fully qualified name (i.e., server name and instance) and port number. 
• Appropriate SQL Server login credentials or Windows login credentials.

Steps to Test SQL Database Access Using a .UDL File

1. Create a .UDL File on the Desktop 

1. Open a text editor, such as Notepad. 
2. Save the new file with a .UDL file extension on your Desktop (e.g., TestDatabaseConnection.UDL).

2. Open the .UDL File 

• Double-click the newly created .UDL file on your Desktop to open the Data Link Properties dialog.

3. Configure the Data Link Properties

In the "Provider" Tab:

Select "Microsoft OLE DB Provider for SQL Server".

In the "Connection" Tab:

Server Name: Enter the SQL Server name and instance name in the format serverName\instanceName (e.g., SQLServer01\SQLInstance1). This can also be entered as a FQDN such as mysqlserver.domain.com

Authentication Type: Choose the appropriate authentication method: 

• Windows Authentication: Leave the User name and Password fields blank. 
• SQL Server Authentication: Enter the SQL Server login credentials (username and password).

Select the database on the server: Choose the target database from the drop-down list.

4. Configure Properties for Trusted Connection (If Required)

If the SQL server requires a trusted connection (for encryption and certificate validation), follow these steps: 

1. Select the "All" tab. 
2. Double-click Extended Properties to open the edit dialog. 
3. In the Extended Properties field, enter the following:
   Encrypt=True;TrustServerCertificate=True; 
4. Click OK to save the changes.
   

5. Test the Database Connection 

• Click the Test Connection button. 
• If the connection is successful, a confirmation message will appear. 
• If the connection fails, an error message will display the issue, which you can use to troubleshoot.

6. Retrieve the Connection String (Optional) 

• Open the .UDL file using a text editor (e.g., Notepad). 
• You will see the connection string in the file, which will look similar to:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=YourDatabaseName;Data Source=YourServerName;

Troubleshooting

If the connection test fails, check the error message for details. Common causes for failure include: 

• Incorrect server name, instance name, or port. 
• Invalid SQL Server authentication credentials (username/password). 
• Missing or incorrect permissions for the user account. 
• Incorrect encryption or trust certificate settings. 
• Issues with network connectivity or firewall settings. 
• If necessary, share the error message and connection details with your IT department or database administrator for further investigation.