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:
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:
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.
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. |
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 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"
Invoke-Sqlcmd -Query "SELECT IS_SRVROLEMEMBER('dbcreator', 'AEG User Account or SQL Login');" -ServerInstance "Server\SQL Instance"
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"
Reason |
Resolution |
---|---|
Query 1 throws an error message after execution. |
|
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). |
Check your certificate installation for SSL issues and vulnerabilities.