Tag Archives: SQL 2012 tutorial

Understanding Logins with SQL Server

screen shot 2013-05-24 at 10.25.02 amSQL Server logins are not part of Windowsthey are saved in and managed by SQL Server. A user who connects to SQL Server via a SQL Server login is prompted for a login name and password. If you select the Windows Only mode of authentication, all SQL Server logins will be disabled and users will be able to connect only by using their Windows logins.

Mixed mode with SQL Server logins is much more flexible—for example, it supports users on Windows 9x computers—but it is also less secure. SQL Server logins and passwords are saved in the system tables in SQL Server, which are file-based. Users who gain access to these files could conceivably hack administrative logins and passwords.

If you configure your SQL Server to support SQL Server logins, there is one built-in SQL Server login that you need to watch out forthe sa login.

Beware of the sa Login

You may have noticed a login named “sa” hanging around with the other logins in the Logins node in Object Explorer. The sa, or system administrator, login is included mainly for backward compatibility with older versions of SQL Server. The sa account is mapped to the sysadmin fixed server role, and anyone who uses sa is a full system administrator, with irrevocable rights over the entire SQL Server instance and all the databases in it.

You can’t modify or delete the sa login. If you select Mixed Mode authentication when you install SQL Server, you’re prompted for a password for the sa user. Unless you set a password, anyone can log in as sa with no password, and play “let’s administer the server.” Needless to say, this is the last thing you want your users doing. Use the sa login only as a backdoor if other system administrators are unavailable or have forgotten their Windows passwords. If that happens, you probably need new admins!

WARNING! Never, EVER, use the sa login for access to a database in an application. Doing so could give a hacker administration-level control over your database server if the hacker is able to get control of the application. This has been an easy way to attack servers and is a horrible practice. Instead, either set up a custom Windows or SQL Server login for the application to use, and give that login the absolute minimum permissions necessary to run the application (which is another application of the principle of least privilege).

Password Policy and Enforcement

In versions of SQL Server before 2005, there was no easy way for a system administrator to enforce password policies that could help make a system more secure. For example, SQL Server had no way to force users to create strong passwords of a minimum length and a mix of alphanumeric and other characters. If someone wanted to create a login with a single letter for a password, you couldn’t configure SQL Server to prevent it. Likewise, there was no way to cause passwords to expire on a regular basis, such as every three months. Some people rightly saw this as a major reason not to use SQL Server logins.

SQL Server now can hook into the password policies of Windows Server 2003, Windows Vista, or later versions. The passwords are still stored in SQL Server, but SQL Server makes a call into the NetValidatePasswordPolicy() Windows API method, which was first introduced in Windows Server 2003. This API function applies the Windows password policy to SQL Server logins and returns a value that indicates whether the password is valid. SQL Server calls this function when a user creates, sets, or resets a password.


Thumbnail for 628

 

 

learnnowonline expert instructor don kielyThis post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Windows Logins via Transact-SQL

Transact-SQL provides full support for creating logins by using the CREATE LOGIN statement. The following example grants login privileges on SQL Server to the Windows user JaneAppDev on a machine named Willow (you’ll need to change the machine name if you want to try the code yourself). The brackets around the Windows login are required.

This statement adds the login but does not provide database access. You do that by using the CREATE USER statement. To add Jane to the AdventureWorks2012 database and make Production her default schema, add the following code:

 

You don’t have to name the database user the same as the server login. For example, you could instead name the user Jane in the database, assuming that it does not already contain a user Jane:

In this case, because the default schema wasn’t changed in the CREATE USER statement, the default would be dbo.

You can change a user account with the ALTER USER statement. For example, if you later wanted to make Jane’s default schema Production, you could use this code:

TIP: Only one user in any given database can be mapped to a single Windows login. A single login may, however, be mapped to users in many databases on the same SQL Server instance.

If you add one of the built-in Windows groups to SQL Server (Users, Power Users, etc.) use BUILTIN in the Windows login name instead of the machine or domain name. The following example grants login privileges to anyone in the Windows Users group. This means that anyone who can log in to Windows doesn’t have to log in a second time to get into SQL Server.

Thumbnail for 566This post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

SQL Server Security: The Threats

cloud-security

Relational databases are used in an amazing variety of applications with connections from a dizzying array of clients, ranging from handheld devices to mainframe Web service applications. This activity exposes data over widely distributed networks, particularly the Internet, which makes it accessible to almost anyone, anywhere. The databases hold a significant portion of human knowledge, including highly sensitive personal information and critical data that makes international commerce work.

These characteristics make databases attractive targets for people who want to steal data or harm its owner by tampering with it. Making sure that your data is secure is a critical part of installing and configuring SQL Server and developing applications that use it to store data.

SQL Server has everything you need to secure your server and data against today’s sophisticated attacks. But before you can use these security features effectively, you need to understand the threats you face and a few basic security concepts.

The Threats

Identifying threats to a particular set of data and its server is an important first step in understanding how to configure and use SQL Server to protect the data. A database you create to manage your grade school soccer team’s equipment inventory probably doesn’t require heavy security measures. You’ll probably want to provide at least minimal access control so that a team member can’t just randomly change the record of who has which box of soccer balls.

On the other hand, if the database has personal data about the minors on the team, such as home addresses and phone numbers, you’ll probably want to step up security protections (and may be legally required to do so). You might protect the privacy of the data by segregating access so that almost anyone with access to the database can change the equipment data but only a select few can access the personal data. If the data includes mom and dad’s credit card number, you’ll need to go to extreme lengths to protect that data.

TIP: Sometimes the best way to protect data is simply not to put it in the database—for example, credit card numbers.

The following list is a sample of the kinds of threats your data may be susceptible to, but it is by no means an exhaustive list. Plenty of resources are available on the Web that can help you analyze the risks for your specific situation. This list is intended to help you start thinking about threats and how to use the features of SQL Server to counter them or at least reduce your data’s exposure to them.

  • Theft of data: Theft of data covers various types of unauthorized access to your data, whether by an outsider hacking into your network or an insider scanning for dirt on famous people. It may involve the thrill of reading forbidden information or be motivated by the sale of stolen credit card numbers.
  • Data vandalism: A hacker who gains access to your data can change it, which can cause a whole range of problems, from public embarrassment to shutting down your entire operation when all of your customer records are deleted.
  • Protecting data integrity: One of the biggest benefits of storing data in a relational database is that the database can help protect the integrity of the data. Data integrity includes mandating that every order have an associated customer, that a date stored in a date field really represents a calendar date, and that a percentage field contains only values between 0 and 100. Data integrity probably isn’t the first thing you think of in connection with security, but it is an important part of protecting your data.
  • Illegal storage: In the past, the data you collected during the course of business was your own business. But now myriad federal laws exist in the U.S., throughout the European Union, and other countries that control the kinds of personal data you can store, how you store it, and how you protect it. The penalties for violations can be severe—both monetary penalties and damage to the public image of your company. This is less a threat to data than a threat to your organization.

You have to understand the threats to your data to know how to protect against them. Don’t waste time on measures that don’t protect against specific threats to your data. You’ll never be able to cover all hypothetical situations, and at worst you’ll make your database server completely unusable by its intended users. Security is always a compromise that balances the risks against the time and money necessary to implement and maintain safeguards.


ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals
course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

T-SQL: Counting Rows & Column Data

One of the primary purposes of a relational database is to summarize information. Other than for data entry, you will rarely look at individual orders. Instead, using the database, you will do things like summarize all the orders by week, month, or year. SQL Server performs these kinds of summaries using aggregate functions. These aggregate functions, combined with the GROUP BY clause, can combine a tremendous amount of data into a clear and concise summary. This article looks at the COUNT aggregate function for counting rows & column data.

Counting Rows

Use the COUNT function to return the number of rows in a table, as shown in the following query. This query uses the asterisk (*) as the value of the argument to COUNT so that it counts all of the rows without considering column values and whether they have NULL values:

The Figure below shows the results of the query, indicating that there are nine employees in that table in the Northwind database.

Counting the number of rows in a table

TIP: Your aggregate queries will execute faster if you use the asterisk in the COUNT function instead of a column name. The asterisk instructs SQL Server to count only the number of rows. Using a column name forces SQL Server to retrieve every value in the column and check for nulls, which aren’t included in the count. If all you’re doing is counting the rows, the asterisk is more efficient.

Counting Column Data

When you specify a column name in the COUNT function, the count excludes any null values in the column. The following query counts the total number of rows in the Employees table, and then counts the non-null values in the Region column:

Because the table contains null Region values, the result set in the Figure below shows a discrepancy between the number of employees and the number of regions.

COUNT aggregate functions do not include null values

As you can see from these results, you’ll want to be careful about whether you use an asterisk or a column name with the COUNT aggregate function (and other aggregate functions). Most of the time an asterisk will work, unless you need to account for NULL values.


ldn-expertdkielyThis post is an excerpt from the online courseware for our
SQL Server 2012: T-SQL Select, Where, and Clauses course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.