Problem solve Get help with specific problems with your technologies, process and projects.

Database server security vs. the triple threat, part 3

In this article, we will focus on security logins, role-based security, and the triple threat.

Is your company taking a holistic view of network security, or is it too deep in the trenches -- and constrained by the economic downturn -- to see the big picture? Is the security life cycle of your database server(s) about to expire? Will you take the initiative to create a security project, lead the esprit de corps to enforce better security and avoid what I call the triple threat? In the absence of security, there exists an opportunity for network intrusion.

We complete our three-part series on protecting your database server with this follow-up to my last article "DBA or CIA - Protect your database server, part 2". In this article, we will focus on security logins, role-based security, and the triple threat. When was the last time you checked your database server security? Have you installed the latest cumulative patch (Q316333) for SQL Server 2000, MS02-056? Join me on a journey to explore and identify the security vulnerabilities in your network.

SQL 2000 security logins
Let's begin by reviewing the security login accounts that exist in your database server. (The Security Login folder is accessible in the Enterprise Manager and is part of the SQL Server Registration Group.) You will notice the active login accounts (in the pane on the right), once you de-collapse the Security folder and click on Logins. Now is the time to thoroughly examine the list of login accounts and verify the need for -- and the type of -- database access level.

Avoid unnecessary database access to your server by removing obsolete or unused accounts. If an account is required, verify the user's role and grant access only to the database tables required to achieve the business objective. It is vitally important that you change the default database (Master) specified for the login account used via MDAC/ODBC from front-end web servers; failure to change this default database will result in a server compromise.

TIP: Change the default Master database
Change the default database assigned to new login accounts, especially for the login accounts that are used via MDAC/ODBC connections from your front-end Web servers. Also, avoid putting any passwords in the Registry where they will be accessible to others and become common knowledge. (I've witnessed environments where the password is defined in a String Value in the Registry and available to anyone remotely.)

Server role
To change the default database, assign server role(s), and define the database access, select the login account and double-click on it. Find the Defaults section and "Specify the default language and database for this login" in the General tab, which is displayed in the Properties box of the login you selected. Next, go to the Server Roles tab and -- with caution -- assign the appropriate role to grant security privileges for the user.

Keep in mind that access granted at the Server Role level is propagated throughout the database scheme. In other words, you're assigning privileges that have system-wide ramifications and can put the integrity of your SQL Server at risk. Take extra precaution when assigning the role of system administrators, as this role rules over all the other (security, server, setup, process, disk, and bulk-insert) administrative roles and the database creator role.

Replace the BUILTIN administrators group with the individual login names to avoid an inherent security issue later due to an overlooked member of the built-in group. Take note of the fact that other administrative roles (for example, process and server) have the kill and/or shutdown privileges, similar to the system administrator's role. Next, click on the Database Access tab and prepare to add or remove the appropriate database(s) necessary for each account.

Your database server should only include the databases of your company and not sample databases, such as Northwind. Make sure that you have a full backup before removing any databases or making changes to a production server. Always make sure that you have a safety net to fall back on, especially when working in a production environment and when time is of the essence (which is also the case in most corporate environments).

Database role
Database Access allows you to set the role for each database that you previously assigned to a login account. Depending on your work situation, you may implement the application role (with set password) instead of the standard role typically used. Although you cannot modify the special database role Public (which is similar to the Guest account in NT) or drop its members, you can restrict users from being the "db_owner" or from being other roles (for example, db_accessadmin, db_securityadmin, db_datawriter, and so forth)

The triple threat
The triple threat is not a new group of hackers (at least to my knowledge) or a set of new hacking tools -- yet it can hurt your organization and/or your career! If you recall, in the beginning of this article, I asked, "Is your company taking a holistic view of network security...?" and "Is the security life cycle of your database server(s) about to expire...?" Security is our weapon against a hacking culture that thrives on creating chaos across a connected world of computer villages. Lack of security is an open invitation to hackers everywhere to take ownership of your data and, ultimately, your network. The triple threat is target, time and cost:

  • Target goal: What are you trying to accomplish?
    You need to see the big picture to know how much network security is needed in your organization. How will you get there?
  • Time goal: How long will it take to get there?
    Know how long it will take to implement network security. If security is a moving target, then when will the implementation of security be complete?
  • Cost goal: How much will it cost to get there?
    Know how much it will cost to maintain network security. How much are you willing to spend to recover from a network attack? Will you maintain your security life cycle?

In short, project management might be an option for you, but security is not. The greatest security threat to your organization occurs when you have an understanding of the security vulnerabilities that exist but do not do anything about them. Having a short-term security plan or taking a passive role will eventually assist an attack on your network.

SQL security complete checklist

  1. Use a custom installation path for SQL program files and SQL data files.
  2. Change the default TCP/IP (1433) port for SQL connections.
  3. Implement security between your front-end servers and the database server.
  4. Restrict access to your database server to front-end servers that require a connection.
  5. Block all TCP/IP and UDP ports except for the SQL port you plan to use.
  6. Disable unnecessary O/S services and processes; uninstall non-SQL programs.
  7. Apply TCP/IP filtering in the O/S level and only accept your SQL port.
  8. Make sure that you are running the most stable and secure MDAC version and patch/hotfix.
  9. Disable unnecessary SQL network libraries, components and agents.
  10. Confirm server role is SQL only and do not run IIS on the same server.
  11. Customize Auto Start SQL Service and Agent to use local System Account.
  12. Use Mixed Mode for Authentication Mode and set unique password for SA account.
  13. Apply service packs and hotfixes at O/S and SQL level.
  14. Restrict Enterprise Manager access.
  15. Implement a mechanism to automatically store production log files off-site without using IPC connections.
  16. Monitor your log files on a daily basis for suspicious or failed attempts.
  17. Don't settle for unlimited default values for present and near-future connections; specify a realistic number.
  18. Implement intermediary security (I can't emphasize this enough) between your clients and database server.
  19. Carefully consider which authentication mode is appropriate for your environment. If you are the DBA or a network administrator, the decision is simple.
  20. Assign another database to new logins other than the default Master database.

It's been my experience that some of these basic steps have often been overlooked by experienced IT professionals. If you're serious about security, you must pay attention to security details and leave no room for hackers.

Please write to me or check out my Web site ( and let me know if this article has brought to light any potential weak links in your network.

Luis Medina is the author of "The Weakest Link Series," which offers network managers an opportunity to identify ongoing network security issues. Luis also answers security questions in's Ask the Expert section. Submit a security question to Luis here or view his previously answered Ask the Expert questions.

This was last published in October 2002

Dig Deeper on Network Security Best Practices and Products

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.