sql 2005 security question

  • hi folks

    i've been handed the unenviable task of locking down out installations of sql 2005 with a server hardening project. the joys.....

    one of the key issues that has been flagged by our compliance reporting software is sql server weak password status.

    i've checked this and we have a complex password for sa account and windows authentication is on.

    on further investigation it appears that the service accounts (that reference the local host name of the server) and are installed by default have blank or null passwords.

    to compound things further these accounts are automatically put into sysadmin status. i've figured a script to remove the accounts from sysadmin status.

    however the questions how do i get round this the null password status? is there a way to script this so the default service passwords are not null.

    to make it a bit more complex i want to automate this via our altiris server. so i won't know the hostname of each server beforehand.

    answers on a postcard to....

  • If SQL Server 2005 is installed on a Windows Server 2003/2008 system, it will, by default, use the account password settings of the domain (or the local computer, if not set with the Default Domain Group Policy in Active Directory). That means someone would have to manually disable the password policy when the SQL Server login is created for this to be bypassed. Is the password policy set at your domain level? If not, you should be able to set minimal settings on the server where SQL Server resides by making the appropriate setting changes in the server's Local Security Policy.

    K. Brian Kelley
    @kbriankelley

  • thanks for getting back.

    it is indeed installed on a 2003 server and complex passwords are enabled at domain level / in AD. therefore editing local policy won't be possible without removing the box from the domain.

    however you have made me realise something. these are not domain user accounts but SQL 2005 groups that are created locally on the server and in sql as part of the install. i'll have to double check the null status of these groups? when i get back to to work tomorrow. i'm snowed in today. the whole country has ground to a halt. best snow in the uk for 15 years 😀

    also note there is a service account that is utilized on the Altiris box for installation that does have rights to the domain so this would allow the bypass of security as it has sufficient permissons.

    i'm off to make a snowman. be back soon.

    cheers obruin

  • made it into work despite the snow and double checked this.

    the password is indeed complex however not complex enough.... it seems like the service account that altiris uses is deemed insecure as it has a portion of the username in the password. this account is added to the local sql groups that are installed as part of the installation. this would be why it comes up as failed.

    case closed.

  • Don't forget to consider other things like where backups are being placed, the same for logfiles, who can run backups, physical security for the backup media itself, and even who's handling your offsite backups.

    Won't do you a lot of good to have the server hardened, but later discover someone stole the offiste backup tapes out of some IT flunkie's car.

    Also look at any kind of log-files and such for applications that access the db.. I did some contracting work for a major wireless carrier once, and they were VERY secure when it came to the db itself etc.. however the call voice reponse system that let customers do things like pay their bills with a credit card over the phone, was logging things like the customer's full name, address, FULL-SSN, credit card, CVN, DoB, etc.. a TON of PII data, all to simple comma delimited plan text files on some unix server somewhere.. not even obfuscated much less encrypted. Frankly I've no idea WHY that system needed to write all that out into logs, but it was doing it.

    Worse yet, when I raised the issue of security for those files, and the risk their simple existance presented for the company, I was basically patted on the head and told 'it's unix, it's secure' and to go back to testing the ACD system. To this day I have no idea if anything has changed, and how secure or insecure those logfiles are.

  • Some other general points to look at are

    - Least privilege for your service accounts. They don't need to be local admins. A standard user account with the necessary privileges is enough. These are mostly documented in the BOL "Setting Up Windows Service Accounts". Note that I said mostly. If you modify the Access This Computer From The Network and remove EVERYONE, then you will need to add in your SQL DBE, SQL AGT, SQL BRW and SQL SSIS accounts in here explicitly.

    - Ensure that there is group based separation between Local Administrators and your SQL Admins. Even if they are the same team. Use Role Based groups (Google RBAC).

    - NTFS permissions for SQL data, log and backup locations. You can have all the internal SQL security you like, but if anyone can just pickup the ?df and bak files via Windows Explorer then you have a gaping hole. By default Windows leaves \Administrators and CREATOR OWNER with access, in some cases FULL CONTROL. There is no need for these principals to have access to the SQL directories. Create RO and RW groups and populate with the SQL Agt, SQL DBE accounts with SQL FTS, SQL SSIS, SQL BRW as required. (Aside: SQL requires that the SQL DBE account has FULL CONTROL to data and log directories as it executes a Take Ownership and Change Permission operation when it creates files)

    - Turn on Auditing within SQL and Windows

    - SQL Auditing - Success and Failed SQL Logins

    sqlcmd -S -Q "USE [master];EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3;"

    - Consider NTFS Auditing. Generally you would only need to Audit for Success and Failure for Take Ownership and Change Permissions for the Administrators group (As Local Administrators can execute Take Ownership (this is set in the Local Security Policy or Group Policy if you override it))

    - Windows Auditing - Object Access Failure is a useful audit tool

    If you enable Object Access Auditing then remove Success auditing for EVERYONE on HKLM\System\CurrentControlSet\Services\EventViewer. Otherwise everytime you look at something in Explorer or EventViewer, you'll fill your Security Log

    - Disable unnecessary windows services

    - Disable unneccesary SQL protocols (eg Named Pipes in most cases)

    - Use static TCP ports for your SQL Instances

    - Use Kerberos, you may need to manually set your SPNs

    - Either disable the SQL Browser completely or hide your SQL Instances from the Browser

    - Standardise as much as possible

    - Stick with known good Service Packs and Cumulative Updates

    - Don't apply Service Packs and Cumulative Updates simply because they are available. Evaluate each SP and CU for specific reasons to apply that are relevant to your organisation and platform

    - Remove [BUILTIN]\Administrators from the sysadmin role

    - Don't use the sa SQL User. Keep the password secure

    That should get you started

    --
    Andrew Hatfield

  • thanks for the posts

    we have enabled the majority of these already.

    all good stuff to know.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply