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!
Sunday, October 27, 2013
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.
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
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
Subscribe to:
Posts (Atom)