When you connect to a SQL Server instance, you must provide valid authentication information. The database engine performs a two-step validation process. First, the engine checks whether you have provided a valid login that has permission to connect to the SQL Server instance. Second, the engine checks whether the login has permission to access the database to which you are trying to connect. SQL Server 2005 defines Principals as the persons, groups, or processes that request access to database resources. Principals are arranged at Operating System, Server, and Database levels and can be indivisible or a collection. For example, a SQL login is an indivisible principal at the SQL Server instance level, and a Windows group is a collection principal at the Windows level. Selecting an Authentication ModeSQL Server 2005 supports two authentication modes for granting access to a SQL Server instance: Windows Authentication Mode and Mixed Authentication Mode. You can configure the authentication mode from SQL Server Management Studio as follows: Configuring the Authentication Mode
Important
To decide which authentication mode to use, keep the following guidelines in mind:
Note
In general, Windows Authentication is the recommended authentication mode. In Windows Authentication, there are no passwords flowing through the wire. Also, you can manage user accounts in a central enterprise store, such as Active Directory, and can take advantage of all the security features already available in the operating system. However, there are scenarios where Windows Authentication is not the best option. For example, when you need to provide access to users who do not belong to your operating system environment, such as external suppliers, or who use an operating system with an incompatible security system, select Mixed Authentication Mode and use SQL Server logins to connect these users to SQL Server. Connecting to a SQL Server InstanceWhen a user needs access to a SQL Server instance, an administrator must provide valid authentication information to the user. This authentication information depends on the authentication mode you have configured. This section will explain how to create logins for operating system users to support Windows Authentication and how to create SQL logins to support SQL Authentication. Granting Access to Windows Users and GroupsYou can allow operating system users to connect to SQL Server by creating a login for a Windows user or a Windows group. Only members of the local Windows Administrators group and the service account that starts SQL services have access to SQL Server by default. Note
You can grant access to a SQL Server instance by creating a login, either through code or through SQL Server Management Studio. The following code grants access to your SQL Server instance for the ADWORKS\jlucas Windows domain user: CREATE LOGIN [ADVWORKS\jlucas] FROM WINDOWS; Note
When you connect to SQL Server 2005 using a Windows login, SQL Server relies on operating system validation and only checks whether the Windows user has a mapped login defined in this instance of SQL Server or whether this Windows login belongs to a Windows group with a mapped login in this instance of SQL Server. A connection using a Windows login is known as a Trusted Connection. Caution
The following Microsoft Visual Basic code (included in the sample files as ConnectUsing WindowsAuth.vb.txt) shows how to connect to SQL Server using Windows Authentication: ' Create an instance of a SQLConnection object Dim oConn as New SQLClient.SQLConnection ' Define the connection string oConn.ConnectionString="server=localhost; database=AdventureWorks;" + _ "Integrated Security=SSPI" ' Open the connection oConn.Open() ' Do some work ... ' Close the connection oConn.Close() Granting Access to SQL Server LoginsIn Mixed Authentication Mode, you can also create and manage SQL Server logins. When you create a SQL Server login, you should assign a password for the login. The users must provide this password when connecting to the SQL Server instance. When you create a SQL login, you can assign the default database and the default language for this login. When an application connects to SQL Server without specifying the database context and the language, SQL Server uses the default properties from this login for the connection. Note
You can grant access to a SQL Server instance by creating a SQL Server login, either through code or through SQL Server Management Studio. The following sample creates a SQL login called "Mary" and assigns AdventureWorks as the default database for Mary: CREATE LOGIN Mary WITH PASSWORD = '34TY$$543', DEFAULT_DATABASE =AdventureWorks; The SQL Server 2005 installation process creates a single SQL Server login named sa. The sa login is always created, even when you select Windows Authentication Mode during the installation wizard. Best Practices
You can obtain information about SQL Server logins from the sql_logins catalog view using the following code: SELECT * FROM sys.sql_logins; Enforcing Password PolicyWhen you use SQL Server logins, you should implement strong password policies for these logins to avoid weakening SQL Server security over time. SQL Server 2005 provides the ability to enforce the operating system password policy on SQL Server logins. If you are running SQL Server on a Windows 2003 server, SQL Server uses the NetValidatePasswordPolicy API (application programming interface) to control:
If you are running SQL Server on a Windows 2000 server, SQL Server uses the SQL Server Native Password Complexity rule, introduced by Microsoft Baseline Security Analyzer (MBSA) to enforce the following password rules:
You can turn on the password policy using Transact SQL code: CREATE LOGIN Mary WITH PASSWORD = '34TY$$543' MUST_CHANGE, CHECK_EXPIRATION = ON, CHECK_POLICY = ON; Managing Instance PermissionsYou've learned how to give a user access to a SQL Server instance, but we haven't discussed what permissions these logins might have in SQL Server. Typically, a user needs access to some data. However, you might need to create some logins with permissions to perform administrative tasks. SQL Server provides server roles at the instance level to accomplish this task. (Server roles are fixed, so you cannot create roles at the instance level.) Table 2-2 lists the fixed server roles created by SQL Server 2005.
Getting server role membership informationBy querying the IS_SRVROLEMEMBER system function, you can find out whether the current user belongs to a server role or not. The following Transact SQL sample returns 1 if the actual login belongs to the sysadmin server role and 0 otherwise. SELECT IS_SRVROLEMEMBER ('sysadmin'); Adding a login to a server roleYou can add a login to an existing server role using the sp_addsrvrolemember system stored procedure. The following sample adds Mary to the sysadmin server role: EXECUTE sp_addsrvrolemember 'Mary', 'sysadmin'; Removing a login from a server roleTo remove a login a from a server role, you can use the sp_dropsrvrolemember stored procedure. The following sample removes Mary from the sysadmin server role: EXECUTE sp_dropsrvrolemember 'Mary', 'sysadmin'; Granting individual permissionsSQL Server 2005 provides a more granular permission structure that allows you to have finer control over login operations. You can manipulate permissions with the GRANT, DENY, and REVOKE statements. Information about servers' permissions is accessible from the sys.server_permissions catalog view. The following sample grants Mary rights to create and execute a SQL Server Profiler trace: GRANT ALTER TRACE TO Mary; Users can obtain information about their own permissions by using the fn_my_permissions function. The following sample displays the permissions for the user: SELECT * FROM fn_my_permissions (NULL, 'SERVER'); More Info You can see the list of server permissions in SQL Server Books Online in the section titled "GRANT Server Permissions (T-SQL)." Denying User AccessIn some situations, such as when a user leaves the organization, you need to deny access to a specific login. If this denial is temporary, you can disable the login instead of removing the login from the instance. By disabling the access, you retain the login properties and mappings to database users. When you re-enable the login, you can work with the same properties as before. To disable and enable a login, execute the following ALTER statement. -- Disable the login ALTER LOGIN Mary DISABLE; -- Enable the login ALTER LOGIN Mary ENABLE; You can check for disabled logins by querying the sql_logins catalog view, as in the following sample: -- Disable the login ALTER LOGIN Mary DISABLE; GO -- Query the system catalog view SELECT * FROM sys.sql_logins WHERE is_disabled=1; GO -- Enable the login ALTER LOGIN Mary ENABLE; Tip
On the other hand, if you need to remove a login from the instance, you should use the DROP LOGIN statement. The following sample removes a login. DROP LOGIN Mary; Caution
Caution
Connecting to SQL Server Using SQL Server AuthenticationThe following Visual Basic code (included in the sample files as ConnectUsingSQL Auth.vb.txt) shows how to connect to SQL Server using SQL Server authentication: ' Create an instance of a SQLConneciton object Dim oConn as New SQLClient.SQLConnection ' Declare the connection string, ' with specific username and password oConn.ConnectionString="server=localhost; database=AdventureWorks; " + _ "user id= Mary; password=34TY$$543" ' Open the connection oConn.Open() ' Do some work ... ' Close the connection oConn.Close() |