SQL Server 2008- Audit

SQL server database auditing improves access time, saves user energy, optimize client’s opportunities, and builds customer confidence. Auditing SQL server databases depends on types of authentication [1] and roles [2] defined within database engines. Database security is important to review the security lapses and helps to improve existing login authentication policies and server roles. Login authentication can be through user’s window account, through SQL server login, or mapping through asymmetric key, third party authentication certificate or third party service provider's credential authentication. Window’s active directory groups that are set up for logging into windows/SQL server accounts, can be viewed exactly for all logins that have access to database. The xp_logininfo [3] is a Microsoft SQL server 2008 R2 stored procedure, that audits and query active directory group accessing SQL server database:

xp_logininfo [ [ @acctname = ] 'sys_name' ]

[ , [ @option = ] 'all' | 'members' ]

[ , [ @privilege = ] variable_name OUTPUT]


Most common vulnerability in MS SQL server 2008 is SQL injection, which is a method by which a hacker gains access to the database server by injecting specially formatted data through the user interface input fields. As previously thought SQL injection is a SQL server problem, but in fact, it is caused by unsafe application development procedures. Dynamic queries that requires user inputs are more vulnerable to such types of attacks. Simple attack methods include text messages, while advance attack methods follow binary and ASCII coding.

Text attack starts with an apostrophe (') to end the string column (usually username) check, continues with malicious SQL, and then ends with the SQL comment mark (--) in order to comment out the full original SQL that was intended to be submitted.

First line of defense is to use dual login: One for window and other for SQL server. Second line of defense: Keep the error messages hidden from end user. Malicious users can extract client’s data during dynamic execution of SQL code which requires user interaction of submitting data such as user id and password by string concatenation, and then submitting it for execution. Stored procedure use in SQL server and parameterizing it for master access to the user is also susceptible to SQL injection attack. For further reading, I would suggest useful links that are helpful for users and clients [4 -7]

Note: All of these resources were accessed on October 2, 2011.

[1] http://technet.microsoft.com/en-us/library/ms144284.aspx

[2] http://msdn.microsoft.com/en-us/library/ms188659.aspx

[3] http://msdn.microsoft.com/en-us/library/ms190369.aspx

[4] http://weblogs.sqlteam.com/mladenp/archive/2011/02/16/sql-server-sql-injection-from-start-to-end.aspx

[5] http://en.wikipedia.org/wiki/SQL_injection

[6] http://searchsqlserver.techtarget.com/tip/Secure-SQL-Server-from-SQL-injection-attacks

[7] http://hungryhackers.blogspot.com/2008/01/top-10-tricks-to-exploit-sql-server.html

MS SQL Server 2008 Instances

PROBLEM STATEMENT:

You maintain SQL Server 2008 instances for a large global manufacturer. You currently have two SQL Server 2008 instances, SQL01 and SQL02.SQL01 contains several large production databases that are directly accessed by users, and also contains numerous third party applications.The Manufacturing database is used 24x7 to support all activities related to the manufacturing process. The database uses the full recovery model.You want to provide failover capability for the Manufacturing database. You also need to provide read-only reporting access to manufacturing details. The data is used for reports and does not have to be the most recent data, but it must be readily available and current.You want to accomplish this with the least effort possible and allow for minimal ongoing administration effort.

SOLUTION:

You should implement database mirroring with SQL01 as the principal server and SQL02 as the mirror server, and periodically create a snapshot of the mirror database for reporting. Database mirroring provides failover capability by maintaining two separate database copies on different SQL Server instances. The principal server contains the currently used data, andthe mirror server maintains a copy of the data that can be used if the principal server fails. To implement mirroring, you back up the database on the principal and restore it to the mirror server with no recovery.Although clients cannot directly access the mirror database, you can create a database snapshot on the mirror database and provide read-only access on the snapshot. Clients would be able to access the data as it existed when the snapshot was taken to produce reports. This solution would meet the failover and reporting requirements in this scenario.You should not configure log shipping. Log shipping would be more difficult to administer than using database mirroring with a snapshot on the mirror. With log shipping,

transaction log backups from a primary database are automatically transferred to another database and then applied.You should not implement a partitioned view of the data and provide query access to the view because this would not meet the failover requirements in this scenario. Partitioned views are used when you have similar data stored in multiple tables and want to create a view to allow access to all of the data as if it were stored in a single table. Partitioned views are implemented by creating a view that queries several tables and combines the results using the UNION ALL operator. A partitioned view can

be implemented across multiple servers to improve performance and increase data availability, and to make a database solution more scalable. This decentralizes the processing load and administration effort to individual servers, but the view provides for collective data access.You should not periodically use the Import and Export Wizard in BIDS to transfer the needed data from SQL01 to SQL02 to be used for reporting.Although you could transfer information to another server instance and use the data for reporting purposes, this would not provide the failover capability required in this scenario. The Import and Export Wizard creates SSIS packages to perform the data transfer tasks. These packages can be saved and reused.

SQL Server 2008- Replication

Problem Statement:

An organization needs to configure replication whereby it is able to send different updates to different subscribers at different locations. It should replicate a certain set of information to one subscriber as well as another set to another subscriber. It is to be ensured that subscribers are able to work offline and later synchronize the modifications with the publisher.

Solution:

In this scenario, merge replication would be able to meet the required goal. Use merge replication typically to meet the following requirements:

Allow subscribers to modify information in an offline state and then replicate it back to the publisher. A subscriber can also replicate information to other subscribers. Provide data filtering. In data filtering, filter different updates to different subscribers at different locations. Allow information to be replicated to users who are mobile and are not always connected to the SQL Server. Provide the ability to resolve conflicts that occur during replication.

Kerberos Authentication Using MS SQL Server 2008

If you need to incorporate computers from a different work group into your company domain, you need to ensure that your company users make use of Kerberos authentication in order to communicate with the SQL Server from the work group.Your Company Database is configured as shown in the exhibit:




You must have a Service Principal Name (SPN) registered with Active Directory for the clients of your company domain to use Kerberos authentication with the SQL Server from the workgroup. In the scenario, the SQL Server from workgroup will become part of the company domain. The client
and SQL Server must be a part of the same domain or in a trusted domain to use Kerberos authentication. Once a SPN is registered, Active Directory acts as the Key Distribution Center (KDC) in a Windows domain, and the SPN will map to the Windows account that started the SQL Server instance. If the SPN registration fails or is not completed, Kerberos authentication will not be used because the Windows
security layer cannot determine if a Windows account is associated with a particular SQL Server instance.