Mobile Management For SQL Server(s!) - Siccolo - SQL Management Tool

Siccolo .NET Compact Framework Development Articles - NetworkCredentials - PocketPC DeviceID - PocketPC Sim Number - Personal Security Certificate
Google

Main
NetworkCredential - How to Connect to "Secured" web service - Web Service requires authentication:
 
Access web service with required network credentials - using �Integrated Windows Authentication� or �Basic Authentication�. It is very simple to integrate authentication into the mobile client :
	    Dim objNetworkCredential As System.Net.NetworkCredential = _ 
					New System.Net.NetworkCredential(NetworkUserName, _
                                        				NetworkUserPwd, _
                                                                        NetworkDomain)

            objSQLWebServiceManager = New siccolo.Siccolo()
            objSQLWebServiceManager.Credentials = objNetworkCredential
            objSQLWebServiceManager.PreAuthenticate = True

	

How to Install Personal Security Certificate in C# compact framework using PFXIsPFXBlob(), CertAddCertificateContextToStore() functions :
 
Access web service with required personal security certificate - using x509 PFX personal certificate - Install Personal Security Certificate in C# compact framework.
( all the credits go to:
Security Certificates Treatment with CryptoAPI
Utility to import PFX files into the local machine store
PKCS #12 File Types: Portable Protected Keys in .NET
How to programmatically install SSL certificates for Internet Information Server (IIS)
Example C Code for Opening Certificate Stores )
	   public static bool ImportPFXCertificate(string certificatePath, 
                                                out string errorInfo)
        {
            errorInfo = String.Empty;
            try
            {
                bool result = true;
                
                byte[] pfxData = GetFile(certificatePath);
                if (pfxData  == null || pfxData.Length == 0)
                { throw (new System.Exception("Cannot open Certificate File\n" + certificatePath) ); }

                CRYPT_DATA_BLOB pPFX = new CRYPT_DATA_BLOB();
                pPFX.cbData = pfxData.Length;
                pPFX.pbData = Marshal.AllocHGlobal(pfxData.Length);
                Marshal.Copy(pfxData, 0, pPFX.pbData, pfxData.Length);

                if(!CryptoAPI.PFXIsPFXBlob(ref pPFX))
                { throw (new System.Exception("Invalid Certificate File\n" + certificatePath)); }

                IntPtr hMemStore = IntPtr.Zero;
                IntPtr hCertCntxt = IntPtr.Zero;
                IntPtr pProvInfo = IntPtr.Zero;
                

                string password = null;
                hMemStore = CryptoAPI.PFXImportCertStore(ref pPFX, password, CRYPT_USER_KEYSET);
                password = null;
                if (hMemStore == IntPtr.Zero)
                {
                    string errorMessage = new Win32Exception(Marshal.GetLastWin32Error()).Message;
                    Marshal.FreeHGlobal(pPFX.pbData);
                    throw (new System.Exception("Failed import Certificate File\n" + errorMessage));
                }
                Marshal.FreeHGlobal(pPFX.pbData);

                //open "MY" store:
                IntPtr cert_MY_store = CryptoAPI.CertOpenStore(new IntPtr(CERT_STORE_PROV_SYSTEM_W),
                                                           0, 
                                                            IntPtr.Zero,
                                                            CERT_SYSTEM_STORE_CURRENT_USER, 
                                                            "MY");
           
                if (cert_MY_store == IntPtr.Zero)
                {
                    errorInfo = "Failed to open personal store:\n" + new Win32Exception(Marshal.GetLastWin32Error()).Message;
                    result = false;
                }
                else
                {

                    //int i = CryptoAPI.CertAddCertificateContextToStore(cert_MY_store,
                    //                                                    hCertCntxt,(int) CERT_STORE_ADD_REPLACE_EXISTING, IntPtr.Zero);

                    while ((hCertCntxt = CryptoAPI.CertEnumCertificatesInStore(hMemStore, hCertCntxt)) != IntPtr.Zero)
                    {
                        if (0==CryptoAPI.CertAddCertificateContextToStore(cert_MY_store,
                                                               hCertCntxt,
                                                               (int)CERT_STORE_ADD_REPLACE_EXISTING, IntPtr.Zero)
                            )
                        {
                            //oh-ho!
                            errorInfo = "Failed to add personal certificate:\n" + new Win32Exception(Marshal.GetLastWin32Error()).Message;
                            result = false;
                        }
                        else
                        {
                            //worked?!
                            result = true;
                        }

                    }
                }       

                if (pProvInfo != IntPtr.Zero)
                    Marshal.FreeHGlobal(pProvInfo);

                if (hCertCntxt != IntPtr.Zero)
                    CryptoAPI.CertFreeCertificateContext(hCertCntxt);

                if (hMemStore != IntPtr.Zero)
                    CryptoAPI.CertCloseStore(hMemStore, 0);

                if (cert_MY_store != IntPtr.Zero)
                    CryptoAPI.CertCloseStore(cert_MY_store, 0);
                return result;
            }

            catch (Exception ex_import_certificate)
            {
                errorInfo = ex_import_certificate.Message;
                return false;
            }
        }
	
where declarations are:
	private static byte[] GetFile(string filename)
        {
            if (!File.Exists(filename))
                return null;
            Stream stream = new FileStream(filename, FileMode.Open);
            int datalen = (int)stream.Length;
            byte[] fileBytes = new byte[datalen];
            stream.Seek(0, SeekOrigin.Begin);
            stream.Read(fileBytes, 0, datalen);
            stream.Close();
            return fileBytes;
        }

	...

	[StructLayout(LayoutKind.Sequential)]
    	public struct CRYPT_DATA_BLOB
    	{
        	public int cbData;
        	public IntPtr pbData;
    	}

	...

	[DllImport("crypt32.dll", SetLastError = true)]
        public static extern bool PFXIsPFXBlob(ref CRYPT_DATA_BLOB pPfx);

	...

	[DllImport(@"crypt32.dll", CharSet = CharSet.Auto)]
        internal static extern IntPtr CertOpenStore(
                                    IntPtr lpszStoreProvider, 
                                    int dwMsgAndCertEncodingType, 
                                    IntPtr hCryptProv, 
                                    uint dwFlags, 
                                    string pvPara);

	
	...
	
	[DllImport(@"crypt32.dll", SetLastError = true)] 
        internal static extern int CertAddCertificateContextToStore(
                                    IntPtr hCertStore, 
                                    IntPtr pCertContext, 
                                    int dwAddDisposition, 
                                    IntPtr ppStoreContext);

	...

	
	[DllImport("crypt32.dll", SetLastError = true)]
        public static extern IntPtr CertEnumCertificatesInStore(
                                    IntPtr hCertStore,
                                    IntPtr pPrevCertContext);

	...

	[DllImport("crypt32.dll", SetLastError = true)]
        public static extern bool CertCloseStore(
                                    IntPtr hCertStore,
                                    uint dwFlags);

	...

        [DllImport("crypt32.dll", SetLastError = true)]
        public static extern bool CertFreeCertificateContext(
                                    IntPtr hCertStore);

	...

	

Side Note:
PFXIsPFXBlob() attempts to decode the outer layer of a BLOB as a PFX packet
The function returns TRUE if the BLOB can be decoded as a PFX packet. If the outer layer of the BLOB cannot be decoded as a PFX packet, the function returns FALSE.

CertOpenStore() - opens a certificate store by using a specified store provider type.
In ImportPFXCertificate() function, using CERT_STORE_PROV_SYSTEM_W - Initializes the store with certificates, CRLs, and CTLs from the specified system store. The system store is a logical, collection store that consists of one or more physical stores. A physical store associated with a system store is registered with the CertRegisterPhysicalStore function. After the system store is opened, all of the physical stores that are associated with it are also opened by calls to CertOpenStore and are added to the system store collection by using the CertAddStoreToCollection function. The high word of dwFlags indicates the system store location, usually set to CERT_SYSTEM_STORE_CURRENT_USER.

CertCloseStore() - closes a certificate store handle and reduces the reference count on the store. There needs to be a corresponding call to CertCloseStore for each successful call to the CertOpenStore function.

CertAddCertificateContextToStore() - adds a certificate context to the certificate store.
In ImportPFXCertificate() function, using CERT_STORE_ADD_REPLACE_EXISTING - if a link to a matching certificate exists, that existing certificate or link is deleted and a new certificate is created and added to the store. If a matching certificate or a link to a matching certificate does not exist, a new link is added.

CertFreeCertificateContext() - frees a certificate context by decrementing its reference count. When the reference count goes to zero, CertFreeCertificateContext frees the memory occupied by a certificate context.
To free a context obtained by a get, duplicate, or create function, call the appropriate free function. To free a context obtained by a find or enumerate function, either pass it in as the previous context parameter to a subsequent invocation of the function, or call the appropriate free function. For more information, see the reference topic for the function that obtains the context.

CRYPT_INTEGER_BLOB structure - contains an arbitrary array of bytes. The structure definition includes aliases appropriate to the various functions that use it.

The certificate store is central to all certificate management operations. CertOpenStore opens an empty store in memory and calls the store provider function (if it is registered or installed) by using the object identifier (OID) that was passed in the lpszStoreProvider parameter. For a list of the predefined provider types that are supplied with the CryptoAPI, see CertOpenStore. The store provider function copies its certificates and certificate revocation lists (CRLs) to the in-memory store specified by the hCertStore handle passed to it. The new store provider function can use any of the CryptoAPI certificate store functions, such as, CertAddCertificateContextToStore or CertAddSerializedElementToStore, to add its certificates and CRLs to the in-memory store. In addition, the store-provider function optionally returns values for all of the data members of the CERT_STORE_PROV_INFO structure.


How to Check if Personal Security Certificate Exists in C# compact framework using System.Security.Cryptography.X509Certificates namespace:
(using .NET Compact Framework 3.5 w/Visual Studio 2008)
	public static bool IsCertificatePresent(string issuerToFind, out string errorInfo)
        {
            errorInfo = String.Empty;
            try
            {
                X509Store store = new X509Store(StoreName.My,
                                                System.Security.Cryptography.X509Certificates.StoreLocation.CurrentUser);

                store.Open(OpenFlags.ReadOnly);
                //for example, issuerToFind = "CN=Your Certificate Authority CA";

                //X509Certificate2Collection collectionX509 =
                //        store.Certificates.Find(X509FindType.FindBySubjectName,
                //                subject, true);
                //bool found = collectionX509.Count > 0;

                //iterate in store Certificates and check if we have ours:
                bool found = false;
                X509Certificate2Collection collectionX509 =
                        (X509Certificate2Collection)store.Certificates;
                foreach (X509Certificate2 x509 in collectionX509)
                {
                    string issuer = x509.GetIssuerName();
                    if (issuer == issuerToFind)
                    { found = true; break; }

                }
                store.Close();
                return found;
            }
            catch (Exception ex_certificate_present)
            {
                errorInfo = ex_certificate_present.Message;
                return false;
            }

        }	
	

Side Note:
X509Store() class in System.Security.Cryptography.X509Certificates namespace - Represents an X.509 store, which is a physical store where certificates are persisted and managed.
X509Store Constructor (StoreName, StoreLocation) - Initializes a new instance of the X509Store class using the specified StoreName and StoreLocation values.

X509Certificate2Collection class - represents a collection of X509Certificate2 objects.

X509Store.Certificates property - returns a collection of certificates located in an X.509 certificate store.

 
Access web service with required personal security certificate - using x509 PFX personal certificate - Install Personal Security Certificate in C# compact framework.

How to Retrieve Windows Mobile Device ID in C# Compact Framework:
	[DllImport("coredll.dll")]
        private extern static int GetDeviceUniqueID(
            [In, Out] byte[] appdata,
            int cbApplictionData,
            int dwDeviceIDVersion,
            [In, Out] byte[] deviceIDOuput,
            out uint pcbDeviceIDOutput);

	public static string DeviceID(string ApplicationName)
        {
            byte[] buffer = GetDeviceID(ApplicationName);
            StringBuilder sb = new StringBuilder();
            for (int x = 0; x < buffer.Length; x++)
            {
                //sb.Append('{');
                sb.Append(string.Format("{0:x2}", buffer[x]));
                //sb.Append("} ");
            }

            return sb.ToString();

        }

        private static byte[] GetDeviceID(string AppString)
        {
            // Call the GetDeviceUniqueID
            byte[] AppData = new byte[AppString.Length];
            for (int count = 0; count < AppString.Length; count++)
                AppData[count] = (byte)AppString[count];
            int appDataSize = AppData.Length;
            byte[] DeviceOutput = new byte[20];
            uint SizeOut = 20;
            GetDeviceUniqueID(AppData, appDataSize, 1, DeviceOutput, out SizeOut);
            return DeviceOutput;
        }

	

Side Note:
GetDeviceUniqueID() function - returns an application-specific hash of the device identifier. The application can use this hash to uniquely identify the device
GetDeviceUniqueID protects the privacy of a device. Multiple applications need to use a device's unique identifier to communicate with servers. To protect a device's privacy, multiple servers should not be able to correlate data from the same device. GetDeviceUniqueID does not use any additional information other than the data that is provided by the application, so any application that passes in the same application data buffer will obtain the same hash.


How to Retrieve Windows Mobile SIM Phone Number in C# Compact Framework:
	[DllImport("sms.dll")]
      	private static extern IntPtr SmsGetPhoneNumber(IntPtr psmsaAddress);

	    public enum AddressType 
	    {
		    /// Unknown phone number type.
		    Unknown,
		    /// International phone number.
		    International,
		    /// National phone number.
		    National,
		    /// Network-specific phone number.
		    NetworkSpecific,
		    /// Subscriber phone number.
		    Subscriber,
		    /// Alphanumeric phone number.
		    Alphanumeric,
		    /// Abbreviated phone number.
		    Abbreviated
	    }

	    public struct PhoneAddress
	    {
		    /// The address type.
		    public AddressType AddressType;
		    /// The phone number in string format.
		    public String Address;
	    }

        unsafe public static PhoneAddress SIMPhoneNumber()
        {
		    PhoneAddress phoneAddress = new PhoneAddress();

			Byte[] buffer = new Byte[516];
			fixed (byte* pAddr = buffer) 
			{
				IntPtr res = SmsGetPhoneNumber((IntPtr)pAddr);
				if (res != IntPtr.Zero)
					throw new Exception("Could not get phone number from SIM");

				byte *pCurrent = pAddr;
                phoneAddress.AddressType = (AddressType)Marshal.ReadInt32((IntPtr)pCurrent);
                pCurrent += Marshal.SizeOf(phoneAddress.AddressType);
                phoneAddress.Address = Marshal.PtrToStringUni((IntPtr)pCurrent);
			}

            return phoneAddress;

        }

	

Side Note:
SmsGetPhoneNumber() function - get the phone number of the device associated with the Short Message Service (SMS) bearer.


How to Retrieve Windows Mobile Application Directory in C# Compact Framework:
	public string ApplicationFolder
        {
            get
            { return GetApplicationDirectory(); }
        }
        private string GetApplicationDirectory()
        {
            return Path.GetDirectoryName(
                            System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase
                            ).Replace(@"file:\", "");
        }
	

How to Retrieve and Load XML Settings in Windows Mobile Application in C# Compact Framework:
	public bool LoadConfigSettings(out string errorInfo)
        {
            errorInfo = String.Empty;
            try
            {
                string appConfigFile = Path.Combine(GetApplicationDirectory(), ".config");
                if (!File.Exists(appConfigFile))
                { throw (new System.Exception("Config File\n" + appConfigFile + "\nnot found")); }

                DataSet appSettings = new DataSet("settings");
                appSettings.ReadXml(appConfigFile, XmlReadMode.Auto);

                if (appSettings.Tables.Count != 0)
                {
                    //load!
                    DataRow dr = appSettings.Tables[0].Rows[0];
                    //get Web service URL
                    m_WebServiceURL = dr["webserviceURL"].ToString();
                    //log error? 1/0:
                    m_LogError = (dr["logerror"].ToString() != String.Empty);

                    return true;
                }
                else
                { throw (new System.Exception("Invalid Config File\n" + appConfigFile )); }

            }
            catch ( Exception ex_load_config )
            {
                //throw (new System.Exception(ex_load_config.ToString()));
                errorInfo = ex_load_config.Message;
                return false;
                
            }
        }
	

How to Keep Log File in Windows Mobile Application in C# Compact Framework:
	public void SaveToLog(string Message)
        {
            string LogFile = GetApplicationDirectory();
            LogFile = Path.Combine(LogFile, "log_" + System.DateTime.Now.ToString("MM_dd_yyyy") + ".txt");

            try
            {
                StreamWriter fileStream;
                if (File.Exists(LogFile))
                { fileStream = File.AppendText(LogFile); }
                else
                { fileStream = File.CreateText(LogFile); }

                Message = System.DateTime.Now.ToString("MM/dd/yyyy") + " " +
                            System.DateTime.Now.ToString("HH:mm:ss") + " - " +
                            Message;

                fileStream.WriteLine(Message);
                fileStream.Close();

            }

            catch (Exception ex_save_to_log)
            {
                //
                throw (new System.Exception("\n" + LogFile + "\n" + ex_save_to_log.ToString()));
            }
        }

	

How to Retrieve Sorted File List in Windows Mobile Application in C# Compact Framework:
	
	...
	string ParentPath = "\\Program Files";
	DirectoryInfo ParentFolder = new DirectoryInfo(ParentPath);

	//get files:
	FileInfo[] FileList = ParentFolder.GetFiles();
	//for sorting:
	FileInfoComparer FileInfoComparer = new FileInfoComparer();
	//sort
	Array.Sort(FileList, FileInfoComparer);
	...
	

where FileInfoComparer:
	class FileInfoComparer : System.Collections.IComparer
    	{
        	int IComparer.Compare(Object objFileInfo1, Object objFileInfo2)
	        {
        	    System.IO.FileInfo FileInfo1 = (System.IO.FileInfo)objFileInfo1;
	            System.IO.FileInfo FileInfo2 = (System.IO.FileInfo)objFileInfo2;

	            return String.Compare(FileInfo1.Name, FileInfo2.Name);
        	}
	}
	

Article keywords: .NET Compact FrameWork Software Development, NetworkCredentials, PocketPC DeviceID, PocketPC Sim Number, Personal Security Certificate, GetDeviceUniqueID(), CertAddCertificateContextToStore(), CertOpenStore(), X509Store(), SmsGetPhoneNumber(), X509Certificate2Collection, X509Store.Certificates, DirectoryInfo, GetFiles(), FileInfoComparer(), XML, Dataset(), DataSet.ReadXml(), DataRow, GetApplicationDirectory(), Path.Combine(), File.Exists(), SmsGetPhoneNumber(), PhoneAddress, NetworkCredential, System.Net, Basic Authentication, Integrated Windows Authentication, System.Net.CredentialCache.DefaultNetworkCredentials, System.Collections.IComparer, IComparer



Free Mobile Management For SQL Server(s!) - Siccolo - SQL Management ToolQuestions? Suggestions? Concerns? - email me to [email protected]    Greg Dubinovsky � 2006
or share your thoughts at Siccolo Blog
Siccolo - SQL Server Management Tool For Mobile Devices is packed with built-in functionality and tools. Siccolo delivers a rich set of management tools for both DBAs and sys admins. SQL Server management has always been an area of DBA concern. The new Management Tool For Mobile Devices - Siccolo - has simple "Enterprise Manager" and the "Query Analyzer". Siccolo is a management tool for the MS SQL Server with administration capabilities and a database query tool. The administration features provide users the ability to browse database structures. An integrated query tool allows users to quickly create, edit and execute SQL queries and scripts. Siccolo also provides an export tool to allow users to easily save and email execution results. Siccolo helps database professionals save time and increase their productivity by utilizing a more efficient approach to database management - use their Windows Mobile empowered device while sipping margarita on the beach For increased security, Siccolo is configured to run under SSL with IIS authentication.
    Siccolo features are:
  • Run SQL Query window (with semi-automated Insert/Update/Delete/Select Statements) Open/Save SQL Scripts in multiple tabs Save/Email results
  • Explore SQL Server Browse databases and database objects (tables/views/stored procedures) Browse/Manage SQL Server jobs Browse/Manage windows services (start/stop) View event logs (Application, System)
  • Manage SQL Server - restart MS SQL Server service and SQL Agent



So, as we can see Web Services is quickly finding its way into major system integration development efforts. In back office and legacy systems alike, Web Servicess has become a main focus in providing mechanisms to make data move more freely between and across long-closed boundaries. An entire industry has joined forces and pushes forward in overcoming both technical hurdles such as security and market specific hurdles such as the need of standardized message formats. While these efforts continue, an entirely different market is rapidly maturing: the mobile devices, software and services market. Very interesting solution options arise in the cross section, where Web Services meets mobility. The Microsoft� Windows� .NET Compact Framework opens up the world of Web Services to Pocket PCs, and the wireless nature of Pocket PCs opens up the world of mobility to Web Services. This means that new fuel is added to the expansion of the XML-based Web Services provider market.