October 14th, 2008 by Gabriel

Microsoft SQL server 2005 has been designed to be more secure with many features that increase the degree of protection.
Authentication, Authorization and encryption are the key features.

Authentication :-

Mixed and windows authentication are the two authentication modes supported by SQL 2005.

In Windows Authentication mode access is given based on a security token assigned during successful domain logon by a Windows account, which obviously have full access to SQL server. The Windows authentication mode only allows you to login/connect to SQL Server with Windows authentication. Windows Authentication is for the environment that all of your users are part of a Windows domain. Your access to SQL Server is controlled and authenticated when you log on to the Windows operating system. If you are a member of Windows’ administrator group then you should be able to use Windows Authentication to connect to SQL Server.
When a user connects through a Windows user account, SQL Server validates the account name and password using the Windows principal token in the operating system. This means that the user identity is confirmed by Windows. SQL Server does not ask for the password, and does not perform the identity validation.

The mixed authentication mode stipulates that the SQL Server authentication be done on the verification of credentials stored and maintained by the SQL Server. The logins are created in SQL Server that are not based on Windows user accounts.Users connecting using SQL Server Authentication must provide their credentials (login and password) every time that they connect.

Password Complexity :-

1. The length of the password used,
2. The type of characters that can be used like special characters, alphabets, numbers etc.
3. System reserved words should not be allwed to use,
4. Non blank password should be disallowed

Password Expiration :-
Password Expiration is determined by the value of “Maximum password age” group policy setting. The group policy settings are defined using the CREATE_LOGIN T_SQL statements.The password expiration and policy for individual accounts can be obtained from the SQL Server Management Studio interface.

Lockout Behavior :-
Lockout behavior is determined by the values assigned to “Account lockout duration”, “Account lockout threshold” and “Reset account lockout counter after” settings.

Authorization :-
Authorization features determine the level of access rights/previleges assigned to a user.
SQL 2005 allows separation of user schema objects and database objects.A schema is nothing but group of objects so that the set of objects in a schema can be treated as a unit for owning permissions.For example Execute permissions can be allowed on a role of stored procedures.

October 12th, 2008 by Gabriel

Some basic security policies that a Web Administrator should have to consider are -

1. who/which user is allowed to use the system
2. when the user is allowed to use it
3. which user granted which level of access
4. procedures for granting access to the system
5. remote and local access methods
6. system monitoring
7. suspected security breaches

If you don’t have a clear picture of what is permitted, you can never be sure when a violation has occurred.

General security precautions to take -

1. Limit the number of login accounts available on the machine and delete inactive users.
2. The Crack programs help to detect poorly chosen passwords hence force the people to use strong             passwords who have login privileges.
3. Unused services should be turned off. For e.g. if FTP is no longer needed then it should be stopped.
4. Check system regularly for suspicious activity.
5. Make sure that permissions are set correctly on system files.