Monday 20 May 2024

Find Sql Server Version, Product and Host details

Check Various SQL Server Instance Related Properties using  SERVERPROPERTY function

 SERVERPROPERTY('MachineName') AS ComputerName,
 SERVERPROPERTY('ServerName') AS InstanceName,
 SERVERPROPERTY('Edition') AS Edition,
 SERVERPROPERTY('ProductVersion') AS ProductVersion,
 SERVERPROPERTY('ProductLevel') AS ProductLevel;

Check TDE is Enabled or not on SQL Server

We can check if TDE Is enabled using SQL Server Management Studio.

Login to Sql Server From SSMS
Expand Databases section
Right Click on DB
Choose Options  

Check if TDE is enabled using a query :

DB_NAME(database_id) AS DatabaseName,
CASE encryption_state
WHEN 0 THEN 'No Database Encryption Key Present'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption In Progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key Change In Progress'
WHEN 5 THEN 'Decryption In Progress'
ELSE 'Unknown'
END AS EncryptionStatus
FROM sys.dm_database_encryption_keys;