Microsoft SQL Server Database Security Techniques
This paper examines the design of a secure online membership and payment management system built on Microsoft SQL Server with a C#.NET front end. It outlines the core goals of database security — prevention, detection, and recovery — and surveys common threats ranging from hardware vulnerabilities to unauthorized user access. The paper then details specific Microsoft SQL Server security mechanisms, including access controls and authentication, views, rules and constraints, concurrency control, audit tracking, encryption, and backup and recovery. It also addresses SQL injection prevention and web-layer security measures such as firewalls, SSL/TLS, and digital certificates, concluding that a layered, multi-mechanism approach is essential for protecting modern web-accessible databases.
- Introduction and Security Goals: Security goals: prevention, detection, and recovery defined
- Sources of Security Threats: Entry points and threat types for web databases
- Physical Security: Isolating SQL Server from direct internet access
- Microsoft SQL Server Security Mechanisms: Eight SQL Server mechanisms from access control to encryption
- Web Application Security Measures: Firewalls, SSL, and digital certificates for web tier
- Conclusion: Layered security essential despite no foolproof system
✍️ How to write this paper — guide, tools & examples ▾
What makes this paper effective
- The paper is logically layered, moving from abstract security goals to concrete SQL Server mechanisms and then to web-layer defenses, creating a clear argument for defense-in-depth.
- Each security mechanism is introduced with a definition, explained in the SQL Server context, and connected to the broader security objective it serves, giving readers both conceptual and practical grounding.
- The use of a threat taxonomy table and numbered entry points organizes a complex threat landscape into digestible, scannable reference material.
Key academic technique demonstrated
The paper demonstrates effective synthesis of multiple technical sources into a coherent, thematically organized argument. Rather than summarizing sources sequentially, it integrates citations from Policht, Bishop, Ambler, and others within topical subsections, showing how different authorities converge on layered security as best practice.
Structure breakdown
The paper opens with a brief context statement, then defines security goals (prevention, detection, recovery). It surveys threat sources before addressing physical security. The longest section systematically covers eight SQL Server security mechanisms as numbered subsections. A shorter section addresses web-tier protections (firewall, SSL, digital certificates). A concise conclusion ties the layers together. The bibliography follows standard citation conventions throughout.
Introduction and Security Goals
The design of an online membership and payment management system for the web — using a Microsoft SQL Server database and a front end built in Microsoft Visual Studio C#.NET — involves a variety of complex security issues. This paper discusses the goals of security and common security threats. It then describes in detail Microsoft SQL Server security techniques, along with a brief overview of web application security measures that can be taken to ensure adequate security for the membership and payment management application.
Database security is the protection of a database against unauthorized access, whether intentional or accidental (Phippen). Security countermeasures should combat threats and their outcomes. Given a security policy's specification of "secure" and "nonsecure" actions, security mechanisms can prevent an attack, detect an attack, or enable recovery from an attack (Bishop, 2003).
Bishop (2003) explains the differences between prevention, detection, and recovery. Prevention seeks to make the attack fail. Typically, prevention involves the implementation of mechanisms that users cannot override and that are trusted to be implemented in a correct, unalterable way, so that an attacker cannot defeat the mechanism by changing it. Detection accepts that an attack will occur; the goal is to determine that an attack is underway or has occurred, and to report it. The attack may be monitored to provide data about its nature, severity, and results. Typical detection mechanisms monitor various aspects of the system, looking for actions or information indicating an attack. Recovery has two forms. The first is to stop an attack and to assess and repair any damage it caused. For example, if an attacker deletes a file, one recovery mechanism would be to restore the file from backup tapes. Because an attacker may return, recovery also involves identifying and fixing the vulnerabilities that were exploited to enter the system.
Sources of Security Threats
A decade ago, database security issues were rarely reported. At that time, databases were physically secured and housed in central data centers, and external access was carefully mediated. Now, however, databases are externally accessible so that customers, suppliers, and partners may be directly connected. Unfortunately, greater access brings greater potential for security threats.
Web-based applications have numerous possible entry points that present opportunities for unwanted access (Burleson):
Internet access — If hackers can guess the IP address of a server, they can telnet to the server and receive a login prompt. At that point, all they need is a user ID and password to gain access.
Port access — All web applications are configured to listen on a predefined port for incoming connections, and they generally use a listener daemon process to poll for connections.
Server access — A four-tiered web application incorporates a series of web servers, application servers, and database servers. Each of these servers presents a potential point of entry, and if remote shell access is enabled, a hacker who gains access to a single server may gain access to many others.
Network access — If hackers know the port, IP address, database ID, and password, they can gain direct access to the database.
The following table summarizes common sources of security threats and the ways each can contribute to security concerns (Phippen).
Hardware: Fire, flood, bombs, power loss or surges, and theft of equipment.
DBMS and application software: Program alteration and theft of programs.
Database: Amendment or copying of data, and data corruption.
Communication networks: Wiretapping and cable breakage.
Administrators: Inadequate security practices.
Users: Using another user's access credentials, unauthorized access, hacking, blackmail, and viruses.
Operators: Trapdoors, alterations, inadequate training, and inadequate security practices.
Physical Security
Physical security means that a SQL Server system is running in a controlled-access environment in which only approved personnel have physical access to the system (Huston, 2003). SQL Server should be isolated from web infrastructure and direct internet access. If SQL access across the internet is absolutely required, it should be available only via a proxy system that enforces strict rules and scrubs out data streams that could compromise or damage the system or the data it contains.
All indirect access — such as access via websites and online applications — should include appropriate bounds checking and input validation. For example, SQL delimiters must be stripped from input before it is passed to the database system. Furthermore, values for SQL access should always be contained and managed only on the server side of the application, since client-side manipulation of any client-side values is both likely and dangerous.
Conclusion
The web provides a convenient, inexpensive, and instantaneous way of providing data access. Now that information can be disseminated so easily, it is equally important to ensure that the information is accessible only to those who have the right to use it in the intended way. Network and operating system security alone are not sufficient to defend against dynamic threats to a web application. Database and web application server security techniques are a necessity. However, even with these extra layers of security in place, there is no such thing as a foolproof security system. As soon as new security features are added, attackers begin finding new flaws. Nevertheless, prevention, detection, and recovery together go a long way toward thwarting security violations.
References
Ambler, S. (2004, April 1). "Concurrency Control." Retrieved January 5, 2005, from http://www.agiledata.org/essays/concurrencyControl.html
"Backup." Microsoft Developer Network. Retrieved January 5, 2005.
Bishop, M. (2003). Computer Security: Art and Science. Addison-Wesley.
Burleson, D. "Database security in your Web-enabled apps." Retrieved January 6, 2005, from http://www.governmentsecurity.org/articles/DatabasesecurityinyourWeb-enabledapps.php
Carpenter, D. (2000, October 18). "Indexed views in SQL Server 2000." Retrieved January 5, 2005, from http://www.sqlteam.com/item.asp?ItemID=1015
Chapple, M. "Access controls in SQL." Retrieved January 5, 2005.
"Database security and administration." (2001). Retrieved January 5, 2005.
"Digital Certificate." TechTarget SearchSecurity. Retrieved January 5, 2005.
"Firewall." TechTarget SearchSecurity. Retrieved January 5, 2005.
Huston, B. (2003, May 18). "Protecting the SQL Server." ITworld. Retrieved January 5, 2005.
Meier, J. D., Mackman, A., Dunner, M., Vasireddy, S., Escamilla, R., and Murukan, A. (2003, June). "Securing your database server." Microsoft Developer Network. Retrieved January 5, 2005.
Mullins, C. (1998, December). "Using constraints in SQL Server." Retrieved January 5, 2005, from http://www.craigsmullins.com/sql_1298.htm
"Optimistic and pessimistic concurrency." Microsoft Developer Network. Retrieved January 5, 2005.
Phippen, A. "Database security." Retrieved January 6, 2005.
Policht, M. (2004, April 23). "SQL Server 2000 security — part 2 (authentication)." Database Journal. Retrieved January 5, 2005, from
Policht, M. (2004, October 8). "SQL Server 2000 security — part 13 — SQL Injection attack." Database Journal. Retrieved January 5, 2005, from
"Secure Sockets Layer." TechTarget SearchSecurity. Retrieved January 5, 2005.
Songini, M. (2004, July 27). "Microsoft offers sneak peek of SQL Server 2005." Computerworld. Retrieved January 5, 2005.
Zikopoulos, P. (2001). "The database security blanket." Retrieved January 5, 2005, from http://www.governmentsecurity.org/articles/Thedatabasesecurityblanket.php
Create your account
Always verify citation format against your institution’s current style guide requirements.