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.
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.
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”.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.)
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.
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..
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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..
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.
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.
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.
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.
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.
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.
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.
Microsoft has just announced the Windows Azure SDK 1.6 and the Windows Azure Tools for Visual Studio 1.6. Now people can download the latest product through the WebPI.
After you downloaded and installed the SDK you will find that
- The SDK 1.6 can be stayed side by side with the SDK 1.5, which means you can still using the 1.5 assemblies. But the Visual Studio Tools would be upgraded to 1.6.
- Different from the previous SDK, in this version it includes 4 components: Windows Azure Authoring Tools, Windows Azure Emulators, Windows Azure Libraries for .NET 1.6 and the Windows Azure Tools for Microsoft Visual Studio 2010.
There are some significant upgrades in this version, which are
- Publishing Enhancement: More easily connect to the Windows Azure when publish your application by retrieving a publish setting file. It will let you configure some settings of the deployment, without getting back to the developer portal.
- Multi-profiles: The publish settings, cloud configuration files, etc. will be stored in one or more MSBuild files. It will be much easier to switch the settings between vary build environments.
- MSBuild Command-line Build Support.
- In-Place Upgrade Support.
Publishing Enhancement
So let’s have a look about the new features of the publishing. Just create a new Windows Azure project in Visual Studio 2010 with a MVC 3 Web Role, and right-click the Windows Azure project node in the solution explorer, then select Publish, we will find the new publish dialog.
In this version the first thing we need to do is to connect to our Windows Azure subscription. Click the “Sign in to download credentials” link, we will be navigated to the login page to provide the Live ID. The Windows Azure Tool will generate a certificate file and uploaded to the subscriptions those belong to us. Then we will download a PUBLISHSETTINGS file, which contains the credentials and subscriptions information.
The Visual Studio Tool will generate a certificate and deployed to the subscriptions you have as the Management Certificate. The VS Tool will use this certificate to connect to the subscription in the next step.
In the next step, I would back to the Visual Studio (the publish dialog should be stilling opened) and click the Import button, select the PUBLISHSETTINGS file I had just downloaded. Then all my subscriptions will be shown in the dropdown list.
Select a subscription that I want the application to be published and press the Next button, then we can select the hosted service, environment, build configuration and service configuration shown in the dialog. In this version we can create a new hosted service directly here rather than go back to the developer portal. Just select the <Create New …> item in the hosted service.
What we need to do is to provide the hosted service name and the location.
Once clicked the OK, after several seconds the hosted service will be established. If we went to the developer portal we will find the new hosted service in my subscription.
a) Currently we cannot select the Affinity Group when create a new hosted service through the Visual Studio Publish dialog.
b) Although we can specify the hosted service name and DNS prefixing through the developer portal, we cannot do so from the VS Tool, which means the DNS prefixing would be the same as what we specified for the hosted service name. For example, we specified our hosted service name as “Sdk16Demo”, so the public URL would be http://sdk16demo.cloudapp.net/.
After created a new hosted service we can select the cloud environment (production or staging), the build configuration (release or debug), and the service configuration (cloud or local). And we can set the Remote Desktop by check the related checkbox as well. One thing should be note is that, in this version when we set the Remote Desktop settings we don’t need to specify a certificate by default.
This is because the Visual Studio will generate a new certificate for us by default. But we can still specify an existing certificate for RDC, by clicking the “More Options” button.
Visual Studio Tool will create another certificate for the Remote Desktop connection. It will NOT use the certificate that managing the subscription.
We also can select the “Advanced Settings” page to specify the deployment label, storage account, IntelliTrace and .NET profiling information, etc..
Press Next button, the dialog will display all settings I had just specified and it will save them as a new profile. The last step is to click the Publish button.
Since we enabled the Remote Desktop feature, the first step of publishing was uploading the certificate. And then it will verify the storage account we specified and upload the package, then finally created the website in Windows Azure.
Multi-Profiles
After published, if we back to the Visual Studio we can find a AZUREPUBXML file under the Profiles folder in the Azure project. It includes all settings we specified before.
If we publish this project again, we can just use the current settings (hosted service, environment, RDC, etc.) from this profile without input them again. And this is very useful when we have more than one deployment settings. For example it would be able to have one AZUREPUBXML profile for deploying to testing environment (debug building, less roles with RDC and IntelliTrace) and one for production (release building, more roles but without IntelliTrace).
In-Place Upgrade Support
Let’s change some codes in the MVC pages and click the Publish menu from the azure project node. No need to specify any settings, here we can use the pervious settings by loading the azure profile file (AZUREPUBXML).
After clicked the Publish button the VS Tool brought a dialog to us to indicate that there’s a deployment available in the hosted service environment, and prompt to REPLACE it or not. Notice that in this version, the dialog tool said “replace” rather than “delete”, which means by default the VS Tool will use In-Place Upgrade when we deploy to a hosted service that has a deployment already exist.
After click Yes the VS Tool will upload the package and perform the In-Place Upgrade. If we back to the developer portal we can find that the status of the hosted service was turned to “Updating…”. But in the previous SDK, it will try to delete the whole deployment and publish a new one.
Summary
When the Microsoft announced the features that allows the changing VM size via In-Place Upgrade, they also mentioned that in the next few versions the user experience of publishing the azure application would be improved. The target was trying to accomplish the whole publish experience in Visual Studio, which means no need to touch developer portal any more. In the SDK 1.6 we can see from the new publish dialog, as a developer we can do the whole process, includes creating hosted service, specifying the environment, configuration, remote desktop, etc. values without going back the the developer portal.
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.
Today the Microsoft announced that the In-Place Upgrade feature had had some improvements. The major one would be, now the user could be able to change the VM Size by In-Place Upgrade, without redeploying the whole service.
What We Did Before
Before this improvement, since the VM Size was defined in the CSDEF file, we have to redeploy the service to change the VM Size property. This means we would remove the existing roles and VMs and then ask the Windows Azure to reallocate the new VMs with the new size, install the OS and runtime, extract and deploy our application.
Changing the VM Size is a very common requirement for scaling-up and down, which should not lead to the service down. But in Windows Azure, we should use redeployment which causes the service invariable at that moment.
What We Can Do Now
Let’s just have a look on what we can do to change the VM Size without redeploying the service. First of all, we need a hosted service created through the developer portal. And then, create a new windows azure project in Visual Studio. Let’s just added a ASP.NET MVC 3 Web Role, and set the VM Size to Extra Small. And then deploy this project to Windows Azure.
Let’s change the VM Size in the Visual Studio from Extra Small to Small and create a new package. After that we back to the developer portal and use In-Place Upgrade to upload the new one. In the In-Place Upgrade dialog we should check the box “Allow VM size or role count to be updated”, otherwise the upgrading will be failed.
The upgrade will take longer than the one without changing VM Size, since the Fabric Controller need to find a proper machine to host the application. This will cause more time, and more importantly, changing VM Size by In-Place Upgrade will erase all customized data on the original VM.
What I Can Do Else via the In-Place Upgrade
Not only changing the VM Size, now we can add or remove the roles, change the endpoints number and type, and increase the local storage size by using In-Place Upgrade. For example, in Visual Studio let add a new Worker Role and add a new input endpoint on the MVC 3 Web Role to 8080.
Then package and use In-Place Upgrade to upload to the hosted service. As you can see the new role and endpoint had been established.
PS: Do not forget to check the “Allow VM size or role count to be updated”.
Why I should Use In-Place Upgrade
In-Place Upgrade provides the ability to ensure our service will be keep running and available during the upgrading. Different from the redeployment, if we have more than one instance per role, it will be available during the In-Place Upgrade process, by performing the operation in each upgrade domain one by one. For example, if we have a web role with 2 small VM instances, when we changed it to small, only one instance will be changed at a time, then after it had been finished, the rest instance will be changed.
What’s Next
It’s said that in the coming next release, we can do more in Visual Studio directly rather than navigate to the developer portal. At that time the developer can finish the whole deployment task just in Visual Studio.
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.
On 13rd Oct, I had had a presentation on TechED 2011 Beijing. It was about how to build a multi-tenant application on top of Windows Azure platform. Thanks to the people attended my session, and special thanks to my friend Wang Tao, Robin Lo, Jason Lee, etc.
I had uploaded the PPT and demo here please feel free to download (The PPT was in Chinese).
Hope my session helps, and looking forward to see you all in TechED 2012.
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.
In the BUILD event, Microsoft had just announced the latest Windows Azure SDK 1.5, Visual Studio Tool for Windows Azure SDK 1.5, and the Windows Azure AppFabric SDK 1.5. You can find them simply from the Web Platform Installer.
One thing I found for now is that, the database had been changed for the local storage emulator in this version. That means you need to recreate the storage database by running the DSInit command from the folder (let’s say you installed the SDK in driver C) C:\Program Files\Windows Azure SDK\v1.5\bin\devstore.
More information about the new features of SDK 1.5 please refer to the MS announcement here.
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.
On the 9th Sep, the SQL Azure team announced that the new version of SQL Azure had just been release. In this version not only the SQL Azure engine had been upgraded, the SQL Azure Management Portal had been upgraded massively.
Below are the features and improvements available in this release:
- Foundational updates for scalability and performance.
- Co-administrator support, which enables customers to specify multiple database administrators.
- Increased capability for using spatial data types, which makes it an ideal cloud database for location-aware cloud and mobile applications.
- New SQL Azure Management Portal.
New Version of SQL Azure
The SQL Azure database engine had been upgraded to v11.0.XXXX.XX. Normally this will not impact any of our application that is using SQL Azure as the backend database. But if the application was relying on a specified version you should pay more attention.
To retrieve the current version of database engine, just use the standard T-SQL query as following.
Below is the query result on my SQL Azure database, which its version was 11.0.1467.26.
Another potential problem with this version upgrading would be the SQL Server Management Studio (SSMS). You need to patch the SSMS to SP1. The latest upgrade of SQL Server 2008 R2 SP1 can be found here:
To verify if the version of your SSMS is satisfied with the current SQL Azure, just open the About window and make sure that the version number is equals or higher than 10.50.1777.0.
SQL Azure Import/Export CTP
I introduced how to use the Data-tier Application CTP2 to backup and restore the SQL Azure database to local disk or BLOB storage in one of my previous blog post. Now this feature had been included in the Developer Portal directly.
On the Windows Azure Developer Portal (WADD) when you select a SQL Azure database, you can just click the Import or Export button from the top toolbar, to perform the Data-tier Application import and export process. It will export your database to a single DACPAC file and store in the BLOB you specified in the dialog as the following.
And similarly, click the import button to import the DACPAC file from the BLOB to a new SQL Azure database. Currently you cannot import the file to an existing SQL Azure database.
New Toolbar on Developer Portal
As you can see in the screen captures above, in this release the toolbar on the SQL Azure part of the Developer Portal had been redesigned. It’s very easy to provision, remove servers and databases, and import, export your data.
But unfortunately, there’s no context menu available in this version.
SQL Azure Management Portal
Another new big feature in this release is the SQL Azure Management Portal (SAMP). I guess it was come from the Project Houston, which I introduced months ago. But now the SAMP brings a lot of new features to us.
To open up the SAMP just select a SQL Azure server or database and click the Manage button on the toolbar. The manage button under the Server section will let user configure the entire SQL Azure server, while the button under Database section will only for a specified database.
When launch the SAMP you will be asked to provide the logon and password of your SQL Azure server, and the database you are trying to connect would be optional if clicked the Server Manage button.
The SAMP, different from the old Project Houston, need you open the firewall IP of the machine you are using to connect. This means you need to add the firewall rule of current IP to make it work. The Microsoft said that, this is for the security reason. You should explicit define the IP you are trying to use even though trough the SAMP.
In my case, after added the local IP to the firewall rule, I was still being asked to open another IP to the firewall, which was very strange.
After pressed the Log on button, if everything is OK you will see the SQL Azure Management Portal.
Currently the SAMP only works with Silverlight 4. If you had the Silverlight 5 installed you must uninstall and rollback to 4. The MS said they will work hard to make it available with SL 5 soon.
There are 3 categories on the left hand side:
- Overview: Display you SQL Azure server information and status.
- Database Life Cycle: Manage the databases under this server.
- Database Schema and Data: Design and view the schema, data in the databases under the server.
The features and functionalities are very straight forward so I don’t want to dig into every of them. As you can see the developer can design the database through the visual designer.
Create and execute a store procedure is also very easy, as the following.
Summary
This release of SQL Azure brings not only server side upgrade, but the tools as well. Even though the new SAMP are not very stable and only work with SL4, it provide a new approach to manage our SQL Azure database.
For more information about this release please refer to the official announcement from the Windows Azure blog here and here.
And MS had just released the first CTP of SQL Server Developer Tools (Codename "Juneau"), which is another tool to manage, design and deploy SQL Server and SQL Azure. You can have a look here as well.
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.
This morning I got the information that the Windows Azure Tools for Visual Studio 2010 - August 2011 Upgrade had been released. The version of this new upgrade is 1.4.40727.1601 with the Windows Azure SDK v1.4.20407.2049. This upgrade includes some awesome features that I was looking forward for a long time, which are:
- Profiling the application running in Windows Azure.
- New ASP.NET MVC 3 web role template.
- Multi-configuration files in one cloud project.
- More validation in the service package and deployment phase.
Download and Install
To install the Windows Azure Tools for VS2010 - August Upgrade you need to visit here, where using the Web Platform Installer to download and install. Currently there’s no standalone package available. The WebPI will download some dependency packages besides, such as the latest Windows Azure SDK, ASP.NET MVC 3 for Windows Azure, etc.
ASP.NET MVC 3 Web Role
After you installed the package we can find the new ASP.NET MVC 3 Web Role template available when creating a new cloud project.
In the solution explorer we can see the ASP.NET project had been created with the new Razor view engine and HTML5. And the assemblies related to MVC3 are all added and set the Copy Local = True by default.
And we can just press F5 to run the application under the local emulator.
Multiple Service Configurations
Under the cloud project we can find that there are two service configuration files (CSCFG) which are:
- ServiceConfiguration.Cloud.cscfg
- ServiceConfiguration.Local.cscfg
This is another good feature added in this upgrade. As a developer, normally we need to use different settings between development and deployment. For example, when developing in the local emulator I uses the local storage emulator, local SQL Server with 2 web role instances. But when deploying to the cloud I need to use the storage service, SQL Azure and 4 instances. This means I need to tweak the service configuration everytime deploying to the cloud. And this could be more complex if we have more environment such as development, local testing and cloud.
The Multiple Service Configurations feature can make the job simpler. With two configuration files enabled, we can choose which one will be the service configuration file when locally debugging, packaging and deploying. This means we can set different values in different file between local and cloud environment.
In the service setting windows, find the Service Configuration section, where we will choose which CSCFG file is going to be edited. And if I need to edit the Service Definition file (CSDEF) just select the All Configuration.
Let’s firstly select the All Configurations and navigate to the Settings tab to add the connection string to the storage the application is being used.
And then select the Local under the Service Configuration, we will use the storage emulator when locally developing and debugging.
Next, select Cloud and specify the storage account information when deploying to the cloud environment.
And if we opened the two CSCFG files we can find the settings had been updated in the related files.
If we need more service configuration files just select the Manage menu under the Service Configuration dropdown and create a copy from one of the existing. For example, I added a new configuration named TestServer which will be used when deploying to the local test server. And in the solution explorer a new CSCFG file had been created as well.
We can choose which configuration file should be used when debugging under the Visual Studio. Just right click the cloud project and open its property window. In Development tab we can choose which configuration should be used.
New Package and Publish Window
In the previous tool we choose to package or publish our windows azure project by selecting the radio button in the Publish window. In this upgrade the package and publish had been totally separated. In the context menu of the windows azure project we can create a package by using the Package menu, and use the Publish menu to deploy the application directly to the cloud.
In the package window we will be asked to select which service configuration should be used, which build configuration should be used and whether to enable the remote desktop.
In the publish windows, similar as what we did before, we need to select a proper management certificate, hosted service, storage service, and the service configuration, build configuration as well. We can also enable the IntelliTrace if we are using .NET 4.0 and VS2010 Ultimate, and enable the profiling feature, whcih is new in this upgrade.
More Validation
The different environment between the development machine and cloud instance introduced more work when first deployment. We need to verify which references should be set to Copy Local = True and which configuration should be changed. I also introduced a website which can help you to do the validation in this post. But now the Windows Azure Tool helps us to validation the project.
If we just publish this ASP.NET MVC3 web role to the cloud, we can see there is a warning in the error window said we are using a local database which might cause problem after deployed in the cloud.
And not only validating the database connection string, if we added some references which out of the .NET Framework package it will bring an error to indicate us to set the Copy Local = True on them.
Powered by ASP.NET Universal Providers
After deployed the ASP.NET MVC 3 application to the cloud we can open the website, but there’s an exception occurred.
This is because the ASP.NET MVC 3 application utilizes the ASP.NET Universal Providers, and it uses the default session state provider in ASP.NET Universal Providers as the ASP.NET session state provider, which means leverage the SQL Server to store the sessions.
To solve this problem, we can specify a proper SQL Azure connection string in web.config; or we can just use the InProc session provider if no need to scaling-out.
The ASP.NET Universal Providers extends the Session, Membership, Roles and Profile support to SQL Compact Edition and SQL Azure, which means as a developer, we can implement the business logic regardless which backend database we are using. For more information about this cool stuff please check the Scott’s blog post.
After fixed this problem we can see the ASP.NET MVC 3 application running in the Windows Azure.
Summary
The team is keeping improve the tools of Windows Azure Platform by listening the voice from the developers, to make it more convenient and simpler to work with Windows Azure. In this upgrade of Visual Studio Tool it brings not huge but many useful features to us, such as multi-configuration and ASP.NET MVC 3 web role. I personally strong recommended to upgrade to this version.
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.
In the next generation of SQL Server, codename “Denali”, there is a new feature named Data-tier Application Framework v2.0 Feature Pack CTP to enhance the data import and export with SQL Server, and currently it had been available in SQL Azure Labs.
Run Data-tier Application Locally
From the portal we know that the Data-tier Application CTP2 can be executed from the development machine through an EXE utility. So what we need to do is to download the components listed below. Only one thing, the SQLSysClrTypes.msi should be installed before the SharedManagementObjects.msi.
And then just download the DacImportExportCli.zip, the client side utility and proform the import export command in the local machine against the SQL Server and SQL Azure. For example we have a SQL Azure database available here:
- Server Name: uivfb7flu1.database.windows.net
- Database: aurorasys
- Login: aurora@uivfb7flu1
- Password: MyPassword
Then we can run the command like this below to export the database schema and data into a single file on local disk named “aurora.dacpac”.
1: DacImportExportCli.exe -s uivfb7flu1.database.windows.net -d aurorasys -f aurora.dacpac -x -u aurora@uivfb7flu1 -p MyPassword
And if we needed we can import it back to another SQL Azure database.
1: DacImportExportCli.exe -s uivfb7flu1.database.windows.net -d aurorasys_dabak -f aurora.dacpac -i -edition web -size 1 -u aurora@uivfb7flu1 -p MyPassword
But since the SQL Server backup and restore feature is not supported in SQL Azure currently, and many projects I was working on were willing to backup their databases on SQL Azure from the website running on Windows Azure, so in this post I would like to demonstrate how to perform the export from the cloud instead of in local command windows.
Run Data-tier Application on Cloud
From this wiki we know that the Data-tier Application can be invoked by the EXE or directly from the managed library. We can leverage the DACStore class in Microsoft.SQLServer.Management.DAC.dll to import and export from the code. So in this case we will build the web role to export the SQL Azure database via the DACStore.
The first thing we need to do is to add the references about the Data-tier Application to the cloud project. But to be aware that in Windows Azure the role instance virtual machine only has the .NET Framework installed. So we need to add all DLLs Data-tier Application needed in our project and set their Copy Local = True. Here is the list we need to add:
- Microsoft.SqlServer.ConnectionInfo.dll
- Microsoft.SqlServer.Diagnostics.STrace.dll
- Microsoft.SqlServer.Dmf.dll
- Microsoft.SqlServer.Management.DacEnum.dll
- Microsoft.SqlServer.Management.DacSerialization.dll
- Microsoft.SqlServer.Management.SmoMetadataProvider.dll
- Microsoft.SqlServer.Management.SqlParser.dll
- Microsoft.SqlServer.Management.SystemMetadataProvider.dll
- Microsoft.SqlServer.SqlClrProvider.dll
- Microsoft.SqlServer.SqlEnum.dll
If you had installed the components mentioned above you can find the assemblies in your GAC directory. You should copy them from the GAC directory and then added to the project. Regarding how to copy files from GAC please refer to this article. Otherwise you can download the source code from the link at the end of this post which has the assemblies attached.
And next we move back to the source code and create a page to let the user input the SQL Azure database information that he wants to export. In this example I created an ASP.NET MVC web role and let the user enter the information in Home/Index. After the user clicked the button we will begin to export the database. Firstly we should create the connection string from the user input, and then create an instance of ServerConnection, defined in Microsoft.SqlServer.ConnectionInfo.dll, that will be used to connect to the database when exporting.
1: // establish the connection to the sql azure
2: var csb = new SqlConnectionStringBuilder()
3: {
4: DataSource = model.Server,
5: InitialCatalog = model.Database,
6: IntegratedSecurity = false,
7: UserID = model.Login,
8: Password = model.Password,
9: Encrypt = true,
10: TrustServerCertificate = true
11: };
12: var connection = new ServerConnection()
13: {
14: ConnectionString = csb.ConnectionString,
15: StatementTimeout = int.MaxValue
16: };
And then we will create the DACStore from the ServerConnection and handle some events such as action initialized, started and finished. We will store the events into a StringBuilder and show after finished.
1: // create the dac store for exporting
2: var dac = new DacStore(connection);
3:
4: // handle events of the dac
5: dac.DacActionInitialized += (sender, e) =>
6: {
7: output.AppendFormat("{0}: {1} {2} {3}<br />", e.ActionName, e.ActionState, e.Description, e.Error);
8: };
9: dac.DacActionStarted += (sender, e) =>
10: {
11: output.AppendFormat("{0}: {1} {2} {3}<br />", e.ActionName, e.ActionState, e.Description, e.Error);
12: };
13: dac.DacActionFinished += (sender, e) =>
14: {
15: output.AppendFormat("{0}: {1} {2} {3}<br />", e.ActionName, e.ActionState, e.Description, e.ActionState == ActionState.Warning ? string.Empty : (e.Error == null ? string.Empty : e.Error.ToString()));
16: };
The next thing we are going to do is to prepare the BLOB storage. We will export the database to a local file firatly in Windows Azure virtual machine and then upload to BLOB storage for future download.
1: // prepare the blob storage
2: var account = CloudStorageAccount.FromConfigurationSetting("DataConnection");
3: var client = account.CreateCloudBlobClient();
4: var container = client.GetContainerReference("dacpacs");
5: container.CreateIfNotExist();
6: container.SetPermissions(new BlobContainerPermissions() { PublicAccess = BlobContainerPublicAccessType.Container });
7: var blobName = string.Format("{0}.dacpac", DateTime.Now.ToString("yyyyMMddHHmmss"));
8: var blob = container.GetBlobReference(blobName);
Then the final job is to invoke the Export method of the DACStore after the ServerConnection.Connect() to be invoked. The Data-tier Application library will connect to the database and perform the actions to export the schema and data to the file specified.
1: // export to local file system
2: connection.Connect();
3: var filename = Server.MapPath(string.Format("~/Content/{0}", blobName));
4: dac.Export(model.Database, filename);
And after that we will upload the file to BLOB and remove the local one.
1: // upload the local file to blob
2: blob.UploadFile(filename);
3:
4: // delete the local file
5: System.IO.File.Delete(filename);
6:
7: output.AppendFormat("Finised to export to {0}, elapsed {1} seconds.<br />", blob.Uri, sw.Elapsed.TotalMilliseconds.ToString("0.###"));
Finally we can download the DACPAC file from the BLOB storage to local machine.
Summary
The Data-tier Application enables us to be able to export the database schema and data from SQL Azure to anywhere we want, such as the BLOB storage or the local disk. This means we don’t need to pay for the backup database but only the storage usage if we put it into BLOB, and the bandwidth cost if we need to download from the BLOB. But I’m not sure if we need to pay for the bandwidth between the web role and SQL Azure if they are in the same data center (sub-region).
One more thing must be highlighted here is that, the Data-tier Application is NOT a fully backup solution. If you need the transactional consistent backup you should think about the Database Copy or Data Sync. But if you just need the schema and data this might be best choice.
PS: You can download the source code and the assemblies here.
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.
When we build an application that will be deployed on Windows Azure, one thing we should keep in mind is that the assemblies installed on the virtual machines on Windows Azure are very limited. It only contains the default assemblies in .NET 3.5 SP1 and .NET 4.0. For those references not included on Windows Azure, we should set their Copy Local = True.
As the image shown above I set the unity reference as Copy Local = True as I pretty know that in Windows Azure the assemblies of Enterprise Library will not be installed by default. But the problem is that, which references are not on Windows Azure.
I had to mark all suspicious references to copy local until I found this website, http://gacviewer.cloudapp.net.
It lists all assemblies that installed on Windows Azure machine in the default page. And additionally, it allows us to upload our project file (web role or worker role) and verifies which references should be copy local. Now it supports C# and VB.NET projects.
After I uploaded the project file the website verified the references are not present on Windows Azure which need to be set Copy Local = True. It also lists the ones were installed already.
If we forget to set the references not installed on Windows Azure to Copy Local our application will be in trouble while deployed and initialized. The role status loops between start … initializing … busy … stop … start which is very strange. To figure out what’s wrong with it we have to enable the IntelliTrace and Copy Local the reference we forgot and have another try which is very time-consuming. But if we checked our project though this website things will become much simpler.
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.
Cihan Biyikoglu had just announced the SQL Azure Federation Products Evaluation Program had just been opened for nomination. If you are interest in data partitioning, or you had been run into the problem to shard your data, or the 50GB SQL Azure database was not enough to your application, then you can raise your nomination here and hopefully can play with the cool feature earlier than others.
I had sent my nomination, god bless me.
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.