Sunday, September 16, 2012

SQL Injections to database

  The vast majority of databases in use today have some form of web interface, allowing internal and/or external users easy access through familiar browser software. If you're security-conscious, you've undoubtedly spent a significant amount of time setting appropriate security permissions on your databases and web servers. Mot much of the DBA's considered the security of the code that powers the database-web interface.
  One common type of database attack, the SQL Injection, allows a malicious individual to execute arbitrary SQL code on your server. If you don't believe this type of attack could happen to you.Let's take a look at how it works by analyzing a very simple web application that processes customer orders. Suppose Acme Widgets has a simple page for existing customers where they simply enter their customer number to retrieve all of their current order information. The page itself might be a basic HTML form that contains a textbox called CustomerNumber and a submit button. When the form is submitted, the following SQL query is executed:

SELECT FROM Orders
WHERE CustomerNumber = CustomerNumber
GO

The results of this query are then displayed on the results page. During a normal customer inquiry, this form works quite well. Suppose John visits the page and enters his customer ID (14). The following query would retrieve his results:

SELECT *  FROM Orders
WHERE CustomerNumber = 14
GO

However, the same code can be a dangerous weapon in the hands of a malicious user. Imagine that Mal comes along and enters the following data in the CustomerNumber field: “14; DROP TABLE Orders”. This would cause the following query to execute:

SELECT FROM Orders
WHERE CustomerNumber = 14; 
DROP TABLE Orders
GO

Obviously, this is not a good thing! There are several steps that you can take to protect your server against SQL Injection attacks:
  • Implement parameter checking on all applications. For example, if you’re asking someone to enter a customer number, make sure the input is numeric before executing the query. You may wish to go a step further and perform additional checks to ensure the customer number is the proper length, valid, etc.
  • Limit the permissions of the account that executes SQL queries. The rule of least privilege applies. If the account used to execute the query doesn’t have permission to drop tables, the table dropping will not succeed!
  • Use stored procedures (or similar techniques) to prevent users from directly interacting with SQL code.
As with many security principles, an ounce of prevention is worth a pound of cure. Take the time to verify the code running on your servers before disaster strikes! Elsewhere on the site
    

Friday, September 14, 2012

Resource Governor in SQL Server I


        In Production environment DBA's come across the scenarios, When there should be sudden spike in CPU and memory utilization and it lead to slow response to query results.In enterprise  some of running applications are business critical and they shouldn't be disturbed by other applications.Because of this there were problems for DBA about how to allocate specific hardware and resource to the critical applications.

When I involved in project that is about School management there is huge number of data  in the database and several users interact with my database using several applications.There are web users, internal users like staff, students, teachers parents etc. To make this product we have to use low performing machine as a server.Therefor when all users send requests to the same time it response is very low. Some critical functions running on Application in staff and Principle.but all of them become very lazy.

Let know what are the solutions available for DBA

  1. Move database to a different high performing server.But it costly 
  2. Use SQL Server techniques to takeover this problem
What are the Techniques?(I will produce whole post about this later)
  •  Mirroring
                 In mirroring database server maintain two copies of single database, that must reside on different server of SQL Server database engine.Typically these server instances reside on computers on different location.If the principle database server unavailable due to some reason the mirror server takeover the role of principal server and brings its copy of the database online as the principal server. (for more details Click Here)

  • Log Shipping

                      Log shipping is a one of fail over solution comes with SQL Server 2000.Fail over server substituting primary server with a backup server if primary hardware is unavailable.There is two fail over methods.

         Automatic Fail over
                  Backup server detects when the primary server is not available and take over without any intervention from DBA.

         Manual Fail over
                 In manual fail over when the primary is not available DBA  have to perform some steps to bring standby server online.

                
(for more details Click Here)

  • Database Snapshot
           It is a read only static view of a SQL Server database.Snapshot is transactionally consist with the source database as of the snapshot creation. A database snapshot always resides on the same server instances as it source database. As the source database updates the snapshot is updated.

Note :- The longer a database snapshot exists the more likely it is use up available disk space.Because Each snapshot persist until it is explicitly dropped by the Database owner.

(for more details Click Here)
  • Replication
            Replication is set of technology for copying and distributing data and objects from one database to another then synchronizing between database to maintain consistency.

Thursday, September 13, 2012

Installig SQL Server 2012 Step by Step



1. Double click on the Setup.exe.

2. After Installation Wizard runs the SQL Server Installation Center. If you need to create a new installation of SQL Server, click Installation in the left-hand navigation area, and then click New SQL Server stand-alone installation else you need to add features to already installed server click on  add features.

3. The System Configuration Checker runs a discovery operation on your computer.If check is passed  click OK to continue. You can view the details on the screen by clicking Show Details, or you need it  as an HTML report by clicking View detailed report in opened window.

4. On the Setup Support Files page, click Install to install the Setup support files.

5. The System Configuration Checker runs a discovery operation on your computer. To continue, click OK. You can view the details on the screen by clicking Show Details. 
In here if the checker is failed 
 goto redist  folder in setup files.install that you need 


 6. On the Language Selection page, you can specify the language for your instance of SQL Server if you are installing on a localized operating system and the installation media includes language packs for both English and the language corresponding to the operating system. To continue, click Next.

If this Doesn't Work for you.
   May be you are working with Visual Studio 2010 .you have to uninstall visual Studio later version  and try again.

7. On the License Terms page, review the license agreement and, if you agree, select the I accept the license terms check box, and then click Next.

8. On the Product Updates page, the latest available SQL Server product updates are displayed. If you don't want to include the updates, clear the Include SQL Server product updates check box. If no product updates are discovered, SQL Server Setup does not display this page and auto advances to the Install Setup Files page.

 9. On the Install Setup files page, Setup provides the progress of downloading, extracting, and installing the Setup files. If an update for SQL Server Setup is found, and is specified to be included, that update will also be installed.

10. The System Configuration Checker verifies the system state of your computer before Setup continues.

11. On the Setup Role page, select SQL Server Feature Installation, and then click Next to continue to the Feature Selection page.

12. On the Feature Selection page, select the components for your installation.I recommend you to select Select all Option.

13. On the Instance Configuration page, specify whether to install a default instance or a named instance. Instance ID - By default, the instance name is used as the Instance ID.You can manually give ID also.
  • Instance root directory - By default, the instance root directory is C:\Program Files\Microsoft SQL Server\110\. To specify a non-default root directory, use the field provided, or click Browse to locate an installation folder.
  • Installed instances - The grid shows instances of SQL Server that are on the computer where Setup is running. If a default instance is already installed on the computer, you must install a named instance of SQL Server 2012.

14. The Disk Space Requirements page calculates the required disk space for the features that you specify.

15.  Work flow for the rest of this topic depends on the features that you have specified for your installation. You might not see all the pages, depending on your selections.

16. Use the Server Configuration — Service Accounts page to specify login accounts for SQL Server services. The actual services that are configured on this page depend on the features that you selected to install.
      Do not use a blank password. Use a strong password.

17.Use the Database Engine Configuration - Account Provisioning page to specify the following:
  • Security Mode - Select Windows Authentication or Mixed Mode Authentication for your instance of SQL Server. If you select Mixed Mode Authentication, you must provide a strong password for the built-in SQL Server system administrator account.I recommended to use Mixed Mode Authentication.
  • SQL Server Administrators - You must specify at least one system administrator for the instance of SQL Server. To add the account under which SQL Server Setup is running, click Add Current User. 
 

 18.The Ready to Install page shows a tree view of installation options that were specified during Setup. On this page, Setup indicates whether the Product Update feature is enabled or disabled and the final update version.
To continue, click Install.