Sunday, October 27, 2013

Simple SQL Query to Shrink SQL Server Database Log File Size

When SQL Server Service stopped working due to insufficient disk space, one of the possible culprit may be the SQL Server Database Log File that has grown to be very large.

Here is some simple SQL Query to shrink the size of the log file:

====
Use mydatabasename;
GO

ALTER DATABASE mydatabasename
SET RECOVERY SIMPLE
GO

DBCC SHRINKFILE (mydatabasename_Log, 1);
GO

ALTER DATABASE mydatabasename
SET RECOVERY FULL;
GO
====

Waring: The above query will truncate your database log!

Friday, May 17, 2013

Creating SSL/HTTPS Certificate Request with Open SSL

Sometimes, we are required to launch a simple HTTPS website for security reason or simply for testing purposes.

Here is some simple steps to get SSL certificate for your website:

Step 1: Download and Install OpenSSL from http://www.openssl.org/

Step 2: Open command prompt (if you are using Windows)

Step 3: Run: openssl genrsa -des3 -out server.key 2048
Run this command if see openssl.cnf not found error:
set OPENSSL_CONF=C:\OpenSSL-Win32\bin\openssl.cfg

Step 4: Run: openssl req -new -key server.key -out server.csr
Now you will have server.key and server.csr in your openSSL/bin folder, server.key is your private key while server.csr is used to request the certificate from Certificate Authority

C:\OpenSSL-Win32\bin>set OPENSSL_CONF=C:\OpenSSL-Win32\bin\openssl.cfg

C:\OpenSSL-Win32\bin>openssl genrsa -des3 -out server.key 2048
Loading 'screen' into random state - done
Generating RSA private key, 2048 bit long modulus
.......................+++
...........................+++
e is 65537 (0x10001)
Enter pass phrase for server.key:
Verifying - Enter pass phrase for server.key:

C:\OpenSSL-Win32\bin>openssl req -new -key server.key -out server.csr
Enter pass phrase for server.key:
Loading 'screen' into random state - done
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:SG
State or Province Name (full name) [Some-State]:Singapore
Locality Name (eg, city) []:Singapore
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Home Pte Ltd
Organizational Unit Name (eg, section) []:IT
Common Name (e.g. server FQDN or YOUR name) []:terranspot.com
Email Address []:administrator@terranspot.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:


Step 5: Buy the SSL certificate (e.g. www.namecheap.com)

Step 6: Submit your server.csr file and your will receive server.cer which is your SSL certificate
You will need server.key, server.csr and server.cer if you are using Apache web server.

Tuesday, April 2, 2013

IIS ARR Suddenly Stop Working (HTTPS Website)

Received a call from customer today complaining that their web application suddenly stop working. Luckily I was on the way to the data center to settle other issue.

The web application consist of a web server running IIS (Internet Information Services) and a proxy server running IIS ARR (Application Request Routing). The web application which is accessible via HTTPS port 443 has been working fine all along. Nobody was accessing the server recently. No system patch, no Windows update, totally nothing. Nobody touch the system. But... it suddenly refused to work.

Preliminary investigation:

1. Try accessing the website locally on the web server, everything seems fine

2. Try accessing the website from external network, all traffic must pass through IIS ARR Proxy, the website in inaccessible

At first, I was thinking this must be IIS ARR Proxy Server issue. But it can't be, nothing changed. After messing around with the ARR configuration for some time without any result, server certificate issue come to my mind. I remember during the initial set up that I was facing some issue with the ARR due to the IIS HTTPS certificate.

This turn out to be the actual cause of the problem. After some checks, I found out that the Root Certification Authority Cert for the certificate that I use for my IIS HTTPS is indeed expire today. Replacing the certificate immediately solve the problem.

IIS ARR Reverse Proxy will not work for HTTPS website if there is a problem with the server certificate.

If you are:

- Running IIS ARR Reverse Proxy

- With HTTPS

- Website suddenly stop working or become inaccessible

Root Certification Authority Cert expiry may be the cause of the problem.

Solutions to the problem:

1. Click Start | Run
2. Type mmc and press Enter
3. Click File | Add and Remove Snap In
4. Select Certificate
5. Choose Computer Account and Next
6. Click Finish
7. Go to Trusted Root Certification Authority
8. Check the expiry date of the Root Certification Authority Cert that your IIS Cert is using
9. Replace the certificate if it has indeed expired

Note: I noticed that if your IIS Cert has expired but the Root Certification Cert is still valid, the website can still be accessible. The browser will display some warning though.

In my case, below are the exact steps that I did to solve my problem:

1. Go to the server running IIS ARR
2. Run IIS Manager
3. Create a certificate request, save the request file as certreq.txt
4. Google and download OpenSSL
5. Install OpenSSL
6. Run command prompt as administrator
7. Run: set OPENSSL_CONF=c:\[PATH TO YOUR OPENSSL DIRECTORY]\bin\openssl.cfg
8. Run: openssl genrsa -des3 -out cakey.pem 2048
9. Run: openssl req -new -key cakey.pem -x509 -days 1825 -extensions v3_ca -out ca.crt
10. Run: openssl x509 -req -days 365 -in certreq.txt -CA ca.crt -CAkey cakey.pem -CAcreateserial -out iis.cer
11. Close the command prompt
12. Now I have ca.cer and iis.cer
13. Run mmc, add snap-in and install ca.cer under your Trusted Root Certification Authority then delete the expired ca cert
14. Back to IIS Manager and complete the certificate request using iis.cer
15. Go to bindings and select the new cert as the HTTPS cert
16. Export back the certificate as iis.pfx
17. Go to the web server
19. Run mmc, add snap-in and  install ca.cer under your Trusted Root Certification Authority then delete the expired ca cert
20. Go to IIS Manager and import the IIS certificate using iis.pfx
21. Go to bindings and select the new cert as the HTTPS cert
22. Restart IIS and everything is working


Friday, September 28, 2012

How GPS Work?

GPS applications are very popular today. Ever wondering how it works?

Here are simple explanation on how GPS works:

1. First we need GPS Satellites that orbit the earth and GPS Receiver / Device



2. GPS Satellites broadcast GPS Signals that contains Satellite ID and Time



3. From the Satellite ID and Time, the distance between receiver and the satellite can be determined
GPS Receiver will now know that it can be anywhere within X km distance from the satellite.


4. Now, GPS Receiver will calculate signal from 2nd satellite


5. Next, the 3rd satellite


6. GPS Receiver need at least 4 satellites to determine its location


How to ensure that the time is accurate?

The accuracy of the clock on the satellite and receiver is important. This is because the time is used to measure distance from the satellite to the receiver.

To ensure the accuracy of the time, an atomic clock is used on GPS Satellites. But expensive atomic clock is not practical for consumer GPS device.

GPS Receiver use normal inexpensive quartz clock. Therefore it need to periodically synchronize it's time. GPS Receiver calculate / estimate current time by comparing GPS Signal it received from several satellites. The correct time is the one that cause all the spheres (explained above) to align at a single point.

Type of data sent by GPS Satellite

Basically there are 3 types of data sent by GPS Satellites:

1. Almanac Data (sent every 12.5 minutes)

Tell us about approximate location of all satellites.
Tell the GPS Receiver which satellites it should expect to see from it's current location.

2. Ephemeris Data (sent every 30 seconds)

Contains detail satellite orbit information

3. Pseudo Random Code

Contains Satellite ID and Time

How long it took to get first fix?


1. Cold or Factory (15 mins)

Occurred when the last GPS Fix is more than 180 days.
The receiver need to download almanac data which is sent every 12.5 mins and valid for 180 days.

2. Warm or Normal (1 mins)

Occurred when the receiver has approximate time accuracy of ± 20 secs, position ± 100 km from the last GPS Fix, speed < 90 km/h, and a valid almanac data.
The receiver need to download ephemeris data which is sent every 30 secs and valid for 4 hours.

3. Hot or standby (<30 secs)  

The receiver has valid time, position, almanac, and ephemeris data.


How to improve the time to first fix?

Assisted GPS / AGPS that provide Almanac and Ephemeris Data on demand to the GPS Receiver.

Source of GPS Error

What cause the GPS location shown by the device to be inaccurate?

1. Multi-path effect

When the GPS Receiver is placed between tall buildings or urban canyon, the GPS Signal form satellite will hit the tall buildings and bounce before hitting the receiver. This will cause the time calculation to be inaccurate, therefore the location calculated will be inaccurate.

2. Atmospheric effect

Atmospheric condition may effect the GPS Signal travel time.

3. Clock inaccuracy

4. Rounding effect

How to improve GPS accuracy?

Generally, GPS accuracy is between 5-15m.

1. Differential GPS

Produce 3-5m accuracy.

2. Wide Area Augmentation System

Produce 1-3m accuracy.


Sunday, July 22, 2012

All Accounts Locked Due to Accessing User Account Manager from Control Panel in Server 2008

Just faced with interesting problem few days back. All user accounts on our Windows Server 2008 Standard Edition suddenly locked. After browsing through the Event Viewer Security logs, we noticed multiple Audit Failure entry for all user accounts with the following details:
- Event ID : 4625
- Caller Process Name : dllhost.exe
- Happened within the same seconds for all users

It is quite impossible for all users to coincidentally failed to log on at the same time. At first we thought of the possibility of denial of service attack on the servers. After some browsing on the Internet and thinking through what we have done before this incident happened, we suspect that this is due to Account Management feature accessible from Control Panel.

We did some simple test to confirm the root cause of this issue:

1. Add audit success and audit failure on the Audit Policy under Local Security Policy setting

2. Open Event Viewer, go to Windows Log, Security
Filter Event ID : 4625


3. Open User Accounts under Control Panel



4. Click Manage Another Accounts link
This action will trigger Audit Failure on the Event Viewer Log for all user accounts



5. Go back to Event Viewer and press Refresh
You will notice a lot of Audit Failure Entry which occurred at the same time.
This will trigger account lock out if Account Lockout Policy is configured



Tuesday, June 26, 2012

MySQL Spatial Query Example

CREATE TABLE Points (
NAME VARCHAR(16) PRIMARY KEY,
location POINT NOT NULL,
description VARCHAR(128),
SPATIAL INDEX(location)
) ENGINE = MYISAM;

INSERT INTO Points (NAME, location) VALUES ( 'point1' , GEOMFROMTEXT( ' POINT(31.5 42.2) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point2' , GEOMFROMTEXT( ' POINT(10 10) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point3' , GEOMFROMTEXT( ' POINT(20 10) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point4' , GEOMFROMTEXT( ' POINT(30 10) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point5' , GEOMFROMTEXT( ' POINT(40 10) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point6' , GEOMFROMTEXT( ' POINT(10 20) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point7' , GEOMFROMTEXT( ' POINT(20 20) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point8' , GEOMFROMTEXT( ' POINT(30 20) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point9' , GEOMFROMTEXT( ' POINT(40 20) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point10' , GEOMFROMTEXT( ' POINT(10 30) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point11' , GEOMFROMTEXT( ' POINT(20 30) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point12' , GEOMFROMTEXT( ' POINT(30 30) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point13' , GEOMFROMTEXT( ' POINT(40 30) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point14' , GEOMFROMTEXT( ' POINT(10 40) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point15' , GEOMFROMTEXT( ' POINT(20 40) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point16' , GEOMFROMTEXT( ' POINT(30 40) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point17' , GEOMFROMTEXT( ' POINT(40 40) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point18' , GEOMFROMTEXT( ' POINT(10 50) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point19' , GEOMFROMTEXT( ' POINT(20 50) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point20' , GEOMFROMTEXT( ' POINT(30 50) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point21' , GEOMFROMTEXT( ' POINT(40 50) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point22' , GEOMFROMTEXT( ' POINT(10 60) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point23' , GEOMFROMTEXT( ' POINT(20 60) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point24' , GEOMFROMTEXT( ' POINT(30 60) ' ) );
INSERT INTO Points (NAME, location) VALUES ( 'point25' , GEOMFROMTEXT( ' POINT(40 60) ' ) );

SELECT NAME, ASTEXT(location) FROM Points;

Query by Radius (10):

SET @center = GEOMFROMTEXT('POINT(25 25)');
SET @radius = 10;
SET @bbox = CONCAT('POLYGON((',
X(@center) - @radius, ' ', Y(@center) - @radius, ',',
X(@center) + @radius, ' ', Y(@center) - @radius, ',',
X(@center) + @radius, ' ', Y(@center) + @radius, ',',
X(@center) - @radius, ' ', Y(@center) + @radius, ',',
X(@center) - @radius, ' ', Y(@center) - @radius, '))'
);


SELECT NAME, ASTEXT(location)
FROM Points
WHERE INTERSECTS( location, GEOMFROMTEXT(@bbox) )
AND SQRT(POW( ABS( X(location) - X(@center)), 2) + POW( ABS(Y(location) - Y(@center)), 2 )) < @radius;


SELECT NAME, ASTEXT(location), SQRT(POW( ABS( X(location) - X(@center)), 2) + POW( ABS(Y(location) - Y(@center)), 2 )) AS distance
FROM Points
WHERE INTERSECTS( location, GEOMFROMTEXT(@bbox) )
AND SQRT(POW( ABS( X(location) - X(@center)), 2) + POW( ABS(Y(location) - Y(@center)), 2 )) < @radius
ORDER BY distance;

Wednesday, August 3, 2011

Large Image File Processing

Combining large image file can be very tricky. After hours of trying and googling, I stumble upon a cool image processing tools named netpbm. It solved my problem immediately.

I have a very large tiff map image files that need to be stitched into 1 large map. Common image processing software such as Photoshop and Gimp will hang on my laptop since the total file size is over 5 GB.

With netpbm, combining those images is a breeze.

1. Download netpbm from http://gnuwin32.sourceforge.net/packages/netpbm.htm
2. Do not forget to download the "dependencies", you'll need it later on
3. Run tifftopnm to convert all tiff files to portable bitmaps format
4. Run pnmcat -lr or -tb to combine the images
5. Run pnmtojpg to produce the end result in jpeg format