SQL Server security – (Part 1)
Authentication
SQL Server supports two types of authentication:
- Windows authentication: User is authenticated based on a local or domain Windows user account.
- SQL Server authentication: User is authenticated based on a SQL Server login.
You can configure SQL Server to use only Windows authentication or to use both Windows and SQL Server authentication, that is, also known as Mixed Mode authentication. You cannot configure it to support only SQL Server authentication.
Windows authentication
Windows authentication provides the best security because:
- It uses the underlying Windows Server authentication protocols.
- Passwords are governed by domain or local password policies.
- User accounts and passwords are stored and transmitted securely.
When you use Windows authentication only, the sa account (system administrator, an unrestricted administrator account) is disabled.
SQL Server authentication
If you need to support SQL Server authentication for legacy applications, you should make it as secure as possible. Some guidelines are:
- Protect the sa account with a strong password.
- Enforce the password policy.
- Enforce password expiration policy.
- Note: SQL Server enforces the password policy and the password expiration policy, only if it’s installed on a Windows Server 2003 or later computer.
The sa account presents a security risk, because it has a well-known name. In earlier versions of SQL Server, you could configure a sa account with a blank password. This is not allowed in the current version, but it’s still a potential risk. You can reduce the risk is by disabling sa and using only administrator accounts that you define. You can also prevent attacks on the sa account by name by renaming the sa account to a different account name.
Changing authentication type
You select the authentication type during installation. To change it after installation:
- Launch SQL Server Management Studio
- In Object Explorer, right-click the server instance and choose Properties.
- Click Security.
- Select the authentication type as either:
- Windows Authentication mode.
- SQL Server and Windows Authentication mode.
- A warning box appears informing you that you need to restart SQL Server.
- Click OK.
- Right-click the server instance and choose Restart.
- Click Yes to confirm that you want to restart SQL Server. Restarting the SQL Server instance doesn’t restart the operating system.
Recommendations to choose SQL Server Authentication
In accordance with SQL Server security best practices, you should always choose Windows authentication for your SQL Server installation unless legacy applications require SQL Server authentication for backward compatibility and access reasons. If that is the case, then you should choose Mixed Mode authentication.
Windows authentication is more secure than Mixed Mode authentication and, when enabled, Windows credentials (that is Kerberos or Windows NT LAN Manager [NTLM] authentication credentials) are trusted to log on to SQL Server. Windows logins use a number of encrypted messages to authenticate SQL Server and the passwords are not passed across the network during authentication. Moreover, Active Directory provides an additional level of security with the Kerberos protocol. As a result, authentication is more reliable and managing it can be reduced by leveraging Active Directory groups for role-based access to SQL Server. In comparison to Windows Authentication mode, Mixed Mode Authentication supports both Windows accounts and SQL-Server-specific accounts to log into SQL Server. The logon passwords of SQL logins are passed over the network for authentication, which makes SQL logins less secure than Windows logins.
Login management
Login accounts
SQL Server supports the following types of logins:
- Windows: Uses Windows to authenticate the user.
- SQL Server: Uses a password to authenticate the user.
- Certificate-mapped: Uses a Public Key Infrastructure (PKI) certificate to authenticate the user. Primarily used when encrypting or signing data with a digital signature.
- Asymmetric key: Uses a key to authenticate the user. Primarily used when encrypting or signing data with a digital signature.
You can add logins, modify logins, and delete logins using SQL Server Management Studio or using Transact-SQL. You can’t create certificate or asymmetric key logins using SQL Server Management Studio.
Creating a Windows login using SQL Server Management Studio
To create a Windows login using SQL Server Management Studio:
- Right-click Logins and choose New Login.
- Verify that Windows authentication is selected.
- Click Search. The Select User or Group dialog appears.
- Type the name of the user or group you need to map to the login.
- Click Check Names.
- Click OK.
- Select the Default database for the user. The Default database is the one the user connects to if the user doesn’t specify a database.
- Select the Default language for the user’s connections. The language for a connection determines how SQL Server formats date and time information and controls the language used for system messages.
- Click OK.
Creating a SQL Server login using SQL Server Management Studio
You use a similar process to create a SQL Server login:
- Right-click Logins and choose New Login.
- Choose SQL Server authentication.
- Enter and confirm a password. By default, Enforce password policy, Enforce password expiration, and User must change password at next login are selected.
- Clear “Enforce password policy” if you do not want the Windows password policies to be enforced for the account. You should not clear this password unless you support applications that use weak passwords for SQL Server logins. Weak passwords are a security risk.
- Clear “Enforce password expiration” if SQL Server shouldn’t enforce the Windows password expiration policy. SQL Server can enforce the password expiration policy only if the password policy is also enforced.
- Clear “User must change password at next login” if you do not want to require the user to change the password the first time the user logs in. SQL Server can enforce the password change only if the password policy is also enforced.
- Select the Default database for the user.
- Select the Default language for the user.
- Click OK.
A strong password is one that’s difficult to guess. For example, the more characters in a password, the more difficult it is to guess. Also, mixing upper case, lower case, numeric, and non-alphanumeric characters makes a password more difficult to guess.
Using CREATE LOGIN
You can use the CREATE LOGIN statement to create a Windows, SQL Server, certificate-mapped, or asymmetric key login. The syntax for creating a Windows login is:
CREATE LOGIN login_name {
FROM WINDOWS [WITH
The Windows options you can specify are:
- DEFAULT_DATABASE = database
- DEFAULT_LANGUAGE = language
You must use the domain_nameusername syntax, enclosed in square brackets, for the login_name. For example, to create a login for the Finance group in Contoso.com, execute the following:
CREATE LOGIN [contosofinance] FROM WINDOWS
To create a SQL Server login, use the syntax:
CREATE LOGIN login_name WITH PASSWORD = ‘password’
[HASHED] [MUST_CHANGE], [option_list]
The options list can include the following:
- SID — Allows you to specify a Globally Unique Identifier (GUID) for the login. SQL Server assigns one if one isn’t specified.
- DEFAULT DATABASE — Sets the default database.
- DEFAULT LANGUAGE — Sets the default language.
- CHECK_EXPIRATION — Enables the password expiration policy. Off by default.
- CHECK_POLICY — Enables the password policy. On by default.
- CREDENTIAL — Maps the login to a credential. A credential is a SQL Server object used to provide access to resources outside SQL Server.
For example, to create a SQL Server login for BasitFarooq and require that he change his password the next time he logs on, you execute:
CREATE LOGIN BasitFarooq WITH PASSWORD = ‘P*ssw0rd’
MUST_CHANGE, CHECK_EXPIRATION=ON
Continue to Part-2….