locked
Security tips reqd for website database RRS feed

  • Question

  • I need to provide access to a SQL Server 2008 database to a website for a client. I haven't done this before and I'm looking for tips on security.

    The website will be hosted on a server either in a DMZ or external to the network. Access to the SQL server will be through a Cisco router.
    The network is a workgroup, not a domain. The website needs write access to one database.

    The client wants enough flexibility that I can't restrict them to using stored procedures. It'll be their responsibility to ensure they don't wreck their database.

    I'll give them datawriter permissions on that database, and enforce a strict password policy.

    What other things should I do to safeguard the SQL server from the evils of the internet?
    Monday, March 28, 2011 9:43 PM

Answers

  • - give the minimum rights to application logins (avoid sysadmin, db_owner). Usually, db_reader and db_writer are enough, sometimes ddl_admin is required for TRUNCATE.

    - give the minimum rights to the SQL Server Account.

    - do not use “sa” account.

    - Do not open SQL port on the Internet.

    - Access SQL through VPN over Internet.

    - Change SQL default port.

    - Use complex passwords and if possible expiration (could be an issue for application logins).

    - use and enable only TCP-IP (no named pipes) for remote connections.

    - have specialized accounts, distinguish “diag” “admin” “application” etc… and use Windows authentication as much as possible (not easy on a workgroup).

     

    • Proposed as answer by Peja Tao Wednesday, March 30, 2011 3:15 AM
    • Marked as answer by Alex Feng (SQL) Wednesday, April 6, 2011 3:59 AM
    Tuesday, March 29, 2011 9:15 AM

All replies

  • - give the minimum rights to application logins (avoid sysadmin, db_owner). Usually, db_reader and db_writer are enough, sometimes ddl_admin is required for TRUNCATE.

    - give the minimum rights to the SQL Server Account.

    - do not use “sa” account.

    - Do not open SQL port on the Internet.

    - Access SQL through VPN over Internet.

    - Change SQL default port.

    - Use complex passwords and if possible expiration (could be an issue for application logins).

    - use and enable only TCP-IP (no named pipes) for remote connections.

    - have specialized accounts, distinguish “diag” “admin” “application” etc… and use Windows authentication as much as possible (not easy on a workgroup).

     

    • Proposed as answer by Peja Tao Wednesday, March 30, 2011 3:15 AM
    • Marked as answer by Alex Feng (SQL) Wednesday, April 6, 2011 3:59 AM
    Tuesday, March 29, 2011 9:15 AM
  • Thanks for the tips StefDBA, I think I can implement most of them.
    Tuesday, March 29, 2011 9:39 PM