Sql Server: Manage Access and Roles with Transact-SQL (T-SQL) Commands

From: https://technet.microsoft.com/en-us/library/dd433159.aspx

Tip: Manage Access and Roles with Transact-SQL (T-SQL) Commands

SQL Server provides different commands for managing database access and roles. Here is a summary of the commands you can use.

Adding a User to the Current Database:

CREATE USER user_name
        [ { { FOR | FROM }
               { LOGIN login_name
               | CERTIFICATE certificate_name
               | ASYMMETRIC KEY asym_key_name
               }
        ]
        [ WITH DEFAULT_SCHEMA = schema_name ]

Renaming a User or Changing Default Schema:

ALTER USER user_name
        WITH < set_item > [ ,...n ]
        
< set_item > ::=
        NAME = new_user_name
        | DEFAULT_SCHEMA = schema_name

Removing a User from a Database:

DROP USER user_name

Listing Server Role Members:

sp_helpsrvrolemember [[@rolename =] 'role']

Managing Database Standard Roles:

CREATE ROLE role_name [ AUTHORIZATION owner_name ]
ALTER ROLE role_name WITH NAME = new_name
DROP ROLE role_name
sp_helprole [[@rolename =] 'role']

Managing Database Role Members:

sp_addrolemember [@rolename =] 'role',
        [@membername =] 'security_account'
sp_droprolemember [@rolename =] 'role',
        [@membername =] 'security_account'
sp_helprolemember [[@rolename =] 'role']

Managing Application Roles:

CREATE APPLICATION ROLE application_role_name
        WITH PASSWORD = 'password' [ , DEFAULT_SCHEMA = schema_name ]

ALTER APPLICATION ROLE application_role_name
        WITH <set_item> [ ,...n ]

<set_item> ::=
        NAME = new_application_role_name
        | PASSWORD = 'password'
        | DEFAULT_SCHEMA = schema_name

DROP APPLICATION ROLE rolename

From the Microsoft Press book Microsoft SQL Server 2008 Administrator's Pocket Consultant.

Looking for More Tips?

For more SQL Server Tips, visit the TechNet Magazine SQL Server Tips page.

For more Tips on other products, visit the TechNet Magazine Tips index.

JavaScript: Add and remove from arrays / arrays as collections

Did you know you can easily add and remove elements from Javascript arrays ?

Declare an array:

var arr = [];

To add an element:

arr.push(item);

To remove an element at index idx:

arr.splice(idx, 1);

To remove an element elm:

var idx = arr.indexOf(elm);

if (idx > -1) arr.splice(idx, 1);

The splice function allows adding and removing items from an array by providing a range (start index and length) and replacement values.

See also: https://www.w3schools.com/jsref/jsref_splice.asp

Sql Server Advanced: Common Table Expressions

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. It is not stored as an object and lasts only for the duration of the query.

Example:

WITH BelgianCustomers (Id, Name, Zip)

AS (
   SELECT Id, CONCAT(FirstName, ' ', LastName), ZipCode
   FROM Contact
   WHERE Type='Customer' AND CountryCode='BE'
)
SELECT Zip, COUNT(Id)
FROM BelgianCustomers
GROUP BY Zip

CTE’s can also be recursive, which for instance allows to dynamically generate ‘counting’ rows from 1 to 100. I.e:

WITH [counter]

AS (
   SELECT 1 AS [Value]
   UNION ALL
   SELECT [Value]+1 FROM [counter] WHERE [Value]<100
)
SELECT [Value] FROM [counter]

Which outputs 100 rows with 1 column [Value] with the values from 1 to 100.

Recursive CTE’s also allow to query hierarchy trees without limitation on the tree dept. I.e:

WITH [hierarchy] AS (

   -- Get 'root' personnel:
   SELECT 0 AS Level, Id, BossId, Job, Name
   FROM [Personnel]
   WHERE BossId IS NULL
   -- Union to add their subordinates recursively:
   UNION ALL
   SELECT h.Level+1 AS Level, p.Id, p.BossId, p.Job, p.Name
   FROM [Personnel] p
   INNER JOIN [hierarchy] h ON (p.BossId=h.Id)
)
SELECT * FROM [hierarchy]

(See “HierarchyThroughRecursiveCteExample.sql” hereunder to reproduce)

Some about recursive CTE’s:

https://msdn.microsoft.com/en-us/library/ms186243.aspx
https://sqlwithmanoj.com/2011/05/23/cte-recursion-sequence-dates-factorial-fibonacci-series/

Note: when using common table expressions, semi-colons (“;”) are sometimes required to allow the T-SQL interpreter to correctly separate the commands.

HierarchyThroughRecursiveCteExample.sql :

-- Example of a recursive CTE (Common Table Expression) to retrieve a hierarchy:
--------------------------------------------------------------------------------
CREATE TABLE [Personnel]
(
    [Id] int IDENTITY(1,1) NOT NULL,
 [BossId] int null,
 [Job] nvarchar(50) not null,
 [Name] nvarchar(50) not null,
 CONSTRAINT [PK_Personnel] PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [Personnel] ON
INSERT INTO [Personnel] ([Id], [BossId], [Job], [Name]) VALUES (1, NULL, 'CEO', 'Eric Doe')
INSERT INTO [Personnel] ([Id], [BossId], [Job], [Name]) VALUES (2, 1, 'CFO', 'Finn Doe')
INSERT INTO [Personnel] ([Id], [BossId], [Job], [Name]) VALUES (3, 1, 'CIO', 'Inne Doe')
INSERT INTO [Personnel] ([Id], [BossId], [Job], [Name]) VALUES (4, 2, 'Accountant', 'Axel Doe')
INSERT INTO [Personnel] ([Id], [BossId], [Job], [Name]) VALUES (5, 3, 'PM', 'Patrick Doe')
INSERT INTO [Personnel] ([Id], [BossId], [Job], [Name]) VALUES (6, 5, 'DBA', 'David Doe')
INSERT INTO [Personnel] ([Id], [BossId], [Job], [Name]) VALUES (7, 5, 'Dev', 'Dereck Doe')
SET IDENTITY_INSERT [Personnel] OFF
GO
WITH [hierarchy] AS (
   -- Get 'root' personnel:
   SELECT 0 AS Level, Id, BossId, Job, Name
   FROM [Personnel]
   WHERE BossId IS NULL
   -- Union to add their subordinates recursively:
   UNION ALL
   SELECT h.Level+1 AS Level, p.Id, p.BossId, p.Job, p.Name
   FROM [Personnel] p
   INNER JOIN [hierarchy] h ON (p.BossId=h.Id)
)
SELECT * FROM [hierarchy]
GO

Sql Server: Change Data Capture

What is it ?

Change Data Capture is asynchronous and uses the transaction log in a manner similar to replication. Past values of data are maintained and are made available in change tables by a capture process, managed by the SQL Agent, which regularly scans the T-Log. As with replication, this can prevent re-use of parts of the log.

Change Data Capture was introduced with SQL Server 2008+ and requires DataCenter or Enterprise edition. Since SQL Server 2016 SP1 it is available on Standard edition as well. It has similarities but is different to Change Tracking, a feature which was also introduced with SQL Server 2008+ (but on all editions).

Compare to Change Tracking

https://www.littlekendra.com/2010/06/23/cdcvsct/

https://technet.microsoft.com/en-us/library/2008.11.sql.aspx (also excellent in-depth information about both CDC and CT features)

Setup Change Data Capture

To enable CDC on the current database:

EXEC sys.sp_cdc_enable_db

To enable CDC for a specific table(tracking all columns):

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name   = N'Customer',

@role_name     = NULL,

--@filegroup_name = N'MyDB_CT',

@supports_net_changes = 1,

@capture_instance = N'dbo_Customer'

GO

Where:

dbo                        : Schema name of the table;

Customer            : Table name;

historian              : (optional) role name for users having access to the tracking table;

capture_instance  : (optional) instance name. By default “<Schema>_<Table>”. A table can have up to 2 CDC instances which are to be used to migrate the CDC table when schema changes were done on the underlying table.

Retention period

To set/change retention period (default is 3 days):

EXEC sp_cdc_change_job @job_type='cleanup', @retention=7200

http://sqlblog.com/blogs/allen_white/archive/2012/03/07/set-and-verify-the-retention-value-for-change-data-capture.aspx

Sets the retention period to 7200 minutes or 5 days. Maximum is 100 years.

To query/verify the retention period:

SELECT [retention]

  FROM [msdb].[dbo].[cdc_jobs]

  WHERE [database_id] = DB_ID()

  AND [job_type] = 'cleanup'

http://sqlblog.com/blogs/allen_white/archive/2012/03/07/set-and-verify-the-retention-value-for-change-data-capture.aspx

Managing the CDC Capture Job

On SQL Server, a job is scheduled every 5 seconds to read the Transaction Logs and write the mutations to CDC tables.

The job can be stopped with:

EXEC dbo.sp_cdc_stop_job

GO

The job can be started again with:

EXEC dbo.sp_cdc_start_job

GO

Alternatively, the job can be stopped with:

EXEC MSDB.dbo.sp_stop_job 'cdc.<DBName>_capture'

GO

The job can be started again with:

EXEC MSDB.dbo.sp_start_job 'cdc.<DBName>_capture'

GO

Note that the job is only run when the SQLServerAgent is running.

If the capture job is not running, the transaction log will keep all changes and stay growing. Once the capture job is started again, the transaction log will be handled, all change logs will gradually reach the CDC tables and the transaction log should shrink again. No change information should be lost as long as the downtime did not exceed the retention period.

Settings of the CDC jobs can be viewed with following query:

SELECT * FROM msdb.dbo.cdc_jobs

Managing the CDC Cleanup job

Together with the setting up of Change Data Capture, a cleanup job is installed that will delete CDC records passed the retention period.

Note that this job is scheduled (through SQL Server Agent) to run only once a day, at 02:00, and that it by default deletes no more than 5000 rows !

In databases where more than 5000 rows can be inserted/updated/deleted per day (or 2500 rows updated, as an update counts for 2 CDC records ??), CDC tables will still keep growing and retention period will not be honored.

Both retention period (in minutes, by default 3 days = 4320) and threshold (by default 5000 rows) can be set with the following statement:

EXEC sp_cdc_change_job @job_type='cleanup', @retention = 4320, @threshold = 5000

Settings of the CDC jobs can be viewed with following query:

SELECT * FROM msdb.dbo.cdc_jobs

The cleanup job can also be run explicitly:

EXEC sp_MScdc_cleanup_job

Or it’s schedule in SQL Server Agent could be changed.

See also: http://tomaslind.net/2014/04/08/cdc-cleanup-job/

Remove Change Data Capture

To remove Change Data Capture table for a specific table/instance:

EXEC sys.sp_cdc_disable_table 

@source_schema = N'dbo', 

@source_name   = N'Customer', 

@capture_instance = N'dbo_Customer' 

GO 

To disable CDC on the database:

EXEC sys.sp_cdc_disable_db 

GO 

… to be continued …

See all Change Data Capture related stored procedures:

https://technet.microsoft.com/en-us/library/bb500244(v=sql.105).aspx

About Change Data Capture:

https://msdn.microsoft.com/en-us/library/cc645937.aspx

Administer and Monitor Change Data Capture:

https://msdn.microsoft.com/en-us/library/cc645592.aspx

Sql Server: Change Tracking

What is it ?

Change Tracking is a synchronous mechanism which modifies change tracking tables as part of ongoing transactions to indicate when a row has been changed. It does not record past or intermediate versions of the data itself, only that a change has occurred. It is recommended to use snapshot isolation with Change Tracking!

Change Tracking was introduced with SQL Server 2008+ (all editions). It has a lot of similarities, but also differences with Change Data Capture (CDC), which is also a feature of SQL Server 2008+ (but only DataCenter en Enterprise editions).

For a good explanation, see also:

Using Change Tracking in SQL Server 2008

https://www.mssqltips.com/sqlservertip/1819/using-change-tracking-in-sql-server-2008/

Prerequisites

Change Tracking is available since SQL Server 2008 and on Azure SQL.

Tracked tables need to have a primary key.

To use change tracking, the database compatibility level must be set to 90 or greater. If a database has a compatibility level of less than 90, you can configure change tracking. However, the CHANGETABLE function, which is used to obtain change tracking information, will return an error.

Using snapshot isolation is the easiest way for you to help ensure that all change tracking information is consistent. For this reason, we strongly recommend that snapshot isolation be set to ON for the database. For more information, see Workwith Change Tracking (SQL Server).

Enabling change tracking

To setup change tracking on a database:

ALTER DATABASE Samples2012

SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

To change afterwards:

ALTER DATABASE Samples2012

SET CHANGE_TRACKING (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

To enable change tracking on a table after it has been setup on the database:

ALTER TABLE invoicing.Customer

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED = OFF)

By default, TRACK_COLUMNS_UPDATED is OFF. If you set it on, SQL Server will also keep track of which columns were updated with an update statement.

Verifying change tracking

Querying the sys.change_tracking_databases catalog view lists the databases for which change tracking is enabled:

SELECT

       td.database_id,

       d.name AS database_name,

       td.is_auto_cleanup_on,

       td.max_cleanup_version,

       td.retention_period,

       td.retention_period_units,

       td.retention_period_units_desc

FROM sys.change_tracking_databases td

INNER JOIN sys.databases d ON td.database_id = d.database_id

Querying the sys.change_tracking_tables catalog view lists the tables of the current database for which change tracking is enabled:

SELECT

    tt.object_id,

    s.name AS schema_name,

    t.name AS table_name,

    tt.is_track_columns_updated_on,

    tt.min_valid_version,

    tt.begin_version,

    tt.cleanup_version

FROM sys.change_tracking_tables tt

INNER JOIN sys.tables t ON (tt.object_id = t.object_id)

INNER JOIN sys.schemas s ON (t.schema_id = s.schema_id)

ORDER BY 2, 3

Using change tracking

All changes are tracked with a change version. The first ever created change tracking record (over all tables) has version 1.

When AUTO_CLEANUP is started, older versions get removed out of the change tracking tables, and the oldest (minimum) version tracked

With CHANGETABLE(CHANGES Schema.Table, @VersionAlreadyRead) the list of changes can be queried. I.e:

SELECT *

FROM CHANGETABLE(CHANGES invoicing.Customer, 6) AS CustomerChanges

ORDER BY 1

Returns all changes on table Customer with version > 6:

Note the last column(s) containing the primary key which you can use to join with the table to get actual values.

SYS_CHANGE_VERSION contains the version number. Two rows have version 8 which means they both resulted from the execution of a single SQL statement.

SYS_CHANGE_OPERATION contains I for INSERT, U for UPDATE or D for DELETE.

For instance, to get the actual values of the recently inserted records:

SELECT c.*, cc.SYS_CHANGE_OPERATION, cc.SYS_CHANGE_VERSION, cc.Id AS CHANGEDROW_ID

FROM CHANGETABLE(CHANGES invoicing.Customer, 7) AS cc

LEFT OUTER JOIN invoicing.Customer AS c ON (cc.Id = c.Id)

ORDER BY cc.SYS_CHANGE_VERSION

To get the last used CHANGE_VERSION in the database:

SELECT CHANGE_TRACKING_CURRENT_VERSION()

To get the oldest/smallest/minimum version number still available (not yet AutoCleaned) for a given table:

SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('invoicing.Customer'))

Disabling change tracking

To disable change tracking on a table:

ALTER TABLE invoicing.Customer

DISABLE CHANGE_TRACKING

On the database:

ALTER DATABASE Samples2012

SET CHANGE_TRACKING = OFF

Note: Change tracking must first be disabled for all change-tracked tables before change tracking can be set to OFF for the database. To determine the tables that have change tracking enabled for a database, use the sys.change_tracking_tables catalog view.

More information

·         About Change Tracking (SQL Server)
https://msdn.microsoft.com/en-us/library/bb933875(v=sql.130).aspx

·         Enable and Disable Change Tracking (SQL Server)
https://msdn.microsoft.com/en-us/library/bb964713(v=sql.130).aspx

·         Work with Change Tracking (SQL Server)
https://msdn.microsoft.com/en-us/library/bb933874(v=sql.130).aspx

·         Change Data Capture and Other SQL Server Features
About change tracking, database mirroring, transactional replication, restoring and attacching a database enabled for Change Data Capture.
https://msdn.microsoft.com/en-us/library/cc645938.aspx

·         Performance Tuning SQL Server Change Tracking
https://www.brentozar.com/archive/2014/06/performance-tuning-sql-server-change-tracking/

·         Change Tracking Cleanup Limitation
http://sirsql.net/content/2014/04/03/201443change-tracking-cleanup-limitation/

.NET: Cryptography code snippets

Symmetric key encryption

Symmetric key encryption is about encrypting information using a key that can also be used to decrypt the information.

Since the same key is used to encrypt and to decrypt the information, this key is often denoted as a shared secret as both encryptor and decryptor must know it, but it should be secret to the outer world.

More advanced encryption algorithms also use an initialization vector together with the key. An initialization vector is simply a randomly generated set of bytes (characters) that is used so no two pieces of text will generate to the same encrypted data.

Both the key and the initialization vector are required to encrypt, and to decrypt.

Symmetric algorithms are fairly fast and can encrypt large amounts of data. Therefore the encryption/decryption process will be stream based.

Sample code to perform symmetric encryption:

/// <summary>
/// Perform symmetric encryption.
/// </summary>
/// <param name="data">The data to encrypt.</param>
/// <param name="key">The secret encryption key.</param>
/// <param name="iv">The initialization vector.</param>
/// <returns>The encrypted data.</returns>
public byte[] EncryptSymmetric(byte[] data, byte[] key, byte[] iv)
{
    var provider = new TripleDESCryptoServiceProvider();
    provider.Key = key;
    provider.IV = iv;

    using (var mstream = new MemoryStream())
    using (var cstream = new CryptoStream(mstream, provider.CreateEncryptor(),CryptoStreamMode.Write))
    {
        cstream.Write(data, 0, data.Length);
        cstream.FlushFinalBlock();

        return mstream.ToArray();
    }
}

Encrypted data can be decrypted with the following code:

/// <summary>
/// Perform symmetric decryption.
/// </summary>
/// <param name="data">The encrypted data.</param>
/// <param name="key">The secret encryption key.</param>
/// <param name="iv">The initialization vector.</param>
/// <returns>The decrypted data.</returns>
public byte[] DecryptSymmetric(byte[] data, byte[] key, byte[] iv)
{
    var provider = new TripleDESCryptoServiceProvider();
    provider.Key = key;
    provider.IV = iv;

    using (var mstream = new MemoryStream())
    using (var cstream = new CryptoStream(mstream, provider.CreateDecryptor(), CryptoStreamMode.Write))
    {
        cstream.Write(data, 0, data.Length);
        cstream.FlushFinalBlock();

        return mstream.ToArray();
    }
}

The size of the key and the initialization vector is determined by the cryptography algorithm, and can be asked to the algorithm provider:

var provider = new TripleDESCryptoServiceProvider();
provider.LegalKeySizes => array of KeySizes for the encryption key
provider.LegalBlockSizes => array of KeySizes for the initialization vector

Valid sizes are:

Algorithm Legal key sizes (bits) Legal init. vector sizes (bits)
RC2 128 -
DES 64 64
TripleDES 128 or 192 64
Rijndael 128, 192, 256 128, 192, 256

It is also possible to let the encryption algorithm generate a random key and/or initialization vector using:

var provider = new TripleDESCryptoServiceProvider();
provider.GenerateKey();
provider.GenerateIV();

Asymmetric key encryption

Asymmetric key encryption is about encryption with a public/private key pair, usually stored inside an X509 certificate.

Typically (and that’s the only scenario supported in .NET) the public key is used to encrypt, and the private key is used to decrypt the data. Therefore, the encrypted data can only be decrypted by who has access to the private key.

Asymmetric key encryption is much harder to break than symmetric encryption. But it is also slower. Therefore, asymmetric algorithms only support the encryption of a limited amount of data. That size depends on the keylength (which can be given when the keypair is generated, typically 1024 or 2048 bits, but possible values include 512, 1024, 2048, 4096, 8192, 16384, 32768 bits).

Note that the maximum length of a message that can be encrypted using RSA is the size of the key in bytes  - 11. So, for a 512-bit key pair (64 byte), the maximum encryptable message size is 53 bytes (64 - 11). (http://ftp.runrev.com/forums/viewtopic.php?t=11733&p=64179) Therefore:

Key size (bits) Maximum encyptable message size (bytes)
512 53
1024 117
2048 245
4096 501

Sample code to perform asymmetric encryption:

        /// <summary>
        /// Encrypt data with the given certificate's public key.
        /// </summary>
        /// <param name="data">Data to encrypt.</param>
        /// <param name="certificate">Certificate containing the public key.</param>
        /// <returns>Encrypted data.</returns>
        public byte[] EncryptAsymmetric(byte[] data, X509Certificate2 certificate)
        {
            var csp = (RSACryptoServiceProvider)certificate.PublicKey.Key;
            return csp.Encrypt(data, true);
        }

Encrypted data can be decrypted with the following code:

        /// <summary>
        /// Decrypts data with the given certificate's private key.
        /// </summary>
        /// <param name="data">Encrypted data.</param>
        /// <param name="certificate">Certificate containing the private key.</param>
        /// <returns>Decrypted data</returns>
        public byte[] DecryptAsymmetric(byte[] data, X509Certificate2 certificate)
        {
            if (certificate.HasPrivateKey == false)
                throw new ArgumentException("Certificate needs a private key to support decryption.");
            var csp = (RSACryptoServiceProvider)certificate.PrivateKey;
            return csp.Decrypt(data, true);
        }

Creating a certificate for digital signing and encryption

A certificate is essentially a public/private keypair combined with identity information (name of the owner), validity information (valid until date, revocation list URL,…) etc, and which is in its turn signed by an issuer chaining up to (eventually) a (trustworthy) root certification authority.

Therefore an official trustworthy certificate is to be bought from some recognized certification authority.
Although, for SSL purposes, some free SSL certification authorities exist, but they are of course less trustworthy.

For internal/testing/development purposes, one can also create a certificate with the MAKECERT.EXE utility.

Using MAKECERT.EXE you can create a simple certificate issued by a test “Root Agency” for the current user:

    MAKECERT -pe -n "CN=ACME Inc." -ss my -sr CurrentUser -a sha1 -len 2048 "SimpleAcme.cer"

Using MAKECERT.EXE you can create a simple self-signed certificate for the local machine:

    MAKECERT -pe -n "CN=ACME Inc. Self" -ss my -sr LocalMachine -a sha1 -len 2048 -r "SelfAcme.cer"

Or you can create a self-signed or ‘root’ certificate (basically the same thing?):

    MAKECERT -pe -n "CN=ACME Inc. Root" -ss my -sr LocalMachine -a sha1 -len 2048 -sky signature -r "AcmeRoot.cer"

And then, create a ‘child’ certificate using:

    MAKECERT -pe -n "CN=ACME Inc." -ss my -sr LocalMachine -a sha1 -len 2048 -sky exchange -in "ACME Inc. Root" -is my -ir LocalMachine "AcmeChild.cer"

Note: using “-sky signature” for keys makes keys only usable for signing. Use the “-sky exchange” option to make keys that can be used for both signing and encryption. Failing to do so may result in a “Bad key” CryptographicException.

The PVK2PFX tool can be used to create a PFX file (which also contains the private key). For this, user the “-sv” option of MAKECERT to generate a .pvk file, then use PVK2PFX to merge both the .cer and the .pvk file into a single .pfx file. I.e:

    MAKECERT.EXE -r -pe -n "CN=CompanyXYZ Server" -b 01/01/2007 -e 01/01/2027 -sky exchange Server.cer -sv Server.pvk
    PVK2PFX.EXE -pvk Server.pvk -spc Server.cer -pfx Server.pfx 

See also:
How to: Create Your Own Test Certificate
http://msdn.microsoft.com/en-us/library/ff699202.aspx
How-to use MakeCert for trusted root certification authority and SSL certificate issuance
http://blogs.technet.com/b/jhoward/archive/2005/02/02/365323.aspx
MakeCert (for detailed command line option descriptions)
http://msdn.microsoft.com/en-us/library/aa386968.aspx
Using MakeCert (containing sample MakeCert calls)
http://msdn.microsoft.com/en-gb/library/aa388165.aspx
Tools for Signing Drivers (including explanation of MakeCert and CertMgr)
http://msdn.microsoft.com/en-us/library/windows/hardware/ff552958(v=vs.85).aspx

Creating a certificate for Code Signing

An explanation is given here:
http://stackoverflow.com/questions/84847/how-do-i-create-a-self-signed-certificate-for-code-signing-on-windows

The below file “CreateCodeSigningCert.cmd.txt” is a script to generate such signed certificates.

X.509 distinguished names (DNs)

The Distinguished Name (DN) uniquely identifies an entity in an X.509 certificate. The following attribute types are commonly found in the DN:

CN          Common Name
T              Title
O             Organization name
OU         Organizational Unit name
L              Locality name
ST (or SP or S)
State or Province name
C             Country

The Common Name (CN) can describe an individual user or any other entity, for example a Web server.

The DN can contain multiple OU attributes, but one instance only of each of the other attributes is permitted. The order of the OU entries is significant: the order specifies a hierarchy of Organizational Unit names, with the highest-level unit first.

I.e:
C=BE;O=Acme Inc.;OU=Shared Services;OU=Marketing;CN=John Smith

Obtaining a certificate for digital signing and encryption

Alternatively to creating your own certificate, certificates can also be obtained from the following sources:

Various types and sizes of certificates – for testing purposes – can also be obtained from:

• http://www.frank4dd.com/howto/openssl/certexamples.htm
• Free certificates for SSL can be obtained from:
  http://www.startssl.com/?app=1
• Regular commercial certificate issuers as GlobalSign, CertiPost, VeriSign, etc.

Managing certificates in the Windows Certificate Store

The Windows Certificate Store is a location in the registry where certificates are stored for use by different applications:
   HKLM/Software/Microsoft/SystemCertificates
   HKCU/Software/Microsoft/SystemCertificates

CERTUTIL can be used for various certificate related tasks, including importing PFX files into the certificate store.

I.e:
   CERTUTIL -f -p secret -importpfx "MyCert.pfx" NoExport

Installs the certificate with private key (protected by the “secret” password) into the LocalMachine’s personal store. The optional NoExport parameter defines that the private key is not exportable (note: older systems as WinXP do not support this parameter).

Command prompt reference :
http://technet.microsoft.com/library/cc732443.aspx

Assigning a ‘friendly name’ to the certificate can only be done ones the certificate is installed. Either it is done from within MMC (open the certificate, view details, press “Edit Properties…”. Or use following trick:

Create a file named with a .INF extension, with following content:
   [Version]
   Signature = "$Windows NT$"
   [Properties]
   11 = "{text}My Friendly Name"

Then use the following CERTUTIL command:
   certutil -repairstore my <certificate serial number> <.inf file>

I.e:

   certutil -repairstore my 32e6f007924d79ba488a82f10e140e15 "thefile.inf"

See: http://secadmins.com/index.php/category/certutil-exe/

CERTMGR.EXE can be used for manual or scripted installation (and removal) of certificates in the Windows certificate store.

Enter CRTMGR without commandline parameters to open the GUI version.

Command prompt reference:
http://msdn.microsoft.com/en-us/library/e78byta0(v=vs.80).aspx

WinHttpCertCfg.exe can be used to install and configure certificates for use by the IWAM (Internet Server Web Application Manager) account.

I.e:
   winhttpcertcfg -i "MyCert.pfx" -p secret -a "DOMAIN\username" -c LOCAL_MACHINE\My

Installs the “MyCert.pfx” certificate (using password “secret”) in the LocalMachine’s personal store, and make it accessible to the (AppPool run as identity of) DOMAIN\username.

Command prompt reference:
http://msdn.microsoft.com/en-us/library/windows/desktop/aa384088(v=vs.85).aspx

See also:
http://msmvps.com/blogs/luisabreu/archive/2010/09/13/grant-access-to-certificate-s-private-key-in-iis-7-5.aspx

MMC can be used to visually manage certificate stores.

Run MMC (Start, Run, “mmc”), then choose File, Add/Remove Snap-In…, Add…, choose “Certificates”, then choose whether you want to see your personal, a service’s or a computer certificate store.

Programmatically accessing certificates from file or keystore

The following code allows opening a certificate from a .PFX or .CER file (some overload examples):

var cert = new X509Certificate2(@"MyCertificate.cer");

var cert = new X509Certificate2(@"MyCertificate.pfx", "secret");

var cert = new X509Certificate2(@"MyCertificate.pfx", "secret", X509KeyStorageFlags.DefaultKeySet);

The following function lists all certificates stored on a location in the Windows Certificate Store:

/// <summary>
/// Lists all certificates in the given Windows Certificate Store.
/// </summary>
/// <param name="storeName">Store to use.</param>
/// <param name="storeLocation">Store location.</param>
public static IEnumerable<X509Certificate2> ListStoreCertificates(string storeName, StoreLocation storeLocation)
{
    X509Store store = new X509Store(storeName, storeLocation);
    store.Open(OpenFlags.ReadOnly);
    try
    {
        foreach (var cert in store.Certificates)
        {
            yield return cert;
        }
    }
    finally
    {
        store.Close();
    }
}

For instance, called by:

foreach (var cert in ListStoreCertificates("my", StoreLocation.LocalMachine))
{
    Console.WriteLine("  {0}\r\n  {1}\r\n  {2}\r\n  {3}\r\n  {4}\r\n",
        cert.SerialNumber,
        cert.Subject,
        cert.GetPublicKeyString(),
        cert.HasPrivateKey,
        cert.FriendlyName);
}

Searching a specific certificate in the store can be done by iterating over all certificates, as shown in the above ListStoreCertificates() function, or can be done using the Find method. Following function uses the Find method:

public static X509Certificate2 FindCertificate(StoreLocation location, StoreName name, X509FindType findType, string findValue)
{
    X509Store store = new X509Store(name, location);
    try
    {
        // Create and open store for read-only access:
        store.Open(OpenFlags.ReadOnly);
        // Search store:
        X509Certificate2Collection col = store.Certificates.Find(findType, findValue, true);
        // Return first certificate found (or null):
        return (col.Count > 0) ? col[0] : null;
    }
    finally
    {
        // Always close the store:
        store.Close();
    }
}

Searching a certificate by it’s serial number can then be done by:
X509Certificate2 cert = FindCertificate(StoreLocation.LocalMachine, StoreName.My, X509FindType.FindBySerialNumber, "32E6F007924D79BA488A82F10E140E15");

By part of a subject name:

X509Certificate2 cert = FindCertificate(StoreLocation.LocalMachine, StoreName.My, X509FindType.FindBySubjectName, "subjectnamepart");

Valid findTypes are:

• X509FindType.FindByThumbprint
• X509FindType.FindBySubjectName
• X509FindType.FindBySubjectDistinguishedName
• X509FindType.FindByIssuerName
• X509FindType.FindByIssuerDistinguishedName
• X509FindType.FindBySerialNumber
• X509FindType.FindByTimeValid
• X509FindType.FindByTimeNotYetValid
• X509FindType.FindByTimeExpired
• X509FindType.FindByTemplateName
• X509FindType.FindByApplicationPolicy
• X509FindType.FindByCertificatePolicy
• X509FindType.FindByExtension
• X509FindType.FindByKeyUsage
• X509FindType.FindBySubjectKeyIdentifier

The following function (requiring a reference to System.Security.dll) allows the user to choose a certificate from the certificate store by means of a dialog:

/// <summary>
/// Shows a dialog allowing the user to select a certificate.
/// </summary>
/// <param name="storeName">Store to use.</param>
/// <param name="storeLocation">Store location.</param>
/// <param name="caption">Dialog caption.</param>
/// <param name="message">Dialog message.</param>
public static X509Certificate2 GetCertificateFromStoreUI(string storeName, StoreLocation storeLocation, string caption, string message)
{
    X509Store store = new X509Store(storeName, storeLocation);
    try
    {
        store.Open(OpenFlags.ReadOnly);
        // Pick a certificate from the store:
        var result = X509Certificate2UI.SelectFromCollection(store.Certificates, caption, message, X509SelectionFlag.SingleSelection);
        if (result.Count > 0)
            return result[0];
        else
            return null;
    }
    finally
    {
        store.Close();
    }
}

Called for instance by:

var cert = GetCertificateFromStoreUI("My", StoreLocation.LocalMachine, "Choose certificate", "Choose a certificate");

or

var cert = GetCertificateFromStoreUI(new X509Store(StoreName.My).Name, StoreLocation.LocalMachine, "Choose certificate", "Choose a certificate");

Shows a dialog similar to the following (may look different on different Windows versions):

The following function (requiring a reference to System.Security.dll) shows the certificate in a dialog:

/// <summary>
/// Shows the given certificate in a common dialog.
/// </summary>
public static void ShowCertificateUI(X509Certificate2 cert)
{
    X509Certificate2UI.DisplayCertificate(cert);
}

The following type of dialog is shown:

You can also programmatically change properties of certificates in the store. For instance following function sets the friendly name of the certificate in the store:

public static bool SetFiendlyName(StoreLocation location, StoreName name, string certSerialNumber, string friendlyname)
{
    X509Store store = new X509Store(name, location);
    try
    {
        // Create and open store for read-write access:
        store.Open(OpenFlags.ReadWrite);
        foreach (var cert in store.Certificates.Find(X509FindType.FindBySerialNumber, certSerialNumber, false))
        {
            cert.FriendlyName = friendlyname;
            return true;
        }
    }
    finally
    {
        // Always close the store:
        store.Close();
    }
    // If no matching certificate found:
    return false;
}

Programmatically modify certificates

The following code opens a .pfx file, sets it’s FriendlyName property, and saves it to a new .pfx file:

X509Certificate2 certificate = new X509Certificate2(@"MyCert.pfx", "secret", X509KeyStorageFlags.Exportable);
certificate.FriendlyName = "Friendly Name of My Certificate";
var binary = certificate.Export(X509ContentType.Pkcs12, "secret");
File.WriteAllBytes(@"MyCertWithFriendlyName.pfx", binary);

Programmatically generating a public/private key pair

The above examples use a X509 certificate containing the keypair. However it is also possible to just generate, on the fly, a cryptographic keypair (without the need of creating a certificate).

To programmatically create a public/private key pair, simply create a new RSACryptoServiceProvider:

var keypair  = new RSACryptoServiceProvider();
The public and private keys can also be exported using:
RSAParameters privatekey = keypair.ExportParameters(true);
RSAParameters publickey = keypair.ExportParameters(false);

Hashing

Hashing is about computing a short byte sequence that sufficiently uniquely identifies the hashed data but does not allow to reproduce it. Hashing is therefore a one way operation.

Hashing is often used for:
- Password validation,
- Comparison of large files/data streams (similar to CRC64),
- As part of the process of digital signature (validation).

MD5 (using a 128 bit encryption key) and SHA1 (using a 160 bit encryption key) should be considered outdated/inadequate for password hashing (though they are not really unsecure).

Use more secure variants as SHA256, (SHA384) and SHA512. Or look for .NET implementations of RipeMD, WHIRLPOOL, SHA3,…
Or consider using more future-proof key stretching hashing algorithms as PBKDF2 or BCrypt.

For large scale authentication systems (i.e. over a million users), one should consider including hardware components in the solution.
See for instance https://www.yubico.com/products/yubihsm/.

A hash of a byte array or a stream can be obtained using the ComputeHash method of the hashing algorithm:

byte[] hash = new SHA256Managed().ComputeHash(bytearray or stream)

Salted hashing

When hashing passwords, same passwords result in same hashes. Therefore, a hacker could maintain a list of common passwords and their hashcodes according to common algorithms which would allow him to crack passwords without the need of cracking the hashing algorithm.

Therefore it is good practice to ‘salt’ passwords for which a hash is computed.

Salting is really nothing more than somehow altering the data to be hashed. Typical solutions for password salting include concatenating the username with the password, or prepending a Base64 encoded random byte array (i.e. of length 8).

To compute a hash given a password and salt:

/// <summary>
/// Computes a hash for a given password.
/// </summary>
/// <param name="password">Password to hash.</param>
/// <param name="salt">Salt to use.</param>
/// <returns>Base64 hash of the password.</returns>
private static string BuildPasswordHash(string password, string salt)
{
    return Convert.ToBase64String(new SHA1Managed().ComputeHash(Encoding.UTF8.GetBytes(salt + password)));
}

A random salt could be computed using:

/// <summary>
/// Returns a random string usable as hash salt.
/// </summary>
private static string BuildRandomSalt()
{
    var salt = new byte[8];
    new RNGCryptoServiceProvider().GetBytes(salt);
    return Convert.ToBase64String(salt);
}

Both hash and salt must be stored securely. But even together, they do not allow a hacker to rebuild the original password.

To validate a password, simply rebuild the hash with the password and the same salt as used originally, then check that both hashes are identical.

Best practices:

• Generate random salts that are long enough (64-bits, or at least as long as the hash value)
• Each password should be salted with it’s own salt. Therefore, everytime a user creates an account or changes his password, a new salt should be generated.
• The salt is not secret, there’s no problem to store it together with the password hash.

Salt & Pepper Hashing

In addition to salting passwords, passwords can also be peppered.

hash = ComputeHash( password + salt + pepper )

The salt is:

• Unique per user/password
• Stored together with the password hash

The pepper is:

• Common to all users/passwords
• Stored separately (i.e. in web.config)

Pepper adds an additional problem for hackers: since it is not stored together with password hash and salt, an SQL Injection attack or DB attack will not reveal the pepper.

Also, it will prevent a hacker from updating the database record with its own hash and salt providing him access to the system with his own chosen password, since the hash will only match if he also properly peppered his password, which is harder to do since the pepper is not stored in the database.

See also:
http://www.martinstoeckli.ch/hash/en/index.php
http://crackstation.net/hashing-security.htm

Digitally signing

Digitally signing data typically consists of calculating a hash of the data, and then use the private key of a public/private key pair to compute a signature.

Anyone can then verify the authenticity by verifying the signature using the public key of the assumed signer’s certificate.

To compute a signature of data, the following code can be used:

byte[] signature = ((RSACryptoServiceProvider)certificate.PrivateKey)
                   .SignData(bytearray or stream, CryptoConfig.MapNameToOID("SHA1"))

If a hash is already available, you can also compute a signature for that hash:

byte[] signature = ((RSACryptoServiceProvider)certificate.PrivateKey)
                   .SignHash(hash, CryptoConfig.MapNameToOID("SHA1"))

Using the .VerifyData() and .VerifyHash() methods, a signature can be validated.

Note that this only validates the signature towards the given certificate keys. Checking validity of the certificate itself is a different story, involving checking the validity timespan of the certificate, the certificate chain, trusted status of the root certificate, the signature timestamp (if any) and the certificates revocation lists.

See also:
How Certificate Revocation Works (for Windows Server 2008, Windows Vista and up)
http://technet.microsoft.com/library/ee619754.aspx
Windows XP Certificate Status and Revocation Checking
http://social.technet.microsoft.com/wiki/contents/articles/4954.windows-xp-certificate-status-and-revocation-checking.aspx

Miscellaneous helper functions

To convert a byte array to a Base64 string and back, use the following methods:

Convert.ToBase64String(bytearray)
Convert.FromBase64String(string)

To convert text into a byte array and back, use the following methods. Note that here UTF8 encoding is used, consider using other encodings if more appropriate:

Encoding.UTF8.GetBytes(string)
Encoding.UTF8.GetString(bytearray)

The following function generates a random byte array:

/// <summary>
/// Generates a random byte array.
/// </summary>
/// <param name="length">Length of the array (nr of bytes).</param>
public byte[] GenerateRandomBytes(int length)
{
    var result = new byte[length];
    new Random().NextBytes(result);
    return result;
}

However, a better implementation is to use RNGCryptoServiceProvider, the Random Number Generator, which can generate cryptographically strong random number sequences:

/// <summary>
/// Generates a random byte array.
/// </summary>
/// <param name="length">Length of the array (nr of bytes).</param>
private static byte[] GenerateRandomKey(int length)
{
    var result = new byte[length];
    new RNGCryptoServiceProvider().GetBytes(result);
    return result;
}

The following function allows entering a SecureString (sometimes used to contain passwords) on the console:

public static SecureString GetSecureStringFromConsole()
{
    SecureString password = new SecureString();
    Console.Write("Enter Password: ");
    while (true)
    {
        ConsoleKeyInfo cki = Console.ReadKey(true);
        if (cki.Key == ConsoleKey.Enter)
        {
            break;
        }
        else if (cki.Key == ConsoleKey.Escape)
        {
            password.Dispose(); return null;
        }
        else if (cki.Key == ConsoleKey.Backspace)
        {
            if (password.Length != 0) password.RemoveAt(password.Length - 1);
        }
        else
        {
            password.AppendChar(cki.KeyChar);
        }
    }
    return password;
}

References

Cryptography Simplified in Microsoft .NET
http://msdn.microsoft.com/en-us/library/aa302352.aspx
StackOverflow - Signing a file using a private key in .NET
http://stackoverflow.com/questions/720673/sign-a-file-using-a-private-key-in-net
When are self-signed certificates acceptable?
http://www.sslshopper.com/article-when-are-self-signed-certificates-acceptable.html
Understanding Digital Certificates
http://technet.microsoft.com/en-us/library/bb123848(v=exchg.65).aspx
Beginning with Digital Signatures in .NET Framework
https://www.simple-talk.com/dotnet/.net-framework/beginning-with-digital-signatures-in-.net-framework/
Support Certificates In Your Applications With The .NET Framework 2.0 (MSDN Magazine)
http://msdn.microsoft.com/en-us/magazine/cc163454.aspx
Survival guide - SSL/TLS and X.509 (SSL) Certificates (Self Signed)
http://www.zytrax.com/tech/survival/ssl.html
Support Certificates In Your Applications With The .NET Framework 2.0
http://msdn.microsoft.com/en-us/magazine/cc163454.aspx

---

CreateCodeSigningCert.cmd

@ECHO OFF
ECHO Create Self-Signed Certificate for Code Sigining
ECHO Based on: http://stackoverflow.com/questions/84847/how-do-i-create-a-self-signed-certificate-for-code-signing-on-windows
ECHO.
SET /P Subject=Certifiacte Subject:
SET /P Email=Subjects Email:
ECHO Proceed ?
PAUSE
:: Create a self-signed Certificate Authority (CA):
makecert -r -pe -n "CN=%Subject% CA" -ss CA -sr CurrentUser  -a sha256 -cy authority -sky signature -sv "%Subject%CA.pvk" "%Subject%CA.cer"
:: Import in the Windows Certifiacte Store:
certutil -user -addstore Root "%Subject%CA.cer"
:: Create a code-signing certificate (SPC):
makecert -pe -n "CN=%Subject% SPC,E=%Email%" -a sha256 -cy end -sky signature -eku 1.3.6.1.5.5.7.3.3 -ic "%Subject%CA.cer" -iv "%Subject%CA.pvk" -sv "%Subject%SPC.pvk" "%Subject%SPC.cer"
:: Transform into PFX file:
pvk2pfx -pvk "%Subject%SPC.pvk" -spc "%Subject%SPC.cer" -pfx "%Subject%SPC.pfx"
ECHO Done.