Aug 19, 2024

How to Test Connectivity and Permissions to your Database Server - Unpublished

Introduction

In this article, we will provide some useful tools and commands that you can use to test connectivity from your Certificate Automation Manager 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 Certificate Automation Manager in your production environment and want to make sure that connectivity is working adequately.
• You believe the Certificate Automation Manager server is not synchronizing with the database server, it is not showing the settings and features in the Certificate Automation Manager 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 Certificate Automation Manager 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:

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 Certificate Automation Manager 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 Certificate Automation Manager Computer Account (YOURDOMAIN\COMPUTERNAME$)
• The Certificate Automation Manager 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', 'Certificate Automation Manager 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 = 'Certificate Automation Manager User Account or SQL Login'" -ServerInstance "Server\SQL Instance"

Reasons for failed queries and ways to fix problems: