How secure are your databases?

No, not your network. Just your databases. If your company is like most, the strongest security has been placed around the outside of your network, at the perimeter. Not all threats come through the front door, though. An increasing number of breaches occur because a hacker found a way around the perimeter security or an insider leaked data (either accidentally or maliciously). The way to counter both of these is to strengthen the security closer to the data itself.

So let’s talk about security. Over a series of posts, I’ll break down the SQL Server security model into its many components, then show you how they all fit together. As with so many topics in a complex platform like SQL Server, there are exceptions to nearly everything. For now, we’ll stick to the core concepts to keep the discussion straightforward; we’ll talk about the exceptions later.

Who are you, and what do you want?

The first concept to understand about SQL Server’s security model is the difference between authentication and authorization.

• Authentication defines who is being given a right. SQL Server formally calls the authentication objects principals, but you’ll also see the older terms logins and users.
• Authorization defines what rights are being given. Formally, these are called permissions. In modern versions of SQL Server, permissions are very granular and can be found on nearly every object in the instance. There’s also a vast hierarchy that interrelates all of the permissions. (We’ll cover permissions in a future post. For now, know that they’re there.)

If you’ve managed the security of other systems, these classifications will probably be familiar. They’re a fairly common construct.

The authentication objects can be further broken down into two types:

• Server principals, or logins, are located at the instance level. They are, in essence, the connection point to the instance. Only instance-level permissions can be assigned directly to a server principal; they have no authority inside the confines of a database on their own.
• Database principals are also called users. Located inside each database, they relate a database to a given server principal. All permissions within a database are assigned to a database principal. A database principal has no authority outside the confines of a database, and a connection cannot be made directly to a database principal.

The rest of this post will talk only about the server principals (logins).

Where can I find you?

Before we create a login, we need to decide what type of login we want to create – or rather, what should authenticate the login.

• SQL-authenticated logins are authenticated by SQL Server. The login name, password, and SID are all stored in the master database.
• Windows-authenticated logins are authenticated by Windows. Only the SID is stored in SQL Server, and it matches the SID used by Windows. SQL Server never sees nor asks for the account’s actual credentials; instead, it is passed a token by the active Windows session.

When possible, use Windows-authenticated logins. SQL-authenticated logins are less secure since all of the credentials are stored with the data. However, SQL-authenticated logins are still widely used since they can be used in some circumstances in which a Windows-authenticated login cannot.

Aside from what system performs the authentication, there aren’t a lot of differences between the various types of server principals.

Practice: Creating a server principal

Now that we know what a server principal is, let’s create one.

But first, we need to check to see what authentication mode our instance is using. By default, SQL Server is installed with only Windows-authenticated logins enabled. If you also want the option to use SQL-authenticated logins, you must enable mixed mode. You can do this on the Database Engine Configuration page when you’re first installing SQL Server:

If you’re working with an instance that has already been installed, you can change the mode using Management Studio. Connect to the instance, then right-click on the instance name in the Object Explorer, and select Properties. On the Security page under Server authentication, select SQL Server and Windows Authentication mode.

To create a login, expand the Security folder in the Object Explorer, then right-click on Logins and select New Login…. A window will be displayed that looks like this:

To create a Windows-authenticated login, specify the full Windows username in Login name, including the domain: domain\user. (If you’re using a local Windows login, substitute the computer name for the domain name: computer\user.)  You can also search for the login you want to use.

To create a SQL-authenticated login, specify a username, then click SQL Server authentication and enter a password. By default, your password must comply with the password requirements in effect on that computer.

For those that prefer scripts, a login can also be created using Transact-SQL.

-- Windows-authenticated
-- Note the square brackets. A backslash isn't a valid character in a SQL Server object name
-- by default. The brackets allow us to override that.
CREATE LOGIN [MYDOMAIN\ed] FROM WINDOWS;
 
-- SQL-authenticated
CREATE LOGIN ed WITH PASSWORD = 'myPa$$word';

That’s it! You now have a server principal on your SQL Server instance. At this point, it won’t be functional – we haven’t given it any permissions to do anything. That is a subject for our next post.

The post The SQL Server Security Model, Part 1: Logins [Foundations Friday] appeared first on The Data Files.