Security of Azure SQL Database

1.     Sql database firewall

a.      Allowed IPs

b.     Allow from all Azure services

2.     Only support sql server credential, not WindowsAuthentication

3.     Validate in SSL communication

a.      All communication with Azure SQL are based onSSL, but certificate validation is needed to avoid man-in-middle attack

b.     Achieved by:

 i.     To validate certificates with ADO.NETapplication code, set Encrypt=True and TrustServerCertificate=False in thedatabase connection string.

 ii.     To validate certificates via SQL ServerManagement Studio, open the Connect to Server dialog box. Click Encrypt connectionon the Connection Properties tab.

4.     Encrypt connection string in configure file

a.      Automatic conf in ASP.NET by using Pkcs12CertProtectedConfiguratoinProvider.dll(only automatic choice in Azure)

b.     Using PKCS classes:

Generate keys:

VSTool> makecert -r -pe -n "CN=AlbertkoCert4"-sky exchange "AlbertkoCert4.cer" -sv "AlbertkoCert4.pvk"

VSTool>pvk2pfx -pvk AlbertkoCert4.pvk -spc AlbertkoCert4.cer-pfx AlbertkoCer4.pfx-po passxxrd

(password is explicitly needed when pvk2pfx)

Install pubic key in client to encrypt (by mmc->File->snap-ins->certificates->Local Computer to install inStoreName.My,StoreLocation.LocalMachine (btw, use certmgr.msc to manage certificates inStoreLocation.User)) and pfx in Azure to decrypt.

All in one: http://social.technet.microsoft.com/wiki/contents/articles/2951.windows-azure-sql-database-connection-security.aspx

http://msdn.microsoft.com/en-us/library/ff394108.aspx

Securing your Azure SQL:

http://blogs.msdn.com/b/sqlazure/archive/2010/09/07/10058942.aspx

http://blogs.msdn.com/b/sqlazure/archive/2010/09/08/10059359.aspx

http://blogs.msdn.com/b/sqlazure/archive/2010/09/09/10059889.aspx

http://blogs.msdn.com/b/sqlazure/archive/2010/09/10/10060395.aspx

PKCS: http://en.wikipedia.org/wiki/PKCS

Basic knowledge of X509certificate: http://www.cnblogs.com/chnking/archive/2007/09/02/879218.html

System.Security.Cryptography.Pkcs:

http://technet.microsoft.com/zh-cn/ie/ms180945

http://technet.microsoft.com/zh-cn/ie/ms180951

http://technet.microsoft.com/zh-cn/ie/ms180950

 

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Security.Cryptography.Pkcs;

usingSystem.Security.Cryptography.X509Certificates;

 

namespace SecurityToolLib

{

    public staticclassSecurityTool

    {

        public staticX509Certificate2 LoadCertificate(StoreName storeName,StoreLocationstoreLocation,string thumbPrint)

       {

           var certStore = newX509Store(storeName, storeLocation);

           try

           {

               certStore.Open(OpenFlags.ReadOnly |OpenFlags.OpenExistingOnly);

               var certificateCollection =certStore.Certificates.Find(X509FindType.FindByThumbprint,thumbPrint,false);

               if (certificateCollection.Count == 0)

               {

                    thrownewInvalidOperationException(string.Format("Certificatewith thumbprint {0} cannot be loaded.", thumbPrint));

               }

               return certificateCollection[0];

           }

           finally

           {

               certStore.Close();

           }

       }

 

        public staticX509Certificate2 LoadCertificate(string thumbPrint)

        {

           return LoadCertificate(StoreName.My,StoreLocation.LocalMachine,thumbPrint);

       }

 

        public staticstring EncryptWithCertificate(string clearText,X509Certificate2certificate)

       {

           //ValidationHelper.CheckArgumentNull(clearText,"clearText");

           //ValidationHelper.CheckArgumentNull(certificate,"certificate");

 

           byte[] clearBytes = Encoding.UTF8.GetBytes(clearText);

           var contentInfo = new ContentInfo(clearBytes);

           var envelopedCms = new EnvelopedCms(contentInfo);

           var recipient = newCmsRecipient(certificate);

           envelopedCms.Encrypt(recipient);

           byte[] encryptedBytes =envelopedCms.Encode();

           return Convert.ToBase64String(encryptedBytes);

       }

 

        public staticstring EncryptWithCertificate(string clearText,stringthumbPrint)

       {

           return EncryptWithCertificate(clearText,LoadCertificate(thumbPrint));

       }

 

        public staticstring DecryptWithCertificate(string base64EncryptedString,X509Certificate2certificate)

       {

           //ValidationHelper.CheckArgumentNull(base64EncryptedString,"base64EncryptedString");

           //ValidationHelper.CheckArgumentNull(certificate,"certificate");

 

           byte[] encryptedBytes = Convert.FromBase64String(base64EncryptedString);

           var envelopedCms = new EnvelopedCms();

           envelopedCms.Decode(encryptedBytes);

           envelopedCms.Decrypt(new X509Certificate2Collection(certificate));

           byte[] clearBytes =envelopedCms.ContentInfo.Content;

           return Encoding.UTF8.GetString(clearBytes);

       }

 

        public staticstring DecryptWithCertificate(string base64EncryptedString,stringthumbPrint)

       {

           returnDecryptWithCertificate(base64EncryptedString, LoadCertificate(thumbPrint));

       }

    }

}