SQL Server

Step 1: Open SQL Server Configuration Manager

• •Navigate to SQL Server Network Configuration
• •Select Protocols for [Instance Name]
• •Right-click TCP/IP and select Enable

Step 2: Configure TCP/IP Port

• •Right-click TCP/IP and select Properties
• •Go to IP Addresses tab
• •Set TCP Port to 1433 (default)

Step 3: Restart SQL Server Service

# PowerShell
Restart-Service -Name 'MSSQLSERVER'

# Or restart via SQL Server Configuration Manager

SQL Server Authentication

-- Create SQL Server login
CREATE LOGIN db24x7_monitor WITH PASSWORD = 'YourSecurePassword123!';

-- Create user in each database to monitor
USE [YourDatabase];
CREATE USER db24x7_monitor FOR LOGIN db24x7_monitor;

-- Grant VIEW SERVER STATE (required for DMVs)
GRANT VIEW SERVER STATE TO db24x7_monitor;

-- Grant VIEW DATABASE STATE
GRANT VIEW DATABASE STATE TO db24x7_monitor;

-- Grant VIEW ANY DEFINITION (for metadata access)
GRANT VIEW ANY DEFINITION TO db24x7_monitor;

-- Grant SELECT on system views
USE [YourDatabase];
GRANT SELECT ON sys.dm_exec_requests TO db24x7_monitor;
GRANT SELECT ON sys.dm_exec_sessions TO db24x7_monitor;
GRANT SELECT ON sys.dm_exec_query_stats TO db24x7_monitor;
GRANT SELECT ON sys.dm_exec_cached_plans TO db24x7_monitor;
GRANT SELECT ON sys.dm_os_wait_stats TO db24x7_monitor;
GRANT SELECT ON sys.databases TO db24x7_monitor;

Windows Authentication

-- Create Windows login
CREATE LOGIN [DOMAIN\db24x7_monitor] FROM WINDOWS;

-- Apply the same permissions as above
GRANT VIEW SERVER STATE TO [DOMAIN\db24x7_monitor];
GRANT VIEW DATABASE STATE TO [DOMAIN\db24x7_monitor];
GRANT VIEW ANY DEFINITION TO [DOMAIN\db24x7_monitor];

SQL Server Authentication

sqlserver://db24x7_monitor:YourSecurePassword123!@hostname:1433?database=YourDatabase&encrypt=true&trustServerCertificate=false

Named Instance

sqlserver://db24x7_monitor:password@hostname\INSTANCENAME?database=YourDatabase&encrypt=true

Azure SQL Database

sqlserver://db24x7_monitor@yourserver:[email protected]:1433?database=YourDatabase&encrypt=true

Connection Parameters

Cannot Connect to Server

Common connection issues and solutions:

• •Verify SQL Server Browser service is running (for named instances)
• •Check Windows Firewall allows port 1433
• •Ensure TCP/IP protocol is enabled
• •Verify SQL Server is listening on correct port

Login Failed Errors

Verify authentication settings:

-- Check SQL Server authentication mode
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS AuthMode;
-- 0 = Mixed Mode, 1 = Windows Only

-- Verify login exists
SELECT name, type_desc, is_disabled
FROM sys.server_principals
WHERE name = 'db24x7_monitor';

-- Check user mapping
SELECT dp.name, dp.type_desc
FROM sys.database_principals dp
WHERE dp.name = 'db24x7_monitor';

Permission Denied on DMVs

Verify server-level permissions:

-- Check server-level permissions
SELECT pr.name, pe.permission_name, pe.state_desc
FROM sys.server_permissions pe
JOIN sys.server_principals pr ON pe.grantee_principal_id = pr.principal_id
WHERE pr.name = 'db24x7_monitor';

-- Grant missing permissions if needed
GRANT VIEW SERVER STATE TO db24x7_monitor;
GRANT VIEW ANY DEFINITION TO db24x7_monitor;

SSL/TLS Certificate Errors

For self-signed certificates:

# Add trustServerCertificate=true to connection string
sqlserver://user:pass@host:1433?database=db&encrypt=true&trustServerCertificate=true