SQL Management Studio

SQL management studio
---------------------

This is a useful tool. This is used for configuring, managing, and administering all components within Microsoft SQL Server. The tool includes both script editors and graphical tools which work with objects and features of the server.

An important feature of SQL Server Management Studio is the Object Explorer, which allows the user to browse, select, and act upon any of the objects within the server

This can be used to connect to the SQl server remotely.

To create a SQL user please follow these steps.

1, Connect to the server using RDP
2, Open Microsoft SQL Server Management Studio
Start Menu >> All Programs >> Microsoft SQL Server >> SQL Server Management Studio
3, When prompted to login use Windows Authentication. Make sure the Server Name is localhost. Click on connect.
4, Once you are connected expand the security folder and right click on the logins folder, click new login.
5, Then you will get a window. Fill those fields
Make sure that the SQL server authentication radio button is selected. Fill in your password.
6, Make sure the enforce password policy is not checked for normal use.
7, Make sure that the highlighted area that says master is the name of the database you wish the user to be associated with.
8, Then select the User Mapping option on the left of the window.
9, Scroll down to the database you wish to have this user associated with and place a check mark next to it.
10, Click OK and your SQl user is ready.

Now you can connect the SQLMS with SQL authentication with the password.

To connect the server remotely, Please follow these steps

1, Connect to the SQLserver using windows authentication
2, Right click on the server from object explorer and click on properties.
3, Click the Connections node.
4, Under Remote server connections, select or clear the Allow remote connections to this server check box.

Now open SQL Server Configuration Manager

1, unfold the node "SQL Server Network Configuration" and select "Protocols for MSSQLServer"
2, Make sure that TCP/IP is enabled
3, Open the port 1433 from firewall

Now try connecting the SQL server from remote location with IP name and SQL authentication. Note that "sa" user is the administrator user in SQLMS will all privileges. The password is server administrator password.

Permission issue when deleting a folder In windows (as Administrator)

If you get permission issues when deleting a folder, even if you are logged in as Administrator, use the following fix.

To take control of the folder containing the undeletable create a text file called “delete.bat”(or any name) and add the following lines to it:

Code: [Select]

SET DIRECTORY_NAME="C:\Locked Directory"
TAKEOWN /f %DIRECTORY_NAME% /r /d y
ICACLS %DIRECTORY_NAME% /grant administrators:F /t
PAUSE

You will need to change the directory path to match your requirements.

Right click on the file “delete.bat” select “Run As Administrator” and you should now have full control of the directory and all sub directories meaning you can do whatever you wish with them.

MSSQL database migration

To migrate an MSSQL database on another server, follow the steps given below.

1. Make a backup of the existing database.

  You can do that directly from the control panel or through MSSQL management studio.

 a. Login to MSSQL management studio and select the database
 b. Right click on the database > Task > backup > select the backup destination > click on "ok" button.

2. Copy the backup file to your local system through FTP

3. Upload the .bak file you got to the destination server.

4. Restore the database there.

     a. Create the database on the destination server through control panel or SQL management studio
     b. Login to MSSQL management studio and select the database
     c. Right click on the database > Tasks > Restore > Database
     d. under "Source for restore", select "From device" and browse the location of the file. It will then come under "Select the backup sets to restore"
    e. Select the appropriate backup and then go to the "options" on the top left side. check the button "overwrite existing database" there and click on "ok" button

This will do the restore and you can see a restore status option which shows the percentage of restore done. Once the restore is finished, you will get a message that the restore was successful.
   
PS: MSSQL is backward compatible. So, if the source server's MSSQL version is advanced than the destination server's version, you will probably end up in error.


You can restore the database using the .sql backup(Script). Follow the steps given below to generate a .sql backup.

1. Generate a .sql backup.

 a. Login to MSSQL management studio and select the database
 b. Right click on the database > Task > Generate Script >  select "Script entire database and database objects' > click next
 c. Select an apropriate location where the backup should be saved and then click the "Advanced" button
 d. Under "General" > "Types of data to script" select "Data only" from the drop down menu > click "ok" > click "next".
 e. Click "next" for summary and the click "finish" to complete the backup procedure.

2. Download the .sql file to the local system

3. Upload it to the target system and then double click on it. That will make the .sql file to get opened in the query editor of MSSQL manager on the destination server. Just execute the query and that will do the task.

DDOS on windows servers.

In Windows servers, we can use the following methods to check DDOS attacks.

Check the connections on port 80 through the command line:

Code: [Select]

netstat -ano | find /i /c ":80"

For eg:

Code: [Select]

C:\Users\Administrator>netstat -ano | find /i /c ":80"
183927

The following command will give you the amount of connections on a specific IP

Code: [Select]

netstat -ano | find /i /c "IP"

Identify all of the connections on the server:

Code: [Select]

netstat -n -p tcp

Export the connections on the server using the command below:

Code: [Select]

netstat -ano > ddosoutput.txt

Another method to find out the domain or IP address on which the attack is targeted is given below.

1. Download and install Wireshark on the affected server

http://www.wireshark.org/

2. Once installed, start the program on the server

3. Under "Capture", click on the "interface list'. Here, you will see the traffic through all the NIC's connected on the server.

4. Click on the interface where you see high traffic and then click on "Start".

5. You can see the source and destination IPs. If it is a DDOS, you can see different source IP's targeting to a single destination.  If the destination IP is the server's shared IP, then you have to find the domain on which the attack is targeted as there will be manu domains configured on the shared IP.

6. For that, go to "View" > "Name resolution" > "enable for network layer". Once it is enabled, you can see the domain names. pick out the domain name where you see high accesses, set loop back on his DNS and suspend him from the server.

But, the DNS changes we make would take some amount of time to be in effect as there would be a propagation delay. If the attack is really crashing the server, you need to contact your data center and if there are any tools available to filter the attack, make use of it.

Server Monitoring (Advanced)

1)

Code: [Select]

ssh root@hostname -p port (default 22)

3) exim

Code: [Select]

exiqgrep -z -i | xargs exim -Mrm - to delete frozen messages
exim -bpc - to know the message count
exim -bp - to list all the messages
exim -bp | exiqsumm - to list all the messages in a group
exim -bp | grep centralph.com - to view mails under a particular user..for eg centralph.com
exim -bp | grep centralph.com | wc -l - to know count of mails under a particular user.for eg. centralph.com
exim -Mvh id - to view a particular message header
exim -Mvb id -to view a particular message body
exiqgrep -f centralph.com -i | xargs exim -Mrm - to remove messages under centralph.com

6)

Code: [Select]

mysqladmin stat proc

- to view mysql connections running

Code: [Select]

mysqladmin stat proc | grep username

- to view mysql connections running for a particular user
If the connections for a particular user is high, then look on whm daily process also
suspend user(/scripts/suspendacct username- in back end)

7)

Code: [Select]

nice top -d 2 -u user

- to view services run by an individual user

\s - to check mysql uptime after getting into mysql using "mysql"

9) to check ddos

a)

Code: [Select]

 pidof httpd

b)

Code: [Select]

netstat -plan|grep :80|awk {'print $5'}|cut -d: -f 1|sort|uniq -c|sort -nk 1

   -> this can be also used , if more than 100 connections from an IP, then block it.

c) But before blocking any IP, check whether it is server IP. You can check it by the command,

Code: [Select]

ifconfig | grep <ip>

d)

Code: [Select]

csf -d ipaddress

- to block that ip(confirm its not server ip)

e) Also check apache status on whm

11) sites to check website speed and contents(useful when doing server tickets)
a) speed
www.alertra.com
www.tools.pingdom.com
b) contents
http://analyze.websiteoptimization.com/


12) allowing access to particular ip,

Code: [Select]

allow from <ip> 
deny from all

( for allowing access from that particular ip only, enter this on .htaccess file of owners public_html.useful when unsuspending an account by giving access to owner's ip. Owner should provide his local ip from http://www.whatismyip.com/)


13)

Code: [Select]

tracert dotflashop.com

(from windows)

Code: [Select]

traceroute dotflashop.com

(From Mac terminal)


14) when a lot of mails getting frozen for a mailid

Code: [Select]

grep "max emails" /var/log/exim_mainlog |tail

this is the proof, he is sending bulk mails


15)

Code: [Select]

netstat -plan | grep :25

   - to find the connections(mail)

if see anything like

Quote

tcp        0      0 127.0.0.1:45738             127.0.0.1:25                ESTABLISHED 547580/sshd: username
tcp        0      0 127.0.0.1:45737             127.0.0.1:25                ESTABLISHED 547580/sshd: username

ie is a spammer wih script.suspend that account....

16) apache uptime - /usr/local/apache/bin/apachectl status
17) mysql uptime  -

                  mysql(enter mysql)
                  \s
                  exit

DDOS attacks, module install and kernel tweaks

Finding DDOS attacks

Below are some of the useful netstat commands to check during DDOS attack.

To list the connections to the target IPs (server's IP's) use the below command.

Code: [Select]

netstat -alpn | grep :80 | awk '{print $4}' |awk -F: '{print $(NF-1)}' |sort |uniq -c | sort -n

To list the connections from source IP's use the below command:

Code: [Select]

netstat -alpn | grep :80 | awk '{print $5}' |awk -F: '{print $(NF-1)}' |sort |uniq -c | sort -n

Block the IPs with high connection above using CSF or APF firewall.

Code: [Select]

csf -d IP

Code: [Select]

apf -d IP

To see the state of each connection and the value use the below command:

Code: [Select]

netstat -an|grep ":80"|awk '/tcp/ {print $6}'|sort| uniq -c

A sample output would look like:

Quote

root@linux [~]# netstat -an|grep ":80"|awk '/tcp/ {print $6}'|sort| uniq -c
      2 CLOSE_WAIT
      1 ESTABLISHED
      4 LISTEN

Install necessary modules

You can use tcpdump to identify the attacker too:


tcpdump -v -n -i eth"x" -p host IP_Address

where x can be 0 or 1. If it is a VPS, it can be venet0 too. Check the Output of ifconfig.



Try installing the below Apache modules to mitigate the attack

Quote

DOS-Deflate
mod_security
mod_dosevasive
Enable anti-DOS for APF

Tweaking the kernel

To prevent SYN floods change the below kernel parameters:

Quote

sysctl -w net.ipv4.netfilter.ip_conntrack_tcp_timeout_syn_recv=45

sysctl -w net.ipv4.netfilter.ip_conntrack_tcp_timeout_established=332000

sysctl -w net.ipv4.tcp_fin_timeout=15

sysctl -w net.ipv4.tcp_synack_retries=5

sysctl -w net.ipv4.tcp_fin_timeout=15

sysctl -w net.ipv4.tcp_keepalive_time=1500

sysctl -w net.ipv4.tcp_sack=0

sysctl -w net.ipv4.tcp_max_tw_buckets=1440000

sysctl -w net.ipv4.tcp_max_syn_backlog=2048

sysctl -w net.ipv4.tcp_max_syn_backlog=4096

Also increase Apache's MaxClients limit to 500


Further sysctl tweaks

Original values inside ()

Quote

net.ipv4.tcp_fin_timeout=20 (30)
net.ipv4.tcp_keepalive_time = 1800 (3600)
net.ipv4.tcp_fin_timeout=20 (30)
net.ipv4.tcp_keepalive_time=1800 (3600)
net.ipv4.tcp_keepalive_intvl=40 (75)
net.ipv4.tcp_tw_recycle=1 (0)
net.ipv4.tcp_tw_reuse=1 (0)
net.ipv4.tcp_max_syn_backlog=4096 (2048)

Below are some of the recommended Sysctl tweaks for Web server + Database server

Quote

net.ipv4.inet_peer_gc_maxtime = 240 (120)
net.ipv4.inet_peer_maxttl = 500 (600)
net.ipv4.inet_peer_minttl = 80 (120)

Enable SPF for all cpanel accounts

Command to install spf record on single cPanel account : 
--------------------------------------------------------------------------

/usr/local/cpanel/bin/spf_installer <cPanelusername>


Install spf records for all cPanel accounts :
-----------------------------------------------------

cd /var/cpanel/users

for i in `ls /var/cpanel/users` ;do /usr/local/cpanel/bin/spf_installer $i ;done