SQL Server 2008 tips

Support Notes in using Windows Server 2008

SQL Server 2008 tips

Postby stephen » Wed Apr 29, 2009 10:31 am

Microsoft Introduction to SQL Server 2008 Security
download here
http://download.microsoft.com/download/a/c/d/acd8e043-d69b-4f09-bc9e-4168b65aaa71/SQL2008SecurityOverviewforAdmins.docx

Here are some extracts from above document.
SQL Server 2008 comes packed with numerous features, many of which are installed in a disabled state. For example, CLR integration, database mirroring, debugging, Service Broker, and mail functions are installed but are not running and not available until you explicitly turn them on or configure them.

Password Policy Enforcement
With SQL Server 2008, password policy enforcement is built into the server. Using the NetValidatePasswordPolicy() API, which is part of the NetAPI32 library on Windows Server 2003, SQL Server validates a password during authentication and during password set and reset, in accordance with Windows policies for password strength, expiration, and account lockout.

SQL Server always checks the password policy by default, but you can suspend enforcement for individual logins with either the CREATE LOGIN or ALTER LOGIN statements as in the following code:
CREATE LOGIN bob WITH PASSWORD = 'S%V7Vlv3c9Es8',
CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF

If the user unsuccessfully attempts to log on too many times and exceeds the attempts allowed in the password policy, SQL Server locks the account, based on the settings in the Windows policy. An administrator can unlock the account with the ALTER LOGIN statement:
ALTER LOGIN alice WITH PASSWORD = '3x1Tq#PO^YIAz' UNLOCK


Authorization
After authentication, it is time to think about what an authenticated login can do. In this area, SQL Server 2008 and SQL Server 2005 are more flexible than earlier versions. Permissions are now far more granular so that you can grant the specific permissions required rather than granting membership in a fixed role that probably carries with it more permissions than are necessary. You now have far more entities, (securables) to which you can assign permissions that are more granular.

Principals and Securables
In SQL Server 2008 a principal is any individual, group, or process that can request access to a protected resource and be granted permission to access it. As in previous versions of SQL Server, you can define a principal in Windows or you can base it on a SQL Server login with no corresponding Windows principal.

The scope of the influence of the principal depends on the scope of its definition, so that a Windows-level principal is more encompassing than a SQL Server-level principal, which is more encompassing than a database-level principal. Every database user automatically belongs to the fixed public role.
Windows-level principals
• Windows Domain login
• Windows Local login
• Windows group
SQL Server-level principals
• SQL Server login
• SQL Server login mapped to a Windows login
• SQL Server login mapped to a certificate
• SQL Server login mapped to an asymmetric key
Database-level principals
• Database user
• Database user mapped to SQL Server login
• Database user mapped to a Windows login
• Database user mapped to a certificate
• Database user mapped to an asymmetric key
• Database role
• Application role
• Public role

The other part of authorization is the objects that you can secure through the granting or denying of permissions. Figure 1 lists the hierarchy of securable objects in SQL Server 2008. At the server level, you can secure network endpoints to control the communication channels into and out of the server, as well as databases, bindings, and roles and logins. At the database and schema level, virtually every object you can create is securable, including those that reside within a schema.

Roles and Permissions
For a sense of the number of permissions available in SQL Server you can invoke the fn_builtin_permissions system function:
SELECT * FROM sys.fn_builtin_permissions(default)
stephen
 
Posts: 507
Joined: Thu Feb 09, 2006 9:37 am
Location: Brisbane

Re: SQL Server 2008 tips

Postby stephen » Thu May 14, 2009 6:47 pm

This site has a discussion on
Upgrading an Application from SQL Server 2000 to SQL Server 2008
With a summary of how to move your database from SQL Server 2000 to SQL Server 2008
http://www.devx.com/MicrosoftISV/Article/40522/0/page/2
stephen
 
Posts: 507
Joined: Thu Feb 09, 2006 9:37 am
Location: Brisbane


Return to Windows Server 2008

Who is online

Users browsing this forum: No registered users and 2 guests

cron