Is there a quick way to find SQL Server instances across our whole SQL Estate that hold encrypted (SQL TDE) whole databases or database table columns?
Add a comment
|
2 Answers
You can use the DbaTools' Powershell module, which has a command Get-DbaDatabase
Get-DbaDatabase -SqlInstance SomeServer -Encrypted
You can even find all servers using Find-DbaInstance
Find-DbaInstance `
-DiscoveryType IPRange `
-IpAddress "10.1.1.0/24" `
| Get-DbaDatabase -Encrypted;
-
Thanks @Charlieface, this is helpful, as it can be run from our management server, much appreciated.PTL_SQL– PTL_SQL2024-03-07 12:10:21 +00:00Commented Mar 7, 2024 at 12:10
Quick -- that would depend. But if it were me, I'd set up a central management server and register all your SQL Servers there.
There's a really good introduction to the feature here: https://www.red-gate.com/simple-talk/databases/sql-server/tools-sql-server/registered-servers-and-central-management-server-stores/
Then all you need to do is run a query against all the registered servers like the one below:
select name, is_encrypted from sys.databases
Query sys.columns to look for encrypted columns
So it requires an investment to create the central management server but will make you many times more effective as a DBA from that point forward.
answered Feb 22, 2024 at 16:20
Stephen Morris - Mo64
4,6761 gold badge10 silver badges18 bronze badges
-
Query sys.columns to look for encrypted columnsStephen Morris - Mo64– Stephen Morris - Mo642024-02-22 16:27:59 +00:00Commented Feb 22, 2024 at 16:27
-
1You can just edit further details into your answer instead of adding them as comments.Erik Darling– Erik Darling2024-02-22 17:00:53 +00:00Commented Feb 22, 2024 at 17:00
-
@StephenMorris-Mo64, that makes sense. Thank you for your time and response.PTL_SQL– PTL_SQL2024-02-22 17:23:29 +00:00Commented Feb 22, 2024 at 17:23