Skip to main content

Microsoft SQL Server Database Discovery Reference

This document provides a comprehensive reference for SQL Server database discovery in NopeSight. The database scanner automatically discovers SQL Server instances, databases, connections, and linked servers on Windows systems during WMI scans.

Overview​

The SQL Server database discovery extends Windows WMI scanning to collect comprehensive database infrastructure information. When SQL Server is detected on a scanned Windows server, the scanner automatically gathers instance configuration, database inventory, connection details, and security settings.

Key Benefits​

  • Complete Database Inventory - Automatically discover all SQL Server instances and databases across your infrastructure
  • Dependency Mapping - Identify which applications and servers connect to your databases
  • Security Visibility - Track authentication modes, encryption status, and service accounts
  • Backup Compliance - Monitor backup status and identify databases with missing backups
  • Vulnerability Management - CPE generation enables CVE tracking for SQL Server versions
  • Capacity Planning - Track database sizes and growth patterns

How It Works​

Discovery Flow​

Windows Server WMI Scan
    ↓
SQL Server Detection (Registry Check)
    ↓
Named Instance Discovery
    ↓
For Each Instance:
    ├── Instance Configuration
    ├── Database Inventory
    ├── Connection Analysis
    └── Linked Server Detection
    ↓
CI Creation (DatabaseInstance, Database)
    ↓
Relationship Mapping

Automatic Triggering​

Database discovery is automatically triggered when:

  • A Windows server is scanned via WMI
  • SQL Server is detected in the Windows registry
  • SQLCMD utility is available on the target system

No additional configuration is required - if SQL Server exists on a scanned Windows server, it will be discovered.

Network Requirements​

SQL Server discovery uses the same network connectivity as Windows WMI scanning:

PortProtocolPurpose
135TCPRPC Endpoint Mapper (WMI)
445TCPSMB/CIFS (PAExec fallback)
49152-65535TCPDynamic RPC Range

Note: The scanner connects to SQL Server locally on the target machine using SQLCMD, so SQL Server's TCP port (1433) does not need to be accessible from the scanner.

Credential Requirements​

Windows Authentication (Default)​

The scanner uses Windows Authentication with the same credentials provided for WMI scanning. No separate SQL credentials are required.

  • The WMI scan account must have a SQL Server login
  • Windows integrated authentication is used automatically
  • Works seamlessly with domain accounts

Required SQL Server Permissions​

For full database discovery, the scanning account needs the following SQL Server permissions:

PermissionPurposeRequired For
VIEW SERVER STATEServer-level metricsMemory config, connections, services, encryption
VIEW ANY DATABASEDatabase enumerationListing all databases
VIEW ANY DEFINITIONServer objectsLinked server discovery
db_datareader on msdbBackup historyLast backup dates

Setting Up Permissions​

To grant the minimum required permissions to your scanning account:

-- Connect to master database
USE master;
GO

-- Create login from Windows account (if not exists)
CREATE LOGIN [DOMAIN\ScanAccount] FROM WINDOWS;
GO

-- Grant server-level permissions
GRANT VIEW SERVER STATE TO [DOMAIN\ScanAccount];
GRANT VIEW ANY DATABASE TO [DOMAIN\ScanAccount];
GRANT VIEW ANY DEFINITION TO [DOMAIN\ScanAccount];
GO

-- Grant msdb read access for backup history
USE msdb;
GO
CREATE USER [DOMAIN\ScanAccount] FOR LOGIN [DOMAIN\ScanAccount];
ALTER ROLE db_datareader ADD MEMBER [DOMAIN\ScanAccount];
GO

Permission Levels​

Permission LevelWhat's Discovered
No SQL AccessSQL Server not scanned (instance skipped)
Basic (public only)Instance name, version, edition
Standard (VIEW SERVER STATE)+ Memory, connections, services, encryption
Full (All permissions)+ All databases, backup status, linked servers

Data Collected​

SQL Server Instance Information​

For each discovered SQL Server instance:

Data PointDescription
Instance NameNamed instance identifier (e.g., MSSQLSERVER, SQLEXPRESS)
VersionFull version string (e.g., 15.0.4153.1)
EditionEnterprise, Standard, Express, Developer, Web
Authentication ModeWindows only or Mixed mode
CollationServer default collation
ClusteredFailover cluster membership
Memory ConfigurationMin/Max memory settings
Service AccountWindows account running SQL Server
CPECommon Platform Enumeration for vulnerability matching

Database Information​

For each database on the instance:

Data PointDescription
Database NameName of the database
OwnerDatabase owner account
StateOnline, Offline, Restoring, etc.
Recovery ModelFull, Bulk-Logged, Simple
Compatibility LevelSQL Server version compatibility
SizeTotal size in MB (data + log)
Data SizeData file size in MB
Log SizeTransaction log size in MB
EncryptedTDE encryption status
Read-OnlyRead-only mode status
System Databasemaster, msdb, model, tempdb
Last BackupDate of last full backup
Last Log BackupDate of last log backup
Connection CountActive connections to database

Connection Information​

The scanner discovers who is connected to your databases:

Data PointDescription
Client IPIP address of connecting client
Client HostnameHostname of connecting server
Application NameName of connecting application
Login NameSQL login used for connection
DatabaseTarget database
Connection CountNumber of connections

Linked Servers​

For SQL Server linked servers:

Data PointDescription
Linked Server NameLocal name for the linked server
Remote ServerTarget server name/address
ProviderOLE DB provider (e.g., SQLNCLI11)
Default DatabaseDefault database on remote server

CI Types Created​

DatabaseInstance​

Represents a SQL Server instance running on a server.

  • Parent CI Type: Server
  • Naming Convention: ServerName\InstanceName (e.g., SQLPROD01\MSSQLSERVER)

Database​

Represents an individual database within an instance.

  • Parent CI Type: DatabaseInstance
  • Naming Convention: ServerName\InstanceName\DatabaseName

Relationships Created​

The scanner automatically creates the following relationships:

RelationshipSourceTargetDescription
Runs OnDatabaseInstanceServerInstance runs on server
Part OfDatabaseDatabaseInstanceDatabase belongs to instance
Connected ToServerDatabaseClient server connects to database
Linked ToDatabaseInstanceDatabaseInstanceSQL Server linked server

Named Instance Support​

The scanner automatically detects all SQL Server instances on a server:

  • Default Instance - MSSQLSERVER (connects as localhost)
  • Named Instances - Custom names (connects as localhost\INSTANCENAME)

Instance detection is performed via Windows Registry:

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL

Vulnerability Management Integration​

CPE Generation​

The scanner automatically generates CPE (Common Platform Enumeration) identifiers for vulnerability matching:

Format: cpe:2.3:a:microsoft:sql_server:{year}:*:*:*:{edition}:*:*:*

Examples:

  • SQL Server 2019 Enterprise: cpe:2.3:a:microsoft:sql_server:2019:*:*:*:enterprise:*:*:*
  • SQL Server 2017 Standard: cpe:2.3:a:microsoft:sql_server:2017:*:*:*:standard:*:*:*
  • SQL Server 2016 Express: cpe:2.3:a:microsoft:sql_server:2016:*:*:*:express:*:*:*

Version Mapping​

Internal VersionSQL Server Year
16.xSQL Server 2022
15.xSQL Server 2019
14.xSQL Server 2017
13.xSQL Server 2016
12.xSQL Server 2014
11.xSQL Server 2012
10.50.xSQL Server 2008 R2
10.xSQL Server 2008

Best Practices​

Credential Configuration​

  1. Use a Dedicated Service Account - Create a dedicated domain account for scanning
  2. Apply Least Privilege - Grant only the permissions listed above
  3. Use Windows Authentication - Avoid storing SQL passwords
  4. Propagate to All Servers - Ensure the account has access across your environment

Backup Monitoring​

Use the discovered backup dates to identify compliance issues:

  • Databases with no recent full backup
  • Databases with no log backups (for Full recovery model)
  • System databases without backups

Security Auditing​

Monitor these security-relevant findings:

  • Instances with Mixed Mode authentication (potential security risk)
  • Databases without TDE encryption (sensitive data exposure)
  • Service accounts using Local System (should use managed service accounts)
  • Databases with excessive connection counts (potential abuse)

Troubleshooting​

Common Issues​

IssueCauseSolution
SQL Server not discoveredSQLCMD not installedInstall SQL Server command-line tools
Instance skippedNo SQL login for scan accountCreate SQL login from Windows account
Missing databasesInsufficient permissionsGrant VIEW ANY DATABASE
No backup datesCan't read msdbGrant db_datareader on msdb
No connection infoMissing VIEW SERVER STATEGrant VIEW SERVER STATE

Verifying Permissions​

Run this query on your SQL Server to test the scanning account:

-- Test as the service account
EXECUTE AS LOGIN = 'DOMAIN\ScanAccount';

-- Test basic access
SELECT @@VERSION;

-- Test VIEW SERVER STATE
SELECT * FROM sys.dm_exec_sessions WHERE is_user_process = 1;

-- Test VIEW ANY DATABASE
SELECT name FROM sys.databases;

-- Test msdb access
SELECT TOP 1 * FROM msdb.dbo.backupset;

REVERT;

Logging​

Database discovery progress is logged during scans:

  • Starting database discovery scan
  • Found X SQL Server instance(s) to scan
  • Scanning SQL Server instance: localhost\INSTANCENAME
  • Database discovery completed: X instance(s), Y database(s), Z connection(s)

Viewing Discovered Data​

In the CMDB​

  1. Navigate to CMDB > Configuration Items
  2. Filter by CI Type: DatabaseInstance or Database
  3. View instance/database details and relationships

In Reports​

  • Database Inventory Report - All databases across infrastructure
  • Backup Compliance Report - Databases missing recent backups
  • Database Security Report - Encryption and authentication status

This reference provides the details needed to understand and utilize SQL Server database discovery for complete database infrastructure visibility and management.