locked
File Permissions for Private Key/Certificate Exported from SQL 2008 RRS feed

  • Question

  • As part of our new key management system for SQL server, we are backing up the Private Key and Certificate file to a UNC file path that has very restricted access to only Key and Certificate custodians.  When we execute the backup script, it works and puts the Certificate and Key files to the UNC path, but the files do not inherit NTFS permissions from the target path and our Key and Certificate Custodians do not have access to these files.  Is there a way to "force" these files to inherit permissions from their target folder structure?

    Here is the SQL script used to generate our key pair:

    BACKUP CERTIFICATE NewCert

      TO FILE = '\\UNCCertificatePath\CertificateFile.cert’

      WITH PRIVATE KEY

        (FILE = '\\UNCPrivateKeyPath\PrivateKey.key',

         ENCRYPTION BY PASSWORD = 'PASSWORD')

     

    Monday, October 25, 2010 4:28 PM

Answers

  • Hi MSDNShawn,

     

    Based on my test, this files generated by SQL Server only could be accessed by members of Administrator group and SQLServerMSSQLUser$<server name>$<instance name>. Since the second group just has limited permission. I would like to recommend that you add the account used to execute this application to this group and try again.

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Friday, October 29, 2010 6:28 AM

All replies

  • Hi MDSNShawn,

     

    Since there is less information about the version information you are using, I have tested it in SQL Server 2008 R2 on Windows 7.

    Based on my test, if we backup the certificate to an outside file, in the security tab I have just seen “OWNER RIGHTS”, “SQLServerMSSQLUser$<server name>$<instance name>” and “Administrators” by default. It means that the permission on these files will only be accessed by these three groups without inheriting the permission of parent folder.

     

    Therefore as a workaround, I would like to recommend that you add the account used by Key and Certificate Custodian to “SQLServerMSSQLUser$<server name>$<instance name>” group and try again.

     

    Meanwhile if you think this feature is useful, please provide Microsoft your feedback at https://connect.microsoft.com/SQLServer so that our product team will hear your voice and continue to improve our products in the future.

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Wednesday, October 27, 2010 2:23 AM
  • Thanks for the reply!

    I just verified the file permissions on the key files we exported and they only contain 2 entries.  One is Administrators for the local machine hosting the file share and the other is the SQL Service Account that executed the SQL command.  Giving the key custodians Administrator rights on the file server is probably overkill and would definately give those individuals rights into areas they should not see. 

    As an FYI, the end goal is for us to split up the Encryption Certificate, Key and password to different individuals in the organization (not system admins or DBA's).  These "pieces" are then securely stored so that only the specific custodians have access to their pieces in order to meet PCI compliance requirements.

    I'm happy to provide any additional information on versioning, etc - we're running SQL 2008 on a Windows 2003 Server R2 x64 platform (Build 3790.srv03_sp2_gdr.100216-1301 : Service Pack 2).  Here's the additional versioning info from the About help screen on the SQL Management Studio.

    Microsoft SQL Server Management Studio      10.0.1600.22 ((SQL_PreRelease).080709-1414 )
    Microsoft Analysis Services Client Tools      2007.0100.1600.022 ((SQL_PreRelease).080709-1414 )
    Microsoft Data Access Components (MDAC)      2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
    Microsoft MSXML      2.6 3.0 6.0
    Microsoft Internet Explorer      6.0.3790.1830
    Microsoft .NET Framework      2.0.50727.3615
    Operating System      5.2.3790

    I'm more of a systems guy that was given this issue as a "permissions problem," so please let me know if I need to gather any additional SQL-specific info or have our DBA answer any questions/complete any tasks.

    Thanks for your help - I really appreciate it!

    Shawn

    Thursday, October 28, 2010 1:50 PM
  • Hi MSDNShawn,

     

    Based on my test, this files generated by SQL Server only could be accessed by members of Administrator group and SQLServerMSSQLUser$<server name>$<instance name>. Since the second group just has limited permission. I would like to recommend that you add the account used to execute this application to this group and try again.

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Friday, October 29, 2010 6:28 AM
  • Thank you for the quick reply.  We have another test scheduled for Tuesday and we'll try it with the new permissions, but it certainly appears like it should work.

    Thanks again!

    Shawn

    Friday, October 29, 2010 9:34 PM
  • Hi MSDNShawn,

    Any progress?


    Regards,
    Tom Li
    Tuesday, November 2, 2010 9:03 AM
  • As near as I can tell, it's going to work.  Our test for Tuesday was re-scheduled until friday due to illness this week, but it is looking very promising.  I'll post a message here with the results.

    Thanks again for your help on this - I really appreciate it!

    Shawn

    Wednesday, November 3, 2010 1:18 PM