Shaun Xu

The Sheep-Pen of the Shaun


News

logo

Shaun, the author of this blog is a semi-geek, clumsy developer, passionate speaker and incapable architect with about 10 years’ experience in .NET and JavaScript. He hopes to prove that software development is art rather than manufacturing. He's into cloud computing platform and technologies (Windows Azure, Amazon and Aliyun) and right now, Shaun is being attracted by JavaScript (Angular.js and Node.js) and he likes it.

Shaun is working at Worktile Inc. as the chief architect for overall design and develop worktile, a web-based collaboration and task management tool, and lesschat, a real-time communication aggregation tool.

MVP

My Stats

  • Posts - 122
  • Comments - 576
  • Trackbacks - 0

Tag Cloud


Recent Comments


Recent Posts


Archives


Post Categories


.NET


 

This is the second post about SQL Azure Federation. In my first post I described a little bit about the theory of data partitioning, included the different between the horizontal partitioning and the vertical partitioning. I also talked about the features need to be done when we implemented the horizontal partitioning. And finally, I described some basic concept about SQL Azure Federation.

In this post, I will demonstrate how to use SQL Azure Federation in the SQL Server Management Studio (SSMS). I will also demonstrate the benefit of using SQL Azure Federation to build the multi-tenant data solution.

 

Create Federation and Tables

SQL Azure Federation is officially available for every Windows Azure data centers. There no additional register or sign up process. When we create a new server or database in SQL Azure, we can use SQL Azure Federation. For example, I had created a new database in my SQL Azure server which located in East Asia data center. Then I can open my SSMS and connect to this SQL Azure server and use SQL Azure Federation.

Do not forget to set the SQL Azure firewall before you connect to the server from local tools such as SSMS.

In this post I would like to take a very simple scenario as the example. Assuming we have a multi-tenant CRM system which contains accounts, contacts for each tenants. We also have some lookup data such as countries and titles. We also have one table contains some metadata for each tenants. So the database diagram would be like this.

Drawing1

In SQL Azure Federation, if we decided to split some tables we must have the federation ready, then create the tables that wanted to be federated based on the federation. There is no way in SQL Azure Federation to switch tables between the federated and non-federated or reference. So before we start to create the tables we need to firstly define the federation.

Since we decided to have the tenant ID as the key when splitting the database, the federation distribution should be INT type, which is the same as the type of TenantID in the tables.

In order to create the federation, in SSMS connect to the database and execute the T-SQL below.

   1: CREATE FEDERATION Tenant_Fed (TID INT RANGE)
   2: GO

There’s nothing special happened after we executed this T-SQL in SSMS. But in SQL Azure fabric, it created a database which represent our first federation member. The federation member database name was specified by SQL Azure which was a GUID with “system-” prefixed. And the original database, which we created and are connecting now, became the root database.

image

After that, all tables in our system should be in one of these three types.

Table Type Description Where
Federated Table Tables that will be split based on the federation and their federation columns. Federation Members
Reference Table Tables that will not be split, but should be referred by the federated table. These tables will be copied across all federation members. Federation Members
Centre Table Tables that will not be split, and very rarely join-select with the tables in federation members. Federation Root

For the federated table and reference table, we need to create them in a federation member, so that when we split this member, SQL Azure Federation will help us to create them in the new member. In order to connect to a federation member we need to execute the T-SQL below.

   1: USE FEDERATION Tenant_Fed (TID = 0) WITH RESET, FILTERING = OFF
   2: GO

If you are familiar with SQL Azure you should be already know that the USE keyword doesn’t work in SQL Azure. But if we specify the FEDERATION keyword and the federation name it will switch our connection to the federation member database, which contains the distribution key value we specified here (TID = 0). And if we connected to this federation member we can retrieve the database name by using the db_name() function. As you can see in my workstation my first federation member database name was “system-06d60081-6737-413e-85b1-df65cb55f1c9”.

image

Now we have been connecting to the federation member and the next step is to create the federated tables. Based on our design the Account and Contact table should be partitioning by their TenantID column so let’s create them by the following T-SQL.

   1: CREATE TABLE [dbo].[Account](
   2:     [ID] [int] NOT NULL,
   3:     [TenantID] [int] NOT NULL,
   4:     [Name] [varchar](50) NOT NULL,
   5:     [CountryID] [int] NOT NULL,
   6:  CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED 
   7: (
   8:     [ID] ASC
   9: )WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
  10: )
  11: FEDERATED ON (TID = TenantID)
  12: GO

In the T-SQL above we are going to create the Account table with fields and primary key. And at the end of the statement we defined that the TenantID column is the federated column of this table. This means, when SQL Azure Federation split the table it will look up the value of TenantID, decided which rows should be in which member.

But when we executed we will get an error that the primary key index could not be created since the index doesn’t contain the federated column.

image

This is a limitation in SQL Azure Federation. The federated column in a federated table must be the clustered index, or be in the clustered index. Since we defined the ID as the primary key of the Account table, to satisfied this limitation we must include the TenantID as the primary key as well. So our T-SQL should be like this.

   1: CREATE TABLE [dbo].[Account](
   2:     [ID] [int] NOT NULL,
   3:     [TenantID] [int] NOT NULL,
   4:     [Name] [varchar](50) NOT NULL,
   5:     [CountryID] [int] NOT NULL,
   6:  CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED 
   7: (
   8:     [ID] ASC,
   9:     [TenantID] ASC
  10: )WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
  11: )
  12: FEDERATED ON (TID = TenantID)
  13: GO

And similarly, in Contact table we also need to add the TenantID column as the primary key. We also need to amend the foreign keys as well. The T-SQL should be like this.

There is no constraint that the federation column name should be the same in each federated tables. We can specify the federation key to the different columns that has different names in each federated tables when created the tables. For example in Account table we link TID = TenantID while in Contact table we link TID = Tenant_ID or TID = T_ID, etc..

   1: CREATE TABLE [dbo].[Contact](
   2:     [ID] [int] NOT NULL,
   3:     [TenantID] [int] NOT NULL,
   4:     [AccountID] [int] NOT NULL,
   5:     [Name] [varchar](50) NOT NULL,
   6:     [TitleID] [int] NOT NULL,
   7:  CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED 
   8: (
   9:     [ID] ASC,
  10:     [TenantID] ASC
  11: )WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
  12: )
  13: FEDERATED ON (TID = [TenantID])
  14: GO
  15:  
  16: ALTER TABLE [dbo].[Account]  WITH CHECK ADD  CONSTRAINT [FK_Account_Country] FOREIGN KEY([CountryID])
  17: REFERENCES [dbo].[Country] ([ID])
  18: GO
  19: ALTER TABLE [dbo].[Account] CHECK CONSTRAINT [FK_Account_Country]
  20: GO
  21: ALTER TABLE [dbo].[Contact]  WITH CHECK ADD  CONSTRAINT [FK_Contact_Account] FOREIGN KEY([AccountID], [TenantID])
  22: REFERENCES [dbo].[Account] ([ID], [TenantID])
  23: GO
  24: ALTER TABLE [dbo].[Contact] CHECK CONSTRAINT [FK_Contact_Account]
  25: GO
  26: ALTER TABLE [dbo].[Contact]  WITH CHECK ADD  CONSTRAINT [FK_Contact_Title] FOREIGN KEY([TitleID])
  27: REFERENCES [dbo].[Title] ([ID])
  28: GO
  29: ALTER TABLE [dbo].[Contact] CHECK CONSTRAINT [FK_Contact_Title]
  30: GO

Now we have the federated tables created in our first federation member. Next let’s create the reference tables. In this case the reference tables are County and Title. They don’t have the column related with the federation distribution key, which is the tenant ID in our example. But they need to be selected alone with the federated tables. For example we might need to retrieve the account information for a tenant with the country name. So they need to be added as reference tables.

To create a reference table in a federation member would be the same as what we did on a normal database, no need to specify the federated column in the CREATE TABLE statement.

   1: CREATE TABLE [dbo].[Title](
   2:     [ID] [int] NOT NULL,
   3:     [Title] [varchar](50) NOT NULL,
   4:  CONSTRAINT [PK_Title] PRIMARY KEY CLUSTERED 
   5: (
   6:     [ID] ASC
   7: )WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
   8: )
   9: GO
  10:  
  11: CREATE TABLE [dbo].[Country](
  12:     [ID] [int] NOT NULL,
  13:     [Country] [varchar](50) NOT NULL,
  14:  CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED 
  15: (
  16:     [ID] ASC
  17: )WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
  18: )
  19: GO
  20:  
  21: ALTER TABLE [dbo].[Account]  WITH CHECK ADD  CONSTRAINT [FK_Account_Country] FOREIGN KEY([CountryID])
  22: REFERENCES [dbo].[Country] ([ID])
  23: GO
  24: ALTER TABLE [dbo].[Account] CHECK CONSTRAINT [FK_Account_Country]
  25: GO
  26:  
  27: ALTER TABLE [dbo].[Contact]  WITH CHECK ADD  CONSTRAINT [FK_Contact_Title] FOREIGN KEY([TitleID])
  28: REFERENCES [dbo].[Title] ([ID])
  29: GO
  30: ALTER TABLE [dbo].[Contact] CHECK CONSTRAINT [FK_Contact_Title]
  31: GO

And in the T-SQL above we also added the foreign keys between the reference tables and federated tables. It’s possible to add the foreign keys between the federated tables, like what we added between the Account and Contact. It’s possible to add the foreign key from the federated table to reference table, like the key between Account and Country. But it’s not allowed to add the foreign key from a non-federated table to a federated table. For example we cannot add a foreign key between the Account to a table in the federation root.

Now we have all tables and keys ready in our first federation member. Let’s add some sample data.

   1: INSERT INTO Country VALUES (1, 'China')
   2: INSERT INTO Country VALUES (2, 'US')
   3: INSERT INTO Country VALUES (3, 'UK')
   4:  
   5: INSERT INTO Title VALUES (1, 'Mr')
   6: INSERT INTO Title VALUES (2, 'Ms')
   7:  
   8: INSERT INTO Account VALUES (1, 1, 'Tenant 1 - Account 1', 1)
   9: INSERT INTO Account VALUES (2, 1, 'Tenant 1 - Account 2', 2)
  10: INSERT INTO Account VALUES (3, 2, 'Tenant 2 - Account 3', 3)
  11: INSERT INTO Account VALUES (4, 2, 'Tenant 2 - Account 4', 1)
  12: INSERT INTO Account VALUES (5, 3, 'Tenant 3 - Account 5', 2)
  13: INSERT INTO Account VALUES (6, 3, 'Tenant 3 - Account 6', 3)
  14: INSERT INTO Account VALUES (7, 4, 'Tenant 4 - Account 7', 1)
  15: INSERT INTO Account VALUES (8, 4, 'Tenant 4 - Account 8', 2)
  16: INSERT INTO Account VALUES (9, 5, 'Tenant 5 - Account 9', 3)
  17: INSERT INTO Account VALUES (10, 5, 'Tenant 5 - Account 10', 1)
  18:  
  19:  
  20: INSERT INTO Contact VALUES (1, 1, 1, 'Tenant 1 - Account 1 - Contact 1', 1)
  21: INSERT INTO Contact VALUES (2, 1, 1, 'Tenant 1 - Account 1 - Contact 2', 2)
  22: INSERT INTO Contact VALUES (3, 1, 1, 'Tenant 1 - Account 1 - Contact 3', 1)
  23: INSERT INTO Contact VALUES (4, 1, 2, 'Tenant 1 - Account 2 - Contact 4', 2)
  24: INSERT INTO Contact VALUES (5, 1, 2, 'Tenant 1 - Account 2 - Contact 5', 1)
  25: INSERT INTO Contact VALUES (6, 1, 2, 'Tenant 1 - Account 2 - Contact 6', 2)
  26: INSERT INTO Contact VALUES (7, 2, 3, 'Tenant 2 - Account 3 - Contact 7', 1)
  27: INSERT INTO Contact VALUES (8, 2, 3, 'Tenant 2 - Account 3 - Contact 8', 2)
  28: INSERT INTO Contact VALUES (9, 2, 3, 'Tenant 2 - Account 3 - Contact 9', 1)
  29: INSERT INTO Contact VALUES (10, 2, 4, 'Tenant 2 - Account 4 - Contact 10', 2)
  30: INSERT INTO Contact VALUES (11, 2, 4, 'Tenant 2 - Account 4 - Contact 11', 1)
  31: INSERT INTO Contact VALUES (12, 2, 4, 'Tenant 2 - Account 4 - Contact 12', 2)
  32: INSERT INTO Contact VALUES (13, 3, 5, 'Tenant 3 - Account 5 - Contact 13', 1)
  33: INSERT INTO Contact VALUES (14, 3, 5, 'Tenant 3 - Account 5 - Contact 14', 2)
  34: INSERT INTO Contact VALUES (15, 3, 5, 'Tenant 3 - Account 5 - Contact 15', 1)
  35: INSERT INTO Contact VALUES (16, 3, 6, 'Tenant 3 - Account 6 - Contact 16', 2)
  36: INSERT INTO Contact VALUES (17, 3, 6, 'Tenant 3 - Account 6 - Contact 17', 1)
  37: INSERT INTO Contact VALUES (18, 3, 6, 'Tenant 3 - Account 6 - Contact 18', 2)
  38: INSERT INTO Contact VALUES (19, 4, 7, 'Tenant 4 - Account 7 - Contact 19', 1)
  39: INSERT INTO Contact VALUES (20, 4, 7, 'Tenant 4 - Account 7 - Contact 20', 2)
  40: INSERT INTO Contact VALUES (21, 4, 7, 'Tenant 4 - Account 7 - Contact 21', 1)
  41: INSERT INTO Contact VALUES (22, 4, 8, 'Tenant 4 - Account 8 - Contact 22', 2)
  42: INSERT INTO Contact VALUES (23, 4, 8, 'Tenant 4 - Account 8 - Contact 23', 1)
  43: INSERT INTO Contact VALUES (24, 4, 8, 'Tenant 4 - Account 8 - Contact 24', 2)
  44: INSERT INTO Contact VALUES (25, 5, 9, 'Tenant 5 - Account 9 - Contact 25', 1)
  45: INSERT INTO Contact VALUES (26, 5, 9, 'Tenant 5 - Account 9 - Contact 26', 2)
  46: INSERT INTO Contact VALUES (27, 5, 9, 'Tenant 5 - Account 9 - Contact 27', 1)
  47: INSERT INTO Contact VALUES (28, 5, 10, 'Tenant 5 - Account 10 - Contact 28', 2)
  48: INSERT INTO Contact VALUES (29, 5, 10, 'Tenant 5 - Account 10 - Contact 29', 1)
  49: INSERT INTO Contact VALUES (30, 5, 10, 'Tenant 5 - Account 10 - Contact 30', 2)

After executed these T-SQL we had put all data in our first federation member. It contains two references tables (Country, Title) and two federated tables (Account, Contact), and some foreign keys between the reference table and federated table.

image

And we can select the data in this federation member with the tables joining, for example the T-SQL below will list all accounts and contacts information.

   1: SELECT Account.Name, Country.Country, Contact.Name, Title.Title FROM Contact
   2: LEFT JOIN Account ON AccountID = Account.ID
   3: LEFT JOIN Country ON Account.CountryID = Country.ID
   4: LEFT JOIN Title ON Contact.TitleID = Title.ID

 

Split Federation Member

The key feature of SQL Azure Federation is to split a federation member into two based on the federated value specified without any downtime. Since we have inserted some data in our first federation member, let’s split it into two.

Split a federation member is very easy. We use the ALTER FEDERATION command and specify from what value of the distribution key to split. For example, since the federation distribution key is the TID (tenant ID), we will split all federated tables based on their federated column value at 3. This means all data that the tenant ID is less than 3 will be in the federation member 1 while others will be in the federation member 2.

In order to split the federation we need to firstly connect to the federation root, and then execute the ALTER FEDERATION command specifying the boundary value.

   1: USE FEDERATION ROOT WITH RESET
   2: GO
   3:  
   4: ALTER FEDERATION Tenant_Fed SPLIT AT (TID = 3)
   5: GO

When execute this command the SQL Azure engine will perform the operations listed below.

  • First, it will configure the federation metadata information on the federation root, to indicate that the federation should be split.
  • Then it will create two databases for the new federation members. Please note that even though we have had a federation member already when executing the split command, SQL Azure Federation will NOT use this member in the future. It will create two new members.
  • SQL Azure will create the tables, keys and constraints, etc. from the current federation member to the new members.
  • It will copy the records in the reference tables to new members.
  • It will copy the records in the federated tables to new members based on the boundary value specified. Records with the federation column value less than the boundary value will be put into the low member while those equals or more than the boundary value will be put into the high member.
  • Finally, SQL Azure will re-map the connections from the original federation member to the new members and drop the original member.

After the split command finished we can connect one federation member by using the USE FEDERATION command. In SQL Azure Federation we cannot connect to a member database by specifying its database name, instead we need to tell the SQL Azure Federation the value of the distribution key we want to us, then SQL Azure Federation will rout us to that member database. For example, we split our data at tenant ID = 3, then if we want to read the data of tenant 4 we can use the T-SQL below.

   1: USE FEDERATION Tenant_Fed (TID = 4) WITH RESET, FILTERING = OFF
   2: GO

Then execute the command we had just preformed before to see the data in this federation member.

   1: SELECT Account.Name, Country.Country, Contact.Name, Title.Title
   2: FROM Contact
   3: LEFT JOIN Account ON AccountID = Account.ID
   4: LEFT JOIN Country ON Account.CountryID = Country.ID
   5: LEFT JOIN Title ON Contact.TitleID = Title.ID

And as you can see, the SSMS returned the data that the tenant ID is equal or more than 4.

image

And if we want to see the data in the first member, we can just specify the TID = 0, 1 or 2, any value less than the boundary value of that federation member.

image

Connect to Atomic Unit

When we connected to a federation member we use the USE FEDERATION command. There’s a clause in this T-SQL statement which is FILTERING = OFF. In the example above we can select all data in the federation member unless we didn’t put any criteria in WHERE clause in SELECT command. This is because we utilized FILTERING = OFF. It will make the connection scope set to the full range covered by the federation member containing the specified key value. The connection behaves the same if it were connected to the member through its physical federation member name (the database name).

SQL Azure Federation provides another valuable feature that can force the connection scope set to the federation key instance (federation key value) in a federation member rather than to the full range of federation member, by using FILTERING = ON clause.

image

So let’s have a try and to see what will happen if we set the FILTERING = ON. At this time we still specify the SSMS to use federation where the key equals to 2 but specify the FILTERING = ON, and select the data without any criteria.

   1: USE FEDERATION Tenant_Fed (TID = 2) WITH RESET, FILTERING = ON
   2: GO
   3:  
   4: SELECT Account.Name, Country.Country, Contact.Name, Title.Title
   5: FROM Contact
   6: LEFT JOIN Account ON AccountID = Account.ID
   7: LEFT JOIN Country ON Account.CountryID = Country.ID
   8: LEFT JOIN Title ON Contact.TitleID = Title.ID

Now we can see that only the records that the TenantID = 2 were returned. We didn’t provide anything in the WHERE clause in SELECT command but it worked as if we specified WHERE TenantID = 2. This is the benefit that using the FILTERING = ON in the USE FEDERATION command.

image

We mentioned in the previous post, in SQL Azure Federation all records that related with the same federation key value in a federation member is called an Atomic Unit. In the case above we connected to the federation member’s atomic unit which the value is TenantID = 2, by using the FILTERING = ON clause.

This feature is very useful especially when we need to migrate a single tenant application to multi-tenant style. As you know we might need to add the tenant identity column in the tables that tenant-award when migrating, but this might lead us to rewrite all related SQL scripts. For example if we have a SQL script that retrieves all data in table Order, it might look like this in single tenant application.

   1: SELECT Order.ID, Order.Name FROM Order

But if we implemented the multi-tenant feature we have to add the TenantID column in this table to indicate which tenant the order is. And accordingly we have to rewrite the SQL script if a participant tenant user is currently logging on.

   1: SELECT Order.ID, Order.Name FROM Order
   2: WHERE Order.TenantID = @UserTenentID

Assuming that we have 50 tables that are tenant-award and 10 scripts for each table. Then we need to amend 50 * 10 = 500 SQL scripts. And almost all changes are very similar.

But if we are using SQL Azure Federation we can make the tenant ID as the federation distribution key and let the tables split based on their TenantID column. Then if we have an user logged in under a tenant, we can invoke the USE FEDERATION statement specifying the TenantID value with FILTERING = ON before any data command, then all following SQL scripts will no need to be changed since the SQL Azure Federation helped us to filter the connection scope to this tenant. This can be done very easily by using AOP.

 

Summary

In this post I demonstrated how to use SQL Azure Federation. We talked about how to create federation, federation members and tables. And we demonstrated how to split a federation member, which is horizontal data partitioning without any downtime.

I also talked about the feature of FILTERING = ON in USE FEDERATION statement and the benefit in multi-tenant solution.

SQL Azure Federation is a very powerful tool for us to build scale-out and/or multi-tenant application. In the coming few posts I would like to discuss the metadata system of the federation, the pricing and how to split the federation members based on the data size or record count.

And I would like to introduce on how to implement our own horizontal partitioning data access layer without SQL Azure Federation available.

 

Hope this helps,

Shaun

All documents and related graphics, codes are provided "AS IS" without warranty of any kind.
Copyright © Shaun Ziyan Xu. This work is licensed under the Creative Commons License.

 

As many of you may already know that, I'm working at a global gaming and entertainment company taking the responsible for design and implement the next generation platform which will be running on the cloud, and also design the cloud platform as well. Currently one of the goal is to replace the active directory integrated security and identity solution with certificate-based solution in our product. In short, we need to work with Active Directory Certificate Service to request and issue the certificates for vary clients, so that they can use these certificates to connect our services from any devices, such as PC, smart phone, and pad, etc., securely.

Since what we need to implement first is a certificate service that can be used by any clients. This service will be talking to the Windows Active Directory Certificate Service (AD CS) through C#.

There are many articles for IT Pro on how to install and configure the AD CS, but very few on how to communicate with AD CS by C#. In this post I will describe and demonstrate how to work with the AD CS via C#. So I will not talk much about the theory of digital certificate, public key infrastructure and certificate authority, but will focus on how to use them.

 

Basic Knowledge of Certificate

Certificate, also known as public key certificate or digital certificate, is an electronic document which uses a digital signature to bind a public key with an identity. The identity could be anything. For example it could be represent a user, a device, a service or even a few lines of code.

The certificate can be used to sign the identity and could be verified by others. For example a message being signed by a certificate could be verified by the receiver, so that it will be able to know whether the message is the original one or had been modified by someone else. The certificate can also be used to encrypt and decrypt. This is the reason why we can bind a certificate on a website so that the data between the browser and server would be secured, since they are been encrypted and signed by the certificate.

The certificate authority (CA) takes the responsible to issue the certificates. In Unix people can use OpenSSL's ca command or SuSE's gensslcert to issue certificate. In Windows we can use the Active Directory Certificate Service.

In an enterprise there might be more than one CAs and normally they will be organized hierarchically. The top level would be the Root CA, which have a certificate signed by itself. All subordinate CAs’ certificate should be requested to and signed by the root CA.

image

Each CA can receive the certificate request from the client and issue them. Normally, the root CA would not be reachable by the clients since it holds the root CA certificate which is very important. Clients may send the certificate request to some subordinate CAs and get the certificate installed.

image

The certificate contains a key pair, which includes a private key and a public key. In order to make the private key secured, when requesting and installing the certificate, the private key should never be passed out of the client. Certificate request could be in PKCS #10 or CMC format, sent from the client to the CA. The subordinate CA received the request, and based on the request handling and policies, it will mark the request as pending status and let the administrator issue or deny manually, or automatically issue them. The certificate response would be in PKCS #7 format, signed by the CA certificate. Then the client will verify the response and combine it with the original private key to a full certificate.

image

So when we need to create a certificate, what we need to do is to

  • Generate the key pair and some other stuff in order to send to the CA.
  • Generate the certificate request in PKCS #10 or CMC format, and submit to CA.
  • Download the CA response.
  • Combine and install the full certificate on client based on the local key pair and the CA response.

 

In Windows Server 2008 R2, the AD CS introduced a new component named CES and CEP, which are Certificate Enrollment Service and Certificate Enrollment Policy. The client can communicate with CA through these web services. But in the prior version we have to use two COM: CertCli and CertEnroll.

CertCli component takes the responsible for connecting the CA server to submit the certificate request, certificate renew request and look for the request ID that CA server has. When connect from other machine the CertCli utilizes DCOM technology to invoke the CA functionality. This means, the CertCli cannot be used out of the domain or between the firewall.

CertEnroll component takes the responsible for generate the PKCS message, install and export the certificate. It doesn’t need to communicate to the CA directly.

Since in .NET we can wrap the COM and use it in managed code, we should be able to communicate with the CA by using them.

 

Generate Certificate Request Message to Standalone CA

There are two types of CA: enterprise CA and standalone CA. There are many differences between them. But to be simplified, the standalone CA cannot use the certificate template. In this post I will firstly demonstrate how to request the certificate to a standalone CA.

It’s only two steps to request a certificate, first one is to generate the request message, and then send the message to CA. To generate a valid certificate request message we need to use the CertEnroll COM, to send the request to need to use the CertCli COM. So let’s create a new console application and added these 2 COM components into the references.

image

To make sure the code runs successfully, it’s recommended to execute the sample code on the CA server, or at least the server on the same domain with the CA server. I will explain more about this later.

There are many information we need to specify or provide in order to build the request message. The first one is to select the valid cryptographic service provider (CSP). There are many CSPs built in the Windows. We can choose one of them, or we can just let the operation system retrieve all valid CSPs to us to use.

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Text;
   5: using CERTENROLLLib;
   6: using CERTENROLLLib;
   7:  
   8: namespace ShaunXu.ADCSviaCSharp
   9: {
  10:     class Program
  11:     {
  12:         private string CreateCertRequestMessage()
  13:         {
  14:             var objCSPs = new CCspInformations();
  15:             objCSPs.AddAvailableCsps();
  16:         }
  17:  
  18:         static void Main(string[] args)
  19:         {
  20:         }
  21:     }
  22: }

Then we will create the key pair of the certificate. In this step we need to specify information below:

  • Length: The key length of the private key. Normally the key length should NOT less than 1024 for security consideration.
  • Key Spec: Define how this key pair, and the certificate will be used. For example digital signature or key exchange.
  • Key Usage: The key usage value will be upgrade based on the Key Spec we defined.
  • Machine Context: Specify whether the certificate will be used for current user and machine.
  • Export Policy: Specify whether the private key can be exported or not from this machine.
  • CSP Information: The valid CSPs for this key pair.

When we finished to define all information listed above we can just invoke CX509PrivateKey.Create to let the operation system generate a key pair for us. It will be stored in the machine in a “magic” folder.

   1: var objPrivateKey = new CX509PrivateKey();
   2: objPrivateKey.Length = 2048;
   3: objPrivateKey.KeySpec = X509KeySpec.XCN_AT_SIGNATURE;
   4: objPrivateKey.KeyUsage = X509PrivateKeyUsageFlags.XCN_NCRYPT_ALLOW_ALL_USAGES;
   5: objPrivateKey.MachineContext = false;
   6: objPrivateKey.ExportPolicy = X509PrivateKeyExportFlags.XCN_NCRYPT_ALLOW_EXPORT_FLAG;
   7: objPrivateKey.CspInformations = objCSPs;
   8: objPrivateKey.Create();

Next step, initialize the PKCS #10 object from the private we had just created. We need to specify whether the certificate should be used for current user or machine, which must be as same as the value of Machine Context that we defined in previous step. Since we will send the request to a standalone CA we will not specify the template here.

   1: var objPkcs10 = new CX509CertificateRequestPkcs10();
   2: objPkcs10.InitializeFromPrivateKey(
   3:     X509CertificateEnrollmentContext.ContextUser,
   4:     objPrivateKey,
   5:     string.Empty);

Next, specify some extension information to the certificate. I will not deep into these extensions. Just one thing, all extensions in certificate will be defined by an identity named Object ID (OID). So if we want to add some extensions to the certificate we need to specify the OID rather than the name. For example, in the code below I added “Client Authentication” enhanced key usage extension to the certificate by specifying its OID “1.3.6.1.5.5.7.3.2”.

   1: var objExtensionKeyUsage = new CX509ExtensionKeyUsage();
   2: objExtensionKeyUsage.InitializeEncode(
   3:     CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_DIGITAL_SIGNATURE_KEY_USAGE |
   4:     CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_NON_REPUDIATION_KEY_USAGE |
   5:     CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_KEY_ENCIPHERMENT_KEY_USAGE |
   6:     CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_DATA_ENCIPHERMENT_KEY_USAGE);
   7: objPkcs10.X509Extensions.Add((CX509Extension)objExtensionKeyUsage);
   8:  
   9: var objObjectId = new CObjectId();
  10: var objObjectIds = new CObjectIds();
  11: var objX509ExtensionEnhancedKeyUsage = new CX509ExtensionEnhancedKeyUsage();
  12: objObjectId.InitializeFromValue("1.3.6.1.5.5.7.3.2");
  13: objObjectIds.Add(objObjectId);
  14: objX509ExtensionEnhancedKeyUsage.InitializeEncode(objObjectIds);
  15: objPkcs10.X509Extensions.Add((CX509Extension)objX509ExtensionEnhancedKeyUsage);

Next, we will specify the subject of the certificate. As I mentioned earlier, a certificate can represent anything. So the subject will take the information of what is being identified y this certificate. There are some fields in subject:

  • CN: Common Name
  • C: Country (Must be 2 letter.)
  • S: State
  • L: Locality
  • O: Organization
  • OU: Organization Unit
  • E: Email

We can define one or more fields when request the certificate and it will combine in the format like this.

   1: [Field_Name_1] = [Field_Value_1], [Field_Name_2] = [Field_Value_2], [Field_Name_3] = [Field_Value_3]

For example this is a valid subject with the CN, C, S, L, O and OU defined.

   1: CN = UIX, OU = NAS, O = IGT, L = Reno, S = Nevada, C = US

To specify the subject in C# we also need to provide them into the same format, and set into the PKCS #10 object.

   1: var objDN = new CX500DistinguishedName();
   2: var subjectName = "CN = shaunxu.me, OU = ADCS, O = Blog, L = Beijng, S = Beijing, C = CN";
   3: objDN.Encode(subjectName, X500NameFlags.XCN_CERT_NAME_STR_NONE);
   4: objPkcs10.Subject = objDN;

Finally we initialize the CertEnroll COM object by passing the PKCS #10 in and invoke its CreateRequest method to generate the certificate request in base64 format.

   1: var objEnroll = new CX509Enrollment();
   2: objEnroll.InitializeFromRequest(objPkcs10);
   3: var strRequest = objEnroll.CreateRequest(EncodingType.XCN_CRYPT_STRING_BASE64);

So the full code for certificate request generation would be like this.

   1: private string CreateCertRequestMessage()
   2: {
   3:     var objCSPs = new CCspInformations();
   4:     objCSPs.AddAvailableCsps();
   5:  
   6:     var objPrivateKey = new CX509PrivateKey();
   7:     objPrivateKey.Length = 2048;
   8:     objPrivateKey.KeySpec = X509KeySpec.XCN_AT_SIGNATURE;
   9:     objPrivateKey.KeyUsage = X509PrivateKeyUsageFlags.XCN_NCRYPT_ALLOW_ALL_USAGES;
  10:     objPrivateKey.MachineContext = false;
  11:     objPrivateKey.ExportPolicy = X509PrivateKeyExportFlags.XCN_NCRYPT_ALLOW_EXPORT_FLAG;
  12:     objPrivateKey.CspInformations = objCSPs;
  13:     objPrivateKey.Create();
  14:  
  15:     var objPkcs10 = new CX509CertificateRequestPkcs10();
  16:     objPkcs10.InitializeFromPrivateKey(
  17:         X509CertificateEnrollmentContext.ContextUser,
  18:         objPrivateKey,
  19:         string.Empty);
  20:  
  21:     var objExtensionKeyUsage = new CX509ExtensionKeyUsage();
  22:     objExtensionKeyUsage.InitializeEncode(
  23:         CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_DIGITAL_SIGNATURE_KEY_USAGE |
  24:         CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_NON_REPUDIATION_KEY_USAGE |
  25:         CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_KEY_ENCIPHERMENT_KEY_USAGE |
  26:         CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_DATA_ENCIPHERMENT_KEY_USAGE);
  27:     objPkcs10.X509Extensions.Add((CX509Extension)objExtensionKeyUsage);
  28:  
  29:     var objObjectId = new CObjectId();
  30:     var objObjectIds = new CObjectIds();
  31:     var objX509ExtensionEnhancedKeyUsage = new CX509ExtensionEnhancedKeyUsage();
  32:     objObjectId.InitializeFromValue("1.3.6.1.5.5.7.3.2");
  33:     objObjectIds.Add(objObjectId);
  34:     objX509ExtensionEnhancedKeyUsage.InitializeEncode(objObjectIds);
  35:     objPkcs10.X509Extensions.Add((CX509Extension)objX509ExtensionEnhancedKeyUsage);
  36:  
  37:     var objDN = new CX500DistinguishedName();
  38:     var subjectName = "CN = shaunxu.me, OU = ADCS, O = Blog, L = Beijng, S = Beijing, C = CN";
  39:     objDN.Encode(subjectName, X500NameFlags.XCN_CERT_NAME_STR_NONE);
  40:     objPkcs10.Subject = objDN;
  41:  
  42:     var objEnroll = new CX509Enrollment();
  43:     objEnroll.InitializeFromRequest(objPkcs10);
  44:     var strRequest = objEnroll.CreateRequest(EncodingType.XCN_CRYPT_STRING_BASE64);
  45:     return strRequest;
  46: }

 

Send Certificate Request to CA

Send the certificate request message we had just generated to a CA would be easy. In fact we can save the message into a text file and copy to the CA server, request the certificate by using the CA manage portal. But if we are going to use C# then we will need to use CertCli COM to send the message, and verify the status by retrieving the disposition status and request ID.

First we will create the object of CertCli and invoke its Submit method by passing the certificate request message and CA address. The CA address should be in the format:

  • [CA_SERVER_IP]\[CA_NAME]
  • [CA_SERVER_NAME]\[CA_NAME]

The CA name can be found by logging on the CA server and navigate to the Active Directory Certificate Service node in Server Manager window. Right click the CA node and select Properties.

image

The code would be like this.

   1: private static int SendCertificateRequest(string message)
   2: {
   3:     var objCertRequest = new CCertRequest();
   4:     var iDisposition = objCertRequest.Submit(
   5:             CR_IN_BASE64 | CR_IN_FORMATANY,
   6:             message,
   7:             string.Empty,
   8:             @"192.168.56.101\pal-CPAL-CA");
   9: }

The return value of the Submit method indicates the status of the certificate request, normally it would be in the statuses below.

  • 0x03: Issued. This means the certificate had been issued by the CA that we can download and install to the local machine.
  • 0x05: Under submission. This means the request was in pending status, the certificate administrator need to issue it manually.
  • Failed due to some reason. We can use CCertRequest.GetDispositionMessage method to retrieve the failure reason.
   1: switch(iDisposition)
   2: {
   3:     case CR_DISP_ISSUED:
   4:         Console.WriteLine("The certificate had been issued.");
   5:         break;
   6:     case CR_DISP_UNDER_SUBMISSION:
   7:         Console.WriteLine("The certificate is still pending.");
   8:         break;
   9:     default:
  10:         Console.WriteLine("The submission failed: " + objCertRequest.GetDispositionMessage());
  11:         Console.WriteLine("Last status: " + objCertRequest.GetLastStatus().ToString());
  12:         break;
  13: }
  14: return objCertRequest.GetRequestId();

Download and Install Certificate

Once the certificate request had been sent, it will be processed by CA request handling module. By default, for standalone CA all certificate requests will be at pending status and wait for the administrator to issue manually. The administrator should go to the CA portal and select the Pending Requests node, right click on the item and click Issue. (The administrator can click Deny if he/she don’t want to send this certificate.)

image

Then go to the Issued Certificates node we can see the issued certificate available. The certificate couldn’t be downloaded and installed into the machine where it requested unless in this status.

image

Back to the source to implement the code to download and install the full certificate. First of all, we will utilize the CCertRequest.RetrievePending method to detect the status of our certificate request had sent. If it’s issued then we will download the response, which is in PKCS #7 format, to the local machine by using the method CCertRequest.GetCertificate.

   1: private static void DownloadAndInstallCert(int requestId)
   2: {
   3:     var objCertRequest = new CCertRequest();
   4:     var iDisposition = objCertRequest.RetrievePending(requestId, @"192.168.56.101\pal-CPAL-CA");
   5:  
   6:     if (iDisposition == CR_DISP_ISSUED)
   7:     {
   8:         var cert = objCertRequest.GetCertificate(CR_OUT_BASE64 | CR_OUT_CHAIN);
   9:     }
  10: }

Then initialize the CertEnroll object from context user certificate store, install the response that we had just retrieved.

   1: var objEnroll = new CX509Enrollment();
   2: objEnroll.Initialize(X509CertificateEnrollmentContext.ContextUser);
   3: objEnroll.InstallResponse(
   4:     InstallResponseRestrictionFlags.AllowUntrustedRoot,
   5:     cert,
   6:     EncodingType.XCN_CRYPT_STRING_BASE64,
   7:     null);
   8: Console.WriteLine("The certificate had been installed successfully.");

After it downloaded and installed the certificate we can check it’s in the current user certificate store. And from now on, since the certificate was in the store, we can use X509Store and X509Certificate2 class to export and view the attributes such as subject, thumbprint, etc..

image

The full code is listed below.

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Text;
   5: using CERTENROLLLib;
   6: using CERTCLIENTLib;
   7:  
   8: namespace ShaunXu.ADCSviaCSharp
   9: {
  10:     class Program
  11:     {
  12:         private static string CreateCertRequestMessage()
  13:         {
  14:             var objCSPs = new CCspInformations();
  15:             objCSPs.AddAvailableCsps();
  16:  
  17:             var objPrivateKey = new CX509PrivateKey();
  18:             objPrivateKey.Length = 2048;
  19:             objPrivateKey.KeySpec = X509KeySpec.XCN_AT_SIGNATURE;
  20:             objPrivateKey.KeyUsage = X509PrivateKeyUsageFlags.XCN_NCRYPT_ALLOW_ALL_USAGES;
  21:             objPrivateKey.MachineContext = false;
  22:             objPrivateKey.ExportPolicy = X509PrivateKeyExportFlags.XCN_NCRYPT_ALLOW_EXPORT_FLAG;
  23:             objPrivateKey.CspInformations = objCSPs;
  24:             objPrivateKey.Create();
  25:  
  26:             var objPkcs10 = new CX509CertificateRequestPkcs10();
  27:             objPkcs10.InitializeFromPrivateKey(
  28:                 X509CertificateEnrollmentContext.ContextUser,
  29:                 objPrivateKey,
  30:                 string.Empty);
  31:  
  32:             var objExtensionKeyUsage = new CX509ExtensionKeyUsage();
  33:             objExtensionKeyUsage.InitializeEncode(
  34:                 CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_DIGITAL_SIGNATURE_KEY_USAGE |
  35:                 CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_NON_REPUDIATION_KEY_USAGE |
  36:                 CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_KEY_ENCIPHERMENT_KEY_USAGE |
  37:                 CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_DATA_ENCIPHERMENT_KEY_USAGE);
  38:             objPkcs10.X509Extensions.Add((CX509Extension)objExtensionKeyUsage);
  39:  
  40:             var objObjectId = new CObjectId();
  41:             var objObjectIds = new CObjectIds();
  42:             var objX509ExtensionEnhancedKeyUsage = new CX509ExtensionEnhancedKeyUsage();
  43:             objObjectId.InitializeFromValue("1.3.6.1.5.5.7.3.2");
  44:             objObjectIds.Add(objObjectId);
  45:             objX509ExtensionEnhancedKeyUsage.InitializeEncode(objObjectIds);
  46:             objPkcs10.X509Extensions.Add((CX509Extension)objX509ExtensionEnhancedKeyUsage);
  47:  
  48:             var objDN = new CX500DistinguishedName();
  49:             var subjectName = "CN = shaunxu.me, OU = ADCS, O = Blog, L = Beijng, S = Beijing, C = CN";
  50:             objDN.Encode(subjectName, X500NameFlags.XCN_CERT_NAME_STR_NONE);
  51:             objPkcs10.Subject = objDN;
  52:  
  53:             var objEnroll = new CX509Enrollment();
  54:             objEnroll.InitializeFromRequest(objPkcs10);
  55:             var strRequest = objEnroll.CreateRequest(EncodingType.XCN_CRYPT_STRING_BASE64);
  56:             return strRequest;
  57:         }
  58:  
  59:         private const int CC_DEFAULTCONFIG = 0;
  60:         private const int CC_UIPICKCONFIG = 0x1;
  61:         private const int CR_IN_BASE64 = 0x1;
  62:         private const int CR_IN_FORMATANY = 0;
  63:         private const int CR_IN_PKCS10 = 0x100;
  64:         private const int CR_DISP_ISSUED = 0x3;
  65:         private const int CR_DISP_UNDER_SUBMISSION = 0x5;
  66:         private const int CR_OUT_BASE64 = 0x1;
  67:         private const int CR_OUT_CHAIN = 0x100;
  68:  
  69:         private static int SendCertificateRequest(string message)
  70:         {
  71:             var objCertRequest = new CCertRequest();
  72:             var iDisposition = objCertRequest.Submit(
  73:                     CR_IN_BASE64 | CR_IN_FORMATANY,
  74:                     message,
  75:                     string.Empty,
  76:                     @"192.168.56.101\pal-CPAL-CA");
  77:  
  78:             switch(iDisposition)
  79:             {
  80:                 case CR_DISP_ISSUED:
  81:                     Console.WriteLine("The certificate had been issued.");
  82:                     break;
  83:                 case CR_DISP_UNDER_SUBMISSION:
  84:                     Console.WriteLine("The certificate is still pending.");
  85:                     break;
  86:                 default:
  87:                     Console.WriteLine("The submission failed: " + objCertRequest.GetDispositionMessage());
  88:                     Console.WriteLine("Last status: " + objCertRequest.GetLastStatus().ToString());
  89:                     break;
  90:             }
  91:             return objCertRequest.GetRequestId();
  92:         }
  93:  
  94:         private static void DownloadAndInstallCert(int requestId)
  95:         {
  96:             var objCertRequest = new CCertRequest();
  97:             var iDisposition = objCertRequest.RetrievePending(requestId, @"192.168.56.101\pal-CPAL-CA");
  98:  
  99:             if (iDisposition == CR_DISP_ISSUED)
 100:             {
 101:                 var cert = objCertRequest.GetCertificate(CR_OUT_BASE64 | CR_OUT_CHAIN);
 102:                 var objEnroll = new CX509Enrollment();
 103:                 objEnroll.Initialize(X509CertificateEnrollmentContext.ContextUser);
 104:                 objEnroll.InstallResponse(
 105:                     InstallResponseRestrictionFlags.AllowUntrustedRoot,
 106:                     cert,
 107:                     EncodingType.XCN_CRYPT_STRING_BASE64,
 108:                     null);
 109:                 Console.WriteLine("The certificate had been installed successfully.");
 110:             }
 111:         }
 112:  
 113:         static void Main(string[] args)
 114:         {
 115:             Console.WriteLine("Request a new certificate? (y|n)");
 116:             if (Console.ReadLine() == "y")
 117:             {
 118:                 var request = CreateCertRequestMessage();
 119:                 var id = SendCertificateRequest(request);
 120:                 Console.WriteLine("Request ID: " + id.ToString());
 121:             }
 122:  
 123:             Console.WriteLine("Download & install certificate? (y|n)");
 124:             if (Console.ReadLine() == "y")
 125:             {
 126:                 Console.WriteLine("Request ID?");
 127:                 var id = int.Parse(Console.ReadLine());
 128:                 DownloadAndInstallCert(id);
 129:             }            
 130:         }
 131:     }
 132: }

 

Enterprise CA and Certificate Template

In the section above we discussed on how to use C# to communicate with AD CS, that is a standalone CA. A standalone CA has some limitation comparing with the enterprise CA. The biggest difference is that, the standalone CA cannot use the certificate templates.

When we implement the certificate request function, we specified everything the certificate needs. And for a CA there’s no way to define what kind of information can be set by request, what policy should the request follow. And there’s no way to define how long the certificate will be valid, which is the validity period, as well. All certificates issued by a standalone CA will have the same validity period, which is defined at the register in CA server. But if we are using enterprise CA, we can define vary rules and validity period in each template.

The enterprise templates are stored in the active directory, which means all CAs in the AD can select which templates they can use. This is a good way to control the certificate issuing permission.

You can verify if a CA is enterprise or not by opening the CA portal. If there’s a sub folder named Certificate Templates it means this is an enterprise CA.

image

Let’s create a template and specify some rules. Click the Certificate Templates node which under the Active Directory Certificate Service node and select a template named Computer. Right click the template and click Duplicate Template.

You can not create a brand new template. Instead you have to duplicate an existing template.

image

Select Windows Server 2008 Enterprise version on the popping up windows and specify a template name. In the template properties window we can see that it’s possible to define the validity period of it. All certificates that requested and issued on this template will have the same validity period.

image

And there are many items we can define as well. For example we can have the “Client Authenticate” in the application policies extension, which we had specified in code in previous sample.

image

And we can define what kind of value can be set to the subject in certificates. This provides a good way for certificate administrator to control the value of the certificates. For example, if the certificate is to represent a domain user, the subject must be a valid AD user. But in this case we will let the request supply the subject which means no control on CA side.

image

Once we created the template we also need to issue this template to this CA server, which means it can be received and issued by this CA. Right click the Certificate Templates node and select New > Certificate Template to Issue, and select the template we have just created.

image

Now the template is ready for use. Then we will change our code to send request to enterprise CA with template specified.

 

Send Request to Enterprise CA with Template

Send the certificate request to an enterprise CA would be very similar as what we did on a standalone CA. Previously when we generated the key pair we used an empty string on the template name parameter. So now for enterprise CA we will specify which template we are going to use.

   1: var objPkcs10 = new CX509CertificateRequestPkcs10();
   2: objPkcs10.InitializeFromPrivateKey(
   3:     X509CertificateEnrollmentContext.ContextUser,
   4:     objPrivateKey,
   5:     "ShaunXu");

It’s not allowed to request a certificate without template specified on an enterprise CA. This means we have to set a template. On the other hand, standalone CA does not allow the request related with a template.

Seems that we finished, but if we just execute it will throw an exception to us, said that the file exists when adding some extensions.

image

The exception message could be a little bit confusing. In fact this is because we defined something which had been defined in the certificate template. If we dig into the source code we can see that the exception occurred when we added the key usage extension.

image

And if we get back to the CA server and open the template we are using, we can find that the key usage had been defined in the template. This means in the code, or in the certificate request we should not specify it again.

image

Hence we need to comment the code for adding the key usage, also we need to comment the enhanced key usage part since it had been defined in the template, too. Because we let the request supply the subject name so here we can still specify the subject information in the request. The method for generating request message would be like this.

   1: private static string CreateCertRequestMessage()
   2: {
   3:     var objCSPs = new CCspInformations();
   4:     objCSPs.AddAvailableCsps();
   5:  
   6:     var objPrivateKey = new CX509PrivateKey();
   7:     objPrivateKey.Length = 2048;
   8:     objPrivateKey.KeySpec = X509KeySpec.XCN_AT_SIGNATURE;
   9:     objPrivateKey.KeyUsage = X509PrivateKeyUsageFlags.XCN_NCRYPT_ALLOW_ALL_USAGES;
  10:     objPrivateKey.MachineContext = false;
  11:     objPrivateKey.ExportPolicy = X509PrivateKeyExportFlags.XCN_NCRYPT_ALLOW_EXPORT_FLAG;
  12:     objPrivateKey.CspInformations = objCSPs;
  13:     objPrivateKey.Create();
  14:  
  15:     var objPkcs10 = new CX509CertificateRequestPkcs10();
  16:     objPkcs10.InitializeFromPrivateKey(
  17:         X509CertificateEnrollmentContext.ContextUser,
  18:         objPrivateKey,
  19:         "ShaunXu");
  20:  
  21:     //var objExtensionKeyUsage = new CX509ExtensionKeyUsage();
  22:     //objExtensionKeyUsage.InitializeEncode(
  23:     //    CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_DIGITAL_SIGNATURE_KEY_USAGE |
  24:     //    CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_NON_REPUDIATION_KEY_USAGE |
  25:     //    CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_KEY_ENCIPHERMENT_KEY_USAGE |
  26:     //    CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_DATA_ENCIPHERMENT_KEY_USAGE);
  27:     //objPkcs10.X509Extensions.Add((CX509Extension)objExtensionKeyUsage);
  28:  
  29:     //var objObjectId = new CObjectId();
  30:     //var objObjectIds = new CObjectIds();
  31:     //var objX509ExtensionEnhancedKeyUsage = new CX509ExtensionEnhancedKeyUsage();
  32:     //objObjectId.InitializeFromValue("1.3.6.1.5.5.7.3.2");
  33:     //objObjectIds.Add(objObjectId);
  34:     //objX509ExtensionEnhancedKeyUsage.InitializeEncode(objObjectIds);
  35:     //objPkcs10.X509Extensions.Add((CX509Extension)objX509ExtensionEnhancedKeyUsage);
  36:  
  37:     var objDN = new CX500DistinguishedName();
  38:     var subjectName = "CN = entprise.shaunxu.me, OU = ADCS, O = Blog, L = Beijng, S = Beijing, C = CN";
  39:     objDN.Encode(subjectName, X500NameFlags.XCN_CERT_NAME_STR_NONE);
  40:     objPkcs10.Subject = objDN;
  41:  
  42:     var objEnroll = new CX509Enrollment();
  43:     objEnroll.InitializeFromRequest(objPkcs10);
  44:     var strRequest = objEnroll.CreateRequest(EncodingType.XCN_CRYPT_STRING_BASE64);
  45:     return strRequest;
  46: }

It works well this time and installed the certificate successfully. If we open the certificate store in MMC we can see the new one with the template displayed.

image

 

Certificate Renewal

A certificate must have a limited validity period. For example the certificate we had just request before is valid through 2012-01-13 07:21:48 to 20124-01-12 07:21:48.

image

When the certificate is going to be expired the operation system will send the renew request to the CA server automatically to attempt renew it. But we can ask to renew it by our code.

To send a certificate renewal message we must have this certificate installed in the certificate store. It could be in local machine or current user store. The first step is to find it by using the X509Store.Certificates.Find.

   1: private static int Renew()
   2: {
   3:     X509Certificate2 certificate = null;
   4:     X509Store store = new X509Store(StoreLocation.CurrentUser);
   5:     try
   6:     {
   7:         store.Open(OpenFlags.ReadWrite);
   8:         certificate = store.Certificates.Find(X509FindType.FindByThumbprint, "c1555218deed2c6dbe5101178617ef7628388a85", false)[0];
   9:     }
  10:     catch (Exception ex)
  11:     {
  12:         Console.WriteLine(ex.ToString());
  13:     }
  14:     finally
  15:     {
  16:         store.Close();
  17:     }
  18: }

The certificate renew request is in PKCS #7 format. So in the next step, we will create an object of PKCS #7 and initialize it from the certificate we had just found from the certificate store. When initializing we’d specify that this is a renew request in the parameter. We also need to specify that the new certificate will inherit the validity period and the key pair from the existing one.

   1: var objPkcs7 = new CX509CertificateRequestPkcs7();
   2: objPkcs7.InitializeFromCertificate(
   3:     X509CertificateEnrollmentContext.ContextUser,
   4:     true,
   5:     Convert.ToBase64String(certificate.RawData),
   6:     EncodingType.XCN_CRYPT_STRING_BASE64,
   7:     X509RequestInheritOptions.InheritPrivateKey & X509RequestInheritOptions.InheritValidityPeriodFlag);

Then the following code would be very similar with what we did to send the new request before. Using the CertEnroll to generate the request message and send it out by CertCli, and check the disposition status.

   1: var objEnroll = new CX509Enrollment();
   2: objEnroll.InitializeFromRequest(objPkcs7);
   3: var message = objEnroll.CreateRequest(EncodingType.XCN_CRYPT_STRING_BASE64);
   4:  
   5: var objCertRequest = new CCertRequest();
   6: var iDisposition = objCertRequest.Submit(
   7:         CR_IN_BASE64 | CR_IN_FORMATANY,
   8:         message,
   9:         string.Empty,
  10:         @"192.168.56.101\pal-CPAL-CA");
  11:  
  12: switch (iDisposition)
  13: {
  14:     case CR_DISP_ISSUED:
  15:         Console.WriteLine("The certificate had been issued.");
  16:         break;
  17:     case CR_DISP_UNDER_SUBMISSION:
  18:         Console.WriteLine("The certificate is still pending.");
  19:         break;
  20:     default:
  21:         Console.WriteLine("The submission failed: " + objCertRequest.GetDispositionMessage());
  22:         Console.WriteLine("Last status: " + objCertRequest.GetLastStatus().ToString());
  23:         break;
  24: }
  25: return objCertRequest.GetRequestId();

When the request had been sent to the CA, based on the request handling policy it will be issued automatically or manually by the administrator. To download and install the renewed certificate would be the same like what we did before, so just use the method that download the new certificate should be fine.

The full code would be like this. Just note that I hard-coded my certificate thumbprint in the code.

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Text;
   5: using CERTENROLLLib;
   6: using CERTCLIENTLib;
   7: using System.Security.Cryptography.X509Certificates;
   8:  
   9: namespace ShaunXu.ADCSviaCSharp
  10: {
  11:     class Program
  12:     {
  13:         private static string CreateCertRequestMessage()
  14:         {
  15:             var objCSPs = new CCspInformations();
  16:             objCSPs.AddAvailableCsps();
  17:  
  18:             var objPrivateKey = new CX509PrivateKey();
  19:             objPrivateKey.Length = 2048;
  20:             objPrivateKey.KeySpec = X509KeySpec.XCN_AT_SIGNATURE;
  21:             objPrivateKey.KeyUsage = X509PrivateKeyUsageFlags.XCN_NCRYPT_ALLOW_ALL_USAGES;
  22:             objPrivateKey.MachineContext = false;
  23:             objPrivateKey.ExportPolicy = X509PrivateKeyExportFlags.XCN_NCRYPT_ALLOW_EXPORT_FLAG;
  24:             objPrivateKey.CspInformations = objCSPs;
  25:             objPrivateKey.Create();
  26:  
  27:             var objPkcs10 = new CX509CertificateRequestPkcs10();
  28:             objPkcs10.InitializeFromPrivateKey(
  29:                 X509CertificateEnrollmentContext.ContextUser,
  30:                 objPrivateKey,
  31:                 "ShaunXu");
  32:  
  33:             //var objExtensionKeyUsage = new CX509ExtensionKeyUsage();
  34:             //objExtensionKeyUsage.InitializeEncode(
  35:             //    CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_DIGITAL_SIGNATURE_KEY_USAGE |
  36:             //    CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_NON_REPUDIATION_KEY_USAGE |
  37:             //    CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_KEY_ENCIPHERMENT_KEY_USAGE |
  38:             //    CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_DATA_ENCIPHERMENT_KEY_USAGE);
  39:             //objPkcs10.X509Extensions.Add((CX509Extension)objExtensionKeyUsage);
  40:  
  41:             //var objObjectId = new CObjectId();
  42:             //var objObjectIds = new CObjectIds();
  43:             //var objX509ExtensionEnhancedKeyUsage = new CX509ExtensionEnhancedKeyUsage();
  44:             //objObjectId.InitializeFromValue("1.3.6.1.5.5.7.3.2");
  45:             //objObjectIds.Add(objObjectId);
  46:             //objX509ExtensionEnhancedKeyUsage.InitializeEncode(objObjectIds);
  47:             //objPkcs10.X509Extensions.Add((CX509Extension)objX509ExtensionEnhancedKeyUsage);
  48:  
  49:             var objDN = new CX500DistinguishedName();
  50:             var subjectName = "CN = entprise.shaunxu.me, OU = ADCS, O = Blog, L = Beijng, S = Beijing, C = CN";
  51:             objDN.Encode(subjectName, X500NameFlags.XCN_CERT_NAME_STR_NONE);
  52:             objPkcs10.Subject = objDN;
  53:  
  54:             var objEnroll = new CX509Enrollment();
  55:             objEnroll.InitializeFromRequest(objPkcs10);
  56:             var strRequest = objEnroll.CreateRequest(EncodingType.XCN_CRYPT_STRING_BASE64);
  57:             return strRequest;
  58:         }
  59:  
  60:         private const int CC_DEFAULTCONFIG = 0;
  61:         private const int CC_UIPICKCONFIG = 0x1;
  62:         private const int CR_IN_BASE64 = 0x1;
  63:         private const int CR_IN_FORMATANY = 0;
  64:         private const int CR_IN_PKCS10 = 0x100;
  65:         private const int CR_DISP_ISSUED = 0x3;
  66:         private const int CR_DISP_UNDER_SUBMISSION = 0x5;
  67:         private const int CR_OUT_BASE64 = 0x1;
  68:         private const int CR_OUT_CHAIN = 0x100;
  69:  
  70:         private static int SendCertificateRequest(string message)
  71:         {
  72:             var objCertRequest = new CCertRequest();
  73:             var iDisposition = objCertRequest.Submit(
  74:                     CR_IN_BASE64 | CR_IN_FORMATANY,
  75:                     message,
  76:                     string.Empty,
  77:                     @"192.168.56.101\pal-CPAL-CA");
  78:  
  79:             switch(iDisposition)
  80:             {
  81:                 case CR_DISP_ISSUED:
  82:                     Console.WriteLine("The certificate had been issued.");
  83:                     break;
  84:                 case CR_DISP_UNDER_SUBMISSION:
  85:                     Console.WriteLine("The certificate is still pending.");
  86:                     break;
  87:                 default:
  88:                     Console.WriteLine("The submission failed: " + objCertRequest.GetDispositionMessage());
  89:                     Console.WriteLine("Last status: " + objCertRequest.GetLastStatus().ToString());
  90:                     break;
  91:             }
  92:             return objCertRequest.GetRequestId();
  93:         }
  94:  
  95:         private static void DownloadAndInstallCert(int requestId)
  96:         {
  97:             var objCertRequest = new CCertRequest();
  98:             var iDisposition = objCertRequest.RetrievePending(requestId, @"192.168.56.101\pal-CPAL-CA");
  99:  
 100:             if (iDisposition == CR_DISP_ISSUED)
 101:             {
 102:                 var cert = objCertRequest.GetCertificate(CR_OUT_BASE64 | CR_OUT_CHAIN);
 103:                 var objEnroll = new CX509Enrollment();
 104:                 objEnroll.Initialize(X509CertificateEnrollmentContext.ContextUser);
 105:                 objEnroll.InstallResponse(
 106:                     InstallResponseRestrictionFlags.AllowUntrustedRoot,
 107:                     cert,
 108:                     EncodingType.XCN_CRYPT_STRING_BASE64,
 109:                     null);
 110:                 Console.WriteLine("The certificate had been installed successfully.");
 111:             }
 112:         }
 113:  
 114:         private static int Renew()
 115:         {
 116:             X509Certificate2 certificate = null;
 117:             X509Store store = new X509Store(StoreLocation.CurrentUser);
 118:             try
 119:             {
 120:                 store.Open(OpenFlags.ReadWrite);
 121:                 certificate = store.Certificates.Find(X509FindType.FindByThumbprint, "c1555218deed2c6dbe5101178617ef7628388a85", false)[0];
 122:             }
 123:             catch (Exception ex)
 124:             {
 125:                 Console.WriteLine(ex.ToString());
 126:             }
 127:             finally
 128:             {
 129:                 store.Close();
 130:             }
 131:  
 132:             var objPkcs7 = new CX509CertificateRequestPkcs7();
 133:             objPkcs7.InitializeFromCertificate(
 134:                 X509CertificateEnrollmentContext.ContextUser,
 135:                 true,
 136:                 Convert.ToBase64String(certificate.RawData),
 137:                 EncodingType.XCN_CRYPT_STRING_BASE64,
 138:                 X509RequestInheritOptions.InheritPrivateKey & X509RequestInheritOptions.InheritValidityPeriodFlag);
 139:  
 140:             var objEnroll = new CX509Enrollment();
 141:             objEnroll.InitializeFromRequest(objPkcs7);
 142:             var message = objEnroll.CreateRequest(EncodingType.XCN_CRYPT_STRING_BASE64);
 143:  
 144:             var objCertRequest = new CCertRequest();
 145:             var iDisposition = objCertRequest.Submit(
 146:                     CR_IN_BASE64 | CR_IN_FORMATANY,
 147:                     message,
 148:                     string.Empty,
 149:                     @"192.168.56.101\pal-CPAL-CA");
 150:  
 151:             switch (iDisposition)
 152:             {
 153:                 case CR_DISP_ISSUED:
 154:                     Console.WriteLine("The certificate had been issued.");
 155:                     break;
 156:                 case CR_DISP_UNDER_SUBMISSION:
 157:                     Console.WriteLine("The certificate is still pending.");
 158:                     break;
 159:                 default:
 160:                     Console.WriteLine("The submission failed: " + objCertRequest.GetDispositionMessage());
 161:                     Console.WriteLine("Last status: " + objCertRequest.GetLastStatus().ToString());
 162:                     break;
 163:             }
 164:             return objCertRequest.GetRequestId();
 165:         }
 166:  
 167:         static void Main(string[] args)
 168:         {
 169:             Console.WriteLine("Request a new certificate? (y|n)");
 170:             if (Console.ReadLine() == "y")
 171:             {
 172:                 var request = CreateCertRequestMessage();
 173:                 var id = SendCertificateRequest(request);
 174:                 Console.WriteLine("Request ID: " + id.ToString());
 175:             }
 176:  
 177:             Console.WriteLine("Download & install certificate? (y|n)");
 178:             if (Console.ReadLine() == "y")
 179:             {
 180:                 Console.WriteLine("Request ID?");
 181:                 var id = int.Parse(Console.ReadLine());
 182:                 DownloadAndInstallCert(id);
 183:             }
 184:  
 185:             Console.WriteLine("Renew an existing certificate? (y|n)");
 186:             if (Console.ReadLine() == "y")
 187:             {
 188:                 var id = Renew();
 189:                 Console.WriteLine("Request ID: " + id.ToString());
 190:             }
 191:  
 192:             Console.WriteLine("Download & install renewed certificate? (y|n)");
 193:             if (Console.ReadLine() == "y")
 194:             {
 195:                 Console.WriteLine("Request ID?");
 196:                 var id = int.Parse(Console.ReadLine());
 197:                 DownloadAndInstallCert(id);
 198:             }
 199:         }
 200:     }
 201: }

After executed and back to the certificate store we can see the certificate renewed by the CA, which its validity period had been changed from 2012-01-13 07:21:48 - 20124-01-12 7:21:48 to 2012-01-13 07:52:36 - 20124-01-12 07:52:36. The old certificate had been archived by the operation system automatically.

image

 

Request Certificate Out of Domain

As I mentioned before, the sample code in this post should be executed in the same server of CA, or at least a server in the CA’s domain. This is the limitation when using CertCli and CertEnroll to communication with CA.

First of all, CA integrated with active directory. By default, only the authenticated user can request certificate. Secondly, if we are using enterprise CA, all templates are being stored in the AD. When the client request a new certificate with template specified, it will try to retrieve the template information from AD.

image

So before Windows Server 2008 R2 it would be very difficult to communicate to the CA from the client that out of the domain. This is why, in the beginning of this post I mentioned, that I’m working on a WCF web service working as a proxy to let the client (PC, laptop and mobile) connect and request certificates out side from the domain.

image

But if we have the Windows Server 2008 R2, it introduced a new component of AD CS which called Certificate Enrollment Web Services. Basically it includes two web services that wraps the LDAP invoke and DCOM invoke, so that the client can communicate with them through HTTPS with WS-Trust.

image

 

Summary

When I was beginning to work on this task I found there is very little information on the internet about how to communicate with the CA by C#, or even by code. I think this is because, CA is something related with IT Pro that more focused on how to install and configure. IT Pro doesn’t care about the code. Communicating from C# is more related with development but developer doesn’t care about the CA since it’s something about IT infrastructure. So this topic is in the middle of the two worlds - IT and development.

But I think when we move to the cloud computing, the enterprise application, most of them we need to migration the existing AD integrated architecture to certificate-based architecture, which need to replace the existing security, authentication, identification parts.

In this post I introduced a little bit background knowledge about CA, especially the AD CS. I demonstrated how to request, install and renew a certificate to a standalone and an enterprise CA by C# through COM. I also mentioned a little bit about the new Certificate Enrollment Web Service. Thanks to the great post and articles I  referred recently, this and this.

There are still some topics I didn’t cover. For example the online revocation list, SCEP, OCSP, etc.. We need them if we need to build a fully, robust, online certificate solution.

 

Hope this helps,

Shaun

All documents and related graphics, codes are provided "AS IS" without warranty of any kind.
Copyright © Shaun Ziyan Xu. This work is licensed under the Creative Commons License.

 

The SQL Azure Federation had been publically launched several weeks ago and this is one of the most existing features I’m looking forward. This might be the first post of SQL Azure Federation, and hopefully not the last one.

 

Some Backgrounds

SQL Azure Federation was mentioned in about 2009. The Microsoft told that there will be a feature in SQL Azure allow users to split one database into many based on some sort of rules But from the client side perspective, user can interact their data as if in one database. This feature was called SQL Azure Federation. At the TechED China 2010, when I talked with Zach, a technical evangelist at Microsoft focus on Windows Azure platform when the SQL Azure Federation would be available, or even though CTP for MVPs or partners. But at that moment Zach said he still don’t have a chance to take a look at it. I remembered it was Dec, 2010.

But the good news came on June 2011, Microsoft had opened the nomination of the PE Program of SQL Azure Federation. And I was very lucky being approved to use this feature and provide some early feedback to Microsoft. During the PE program I had attended several online meetings and have the chance to play with it in some of my projects. Cihan Biyikoglu, the program manager in SQL Azure team and his group gave me a lot of information and suggestion on how the SQL Azure Federation works and how to use it in the best approach. During the PE program, the Microsoft said that SQL Azure Federation will be available at the end of 2011.

In 12th, Dec 2011 the SQL Azure Federation was launched with the SQL Azure Q4 2011 Service Release, with some other cool features, which you can have a reference here.

 

What’s (Data) Federation

Federation is not a new concept or technology in computer science. But it could be meaning differently in different context, such as WS-Federation, Active Directory Federation Services, etc. But in this, and the following blog posts about SQL Azure Federation, the word “Federation” I mentioned would be focus on the Data Federation.

Federation, as known as the data shard or partitioning, is a database design principle whereby rows of a database table are held separately. It also be known as horizontal partitioning. Assuming that we have a database and a table named Product which contains the product records, and now there’s 10,000 records there. After a while the number of the records raised to 10,000,000. If they are all in the same database it might cause performance problem. In this case, there are two solutions we can choose. One is to increase the hardware of the database server. For example, more CPU cores, more memory and higher network bandwidth. This approach we called Scale Up. But there will be a limitation in this way, we can not add cores, memory and much as we want.

image

Another one is to split the database across to multiple databases and servers. Let say we divide the database and Product table in 10 databases (servers), so in each database there will be only 1,000,000 records in Product table. We split the data volume across the multiple servers, as well as split the network load, CPU and memory usage. Furthermore, since we can have as many servers as we need, there will be no limitation to extend our system in this approach. This is called Scale Out.

image

SQL Azure Federation implemented this approach, which helps us to split one database into many that we called federation members, to increase the performance.

 

Horizontal Partitioning and Vertical Partitioning

Let’s deep into the tables in the databases to be federated. If a table was too big that introduced some performance issues, like the Product table I mentioned previously which has 10,000,000 records, we need to split them across to the databases. There are also two approaches we have, the horizontal partitioning and vertical partitioning.

Horizontal partitioning, likes you use a knife to cut the table horizontally, which means split the table by rows. The tables after the partitioning would be:

  • Have the exactly same schema.
  • One database command on a table would be the same on any other tables.
  • Each record represent the full information.
  • Can retrieve all information within one query.
  • Need to touch all databases (partitioned tables) and aggressive process when fan-out query, like SUM, AVG, COUNT, etc..

image

Vertical partitioning means split the table by columns. The table after the partitioning would be:

  • Each table would be in different schema.
  • Query on each tables would be different. And may introduce some data redundant.
  • Each record in a table just represent partial information.
  • Easy to implement COD (Cost Oriented Design) by moving the columns in cheaper storage. For example moving the binary columns into Windows Azure Blob Storage.
  • Need multiple queries when retrieve some information.
  • Fan-out query normally can be finished within one query.

image

SQL Azure Federation utilize the horizontal partitioning to split the tables in multiple databases. But it’s not that simple as I mentioned above. When using horizontal partitioning, we need to firstly define the rule on how to divide the tables. In the picture above, it indicates that the table will be divided by ID, all records that ID less than 4 would into one database, and others (larger than 3) will be in another.

But if we have some tables related, for example UserOrder table which have UserID as well, we need to split that table by the same rule, to make sure that all records in the tables that referred to the same UserID must be in the same partition. This will make the JOIN query quick and easy.

image

There are also some tables that doesn’t related with the ID in this example, for instance the countries, cities, etc.. When we partitioning the database, these tables should not be split and need to be copied to each databases.

image

The last thing is that, there might be some tables that represent the global information, like the system settings, metadata and schema data. They should not be split and should not be copied into the databases. So they will be remained in the original database we can call it root database.

image

Now we have a fully implementation on the horizontal partitioning. We have the rule on how the data should be split. We ensure that all related records will be stored in the same database node and the lookup tables will copied across them. We also have the root database with tables that have the global information stored. I can tell you that this is what SQL Azure Federation does for us, automatically and safely.

 

SQL Azure Federation Concepts

SQL Azure Federation introduces some new concepts around the data partitioning. They are federation, federation distribution, federation member, root database, federation column, federated table, reference table, center table and atomic unit.

Federation is the rule on how to partition our data. There can be more than one federations in one system. But on a particular table there has to be only one federation apply. This means, for example we have a table with columns UserID and ProductID. We can apply a federation that split the table by UserID, or by ProductID, but we cannot apply both of them on the same time.

A federation includes:

  • Federation Name: The name of the federation which can be used when alter or connect.
  • Federation Distribution Name: The identity name that to split the tables in this federation. For example if we want to split the tables based on the UserID then we can name the federation distribution name as “userId”, “uid” or whatever.
  • Federation Distribution Data Type: The data type that the federation distribution name is. Currently the SQL Azure Federation only support int, bigint, uniqueidentifier and varbinary(n).
  • Distribution Type: How SQL Azure Federation will split the data. There are many ways to split the data such as mod, consistent hashing but currently SQL Azure Federation only support “range”.

After we split database into many, based on the federation we specified, the small databases called Federation Member. The original database, may contains some metadata tables would be called Root Database or Federation Root. The tables that is being split into the federation members are Federated Table.

image

The tables that represent the lookup data can be copied to each federation members automatically by SQL Azure Federation, which is called Reference Table. The remaining tables in the federation root would be Center Tables.

As we discussed below, when horizontal partitioning the tables that related with the same split key (here is the federation distribution name) should be put into the same databases (federation members). For example if we move the record in Product table into federation member 1 if UserID = 3, then all records that UserID = 3 in the table ProductDetails should be moved in federation member 1 as well. In SQL Azure Federation, the group of the records that related to the same federation distribution value called Atomic Unit. This is very important and useful when using SQL Azure Federation which I will explain in the coming few posts.

image

Summary

In this post I covered some basic information about the data federation. I talked about the approaches that we can use to partitioning our data. I also described the different between horizontal partitioning and the the vertical partitioning, and the goal of horizontal partitioning. Finally I talked about the concept of SQL Azure Federation.

In the next post I will demonstrate how to create a database and use SQL Azure Federation, to split my original database into members.

 

Hope this helps,

Shaun

All documents and related graphics, codes are provided "AS IS" without warranty of any kind.
Copyright © Shaun Ziyan Xu. This work is licensed under the Creative Commons License.