SQL Server 2012 Security Model
The SQL Server 2012 security ideal is the best place to start to comprehend SQL Server security. The model is founded on three classes that separate the basic foundations of security:
• Principals—Principals are the objects that demand security to SQL Server resources. They include Windows users, SQL Server users, and database users.
• Objects—Objects are the SQL Server resources to which permissions can be granted.
• Permissions—Permissions link principals with objects.
Below shows the security mechanisms contained in each tier of the SQL Server 2012 security model. The columns are ordered from left to right, originated on the way security is recognized.
| Principals | Permissions | Securables |
|---|---|---|
| Windows: | GRANT/REVOKE/DENY | Server Scope |
| Groups | CREATE | Login |
| Domain Login | ALTER | Endpoint |
| Local Login | DROP | Database |
| SQL Server: | CONTROL | Database Scope |
| SQL Login | CONNECT | User |
| Server Role | SELECT | Role |
Some complication has been introduced, based on the classified nature of some of the security modules. Security can be recognized on these classified components, which in turn flows the security to the fundamental components. In addition, not all the permission mechanisms apply to every object. Many of the objects have a select number of authorizations that apply to them; equally, many permissions apply only to a select number of objects. As an example, SELECT permission is applicable to objects such as tables and views but would not be suitable for stored procedures.
Authentication Modes
The first level of security come across when accessing SQL Server is known as authentication. The authentication procedure performs the authentication needed to allow a user or client machine to connect to SQL Server. This linking can be granted via a Windows login or SQL Server login.
Windows Authentication Mode
Windows Authentication mode authorizes the account name and password, using info stored in the Windows OS. A Windows account or group must be recognized first, and then security can be established for that account in SQL Server. This manner has the benefit of providing a single login account and the ability to leverage domain security features, such as password length and expiration, account locking, encryption, and auditing. MS endorses this method.
Mixed Authentication Mode
Mixed authentication permits for both Windows authentication and SQL Server authentication. SQL Server authentication is founded on a login that is produced in SQL Server and lives in SQL Server only. No Windows account is convoluted with SQL Server authentication. The account and password are established and preserved in SQL Server. SQL Server logins can be created with stronger password implementation that help better guard the login.
SQL Server authentication is useful in situations in which a Windows DC does not control network access. It can also be useful for Web apps or legacy apps, where it may be burdensome to start a Windows user account for every connection to the database server.
How to Set SQL Server 2012 Authentication Mode
You can choice the authentication mode when you install SQL Server, and you can alter it after the installation. To change the authentication mode afterward installation, you rightclick the server node in the Object Explorer and select the Properties option. When the Server Properties dialog performs, you select the Security page. The Security page permits you to specify Windows Authentication mode or SQL Server and Windows Authentication mode (that is, mixed authentication). Any changes to the authentication mode need a restart of SQL Server to make the change effective.