Thursday, May 27, 2010

SQLCE – Embedded database

How to use SQLCE

When we develop an application that requires small and handy data store, there are many options such as text file, ISAM, mdb, XML, etc. But I guess one of good candidates is ‘embedded database’ which is a tiny DBMS, tightly integrated with the application.

SQL CE (SQL Server Compact Edition, SSCE) is one of embedded database software, free from Microsoft, which can be used for desktop and mobile devices. SQL CE 3.5 (latest at this point) comes with Visual Studio 2008 but can be installed separately from the web. (Note: VS 2010 / .NET 4.0 doesn’t have new SQL CE 4.0, so we should use SQL CE 3.5)
*Note: SQL CE 4.0 was released on March 2011. Please refer to this link : http://www.microsoft.com/download/en/details.aspx?id=17876.

In order to use SQL CE in VS project, first we need to add reference to System.Data.SqlServerCe.dll.


















Once its reference is added, we can use all SqlCe* classes, pretty similar to other ADO.NET classes. Let’s take a quick look.

(1) SQL CE Namespace
All SQL CE classes are under System.Data.SqlServerCe namespace. In C#,

using System.Data.SqlServerCe;
(2) CE Connection String
CE can specify optional password for security purpose. The followings show without or with password connection string.

string connStr = "Data Source=Test.sdf";
string connStrWithPwd = "Data Source=Test.sdf;Password='1234'"; 
 
(3) Create a database file (.sdf)

CE database is stored in one file whose file extention is .sdf. There are several ways to create this .sdf database file. Programmatically, we can use CreateDatabase method in SqlCeEngine class as below:
SqlCeEngine eng = new SqlCeEngine(connStr);
            eng.CreateDatabase();
Using SSMS

If you have SQL Server Management Studio (SSMS), you can choose [SQL Server Compact] Server Type in Connection Dialog, and select [New Database] in Database file combo box. Then below dialog will pop up, you specific db file name, password and some others.

















Using VS

If you have VS 2008 or VS 2010, goto Server Explorer (View->Server Explorer) and right click from [Data Connections]. Select [Add Connection], and choose [Microsoft SQL Server Compact 3.5] in [Choose Data Source] dialog. The same (similar) dialog as above will show if you choose [Create] database.

(4) DDL/DML/Query - Create/Insert/Update/Delete/Select

All T-SQL statement can be executed in the same way of other ADO.NET. For example, the following example shows (1) create a table, (2) insert a row, (3) select a table.
using (SqlCeConnection conn = new SqlCeConnection(connStr))
{
    conn.Open();

   // Create a table
   string sql = "CREATE TABLE Table1 (Name nvarchar(10))";
   SqlCeCommand cmd = new SqlCeCommand(sql, conn);
   cmd.ExecuteNonQuery();

   // Insert a row
   sql = string.Format("INSERT Table1 VALUES (N'{0}')", "John");
   SqlCeCommand cmdInsert = new SqlCeCommand(sql, conn);
   cmdInsert.ExecuteNonQuery();

   // Select query
   sql = "SELECT * FROM Table1";
   SqlCeCommand cmdSelect = new SqlCeCommand(sql, conn);
   SqlCeDataReader rs = cmdSelect.ExecuteReader();
   while (rs.Read())
   {
      Console.WriteLine(rs[0].ToString());
   }
}
 
Note: In real application, it is not recommended to use TSQL concatenation or string manipulation as seen INSERT statement above because it can lead to SQL injection bug.

Many developers sometime prefer a help class (or wrapper) for underlying data processing. I added a little sample here.
using System.Diagnostics;
using System.IO;
using System.Data.SqlServerCe;

namespace NS
{
   internal class CeProcessor
   {
        private string sdfPath;
        private string pwd;

        public CeProcessor(string dbFilePath, string pwd)
        {
           this.sdfPath = dbFilePath;
           this.pwd = pwd;
        }

        public bool DatabaseExists
        {  get { return File.Exists(this.sdfPath); } }

        public void CreateDatabase()
        {
           Debug.Assert(!DatabaseExists);
           SqlCeEngine eng = new SqlCeEngine(ConnectionString);
           eng.CreateDatabase();
        }

        public void ExecuteSql(string sql)
        {
           using (SqlCeConnection conn = new SqlCeConnection(ConnectionString)) {
              conn.Open();
              SqlCeCommand cmd = new SqlCeCommand(sql, conn);
              cmd.ExecuteNonQuery();
           }
        }

        public object GetScalar(string sql)
        {
            object result = null;

            using (SqlCeConnection conn = new SqlCeConnection(ConnectionString)) {
               conn.Open();
               SqlCeCommand cmd = new SqlCeCommand(sql, conn);
               result = cmd.ExecuteScalar();
            }
            return result;
        }

        public SqlCeDataReader GetDataReader(string sql)
        {
            SqlCeDataReader rs = null;
            SqlCeConnection conn = new SqlCeConnection(ConnectionString);
            conn.Open();
            SqlCeCommand cmd = new SqlCeCommand(sql, conn);
            rs = cmd.ExecuteReader();
            return rs;
         }

         internal string ConnectionString
         {
            get
            {
                if (string.IsNullOrEmpty(pwd))
                {
                    return string.Format("Data Source=\"{0}\";", sdfPath);
                }
                else
                {
                    return string.Format("Data Source=\"{0}\";Password=\'{1}\'", sdfPath, pwd);
                }
          }
        }
   }
}

Tuesday, May 11, 2010

SCM – EnumServicesStatus

SCM – EnumServicesStatus

EnumServiceStatus enumerates NT services in the service control manager (SCM) database. By using this Win32 API, we can get all serivce list in the machine. Here is a typical example for Win32 service enumeration.
SC_HANDLE sc = OpenSCManager(NULL, NULL, SC_MANAGER_ENUMERATE_SERVICE|GENERIC_READ);
    if ( sc == NULL )
    {
        wprintf(L"Unable to open SCM...\n");
        return 0;
    }

    LPENUM_SERVICE_STATUS lpEnumService = 0;
    DWORD dwBuffSize = 0, dwBytesNeeded = 0, dwServicesReturned = 0, dwResumeHandle = 0;

    BOOL bResult = EnumServicesStatus(
        sc,
        SERVICE_WIN32,
        SERVICE_STATE_ALL,
        lpEnumService,
        dwBuffSize,
        &dwBytesNeeded,
        &dwServicesReturned,
        &dwResumeHandle
    );

    if ( bResult != FALSE )
    {
        CloseServiceHandle(sc);
        return 0;
    }

    lpEnumService = (LPENUM_SERVICE_STATUS) LocalAlloc(LPTR, dwBytesNeeded);  
    if ( lpEnumService == 0 )
    {
        CloseServiceHandle(sc);
        return 0;
    }

    bResult = EnumServicesStatus(
        sc,
        SERVICE_WIN32,
        SERVICE_STATE_ALL,
        lpEnumService,
        dwBytesNeeded,
        &dwBytesNeeded,
        &dwServicesReturned,
        &dwResumeHandle
    );

    LPENUM_SERVICE_STATUS lpCurrentEnum = lpEnumService;

    for ( DWORD i = 0; i < dwServicesReturned; i++ )
    {
        wprintf(L"%s\n", lpCurrentEnum->lpServiceName);
        ++lpCurrentEnum;      
    }

    wprintf(L"Total: %d\n", dwServicesReturned);

    LocalFree(lpEnumService);
    CloseServiceHandle(sc);
One interesting thing to mention is EnumServicesStatus() API can return all or partial service list depending on user security permission. Let’s say, user A has access permission to 20 services out of total 200 services. If the code above runs with user A account, EnumServicesStatus only returns 20 services without returning error.

How can we know whether an user has access permission for any specific service? There are many ways of doing it. One of them is checking security descriptor of the service process using kernel debugger. Another handy way is using Service Control utility (sc.exe in resource kit). For example, below SC command is to show security descriptor of default SQL instance.

C > sc sdshow MSSQLSERVER

D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)

The output is text format of security descriptor, known as SDDL (Security Descriptor Definition Language). MSDN explains all the details of this format. Basically the example is saying LocalSystem (SY), Builtin Admin (BA), Interactive User (IU), Logged as service user (SU) can access MSSQLSERVER service (please note that each user principal has different ACL but all user principals allow query). By checking security descriptor for each service, we can figure out why some services cannot be enumerated while others can.

By the same token, the following WMI query (in Powershell) will also return the all or partial service list depending on user's access token. It’s connecting to remoteServer to get Win32 service names.

gwmi -query "SELECT * FROM Win32_Service" -computer remoteServer | select Name

If it returns partial results, chances are the user might not have enough permission so you might want to use SC tool for further investigation.

Tuesday, May 4, 2010

SQL WMI Permission Issue

WMI Permission / SQL WMI

When connecting to remote SQL WMI provider, what kind of security permission should be considered? In fact, it might not be simple as you think. Because there are various layers for the WMI request to go through.

Here is the list to address permission issue:

(1) Firstly, RPC calls should be allowed between the machines. To enable RPC communication, firewall should allow RPC/DCOM port. One way of doing it is:
C> netsh firewall set service remoteadmin


(2) Secondly, since WMI is based on RPC/DCOM, you need to configure DCOM security correctly on remote target machine. Run Dcomcnfg.exe and open [My Computer] Properties page. Go to [COM security] tab and click [Edit Limits] under [Launch and Activation Permission]. Choose the user and allow [Remote Launch] and [Remote Activation]. This will enable remote DCOM calls for the target machine.




(3) Once RPC/DCOM is enabled, it’s now WMI security turn. WMI security is namespace based, which means each WMI namespace has its own security settings. In order to enable WMI security, run WinMgmt.msc on target machine.

C:\Windows\System32> winmgmt.msc

In MMC, rightclick [WMI Control] and select [Properties]. Click [Security] tab and navigate WMI namespace where you want to set permission. Click [Security]



In Security tab, choose the user and allow [Enable Account], [Remote Enable], [Read Security] and optionally [Execute Methods] (if you need to execute WMI method).

Generally the three steps above are good enough to access remote WMI providers. With those settings, you can try WMI call from source machine to remote target machine, say, by using Powershell.

gwmi -namespace "root/Microsoft/SqlServer/ComputerManagement10" -computer RemoteServer -query "select * from ServerSettingsGeneralFlag"


One of interesting issue I recently happen to know is some classes (such as SqlService) in SQL WMI provider (sqlmgmprovider.dll) might need more permission. The case is a domain user tried to remote query SqService class, but strangely the query returns nothing (0 row) even if there exist SQL Services on the machine.

gwmi -namespace "root/Microsoft/SqlServer/ComputerManagement10" -computer RemoteServer -query "select * from SqlService"

Some investigation showed that it’s related to permission issue.

0:004> u .
svrenumapi100!ServiceItemFactory::GetServicesOnMachine+0x47
00000000`49f61817 ff15dbf8fdff    call    qword ptr [svrenumapi100!_imp_OpenSCManagerW (00000000`49f410f8)]
00000000`49f6181d 4889442448      mov     qword ptr [rsp+48h],rax
00000000`49f61822 48837c244800    cmp     qword ptr [rsp+48h],0
00000000`49f61828 7507            jne     svrenumapi100!ServiceItemFactory::GetServicesOnMachine+0x61 (00000000`49f61831)
00000000`49f6182a 33c0            xor     eax,eax
00000000`49f6182c e90d030000      jmp     svrenumapi100!ServiceItemFactory::GetServicesOnMachine+0x36e (00000000`49f61b3e)
00000000`49f61831 c744245000000000 mov     dword ptr [rsp+50h],0
0:004> p
svrenumapi100!ServiceItemFactory::GetServicesOnMachine+0x4d:
00000000`49f6181d 4889442448      mov     qword ptr [rsp+48h],rax ss:00000000`017fe258=0000000049f5e8f6
0:004> !gle
LastErrorValue: (Win32) 0x5 (5) - Access is denied.
LastStatusValue: (NTSTATUS) 0xc0000100 - Indicates the specified environment variable name was not found in the specified environment block.


SQL WMI provider is using svrenumapi*.dll which tries to open Service Control Manager(SCM) to enumerate SQL instances. Since the domain user for this case did not have enough permission to access SCM of the target machine, the access was denied. This permission issue can be solved if the domain user simply becomes local admin of the remote machine (or if the user has enough permission to enumerate all services including SQL services. That may require sophisticated understanding about serivce control manager security). A little more details explained here.