-- Check the current port configuration
SELECT local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

# Change TCP port via SQL Server Configuration Manager
# Or via registry (caution: this requires a restart)
Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IPAll" `
    -Name "TcpPort" -Value "41433"

# Disable SQL Browser service if you don't discover named instances
Set-Service -Name "SQLBrowser" -StartupType Disabled
Stop-Service -Name "SQLBrowser"

# Check the current version and patch level
SELECT @@VERSION;
SELECT SERVERPROPERTY('ProductVersion') AS Version,
       SERVERPROPERTY('ProductLevel') AS PatchLevel,
       SERVERPROPERTY('Edition') AS Edition;

-- Step 1: Rename the sa account
ALTER LOGIN sa WITH NAME = [sql_disabled_admin];

-- Step 2: Set a strong password (minimum 30 characters)
ALTER LOGIN [sql_disabled_admin] WITH PASSWORD = 'HIER-EEN-LANG-RANDOM-WACHTWOORD-VAN-30+-TEKENS!';

-- Step 3: Disable the account
ALTER LOGIN [sql_disabled_admin] DISABLE;

-- Verify that sa is disabled
SELECT name, is_disabled, is_policy_checked, is_expiration_checked
FROM sys.sql_logins
WHERE principal_id = 1;

-- Create a login with password policy enforcement
CREATE LOGIN [app_service]
WITH PASSWORD = 'SterkWachtwoord!ComplexGenoeg123',
    CHECK_POLICY = ON,
    CHECK_EXPIRATION = ON;

-- Check existing logins for policy status
SELECT name,
       is_policy_checked,
       is_expiration_checked,
       create_date,
       modify_date
FROM sys.sql_logins
WHERE is_policy_checked = 0 OR is_expiration_checked = 0;

-- Step 1: Create a server login
CREATE LOGIN [app_reader] WITH PASSWORD = 'SterkWachtwoord!456';

-- Step 2: Create a database user
USE [ProductieDB];
CREATE USER [app_reader] FOR LOGIN [app_reader];

-- Step 3: Assign ONLY the minimal permissions
-- NOT: ALTER ROLE db_datareader ADD MEMBER [app_reader];
-- INSTEAD: granular permissions at schema level
GRANT SELECT ON SCHEMA::dbo TO [app_reader];

-- Or even more specific: only certain tables
GRANT SELECT ON dbo.Products TO [app_reader];
GRANT SELECT ON dbo.Categories TO [app_reader];

-- Explicitly deny what is not allowed
DENY SELECT ON dbo.AuditLog TO [app_reader];
DENY SELECT ON dbo.UserCredentials TO [app_reader];

-- Verify effective permissions
EXECUTE AS USER = 'app_reader';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
REVERT;

-- REVOKE removes a previously granted permission
REVOKE EXECUTE ON dbo.sp_GetSensitiveData FROM [app_user];

-- DENY explicitly blocks, even if another role has GRANT
DENY VIEW DEFINITION ON SCHEMA::dbo TO [app_user];

-- Check the current status
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell';

-- Disable
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

-- Verify
EXEC sp_configure 'xp_cmdshell';
-- run_value should be 0

-- Disable
EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE;

-- Disable if not needed
EXEC sp_configure 'clr enabled', 0;
RECONFIGURE;

-- If CLR is needed: restrict to SAFE assemblies
-- and use CLR strict security (SQL 2017+)
EXEC sp_configure 'clr strict security', 1;
RECONFIGURE;

EXEC sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;

-- Restrict who can use Database Mail
-- Remove unnecessary users from the DatabaseMailUserRole
USE msdb;
EXEC sp_droprolemember 'DatabaseMailUserRole', 'onnodige_user';

-- Check current members
SELECT dp.name
FROM sys.database_role_members drm
JOIN sys.database_principals dp ON drm.member_principal_id = dp.principal_id
JOIN sys.database_principals dr ON drm.role_principal_id = dr.principal_id
WHERE dr.name = 'DatabaseMailUserRole';

-- Show all configured linked servers
EXEC sp_linkedservers;

-- Detailed information about linked server logins
SELECT s.name AS LinkedServer,
       s.data_source,
       s.provider,
       ll.uses_self_credential,
       ll.remote_name
FROM sys.servers s
LEFT JOIN sys.linked_logins ll ON s.server_id = ll.server_id
WHERE s.is_linked = 1;

-- Restrict a linked server: disable RPC
EXEC sp_serveroption @server = 'LINKED_SRV01', @optname = 'rpc out', @optvalue = 'false';

-- Change the login mapping to a dedicated account
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'LINKED_SRV01',
    @useself = 'false',
    @locallogin = NULL,
    @rmtuser = 'linked_readonly',
    @rmtpassword = 'SterkWachtwoord!789';

# Windows Firewall: allow only specific subnets
New-NetFirewallRule -DisplayName "SQL Server - App Servers" `
    -Direction Inbound -Protocol TCP -LocalPort 41433 `
    -RemoteAddress "10.1.2.0/24" -Action Allow

New-NetFirewallRule -DisplayName "SQL Server - Management" `
    -Direction Inbound -Protocol TCP -LocalPort 41433 `
    -RemoteAddress "10.1.0.0/24" -Action Allow

# Block all other SQL traffic
New-NetFirewallRule -DisplayName "SQL Server - Block All Other" `
    -Direction Inbound -Protocol TCP -LocalPort 41433 `
    -Action Block

# SQL Server Configuration Manager > SQL Server Network Configuration
# > Protocols > Properties > Flags tab
# Set "Force Encryption" to "Yes"

# Or via registry
Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib" `
    -Name "ForceEncryption" -Value 1

# Disable older TLS versions at OS level
# Disable TLS 1.0
New-Item -Path "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server" -Force
Set-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server" `
    -Name "Enabled" -Value 0

-- Step 1: Create a Server Audit
CREATE SERVER AUDIT [SecurityAudit]
TO FILE (
    FILEPATH = 'D:\SQLAudit\',
    MAXSIZE = 256 MB,
    MAX_ROLLOVER_FILES = 20,
    RESERVE_DISK_SPACE = OFF
)
WITH (
    QUEUE_DELAY = 1000,
    ON_FAILURE = CONTINUE
);

-- Step 2: Enable the audit
ALTER SERVER AUDIT [SecurityAudit] WITH (STATE = ON);

-- Step 3: Create a Server Audit Specification
CREATE SERVER AUDIT SPECIFICATION [LoginAuditSpec]
FOR SERVER AUDIT [SecurityAudit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP);

ALTER SERVER AUDIT SPECIFICATION [LoginAuditSpec] WITH (STATE = ON);

-- Step 4: Database-level audit (per database)
USE [ProductieDB];
CREATE DATABASE AUDIT SPECIFICATION [DataAccessAudit]
FOR SERVER AUDIT [SecurityAudit]
ADD (SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo BY public),
ADD (EXECUTE ON SCHEMA::dbo BY public);

ALTER DATABASE AUDIT SPECIFICATION [DataAccessAudit] WITH (STATE = ON);

-- Set login auditing (0=none, 1=failed, 2=success, 3=both)
EXEC xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'AuditLevel',
    REG_DWORD, 3;

-- Step 1: Create a Database Master Key
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyWachtwoord!Complex789';

-- Step 2: Create a certificate for backup encryption
CREATE CERTIFICATE BackupEncryptCert
WITH SUBJECT = 'Database Backup Encryption Certificate',
    EXPIRY_DATE = '2030-12-31';

-- Step 3: Create an encrypted backup
BACKUP DATABASE [ProductieDB]
TO DISK = 'D:\Backups\ProductieDB_encrypted.bak'
WITH COMPRESSION,
    ENCRYPTION (
        ALGORITHM = AES_256,
        SERVER CERTIFICATE = BackupEncryptCert
    );

-- IMPORTANT: Back up the certificate itself as well!
BACKUP CERTIFICATE BackupEncryptCert
TO FILE = 'D:\CertBackup\BackupEncryptCert.cer'
WITH PRIVATE KEY (
    FILE = 'D:\CertBackup\BackupEncryptCert.pvk',
    ENCRYPTION BY PASSWORD = 'CertBackupWachtwoord!456'
);

-- Step 1: Master Key (if it doesn't exist yet)
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDEMasterKey!Complex123';

-- Step 2: Certificate
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';

-- Step 3: Database Encryption Key
USE [ProductieDB];
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;

-- Step 4: Enable TDE
ALTER DATABASE [ProductieDB] SET ENCRYPTION ON;

-- Check the status
SELECT db.name, db.is_encrypted,
       dek.encryption_state, dek.key_algorithm, dek.key_length
FROM sys.databases db
LEFT JOIN sys.dm_database_encryption_keys dek
    ON db.database_id = dek.database_id;

# Restrict access to the backup directory
$acl = Get-Acl "D:\Backups"
$acl.SetAccessRuleProtection($true, $false)

# Only SQL Server service account and backup operators
$rule1 = New-Object System.Security.AccessControl.FileSystemAccessRule(
    "NT SERVICE\MSSQLSERVER", "FullControl", "ContainerInherit,ObjectInherit", "None", "Allow")
$rule2 = New-Object System.Security.AccessControl.FileSystemAccessRule(
    "DOMAIN\SQL-BackupOperators", "Read,Write", "ContainerInherit,ObjectInherit", "None", "Allow")

$acl.AddAccessRule($rule1)
$acl.AddAccessRule($rule2)
Set-Acl "D:\Backups" $acl