March 24, 2018 at 9:29 pm
I need to encrypt my database and I have the standard edition SP1. With the standard edition I cant use TDE. I also looked online to see if I can use Always Encrypt and I couldn't find any info there either. How do I encrypt databases with the standard edition? (if I can)
Thanks.
March 25, 2018 at 3:09 am
Always Encrypted is supported on Standard Edition.
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016
See the section RDBMS Security
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 26, 2018 at 7:06 am
I wasn't sure to open a new ticket or simply reply to this post since its about the same issue. (Always Encrypt)
I tried the following guides:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted
https://channel9.msdn.com/events/DataDriven/SQLServer2016/AlwaysEncrypted
I goto the database, then find the table that contains columns I want to encrypt, but there is no "Encrypt Columns" option. I created a Master Key, but I cant create a Database Encryption key because of my version for SQL Server 2016 (Standard). Is there something else I need to do to enable the Always Encrypt Wizard?
March 26, 2018 at 7:18 am
Database Encryption keys are not, afaik, limited by edition.
What's the error that you're getting?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 26, 2018 at 7:28 am
WC_Admin - Monday, March 26, 2018 7:06 AMI wasn't sure to open a new ticket or simply reply to this post since its about the same issue. (Always Encrypt)I tried the following guides:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted
https://channel9.msdn.com/events/DataDriven/SQLServer2016/AlwaysEncrypted
I goto the database, then find the table that contains columns I want to encrypt, but there is no "Encrypt Columns" option. I created a Master Key, but I cant create a Database Encryption key because of my version for SQL Server 2016 (Standard). Is there something else I need to do to enable the Always Encrypt Wizard?
How did you create the master key?
What keystore are you using?
What version is your sql server instance?
It needs to be at least 13.0.4001.0
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 26, 2018 at 7:30 am
This is the query I'm trying to use and the error underneath:
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE (database_name)
SET ENCRYPTION ON;
GO
Msg 33117, Level 16, State 1, Line 3
Transparent Data Encryption is not available in the edition of this SQL Server instance. See books online for more details on feature support in different SQL Server editions.
Msg 195, Level 15, State 5, Line 9
'ENCRYPTION' is not a recognized SET option.
March 26, 2018 at 7:35 am
WC_Admin - Monday, March 26, 2018 7:30 AMThis is the query I'm trying to use and the error underneath:
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GOALTER DATABASE (database_name)
SET ENCRYPTION ON;
GOMsg 33117, Level 16, State 1, Line 3
Transparent Data Encryption is not available in the edition of this SQL Server instance. See books online for more details on feature support in different SQL Server editions.Msg 195, Level 15, State 5, Line 9
'ENCRYPTION' is not a recognized SET option.
That's the process to configure and enable TDE, this is not available in standard edition as you have already cited.
Are you wanting to use Always Encrypted?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 26, 2018 at 7:56 am
Perry Whittle - Monday, March 26, 2018 7:28 AMHow did you create the master key?
What keystore are you using?
What version is your sql server instance?
It needs to be at least 13.0.4001.0
I created the master key this way:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-master-key-transact-sql
Do I need to create a new one?
My SQL version instance is 13.0.1601.5, so I should be able to use Always Encrypt.
March 26, 2018 at 8:00 am
WC_Admin - Monday, March 26, 2018 7:56 AMPerry Whittle - Monday, March 26, 2018 7:28 AMHow did you create the master key?
What keystore are you using?
What version is your sql server instance?
It needs to be at least 13.0.4001.0I created the master key this way:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-master-key-transact-sqlDo I need to create a new one?
My SQL version instance is 13.0.1601.5, so I should be able to use Always Encrypt.
That's not how you create a column master key for use with AE, that's the command to create a database master key for use with TDE and encrypted backups, etc.
Follow the tutorials for AE to create the appropriate keys and enable encryption on the columns.
Important: Before you blindly enable AE, do you understand what it does and how it works??[/I]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 26, 2018 at 8:01 am
March 26, 2018 at 8:05 am
WC_Admin - Monday, March 26, 2018 7:56 AMMy SQL version instance is 13.0.1601.5, so I should be able to use Always Encrypt.
That's RTM. You won't be able to use Always Encrypted, or any of the previously Enterprise-only features, until you apply SP1. You should always patch up to date, so why not also add the latest CU while you have the downtime?
John
March 26, 2018 at 8:11 am
John Mitchell-245523 - Monday, March 26, 2018 8:01 AMWC_Admin - Monday, March 26, 2018 7:56 AMMy SQL version instance is 13.0.1601.5, so I should be able to use Always Encrypt.That's RTM. You won't be able to use Always Encrypted, or several other previously Enterprise-only features, until you apply SP1. You should always patch up to date, so why not also apply the latest CU while you have the downtime?
John
Ah, I see. I don't have the SP1 patch. That explains why I didnt see the option right away when I right clicked on the DB. I will scheduled that for next downtime.
March 26, 2018 at 9:37 am
WC_Admin - Monday, March 26, 2018 8:11 AMJohn Mitchell-245523 - Monday, March 26, 2018 8:01 AMWC_Admin - Monday, March 26, 2018 7:56 AMMy SQL version instance is 13.0.1601.5, so I should be able to use Always Encrypt.That's RTM. You won't be able to use Always Encrypted, or several other previously Enterprise-only features, until you apply SP1. You should always patch up to date, so why not also apply the latest CU while you have the downtime?
John
Ah, I see. I don't have the SP1 patch. That explains why I didnt see the option right away when I right clicked on the DB. I will scheduled that for next downtime.
confirm you have understood that even with SP1 applied, the tsql you are using creates a database master key and not a column master key?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 26, 2018 at 10:34 am
Perry Whittle - Monday, March 26, 2018 9:37 AMconfirm you have understood that even with SP1 applied, the tsql you are using creates a database master key and not a column master key?
Understood. Some guides can be confusing. I spoke with my Information Assurance Managers and we weren't supposed to have the Standard version anyway. So I'll have to upgrade to the Enterprise version, then constinue with TDE since the database master key is already there.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply