2

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?

2 Answers 2

3

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;
1
  • Thanks @Charlieface, this is helpful, as it can be run from our management server, much appreciated.
    – PTL_SQL
    Mar 7 at 12:10
3

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.

3
  • Query sys.columns to look for encrypted columns Feb 22 at 16:27
  • 1
    You can just edit further details into your answer instead of adding them as comments. Feb 22 at 17:00
  • @StephenMorris-Mo64, that makes sense. Thank you for your time and response.
    – PTL_SQL
    Feb 22 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.