Block access to SQL Server

Imagine what will happen if some unauthorized people get access to your database. They gets the username/password and hacked into your server. You will lose all of your data. They can manipulate or delete data. That might cost you a fortune. Data plays a vital role in your application and are the key of any application. From security point of view, we need to prevent our data in database. This is our primary job.
Is there any alternatives to prevent this? Yes, by blocking access to database based on IP address. In MySQL, there is a built-in control that can allow access from certain host IPs only. But in SQLSERVER there is none. But there definitely is a workaround. We can block access to sqlserver by IP by using a function called EVENTDATA(). This function is used with a trigger and we will know who is trying to connect to the sqlserver. This can be used with any trigger like create table, drop table, logon etc. With each of these event types, EVENTDATA returns specific xml data that can be used as per own need. In case of LOGON, it returns XML data that holds the IP accress of the connecting client. This is the key for our topic. WE will use this IP to filter the connection request and allow or deny the connection even if it has valid credential.

How to do it? We will do this by creating a trigger for "LOGON" on all server. PLease note, triggers like "Create table" etc can be done on database level, but the "LOGON" has to be created on All Server. Lets create a trigger like

CREATE TRIGGER CheckLogin
ON ALL SERVER
FOR LOGON
AS
Declare @IP NVARCHAR(4000);
SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(max)'))
if (@IP != 'xxx.xxx.xxx.xxx')
ROLLBACK;
GO

This will check if the login is originated from "xxx.xxx.xxx.xxx" or not. If not , it will reject the connection even if valid credentials were supplied. There is little more we can do with this. Instead of hard coding the IP, we can create a table that stores a valid IPs. and we can check the @IP against this table to decide whether to allow or deny the connection. To check against the table, we need to access table as [databse].dbo.table. Since this Trigger is fired in root level, we need to specify the table with its database name.
We can even store the information from EVENTDATA() in certain table to check who are trying to access the database and take necessary action.
Please note that, after you create this trigger, make sure you are logged into the server or otherwise you might lose access to the server yourself unless you filtered your IP out in the check. Be Careful.
To drop this trigger simply run following command
DROP TRIGGER CheckLogin
ON ALL SERVER;
GO

Hope this is some information that will be of use to most of us.

ASP.NET v4.0 User

There are times when you need to add rights to user ASP.NET v4.0 and you could not find it in user list. This happens a lot. Recently i needed to add this user "ASP.NET v4.0", and searched user list with no success. Until I found this. The use ASP.NET v4.0 is actually "IIS AppPool\ASP.NET v4.0" . If you lookup for user "IIS AppPool\ASP.NET v4.0" it will give you "ASP.NET v4.0" Hope this will help.

USB Mouse dies

Hi all, This is really frustrating when your mouse dies or stops responding for few seconds. And the nit responds. And if this happens a lot, you feel like throwing your mouse away. That's what i felt when this happened to me. I spent almost a week for solution. With no success, i re-installed OS (win 7 and 8) two times in a single day. This also dint help me at all. At last, i found the solution. Like mentioned in other blogs, the reason could be any. So it is not necessary that what worked for me will work for you, but you can give it a try.

Cause
Root of my cause was due to USB root setting in device manager. It allows the machine to turn off the device. Like if to control power or to save power, the device can be stopped by the machine. This was the cause for my problem. I dont know how, but it was working perfectly before couple of weeks.

This is what i did step by step.
1) Open your device manager. Right click on computer > manage > This will open "Computer Management" screen.
2) Click on "Device Manager" on left menu. this will give you list of all the devices installed on your pc/laptop.
3) Locate and expand "Universal Serial bus Controller". Normally this will the last one in the device list. Expand this.
4) Right click on "USB ROOT HUB" and select properties.
5) There is a "Power Management" tab on this property window, usually the last tab.
6) You will see a check box for "Allow the computer to turn off this device to save power" This is checked by default. Uncheck it and click on OK button.

This is how i solved my problem. Hope this will help some of you having same problem.

Please share this post if it helped you to help others.

windows could not find backup devices on this computer

Recently i installed windows 8 in my Dell N5050 Laptop, that had Windows 7 installed previously. IN win7, i could create a system image in my external drive from where i could reinstall everything i had in image file in one click, if my laptop got into any problem. That used to save a lot of time reinstalling everything. But once i switched to windows 8, i could not make a system image. I got error :Windows could not find a backup device in this computer" with error code "incorrect function. (0x80070001)". I nearly gave up creating system image after spending lots of hours to figure it out. But finally it worked. The solution was quite simple.
Firslty i would like to mention that in WIN8, we can create system image like in windows 7 from control panel\ system and security\File history. In this screen, on left bottom side you will see "windows 7 recovery".  like in image below:

Now before creating system image, I had all my drives connected like external drive, two iso images mounted, etc etc with c, d, e,f, g drives.

So i unmounted everything and ejected external drive so that it would leave me with only C and D partition. If you have more partition of your actual hardrive, then unmount everything only leaving your actual partition.

From Disk Management, select your primary drive (which is C: in most of the case), and right click on it and set is as active. (This step could be optional but this is what i did.)

Now when u try to create system image, it will show your D drive and any other partitions from hard disk. Now if u connect your external drive and do a refresh, it will show your external drive where you can create your system image.

Finally i got this screen

This was a relief. 
Hope this will help some of you. 
Do not forget to like and share this post if this helps.
NOTE: There might be others who are in same boat. They also needs help.

Restore IE9 after installing IE10

Recently i installed ie10 after i format my PC. Then i found out that it is not working as expected. So i decided to roll back to IE9. It was a PIA to uninstall IE10 and reinstall IE9, untill i found the solution. Hope this will be of help to atleast some of you. Because, even after removing IE10 i could not install IE9. I was getting alert "IE9 is already installed in this system".
 IE10 can not be uninstalled from Control Panel\Programs\Programs and Features as this is not installed as an application. IE is installed using automatic updates of Windows. Here are the steps to uninstall IE10 and reinstall IE9

1) Click on Start button and type "View installed Updates". Click on search "View installed Updates" from the search results. OR click on "Installed Updates" in a left side of Control Panel\Programs\Programs and Features . You will see a window like below

Here i have already uninstalled IE10 so i get Windows Internet Explorer 9. But if IE10 is present you will see "Windows Internet Explorer 10". Click on it. a button "uninstall" will appear next to "Organize" on top.
2) Click on Uninstall

Your Internet explorer is uninstalled completly.
3) You can also remove IE10 from "Turn Windows feature on/off" from control panel. But you have to follow step 1 and 2 anyway.

Now that IE10 is uninstalled, You can now install IE9 from its setup file. Unless you follow above steps, you wont be able to install IE9. Since IE10 is still in your system.

Restart your PC when asked.

Hope this will help some of you.

Upload file in MVC3 via Ajax

Ther are quite a few Jquery plugins that will upload file using ajax. Here i will write a quick sample for acheiving the same using jquery.form.js.

First thing's first. get jquery.form.js plugin. You can search for this in google. :)

Now that you have this pluging and included in the script,
Follow these  simple steps.
1) Create form
     @using (Html.BeginForm("ActionName", "Controller", FormMethod.Post, new { enctype = "multipart/form-data", id = "ajaxUploadForm" }))
{}

2) IN this form, u can place "file" element to browse and choose file.
3) Create one button with type "Submit"
4) JS script in same form
$(function () {
 $("#ajaxUploadForm").ajaxForm({
            iframe: true,
            type: 'POST',
            dataType: "json",
            cache: false,
            timeout: 1200000,
            async: false,
            beforeSubmit: function () {
                //Do something here if needed like show in progress message
            },
            success: function (result) {
                alert("sucess"
            },
            error: function (xhr, textStatus, errorThrown) {
                alert("Error uploading file");
            }
        });
 });


Done with form.

Create controller

public string ActionName(HttpPostedFileBase file)
        {
            string message = "File Uploaded Successfully";
            if (file != null && file.ContentLength > 0)
            {
     //file extensin can be checked with Path.GetExtension(file.FileName)
                    if (file!= null && file.ContentLength > 0)                   
      string fileName = Path.GetFileName(file.FileName);
      string basePath = ConfigurationManager.AppSettings["some path from config OR hardcode path"];
      string path = Path.Combine(basePath, fileName);
      Check for directory in the server. If not exist create one or manualy create required folder.
  
      file.SaveAs(path);                   
                }
            }
            else
            {
                message = "File is empty or null";
                return message;
            }
            return message;
        }


You are ready to go. You can return message in json format as your requirement, for sucess, some error while uploading etc etc.


Please check out my  blog to see how extra parameters can be passed to controller while uploading file.

Happy Coding

Passing extra parameter to controller in jquery.form.js

In MVC, when we have to submit the page via ajax, lots of us uses "jquery.form.js" to upload file. And sometimes it is needed to pass extra parameters along with selected file. I had such requirements and found that, if the controller has some parameter that accepts for example Firstame, LastName as extra parameter, we have to create inputs (hidden or visible) with exact same name and when the form is submitted, it will automatically assigns the value from these input to the parameter.

It is described as below.

Controller

public string AjaxUpload(HttpPostedFileBase file, SOMECLASS obj)
{}



Here SOMECLASS has members like

public class SOMECLASS {
           public string FirstName{ get; set; }
           public string LastName{ get; set; }
    }



Most of the times, we try to pass FirstName and LastName as parameter as a part of "data" field while using ajax. But this wont work in case of jquery.form.js



But if we have following field in form



input type="text" name="FirstName" id="FirstName"
input type="text" name="LastName" id="LastName"



second parameter of AjaxUpload "Obj" will have values directly from Firstname and LastName inputs, when the action "AjaxUpload" is called.