SQL Server Security Modes

SQL Server provides three different security modes for validating logon information.

Standard Security

Standard security is the default installation option for SQL Server. It provides the simplest security model because security exists independently of the Windows NT domain model. You control the level of access a user has to the database and its objects by setting security options within SQL Server itself.

Standard security means the SQL Server uses its own validation process for checking all logon accounts. With standard security, each user provides an additional valid SQL Server logon ID and password. Each SQL Server logon specifies the allowed access to each database and its objects (tables, views, stored procedures, and rules). The logon accounts are considered valid if they appear in the encrypted syslogins table. Authentication consists of comparing the provided user name and password against similar information maintained in the SQL Server database. This is the easiest security model to integrate with IIS.

You must use standard security if you are not using multi-protocol or Named Pipes. Standard security works for all network configurations. With standard security, SQL Server does not consider the domain the network logon accounts are using, and also ignores the Windows NT user name and password scheme.

Standard security is the best choice if your application uses Internet Information Server. An important part of this configuration is whether or not an authenticated protocol will be used.

Windows NT Integrated Security

With integrated security, SQL Server leverages Windows NT authentication to validate SQL Server logon accounts. This allows the user to bypass the standard SQL Server logon process. With this approach, a network user can access a SQL Server database without supplying a separate logon identification or password because SQL Server obtains the user and password information from the Windows NT network security process.

Integrated security works for all trusted connections and requires either Named Pipes or Multi-Protocol (with Named Pipes). Trusted connections can be from other Windows NT, Windows 95, or Windows for Workgroups workstations, and also from Microsoft LAN Manager running under either MS-DOS or Microsoft Windows clients.

SQL Server applications with integrated security benefit from all of the Windows NT security features. This includes domain-wide user accounts, encrypted passwords, password aging, logon auditing, and general user administration. Integrated security requires working closely with the network administrator to grant the necessary access permissions according to your application's security model.

Note   If your application uses Internet Information Server, you probably will not use SQL Server integrated security.

You can implement integrated security with SQL Server by creating several Windows NT security groups and granting each group the necessary data access rights. For example, you would grant the SystemUsers group permission to perform SELECT, UPDATE, INSERT, and DELETE using normal application processes. Similarly, you would grant the SystemAdmin group full SQL Server administrator rights and permissions.

Note   Regardless of the SQL Server's logon security mode, ODBC and DB-Library client applications can be configured to always request a trusted connection from the server. The benefit is that — with a correctly configured Windows NT account — the SQL Executive can connect to remote servers.

Mixed Security

The mixed security mode allows validation by using either standard or integrated security modes. With mixed security mode, SQL Server uses integrated security for all trusted connections. For example, for a connection to a trusted ODBC source, authentication occurs via the Windows NT authentication process. If the integrated mode authentication fails, standard security mode is used requiring the entry of valid SQL Server logon information.