SQL Server Security

8 Key excerpts on "SQL Server Security"

• 

  eBook - ePub

  Microsoft SQL Server 2012 Administration

  Real-World Skills for MCSA Certification and Beyond (Exams 70-461, 70-462, and 70-463)

  • Tom Carpenter(Author)
  • 2013(Publication Date)
  • Sybex

    (Publisher)

  Even in everyday life you cannot really be free from danger or threat. You can only manage the level of risk you accept in relation to dangers and threats. The same is true for computer and network security. Computer or network security should not be thought of as the state of being free from danger or threat, because this results in an unusable system. So, let’s use the following as our working definition of security in relation to SQL Server: SQL Server Security is the state in which an acceptable level of risk is achieved through the use of policies and procedures that can be monitored and managed. The phrase “acceptable level of risk” establishes a foundation that is both achievable and measurable. You can achieve an acceptable level of risk by creating and documenting policies, implementing procedures in compliance with those policies, and ensuring the adherence to the policies through auditing and enforcement. In a SQL Server environment, security is about data protection. The procedures should result in a state that ensures the following: The data can be accessed by authorized users only. The data is secure in storage. The data is secure in transit. The data is recoverable. Now let’s cover each of these more fully: The data can be accessed by authorized users only. To ensure that the data can be accessed by only authorized users, a strong authentication system must be utilized. SQL Server provides both SQL logins and Windows authentication, which are covered in Chapter 19. Windows authentication should be used whenever possible because it provides better security than SQL logins. The data is secure in storage. The data is secure in storage when the database files are stored in a secure file system. A secure file system requires that a user be authenticated before accessing any files. The NTFS file system provides such security for internal drives and many external drive systems as well

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  SQL Server 2019 Administrator's Guide

  A definitive guide for DBAs to implement, monitor, and maintain enterprise database solutions, 2nd Edition

  • Marek Chmel, Vladimír Mužný(Authors)
  • 2020(Publication Date)
  • Packt Publishing

    (Publisher)

  Chapter 4 : Securing Your SQL Server

  Securing SQL Server is a crucial task, as SQL Server usually holds very important and sensitive information in your environment. You need to apply many principles in order to properly secure your databases. Fortunately, SQL Server offers many options to help you with securing the data you store on it. Securing an SQL server is quite a complex task; you need to consider that SQL Server is a client application running on the Windows server, which is accessible via a network. In order to fully secure the environment, you need to secure the Windows Operating System (OS ) too and put proper security measures on the network as well.

  In this chapter, we will be covering the following topics:

  • Configuring SQL Server service accounts
  • Configuring authentication and authorization
  • Encrypting SQL Server data
  • Data Discovery and Classification
  • SQL Server vulnerability assessment
  • Encrypting SQL Server connections

  Configuring SQL Server service accounts

  An important part of the configuration of your SQL Server environment is the service accounts that are used for running your SQL Server services. Many of these can be configured immediately during the installation of your SQL Server. There are several options for you to select from while configuring an account for SQL Server services, as follows:

  • Virtual accounts
  • Managed service accounts
  • Group managed service accounts
  • Built-in system accounts
  • Domain user accounts
  • Local Windows accounts

  Let's now get into each of the accounts in detail.

  Virtual accounts

  The default choice of any OS higher than Windows Server 2008 R2 is a virtual account

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Professional Microsoft SQL Server 2014 Administration

  • Adam Jorgensen, Bradley Ball, Steven Wort, Ross LoForte, Brian Knight(Authors)
  • 2014(Publication Date)
  • Wrox

    (Publisher)

  So, in reality, anything can happen, and it is best to be prepared for all scenarios. Your corporate databases are key strategic assets, and the data in them must be protected from leakage and tampering. You don't want your company to be the next subject of headlines about leaking customer information to identity thieves.

  The key concepts of software security are authentication and authorization. Authentication is the way the software securely identifies who is trying to connect and authorization is the way the software decided what the user can do once connected. Authentication will keep bad guys out, and authorization will keep authenticated users from doing things they shouldn't. SQL Server 2014 has very sophisticated authentication and authorization features that provide many options for securing data. This chapter covers the options and how to configure them.

  AUTHENTICATION TYPES

  Securing data consists of two essential parts: authentication (which is proving you are who you say you are) and authorization (which defines the data you have access to, and what you can do to the data). You authenticate yourself to SQL Server by providing something only you have—your fingerprints, a smart card, or a password, for example.

  You have two ways to authenticate to the Microsoft SQL Server instance: via SQL Server authentication and via Windows authentication. When you install SQL Server, you have an option to select whether the SQL Server instance should support Windows authentication only, or whether it should support both Windows and SQL Server authentication.

  SQL Authentication

  SQL Server authentication was the original authentication method supported by SQL Server when it was based on the Sybase codebase. With SQL Server authentication, the application or the user specifies the username and password to be used to authenticate against the SQL Server instance. When specified, the username and password are put into the connection string, which the application then uses when it connects to the SQL Server instance. With SQL Server authentication, the actual username and password are stored in the master database within the database instance.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Designing SQL Server 2000 Databases

  • Syngress(Author)
  • 2001(Publication Date)
  • Syngress

    (Publisher)

  Chapter 5

  Database and Server Security

  Solutions in this chapter:

  ■  Planning SQL Server Security

  ■  Security Options in SQL Server

  ■  Implementing Server and Database Security

  Introduction

  Although data are always considered a priceless asset in most organizations, and protecting data is a high priority, new applications such as electronic commerce, or e-commerce, have opened additional pathways to the vault through public network connections such as the Internet. Even though most organizations remain unscathed by attacks, it is not without significant effort spent planning and implementing security in the organization. By combining server- and file-level security provided by Windows 2000 with SQL Server 2000’s user- and role-level security models and encrypted network communications, you can protect your data, the heart of your organization. SQL Server 2000 offers configurable user and role security levels for database and object access. With features such as replication and database access over the Internet, multiprotocol encryption and SQL Server’s new support for SSL can protect your data on the wire. SQL Server 2000’s integration with Windows 2000 Active Directory adds support for Kerberos, context delegation, and centralized management.

  This chapter reviews the security architecture of SQL Server 2000 and the available security modes. As you work through the chapter, you will create users and database roles and assign permissions to the database and its objects, such as tables and views. To further secure your server, you will learn the advantages and disadvantages of multiprotocol encryption and SSL support in SQL Server.

  Planning SQL Server Security

  SQL Server Security, or any system’s security for that matter, is easiest to manage when you start with a plan. You might be tempted to start with a rather relaxed view toward your security configuration if you have only a few databases, objects, or users. Don’t give in to that temptation. As systems grow, if their security model doesn’t support that growth, security holes will appear. In addition to planning for the future, a good security model can make your administration easier today. A well-planned security model is much simpler to administer than one that is not logical, well documented, and standardized.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Microsoft SQL Server 2012 Bible

  • Adam Jorgensen, Jorge Segarra, Patrick LeBlanc, Jose Chinchilla, Aaron Nelson(Authors)
  • 2012(Publication Date)
  • Wiley

    (Publisher)

  It's essential to secure the database objects to prevent unauthorized access that may compromise data. SQL Server 2012 provides the ability to create user-defined roles for easier security management. Role-based security enables database administrators to standardize access control over databases and provide a higher degree of granularity of object permissions.

  Role-based security is an integral part of the SQL Server Security model. This chapter covered securables, and by now you can design, implement, and maintain a robust security model that satisfies your organization's security requirements.

  Passage contains an image

  Chapter 34 Data Encryption

  In This Chapter

  1. Introducing Data Encryption
  2. Using the SQL Server Tools

  Securing access to the table is usually sufficient (commonly called data protection in motion); if not, securing the column suffices (commonly called data protection at rest). However, for some information, such as Social Security numbers or secret government data, the information's sensitivity warrants further security by encrypting the data stored in the database.

  SQL Server 2012 can encrypt data inside SQL Server with passwords, keys, certificates, or transparent data encryption in the Enterprise edition. All editions of SQL Server support data encryption.

  What's New with Encryption in SQL Server 2012

  • Transparent Data Encryption (in Enterprise only)

  Introducing Data Encryption

  Data encryption is the process to transform information using a process that basically makes text unreadable. The data is unreadable to anyone except those who have a special key to decrypt the data. Without the key, the data is impossible to unscramble.

  Symmetric encryption uses one key to both encrypt and decrypt the sensitive data in the database. The same key can encrypt and decrypt the messages, which is considered riskier because of the encryption algorithm. This may not be a problem when encrypting and decrypting data inside SQL Server.

  Asymmetric is considered more secure because the decryption key does not need to be known to encrypt. This type of encryption has two keys, one being a private key, which is paired with a second public key. In other words, if I encrypt some data using my public key and you already have my private key, then you can decrypt the data.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  How to Cheat at Securing SQL Server 2005

  • Mark Horninger(Author)
  • 2011(Publication Date)
  • Syngress

    (Publisher)

  Chapter 1

  Introduction to SQL Server Security

  Solutions in this chapter:

  ■  Security: Why Worry About It?

  ■  Installing SQL Server

  ■  Building Security into Your Application

  ■  Managed Code

  ☑  Summary

  ☑  Solutions Fast Track

  ☑  Frequently Asked Questions

  Introduction

  This chapter explains why you should be concerned with SQL Server Security and introduces some of the more generic ideas, such as the principle of least access. It also covers the concept of planning for security in the design phase, building security into your application from the ground up, as opposed to “bolting it on” afterward, and installing and configuring SQL Server features. We also discuss the security risks associated with managed code in SQL server. CLR integration is the feature that allows managed code to be run in SQL server.

  Multifaceted SQL Server Security

  Security in SQL Server 2005 is multifaceted, and it can seem impossibly complicated. SQL Server 2005 security starts at the ground level and builds upon itself. This chapter discusses producing the foundations required to begin thinking in a natively secure manner, upon which the rest of the security principle in this book can be built. This chapter also starts you on the learning curve required to implement SQL Server 2005 security by providing a guide in your journey into SQL Server 2005 security.

  Security: Why Worry About It?

  In February 2000, the company RealNames informed its customers that its database had been broken into and that information including credit card numbers had been taken. The thought of being the person in charge of security on that database is enough to make anyone break into a cold sweat. How exactly do you go to your boss and tell him that the database that fuels your company and holds your customer’s information has been broken into?

  Then there were the W32.CBlade and W32.Digispid worms. These worms attacked SQL Servers using the SA account and a blank password. The fact that either of these two worms could get into systems spoke volumes about the security of the databases they were attacking. The one positive aspect was that when the SQL Slammer worm hit in 2003, IT security professionals had some knowledge of how databases are attacked by worms. Even more fortunate was that even though the Slammer worm was one of the most aggressive worms to date, it was dedicated to creating a denial-of-service (DoS) type attack where the goal was to flood the Internet with traffic, versus a database breach.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  The Best Damn Exchange, SQL and IIS Book Period

  • Henrik Walther, Mark Horninger, Chris Adams(Authors)
  • 2011(Publication Date)
  • Syngress

    (Publisher)

  Chapter 19

  Introduction to SQL Server Security

  Solutions in this chapter:

  ■  Security: Why Worry About It?

  ■  Installing SQL Server

  ■  Building Security into Your Application

  ■  Managed Code

  ☑  Summary

  ☑  Solutions Fast Track

  ☑  Frequently Asked Questions

  Introduction

  This chapter explains why you should be concerned with SQL Server Security and introduces some of the more generic ideas, such as the principle of least access. It also covers the concept of planning for security in the design phase, building security into your application from the ground up, as opposed to “bolting it on” afterward, and installing and configuring SQL Server features. We also discuss the security risks associated with managed code in SQL server. CLR integration is the feature that allows managed code to be run in SQL server.

  Multifaceted SQL Server Security

  Security in SQL Server 2005 is multifaceted, and it can seem impossibly complicated. SQL Server 2005 security starts at the ground level and builds upon itself. This chapter discusses producing the foundations required to begin thinking in a natively secure manner, upon which the rest of the security principle in this book can be built. This chapter also starts you on the learning curve required to implement SQL Server 2005 security by providing a guide in your journey into SQL Server 2005 security.

  Security: Why Worry About It?

  In February 2000, the company RealNames informed its customers that its database had been broken into and that information including credit card numbers had been taken. The thought of being the person in charge of security on that database is enough to make anyone break into a cold sweat. How exactly do you go to your boss and tell him that the database that fuels your company and holds your customer’s information has been broken into?

  Then there were the W32.CBlade and W32.Digispid worms. These worms attacked SQL Servers using the SA account and a blank password. The fact that either of these two worms could get into systems spoke volumes about the security of the databases they were attacking. The one positive aspect was that when the SQL Slammer worm hit in 2003, IT security professionals had some knowledge of how databases are attacked by worms. Even more fortunate was that even though the Slammer worm was one of the most aggressive worms to date, it was dedicated to creating a denial-of-service (DoS) type attack where the goal was to flood the Internet with traffic, versus a database breach.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Professional Microsoft SQL Server 2008 Administration

  • Brian Knight, Ketan Patel, Wayne Snyder, Ross LoForte, Steven Wort(Authors)
  • 2011(Publication Date)
  • Wrox

    (Publisher)

  The term surface area is taken from the mathematical concept of surface area but is used in the security realm to describe what is attackable or accessible in an application or system. For instance, if a Windows server has SQL Server and IIS installed, those two applications represent attackable points of the server. Compared to a server that has neither installed, it could be said that all else being equal, the server with SQL Server and IIS has a greater surface area than the second server.

  The CIA Triad

  Another security concept you should use for your planning is called the CIA triad (see Figure 9.1 ).

  Figure 9.1

  When talking about security, especially data security, the following three areas are core principles of our goals:

  • Confidentiality: Data is not accessed by unauthorized people or in an unauthorized way.
  • Integrity: Data is not modified by unauthorized people or in an unauthorized way.
  • Availability: Data is available whenever needed by authorized people.

  How you protect and lock down your SQL Servers affects all three principles. Confidentiality and integrity are logical when dealing with data. Availability, however, is also crucial to security. Inaccessible data doesn't do anyone any good, so your configuration must reflect how your customers will access the data. If this means putting a SQL Server in mixed mode so a web server can connect, this meets the availability principle.

  Defense in Depth

  Defense in depth is a relatively simple concept: When possible, use different security mechanisms to protect an asset so that should a single mechanism fail, additional safeguards are in place. For instance, consider your work PC. The following mechanisms are likely in place to protect your PC from some of the malicious attacks that might originate from the Internet:

  • External router at the perimeter.
  • Firewall at the perimeter.
  • Personal firewall on the PC itself.
  • Anti-virus software running on the PC.
  • Patch management methodology and deployment software.

  For instance, imagine a new attack using the NetBIOS protocol. NetBIOS is a standard communications means for PCs that must trust one another on a LAN or a WAN. It's not something that should be received from the Internet. Therefore, both the external router and the firewall should prevent NetBIOS traffic coming from the Internet. Your PC's personal firewall should realize that the IP address doesn't match the trusted network your PC resides on and prevent the traffic. Failing that, if Microsoft has released a security patch for the vulnerability being attacked, that patch management methodology and deployment software will have deployed the patch and updated your PC. Even if that didn't happen, once the attack is known, definitions downloaded by your anti-virus software should recognize the attack and stop it in its tracks.

  Sign up to read

  Learn more about book