We'll discuss surface area later in the week. Today let's talk about if you're able to connect to SQL Server. Connecting to SQL Server and actually logging in are two different things. I can run a scanner and detect SQL Server is present and then launch a telnet session. That connects. But I can't log in because I can't authenticate. Today we're going to focus on the authentication and what happens immediately thereafter.

Authentication:

SQL Server supports two mechanisms for user connections:

• Windows authentication
• SQL Server authentication

In the first case, SQL Server passes validation off to the operating system. If the OS says you're good, then SQL Server checks against its lists of logins to see if you're explicitly allowed or if a group you're a member of is allowed. This group could be nested, so while I might not see the exact group your user account is a member of, because of the nesting, you're included. SQL Server authentication is where SQL Server keeps track of the login and password and you're submitting those credentials for validation. If they check out, you're in.

The rule of thumb is to try and use Windows authentication whenever you can. There are a myriad of reasons for this that go beyond the blog post. Furthermore, you should use Windows groups whenever you can, and again, the reasons go beyond this short blog post. Basically, you want to get into a mindset of knowing that your default best practice is use windows groups. In cases where something else is proposed, understand why. There may be a valid reason. You just need to know and accept why.

Where do you look? Use the sys.server_principals catalog view in SQL Server 2005 and above. If you're still supporting SQL Server 2000, use syslogins.

Authorization:

Here you're looking to see what the logins can do. There are two things you need to look at. If you're talking SQL Server 2005 and above, you need to look at explicit permissions. You'll need to look at sys.server_permissions. Likely you're going to join this to sys.server_principals to know who is assigned what permission. If you're supporting SQL Server 2000, this functionality doesn't exist. You're off the hook.

In all supported versions, you also need to look at roles at the server level. Prior to SQL Server 2012, you're only looking at fixed server roles, like sysadmin and securityadmin. Know what they can do based on the version of SQL Server. Know what logins are members. You can use sp_helpsrvrolemember with fixed database roles. If you're supporting SQL Server 2012 already, look for user defined server roles. Those can be assigned permissions. That means looking at sys.server_permissions for roles, too. And in order to find out who is a member of the roles, you'll need to use sys.server_role_members. That's because sp_helpsrvrolemember doesn't support user-defined server roles. If you're only supporting SQL Server 2005 and above, you may choose to go this route as the catalog view has been included since that version.

What Is Okay?

This depends from installation to installation, company to company, and application to application. Some general rules we always try to follow:

• Use Windows authentication when we can.
• Grant access to SQL Server using Windows groups when we can.
• Limit the number of logins that are members of the fixed server roles, especially roles like sysadmin and securityadmin.
• Always strive to follow the Principle of Least Privilege (just the rights to do the job and no more).

You may be forced into a situation with a 3rd party product that requires db_creator membership. Another product may not be supported unless it has sysadmin membership (I've seen this... even with products from security companies). And that's why there's no one size fits all solution. Evaluate your server level security individually. Try to follow these rules. Write up and understand why you can't when you can't.