Browse
Tools
Categories

Test Access to SiteAudit Database with Universal Data Link

Reference Number: AA-01336 Views: 842 Last Updated: 09-29-2023 11:22 AM 0 Rating/ Voters

Introduction

This support article provides step-by-step instructions for testing access to a remote Microsoft SQL database using a .UDL (Universal Data Link) file. This method is user-friendly way to verify whether a user can access a specific SQL database on a remote server with their account credentials. The credentials used to access the SiteAudit database must have dbowner rights or a be assigned a SiteAudit Functional Security role. If a user account is unable to access the SiteAudit database to view or monitor data, check to ensure the user account has the appropriate rights needed to read/write data. Learn about the various SiteAudit Functional Security Roles that can be assigned to a user account.

One can use a UDL file, which provides a user-friendly way to test database access. To test access to a remote Microsoft SQL database using a .UDL file, follow these step-by-step instructions. 

Prerequisites

  • A Windows computer.
  • Permissions to access the remote SQL server.
  • Knowledge of the remote SQL server's fully qualified name.
  • Appropriate SQL Server login credentials or Windows login credentials.

Steps

  1. Create a .UDL File on the Desktop:

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

  2. Open the .UDL File:

    • Double-click the newly created .UDL file on the Desktop.

  3. Configure the Data Link Properties:

    • In the "Provider" tab:

      • Select "Microsoft OLE DB Provider for SQL Server."
    • In the "Connection" tab:

      • Enter the Server Name in the format serverName\instanceName (e.g., SQLServer01\SQLInstance1) in the "Server or file name" field.
      • Choose authentication type:
        • Windows Authentication: Leave "User name" and "Password" blank.
        • SQL Server Authentication: Enter SQL Server login credentials in "User name" and "Password" fields.
    • In the "Select or enter a database name" field:

      • Choose the target database from the drop-down list.

  4. Test the Database Connection:

    • Click the "Test Connection" button.
    • If the connection is successful, a confirmation message will appear.
    • If unsuccessful, an error message will indicate the issue.

  5. Retrieve Connection String (Optional):

    • Open the .UDL file with a text editor.

    • You'll find a connection string in the file, which may look like:

  6. UDL Response

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


  7. Troubleshooting:

    • If the connection test fails, share the error message and connection details with your IT department or database administrator for further investigation.

Conclusion

Testing database access using a .UDL file offers a straightforward way to check connectivity to a remote Microsoft SQL database. If you encounter any issues during the test, consult your IT team for assistance in resolving them.