locked
SQL server security RRS feed

  • Question

  • Hi All,

    Please help me to implement below step in SQL server and please suggest what are the step need to follow.

    These vulnerabilities can be resolved with a single step. The estimated time to perform this step is 30 minutes.

    Restrict database access

    Estimated time: 30 minutes

    Configure the database server to only allow access to trusted systems. For example, the PCI DSS standard requires you to place the

    database in an internal network zone, segregated from the DMZ

    This will address the following issue: Database Open Access (database-open-access).

    Thursday, July 14, 2016 12:12 PM

Answers

  • Hi Karthik,

    My apologies for the delay in response and to the best of my knowledge PSI is standard to maintain security.

    However if your project (Client) is trying to acquiring PSI Security standards you can proceed as per their requirements.

    1. Disable sa account:

    Yes, you can disable this account. (As it has sysadmin privileges, SQL Authenticated account and considering this account is maintained by Administrators and can be used any DBA in the group hence any changes to the DB or the SQL can't traced in specific so PSI wants sa to be disabled)

    Note: Before disabling this sa account ensure no jobs or DB's are owned by this account if there are any alter authorization to the required account.

    2. Applying SQL Server security patches: 

    It is a good practice to keep SQL Server updated with the recent patches but test in UAT or Dev server first and backup DB's before applying service pack in PROD.

    Note: Resource DB is a hidden database, should also be backed up. 

    3. Use Windows Authentication and disable mixed authentication:

    This is environment specific but be infromed that disabling mixed authentication and enabling Windows authentication allows only windows authenticated users who are mapped to SQL Server.

    Note: Before applying this change get approval from client and inform to all stake holders. So that client may decide whether any new windows accounts have to be created to get access to the SQL instance.


    Grateful to your time and support. Regards, Shiva

    Wednesday, July 20, 2016 9:59 AM

All replies

  • Hi Karthik,

    In SQL Server you have two authentication modes

    1. Windows Authentication (Here user should have a Windows level authenticated login and this login should be mapped in SQL Server)

    2. Windows & SQL Server Authentication (Mixed) (Here only SQL Login is created, mapped)

    Note: This account can be used anywhere using SSMS client.

    Hence Windows Authentication is more secured/trusted than Mixed authentication for more details please refer below article:

    https://msdn.microsoft.com/en-us/library/ms144284(v=sql.110).aspx


    Grateful to your time and support. Regards, Shiva

    • Proposed as answer by Teige Gao Friday, July 15, 2016 3:24 AM
    Thursday, July 14, 2016 12:22 PM
  • Hi Shiva,

    Thanks for reply, 

    Can please confirm below three point will cover PSI security

    1. Disable the sa account 
    2. Applying all SQL Server security patches
    3. use Windows authentication and disable mixed mode authentication


    Thursday, July 14, 2016 1:25 PM
  • Hi Karthik,

    My apologies for the delay in response and to the best of my knowledge PSI is standard to maintain security.

    However if your project (Client) is trying to acquiring PSI Security standards you can proceed as per their requirements.

    1. Disable sa account:

    Yes, you can disable this account. (As it has sysadmin privileges, SQL Authenticated account and considering this account is maintained by Administrators and can be used any DBA in the group hence any changes to the DB or the SQL can't traced in specific so PSI wants sa to be disabled)

    Note: Before disabling this sa account ensure no jobs or DB's are owned by this account if there are any alter authorization to the required account.

    2. Applying SQL Server security patches: 

    It is a good practice to keep SQL Server updated with the recent patches but test in UAT or Dev server first and backup DB's before applying service pack in PROD.

    Note: Resource DB is a hidden database, should also be backed up. 

    3. Use Windows Authentication and disable mixed authentication:

    This is environment specific but be infromed that disabling mixed authentication and enabling Windows authentication allows only windows authenticated users who are mapped to SQL Server.

    Note: Before applying this change get approval from client and inform to all stake holders. So that client may decide whether any new windows accounts have to be created to get access to the SQL instance.


    Grateful to your time and support. Regards, Shiva

    Wednesday, July 20, 2016 9:59 AM
  • HI Shiva,

    Thanks you very much

    Thursday, July 21, 2016 7:25 AM