April 5, 2018 at 5:57 am
Dear Everyone
I followed the required steps to request a certificate for using SSL in SQL Server 2016 and i generated the request file for a PERSONAL store and then imported it into the Personal store but when i do the import and restart the Database engine the service doesnt start unless i make the service account part of the Admin local group.
However my issue is with the certificate, does it have to be in the personal store or the trusted root certification authorities?
Please advise as online it also states to use the personal store
have a good day
Hurricane
April 5, 2018 at 4:15 pm
hurricaneDBA - Thursday, April 5, 2018 5:57 AMDear Everyone
I followed the required steps to request a certificate for using SSL in SQL Server 2016 and i generated the request file for a PERSONAL store and then imported it into the Personal store but when i do the import and restart the Database engine the service doesnt start unless i make the service account part of the Admin local group.
However my issue is with the certificate, does it have to be in the personal store or the trusted root certification authorities?
Please advise as online it also states to use the personal storehave a good day
Hurricane
Personal store of the machine account
In terms of adding the service account to the Admin group, you don't need to. In the certificates console, Right click on the certificate, select all tasks, select manage private keys. Add the service account and permissions there. Administrators group already has permissions so that's why it worked when adding the account to the Administrators group.
Sue
April 5, 2018 at 4:45 pm
hurricaneDBA - Thursday, April 5, 2018 5:57 AMDear Everyone
I followed the required steps to request a certificate for using SSL in SQL Server 2016 and i generated the request file for a PERSONAL store and then imported it into the Personal store but when i do the import and restart the Database engine the service doesnt start unless i make the service account part of the Admin local group.
However my issue is with the certificate, does it have to be in the personal store or the trusted root certification authorities?
Please advise as online it also states to use the personal storehave a good day
Hurricane
Duh...And I just noticed you have three questions in there....didn't see the title. Is that why you were asking about which store?
In the top of the mmc console on the left, does it say Certificates - Current User or Certificates - Local computer?
certmgr.msc opens for current user
certlm.msc opens for local machine
Sue
April 8, 2018 at 12:28 am
Dear Sue
Thank you that worked great
Just another question shall i use SSL certificates or enable the new Always Encrypt for 2016?
Which is the better route?
Have a lovely week
Kal
April 9, 2018 at 5:56 am
hurricaneDBA - Sunday, April 8, 2018 12:28 AMJust another question shall i use SSL certificates or enable the new Always Encrypt for 2016?
Which is the better route?
They both do very different things, what is it you are trying to do?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 9, 2018 at 7:13 am
Our CISO wants us to do the following:
| Encrypt stored data in accordance with corporate standards and regulatory needs. Use native SQL Server hierarchical encryption and key management infrastructure using certificates, symmetric, or asymmetric keys or equivalent encryption technology. |
| Encrypt stored data in accordance with corporate standards and regulatory needs. Use SQL Server Transparent Data Encryption (TDE) to perform full database-level encryption, using the SQL Server encryption hierarchy and key management infrastructure. |
The above is TDE and only available on the EE correct?
| Encrypt data in transit in accordance with corporate standards by requiring SSL communication between instances of SQL Server and client applications. |
The above is above SSL and certificates so we can use SSL here but can we use Always encrypted here?
I am guessing only SSL
Finally he wants:
| Enable encryption for all necessary extended stored procedures, triggers and views. |
I dont know if Always Encrypted will take care of the above request
Any ideas?
Kal
April 9, 2018 at 7:34 am
Sue
April 10, 2018 at 1:49 am
Hi Sue
So i cant encrypt extended SPs? Unless i go through each one manually and drop and recreate them using the clause WITH ENCRYPTION?
Also for TDE if we are using a backup solution called NETWORKER when the agent takes the backup of the database the backup will already be encrypted right?
Have a great day
Kal
April 10, 2018 at 6:27 am
hurricaneDBA - Tuesday, April 10, 2018 1:49 AMHi Sue
So i cant encrypt extended SPs? Unless i go through each one manually and drop and recreate them using the clause WITH ENCRYPTION?Also for TDE if we are using a backup solution called NETWORKER when the agent takes the backup of the database the backup will already be encrypted right?
Have a great day
Kal
Correct, existing stored procedures would need to be re-created. Be aware, there is *NO* supported method to in-encrypt them later so make sure you (or the developers) keep a copy of the code somewhere.
Correct. The backups are encrypted and cannot be restored without the certificate present on the server. So make sure to *also* backup the certificate every so often.
April 10, 2018 at 7:58 am
It might not be as bad as it seems though. You can create a script, write a query to help with changing the existing stored procedures, triggers, etc to be encrypted. There are at least a few examples of doing this if you search online.
Sue
April 11, 2018 at 2:40 am
Hi Sue / Jasona
I am only mentioning extended SPs so arent we not supposed to modify those SPs?
KY
April 11, 2018 at 6:04 am
hurricaneDBA - Wednesday, April 11, 2018 2:40 AMHi Sue / Jasona
I am only mentioning extended SPs so arent we not supposed to modify those SPs?KY
Ah, I missed that. My general mindset is "hands off the system stuff."
April 11, 2018 at 6:35 am
hurricaneDBA - Wednesday, April 11, 2018 2:40 AMHi Sue / Jasona
I am only mentioning extended SPs so arent we not supposed to modify those SPs?KY
You don't want to modify system objects. Not sure why that was included but not all extended stored procedures are system extended stored procedures. You can created your own although it's deprecated and you are suppose to use CLR integration. Extended stored procedures are really just dlls - the code is in the dlls.
If all of yours are those that system xps, no user defined xps, you can ask them how they want you to change the dlls of which you have no access to the code and if they are aware that changing system objects is not supported and can break functionality for SQL Server.
Auditors, security officers may not know much bout SQL Server and can throw out mandates a bit mindlessly. Nonetheless, you will typically have to document and provide vendor documentation on how things work or why something can't be done.
Sue
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply