Pin Me

Protecting Your SQL Server

written by: Steve Mallard•edited by: Bill Bunter•updated: 5/6/2010

Microsoft's SQL Server is one of the most widely used databases in the world. With assets in the database, the individual tables are capable of holding millions of records. SQL Servers can lie on your intranet, extranet or on the internet.

  • slide 1 of 5

    Microsoft SQL Server

    Microsoft's SQL server is used throughout companies and organizations nationwide. With SQL's unique superior performance, no other database matches the speed and capabilities of this database. SQL can deliver millions of transactions per second delivering consumer needs in a matter of seconds.

    Because industries rely on this server to hold data that affects the assets and continuity of buisiness; information technology professionals need to look at the protection of one of the most valuable assets they have which is this database server. Security is a multipart function inside of industries today. Security is not only stopping hackers but covers a broad range of possible disasters.

  • slide 2 of 5

    Security Basics

    I have written several articles that reflect the need for security throughout all organizations. While many industries struggle with the basics, here is a review of the basic necessities:

    • Hardware Firewall
    • Software Firewall
    • Application Testing and Penetration Testing
    • Rules and Policies for the Firewall
    • Limited Physical Access to Servers
    • Cameras and other physical parameters in place at the server location
    • Indrustion Detection Servers (IDS Systems)
    • Manage Switches
    • Reliable on-site and off-site backups
    • UPS (Battery Backups)
    • Policy and Procedures
    • Trained Personnel with ongoing training
    • Update Servers (WSUS)
    • Application of Service Packs and Patches
    • Protection of OS and Applications
    • Limited logical access control
    • Application Passwords
    • End User Training
    • SSL and VPN connections
    • Limited protocols
    • Self research and training of security trends

  • slide 3 of 5

    Microsoft System Center Data Protection Manager

    The most underused program to protect Microsoft's SQL is Microsoft System Center Data Protection Manager. While Microsoft refers to Microsoft System Center Data Protection Manager as DPM 2007, this software is often called just DPM.

    DPM uses a combination of transaction log replication with SQL Server VSS Writer which helps to ensure that databases can be recovered if nessessary. With block level synchronization, the functionality is added to the aforesaid replication process to ensure your database is secure from failure.

    Information Technology professionals are trained (or should be) in Disaster Recovery and Planning. This software helps protect your databases overall integrity and ensures you will have continuous protection prior to any disaster.

  • slide 4 of 5

    Network and Application Security

    SQL openly runs on port 1433. Using the aforesaid security basics, all ports should be blocked at the server or firewall level to ensure the server is truly safe. If you have an internal server that does not have access to the outside world, port 1433 should be blocked at the hardware firewall level. Because these servers can lie on the DMZ, extra precautions should be taken to limit access and to audit and prepare software to be 'tight'.

    With SQL, backup plans, maintenance plans and the individual security roles that can be defined in Microsoft's SQL server should be set and monitored several times throughout the day. Administrators need a strong sa password and should limited the abilities and functionality of users of this database on the database and on the application.

    Openly functional ODBC connections under the Administrative Tools applet can leave a client computer ultimately connected to the SQL server. Client computers need to be protected and time out periods should be set on the application. Again ultimately if a workstation is left 'open' or unsecure, a malicious person could access the data by simply using an application such as Microsoft Access to link or import the table into the application by using the ODBC connection that may be setup under this applet.

    Systems audits and application audits should be enforced and event logs should be read daily.

  • slide 5 of 5

    Conclusion

    Because a query can be ran in Access through an ODBC connection, records can be exposed by any client pc with such access. The IT Manager or Database Administrator should ensure the above steps are taken. Always use VPNs when available. While servers (SQL) can take the role and functionality from intranet to internet, each role must be carefully analyized and appropriate permissions should be enabled to protect the data. Operating Systems that connect to the database should be secure physically and logically. Wireless access to these types of servers should limited and only on a secure wireless network.

    Applications written specifically for SQL should be analyzed and known 'hacker' scripts should be ran against these types of servers.