August 13, 2014 at 9:55 am
Does anyone have a t-sql query that will encrypt several databases without using the use DB_name? I am trying to create a SP that will look for the flag if 0 then encrypt.
SELECT name AS 'Database_Name' ,
is_encrypted AS 'Encryption Enabled'
FROM sys.databases
where database_id not in (1,2,3,4)
and is_encrypted = 0
however I cannot find a query that will set encryption on with having to use the USE DB_NAME clause. I am very green on this so please be nice... 🙂
MCSE SQL Server 2012\2014\2016
August 13, 2014 at 10:21 am
The use database is required for the creation of the DEK, the ALTER DATABASE ... SET ENCRYPTION ON is not a database specific command, in fact focus should probably be set to master when running this
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 13, 2014 at 10:49 am
yes, I am running the to set the flag in master, I just need a loop to alter but it looks like I cannot. I was hoping to place it in a SP so I could run it nightly to update incase one of the SharePoint admins created a DB without my knowledge.
MCSE SQL Server 2012\2014\2016
August 13, 2014 at 11:19 am
If someone is changing the encryption status, they're creating a lot of load to decrypt, and you'll create load to encrypt.
Don't automatically fix this. Catch it with monitoring (Check the DMVs) and then go find out who is changing it and why.
August 13, 2014 at 12:01 pm
Steve Jones - SSC Editor (8/13/2014)
If someone is changing the encryption status, they're creating a lot of load to decrypt, and you'll create load to encrypt.Don't automatically fix this. Catch it with monitoring (Check the DMVs) and then go find out who is changing it and why.
It's not that someone is changing it, when new sharepoint databases are created the op wants to dynamically set encryption on.
Not something I would recommend.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 13, 2014 at 6:44 pm
I agree. You don't want to necessarily enable this by default.
That being said, I think you'd need to use dynamic SQL to build a script that would do this. I could be wrong, and I'll try to test later, but I'm not sure you can use a variable in the ALTER DATABASE command.
August 14, 2014 at 10:45 am
I found out what was going on, there is NOT an option in SharePoint to do TDE Database Encryption. There is however a SSL for website encryption. The DBA that was here prior to me had setup a job in INTAPP to encrypt the DB's and it was not working. I manually created a script to set the cert and set encryption on. All my current DB's are encrypted now and up to date. I also created a report, that emails me every 4 hours that will let me know if a new SharePoint database gets created so I can run a manual update to set encryption on.
Thanks Guys!
MCSE SQL Server 2012\2014\2016
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply