Managing Access to SQL Server Instances


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 Mode

SQL 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

1.

From the Start Menu, select All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio.

2.

In the Connect To Server dialog box, click the Connect button.

3.

In the Object Explorer, right-click the SQL Server instance and choose Properties from the context menu.

4.

In the Select A Page pane, choose the Security icon.

5.

In the Server Authentication section, select the authentication mode you want, as shown in Figure 2-2.

Figure 2-2. Setting the Authentication Mode from SQL Server Management Studio.


Important

Restart the SQL Server instance for the change in authentication mode to take effect.


To decide which authentication mode to use, keep the following guidelines in mind:

  • Windows Authentication Mode In this mode, SQL Server relies on the operating system to authenticate a user requesting access to the SQL Server instance. The user does not need to provide any user credentials in the connection string because the user has already been authenticated by Windows.

  • Mixed Authenication Mode In this mode, users can connect to SQL Server using either Windows Authentication Mode or SQL Server authentication. In the latter case, SQL Server validates the user credentials against existing SQL Server logins. Using SQL Server authentication requires that the user provide a username and password to SQL Server in the connection string.

Note

It is not possible to specify SQL Server Authentication Mode only in SQL Server 2005. However, if necessary, it is possible to set up security in a SQL Server instance to restrict access to most Windows users.


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 Instance

When 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 Groups

You 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

It is possible to remove access rights to SQL Server from the local Administrators group.


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 use SQL Server Management Studio to create logins, the tool executes a similar T-SQL statement.


Default Windows Logins

When you install a SQL Server 2005 instance, the installation process creates the Windows logins described in Table 2-1.

Table 2-1. Default Windows Logins

Windows Login

Description

BUILTIN\Administrators

Login for local Administrators group on the computer where this SQL Server instance is installed.

This login is not essential for SQL Server to run.

<Servername>\ SQLServer2005MSFTEUser$ <Servername>$MSSQLSERVER

Login for the SQLServer2005MSFTEUser$ <Servername>$MSSQLSERVER Windows group. Members of this group have the required privileges to be assigned as the logon account for the associated instance of SQL Server FullText Search.

This account is essential for SQL Server 2005 Full Text Search to run.

<Servername>\ SQLServer2005MSSQLUser$ <Servername>$MSSQLSERVER

Login for the SQLServer2005MSSQLUser$ <Servername>$MSSQLSERVER Windows group. Members of this group have the required privileges to be assigned as the logon account for the associated instance of SQL Server.

This account is essential for SQL Server 2005 to run because it is the service account for SQL Server when the instance has been set up to use the local service account as its service account.

<Servername>\ SQLServer2005SQLAgentUser$ <Servername>$MSSQLSERVER

Login for the SQLServer2005SQLAgentUser$ <Servername>$MSSQLSERVER Windows group. Members of this group have the required privileges to be assigned as the logon account for the associated instance of SQL Server Agent.

This account is essential for SQL Server 2005 Agent to run.



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

It is possible for a Windows user or group mapped to a Windows login to be removed from the operating system without notifying SQL Server. SQL Server 2005 does not check for this situation, so you should examine your SQL Server instance periodically in order to detect orphaned logins. You can do this easily by using the sp_validatelogins system stored procedure.


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 Logins

In 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

SQL Server 2005 uses a self-signed certificate to encrypt logon packets to prevent unauthorized access to login information. However, once the login process terminates and the login has been validated, SQL Server sends all subsequent information packets in clear text. If you need to provide security and confidentiality for your communications, you could use two approaches: Secure Sockets Layer (SSL) and Internet Protocol Security (IPSec).


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

Although you cannot remove the sa login, you should rename and disable it to avoid unauthorized access to SQL Server through this login. See the section titled "Denying User Access" later in this chapter to learn how to disable a login.


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 Policy

When 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:

  • Password complexity

  • Password age

  • Account lockout

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:

  • Password cannot be blank or NULL

  • Password cannot be the login name

  • Password cannot be the machine name

  • Password is not "Password," "Admin," or "Administrator"

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 Permissions

You'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.

Table 2-2. Fixed Server Roles

Fixed Server Role

Description

bulkadmin

Can run the BULK INSERT statement

dbcreator

Can create, alter, drop, and restore databases

diskadmin

Can manage disk files

processadmin

Can terminate processes

securityadmin

Can manage logins and assign permissions

serveradmin

Can change server options and shut down the server

setupadmin

Can manage linked servers and execute system stored procedures

sysadmin

Can perform any activity on the server


Getting server role membership information

By 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 role

You 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 role

To 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 permissions

SQL 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 Access

In 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

SQL Server Management Studio marks disabled logins with a red arrow. This arrow appears on the login icon, found in the Object Explorer pane under the Security/Logins folder.


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

When you drop a login, SQL Server 2005 does not remove database users mapped to that login.


Caution

Dropping a login mapped to a Windows user or group does not guarantee that this user or members of this group cannot get access to SQL Server. Keep in mind that this user might still belong to some other Windows group with a valid login.


Connecting to SQL Server Using SQL Server Authentication

The 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()





Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net