Microsoft SQL Server

Connecting Microsoft SQL Server to vScope enables comprehensive asset discovery and monitoring across Database Systems, Databases, User Accounts, and Jobs. This guide outlines the setup requirements, credential configurations, and advanced discovery options for Microsoft SQL Server in vScope.

---

Minimum Permissions

To allow vScope to inventory SQL Server instances effectively, ensure that the following permissions are granted to the SQL credential:

• Access to SERVERPROPERTY settings for system properties (e.g., Edition, MachineName).
• Read access on the following system databases:
  • master
  • msdb
  • model
  • sys
• Permissions to execute system stored procedures:
  • sp_who, sp_who2, sp_readerrorlog, xp_enumerrorlogs, sp_spaceused

---

Setting Up Authentication

In vScope, SQL Server authentication can be managed using:

• SQL Server Authentication
• Windows Authentication

Configure your preferred method under Advanced when creating the SQL credential.

---

Overview of Connection Methods

vScope supports two methods for connecting to Microsoft SQL Server instances:

• Single Credential: Use the same credential for every instance on the SQL server.
• Multiple Credentials: Use different credentials for different instances on the SQL server.

Requirements:

• Credential with read permissions (see minimum permissions guide).
• Target (IP or hostname) of the SQL Server.

---

Using a Single Credential for All Instances (Recommended)

This is ideal for setups with Windows Authentication. Simply add the SQL Server with default port 1434 and let vScope use the credential across all instances.

1. Enable SQL Server Browser Service.
2. In Advanced Settings, disable “Only Scan Instances On This Port” to automatically inventory multiple instances.

Tip

Enable Use SQL Server Browser to Find Other Instances to allow the credential to contribute with targets for other credentials.

---

Using Multiple Credentials for Separate Instances

When specific credentials are needed per instance, configure each instance individually:

1. Enable “Only Scan Instances On This Port” for the targeted instance port.
2. Add a description for each credential to track which instance it relates to.

---

Encryption Settings

Use Encryption:
If your SQL database supports encryption, select Use Encryption in the credential settings. Note that vScope will only connect to databases with encryption enabled.

Tip

Use multiple credentials with varied encryption settings for databases with differing security requirements.

---

Minimum Permissions User in SSMS

1. Open SQL Server Management Studio (SSMS).

2. Under Security > Logins, add a new user (vscopesqluser) and configure:

   • Server Roles: Select the public role.
   • User Mapping: Map vscopesqluser to the public role on all databases; map db_datareader and SQLAgentReaderRole on msdb.

3. Grant the following server permissions:

   • Alter trace
   • Connect SQL
   • View any definition
   • View server state

4. Set Execute permission on xp_readerrorlog and xp_enumerrorlogs in the master database.

5. In msdb, enable Execute on sp_help_job for job information retrieval.

Following these steps ensures that your SQL setup is optimized for vScope discovery. For each new database added, remember to assign the public role to vscopesqluser.

---

Find the Instance Port for Microsoft SQL Integration

When integrating Microsoft SQL Server with vScope, it’s essential to know the exact port that each SQL instance is listening on. This guide shows how to locate the listening port using SQL Server Configuration Manager and how to test connectivity.

---

Finding the SQL Server Instance Port

1. Open SQL Server Configuration Manager
   On the machine running the SQL Server instance, open SQL Server Configuration Manager.

2. Navigate to SQL Server Network Configuration

   • Expand SQL Server Network Configuration in the left pane.
   • Select Protocols for MSSQLSERVER (replace MSSQLSERVER with the name of the specific instance you are targeting).

3. Access TCP/IP Properties

   • Right-click TCP/IP and choose Properties.
   • In the Properties dialog, switch to the IP Addresses tab.

4. Locate the Port

   • Scroll to the IPAll section.
   • Check the values in TCP Dynamic Ports or TCP Port. These fields show the port on which the instance is listening.

In the example above, the SQL instance is listening on port 49168.

---

Testing SQL Server Port Accessibility

To ensure the SQL Server instance is accessible to vScope, you can test the port with telnet from the vScope machine. Here’s how to do it in PowerShell:

1. Open PowerShell

   • Make sure the telnet feature is installed on your machine.

2. Run Telnet Test
   Run the following command, replacing [SQL_Server_IP] and [Port_Number] with your SQL Server’s IP address and the port you found in the steps above:

   telnet [SQL_Server_IP] [Port_Number]

Tip

A successful connection shows a blank screen, indicating that the port is open. To close the connection, type any random character to receive a disconnect message. If you see a “Connection failed” message, there is likely a firewall or network configuration blocking the port.

---

Common Errors

Error	What happened?	Suggested action
Connection refused: connect	vScope could not connect to the database.	Ensure that the vScope server can communicate with the database and that the correct ports are used.
No valid credentials: [Login timed out.]	vScope could not login.	Ensure that the credential has access to the database. Is the credential using the recommended format “domain\…”?
Network Error IOException	no database server is listening on the specified port for the IP address	Follow Troubleshooting Network Error IOException

Network Error IOException: Connection Refused: Connect

If you encounter this error, it indicates that no database server is listening on the specified port for the IP address. To troubleshoot, check the following:

1. Correct Server Name/IP: Ensure you’re using the correct server name or IP for the SQL Server.

2. SQL Server TCP/IP Configuration:

   • Open SQL Server Configuration Manager.
   • Enable TCP/IP under SQL Server Network Configuration > Protocol for [InstanceName].
   • In TCP/IP Properties > IP Addresses, set TCP Port to 1433.
   • Restart the SQL Server service.

3. Firewall Configuration: Verify that port 1433 is open on any firewalls between vScope and the SQL Server.

4. Dynamic Ports: If using dynamic ports, enable the SQL Server Browser Service. Refer to this Discovery of SQL instanses on dynamic ports.

Login is from an untrusted domain…

Discovery of MS SQL may result in an error message “The login is from an untrusted domain…”. This error message is known to also appear when trying to connect with incorrect credentials.

1. Ensure that username & password is correct

2. Investigate if the account has been locked out, either in AD or SQL server

3. Make sure that the account has sufficient permission to read the database - Recommended Roles: public & sysadmin

SSPI handshake failed with error code 0x8009030c

Error message MS SQL servers are reporting the following error message: DESCRIPTION:SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed [CLIENT: IP OF VSCOPE SERVER]

Solution

The error message indicates that the issue is related to the user account. Ensure that the credential is working as intended and have permission to access the SQL resource.