How to Test Connectivity and Permissions to your Database Server

How to Test Connectivity and Permissions to your Database Server

Introduction

In this article, we will provide some useful tools and commands that you can use to test connectivity from your AEG server to your database server. Testing connectivity to your database server is crucial in the following scenarios:

  • You are working on a Proof of Concept (POC) stage with our Engineers and want to make sure that connectivity is working appropriately.
  • You are working on implementing the AEG in your production environment and want to make sure that connectivity is working adequately.
  • You believe the AEG server is not synchronizing with the database server, it is not showing the settings and features in the AEG Portal, or you find error logs in the Windows Event Viewer that are related to database connectivity.

Telnet

A basic test you can run from your AEG server is using Telnet to try connecting to your SQL port. A network port could be blocked now due to a recent change in your network, even though it was unblocked in the past.

You can verify that you can run the Telnet Client on your server just by typing telnet and pressing Enter from an Elevated Command Prompt (Run as Administrator). You should see a window like the following:

If you receive an error instead, you can enable the Telnet Client by going to Server Manager > Add Roles and Features > Click Next until you reach Features > Tick the box next to Telnet Client > Click Next until you reach Install > Finish the installation.

Once you have verified that Telnet is running on your server, run the following command:
telnet <FQDN of your database server> <TCP Port used to communicate with your database server>

In the following example, we assume the TCP port to connect to your database server is 1433:

If you receive a blank window like the following after executing the previous command, it means the TCP port and access to your database is possible.

If, instead you receive an error like the following it means that connectivity is not possible and there may be one or reasons for this issue.

Reasons for failed connectivity and ways to fix them:

Reason
Resolution
The database server is down, inaccessible or unreachable. Check with your System/Database Admin if the database server is operating and functional, it could be overloaded and may need additional resources. This is mainly possible when your AEG database instance is running in a cluster where resources are being shared.

The database server does not have an Inbound rule to allow traffic through the specified network port, or the rule may have suffered a change and is now inactive.

Check the Windows Firewall with Advanced Security and verify if there is an existing and active Inbound rule for the database port. The rule must include Domain under Profile, TCP and port under Protocols and Ports.
The network path between the AEG server and the database server is being blocked. Check the network connection, including access through the specified port, is being allowed in your network firewall. For this reason, you will need to liaise with your Network/Firewall Admin.

PowerShell

You can use PowerShell to invoke a remote command that will run SQL queries. In this section, we will provide a series of queries that you can remotely execute from your AEG server to test whether the access and permissions are correct in your database server. Make sure you run the following commands using PowerShell as Administrator. Also, make sure you run these queries for both:

  • The AEG Computer Account (YOURDOMAIN\COMPUTERNAME$)
  • The AEG Admin/SQL Account(YOURDOMAIN\UserAccount or YourSQLAccount)

Query 1: Retrieve time and date from your database server

The following query can easily prove if the database service is running on your database server and whether you have access to your instance or not:

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery" -ServerInstance "Server\SQL Instance"

Query 2: Retrieve whether a Windows User Account or SQL Account has the role of “dbcreator” assigned to it

Invoke-Sqlcmd -Query "SELECT IS_SRVROLEMEMBER('dbcreator', 'AEG User Account or SQL Login');" -ServerInstance "Server\SQL Instance"

Query 3: Retrieve the database roles for a Windows User Account or SQL account

Invoke-Sqlcmd -Query "SELECT DP1.name AS DBRole, isnull (DP2.name,'No members') AS DBUser

FROM sys.database_role_members AS DRM

RIGHT OUTER JOIN sys.database_principals AS DP1

ON DRM.role_principal_id = DP1.principal_id

LEFT OUTER JOIN sys.database_principals AS DP2

ON DRM.member_principal_id = DP2.principal_id

WHERE DP1.type = 'R'

AND DP2.name = 'AEG User Account or SQL Login'" -ServerInstance "Server\SQL Instance"

Reasons for failed queries and ways to fix problems:

Reason

Resolution

Query 1 throws an error message after execution.

  • Verify the server name and SQL instance used in the query parameters.

  • It is possible the user/SQL account specified in the query does not have permission to either access the SQL instance or query it. Liaise with your Database Admin to determine what is the root cause.

Query 2 throws a zero value in the results

The user account or the SQL login does not have the role of “dbcreator” assigned to it. Request this change to your Database Admin and test again.

Query 3 results do not show the “db_owner” DB Role

The user account or the SQL login does not have the “db_owner” database role assigned to it. This assignment must be verified by your Database Admin. Check the user account/SQL login and the AEG Computer Account are mapped appropriately under User Mapping in SQL Server Management Studio (SSMS).

Related Articles

GlobalSign System Alerts

View recent system alerts.

View Alerts

Certificate Inventory Tool

Please click the button below to log in or sign up.

Log In - Sign Up

SSL Configuration Test

Check your certificate installation for SSL issues and vulnerabilities.