Implementing a self-signed certificate

It is a must to be able to generate certificates in order to test various security scenarios like running HTTP over SSL, message signing and/or encryption.

The .Net Framework SDK is shipped with a command line tool makecrt.exe that can create a self-signed certificate. This certificate can then be used on a developer's workstation or testing server.

Note: This should not be used for Production purposes. You can buy certificates from various entities.

The following command can be used to create a self-signed SSL test certificate:

makecert -r -pe -n "CN=www.yourserver.com" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

To install this certificate in IIS (5.x, 6.0), open the IIS Management console:

• Right-click on your site (e.g. Default site) and select Properties
• Open the tab "Directory Security"
• Click the "Server Certificate..." button, pass the first screen of the wizard
• Choose "Assign an existing certificate"
• Select the newly generated certificate from the list and click Next until the end of the wizard

SSL is now enabled in IIS!

Note: Older versions of makecert.exe do not support the "-pe" option, which makes the private key exportable. If you have an old version of makecert.exe, you can omit the "-pe" option, but then the certificate cannot be exported including the private key.

The testing certificate above is also known as self certificate. Self certificates are not "trusted" by your computer or browser, which maintains a list of trusted authorities.

A certificate issued by a trusted authority is a trusted certificate.

When using a testing certificate, you will get security warning in your browser warning you that the certificate is not a trusted one. Moreover, if you force SSL on your VS.Net project you may not be able to open it if it uses a testing certificate.

The only way to get around these issues is to add your testing certificate to the list of trusted authorities.

• Open a command prompt and run the MS Management Console by typing mmc and enter
• Click File and Add/Remove Snap-in
• Click the Add button and choose Certificates
• Choose "Computer Account"
• Select the local computer
• Open the Personal \ Certificates
• Copy your testing certificate to Trusted Root Certification Authorities \ Certificates (Drag and Drop + Ctrl key)

UPDATE: IIS7 offers a new feature to create self-signed certificate easily. The only issue is that you cannot set all the certificate properties.

Self Signed Certificate in IIS6

You can use the IIS 6 Resources Kit to generate and install a self-signed certificate with the SelfSSL.exe command line tool.

The IIS 6 Resouces Kit is available on the Microsoft.com website: http://go.microsoft.com/fwlink/?LinkId=34407

Note that you should use a self-signed certificate when you need to troubleshoot third-party certificate problems or when you need to create a secure private channel between your server and a limited, known group of users, such as exists in a software test environment.

Follow this step in order to generate and install the self-signed certificate.

1. Create a virtual site (or use the one on which you want to install the certificate) and set up SSL (default port is 443)


2. Launch the SelfSSL tool (Start Menu All Programs IIS Resources SelfSSL SelfSSL Prompt)


3. Run the following from the prompt replacing the /N:CN with your DNS name and the /S parameter with the IIS site Id

SelfSSL /N:CN=dnsname.mydomain.org /V:365 /S:siteId /P:433

Note: If you create a SSL certificate for the main IIS site, you can omit the /S, else the site ID can be found from the IIS Manager console)

SelfSSL command help

Installs self-signed SSL certificate into IIS.
SELFSSL [/T] [/N:cn] [/K:key size] [/S:site id] [/P:port]

/T Adds the self-signed certificate to "Trusted Certificates" list.
The local browser will trust the self-signed certificate if this flag is specified.
/N:cn Specifies the common name of the certificate. The computer
name is used if not specified.
/K:key size Specifies the key length. Default is 1024.
/V:validity days Specifies the validity of the certificate. Default is 7 days.
/S:site id Specifies the id of the site. Default is 1 (Default Site).
/P:port Specifies the SSL port. Default is 443.
/Q Quiet mode. You will not be prompted when SSL settings are overwritten.

The default behaviour is equivalent with:
selfssl.exe /N:CN=MYSERVER /K:1024 /V:7 /S:1 /P:443

Resetting security after restoring a database backup

When you restore a database from one server to another (i.e. restoring from Production to QA or Development), the user permissions of SQL logins are not automatically reset even if a user with the same name exists (note that this does not apply if you use Windows authentication)

In order to reset the user permissions, you can use the system command sp_change_users_login.

When passing REPORT as a parameter, it will list all the orphan users.

sp_change_users_login REPORT
GO

You can then re-assign the orphan users to existing users by using the following command:

sp_change_users_login UPDATE_ONE, 'ORPHAN_USER', 'LOGIN'

Example:

sp_change_users_login UPDATE_ONE, 'production_account', 'qa_account'
GO

Resetting SQL identity columns

If you're somehow perfectionnist as I am, you probably want to reset the your identity columns when deleting all records from a table.

With SQL Server, you can do so by using the DBCC CHECKIDENT command.

DBCC CHECKIDENT ('MyTable', RESEED, 0)

Now if you want to reset the identity of a table that still contains some rows, you must reseed it to the last number used.

DBCC CHECKIDENT ('MyTable', RESEED, 123)