SQL Server Hardening for PCI Compliance RRS feed

  • Question

  • We have to harden our SQL Servers, 2008 R2 64bit, standard edition, for PCI compliance, Level 1. Our PCI internal team has recommended that we adhere to the Center for Internet Security's Benchmark for MS SQL Server 2005 v1.2.0 (http://benchmarks.cisecurity.org/en-us/?route=downloads.show.single.sql2005.120). However, after reviewing this document we have several concerns, many of which are caused by conflicting clauses in this document, for example:

    Item: 2.6
    Configuration Item: Authentication mode
    Action/Recommended Parameters: Select Windows authentication mode.
    Comments: Windows provides a more robust authentication mechanism than SQL Server authentication. If SQL Server authentication is required, configure SQL Server account password and lockout properties with local or domain-based group policies.
    Levels: 1 S

    Item: 4.4
    Configuration Item: Assigning System Administrators role
    Action/Recommended Parameters: When assigning database administrators to the System Administrators role, map their Windows accounts to SQL logins, then assign them to the role.
    Comments: Assign only authorized DBAs to the SQL Server System Administrators role.
    Levels: 1 N

    So which is it, Windows Authentication only or mixed mode because of keeping the sa login? There are other issues we have with this as well but that's just a single example.

    As DBAs we aren't comfortable with or convinced that this is the best way to harden our SQL Server and were wondering what other DBAs have used to harden their SQL Servers for PCI compliance?



    Michael MacGregor, Senior SQL Server DBA
    Tuesday, January 3, 2012 5:47 PM

All replies

  • Item 2.6 - Using Windows authentication is the safest choice, due to the authentication mechanisms.  For example, there is no password moving around the network.

    Item 4.4 - System Administrators, map their Windows account to SQL logins...   Yes, this is less than clear.  It should probably be worded differently. 

    This actually means create a Login on the SQL Server for the Windows account, then grant the sysadmin role to that login.  This login is still a Windows login but by being created directly on the SQL Server (rather than making the Windows login a member of a Windows group which is granted login rights) you maintain tighter control over who receives the sysadmin role.    The command would look like this:


    In terms of the 'sa' login, which is a member of the sysadmin role you can rename it, give it a generated password that nobody knows, or totally disable SQL Server logins (WINDOW ONLY authentication).   Ideally you should have nothing at all that uses the sa account. 

    But if you need SQL Server logins for some purposes, then making the password unknowable, but having other Windows logins that serve as sqladmins is still the best.




    Tuesday, January 3, 2012 6:21 PM
  • Hi Russell,

    That is definitely one way to interpret Item 4.4 although it does explicitly state "map their Windows accounts to SQL logins". However this is just one example of the conflicting items in the document, there are many more.

    Even as far as using only Windows authentication, that presents problems for us as we use SQL logins for all web apps, we only use Windows accounts for internal staff or for proxy accounts that also require access to files and folders.

    What I'm really looking for is not a point-by-point analysis of each item in the document, we've already done that, what I'm hoping for is to find out what standards other DBAs have used to harden their SQL Servers for PCI compliance, and whether those standards might be better suited for our particular environment.

    N.B. PCI does not actually make any recommendations as to how to harden SQL Server, it is up to us to determine that, and from what I understand there are multiple standards available and it was our internal PCI team, without consulting with the DBA team, who picked the particular CIS Benchmark document.


    Michael MacGregor, Senior SQL Server DBA
    Tuesday, January 3, 2012 7:01 PM
  • Michael,  The Microsoft site refers to an earlier version of the ParenteBeard site as an example.  You can see that this is a more nuanced recommendation:


    You need to discuss with the PCI board what they expect.  For example: Are they expecting all applications using SQL Server logins to be rewritten?   For proxy accounts, the most secure are those that use at least three tiers, as described here: http://www.sommarskog.se/grantperm.html#appproxies

    Hope someone else can help you more.


    Tuesday, January 3, 2012 7:47 PM
  • Hello Michael,

    I am writing a hardening guide for SQL Server 2008 for my company. I read a few books to gather some security practices. 

    I noticed that CIS benchmark is only for SQL Server 2005 and not 2008.

    Do you have any problem by using it for SQL Server 2008 or some points can provide some issues ?



    Friday, September 7, 2012 7:13 AM
  • Sorry Oliver, even though the thread is set to alert me when someone responds, I never saw it.

    Obviously there have been some changes that require some digging to find the appropriate option but otherwise no it was fairly easy. I also used the MS best practices for setting up SQL Server services and permissions which are covered in the hardening document but the best practices makes a bit more sense of it.

    Michael MacGregor, Senior SQL Server DBA

    Tuesday, January 29, 2013 6:42 PM
  • Hi,

    Be aware that Windows authentication enforces all your users to log to the database with a Windows account.

    Check if you don't have any critical application depending on SQL logins to work.

    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Tuesday, January 29, 2013 6:54 PM