A way to run nearly all functions of dotNet from SQL Server

Last night, I got a case to write a function in SQL Server 2005 to support the user account and password check against Active Directory. The user requirement is quite clear:

  • Create a scalar-valued function named LDAPUserCheck;
  • Parameter @username nvarchar(MAX) for user name to check;
  • Parameter @password nvarchar(MAX) for password matching the username specified;
  • Return bit 1 if succeeded, or 0 for all other reasons.

After a digging, I found that LDAP password authentication is not supported directly by SQL Server. But SQLCLR is a way to build the native dotnet program into SQL Server. In a new created SQLCLR project in VS 2005, I realized it’s unable to add the reference System.DirectoryServices.AccountManagement, which is required by running the code for Active Directory authentication. But a Web Service is a choice.

My steps to achieve that:

1 Create and deploy a Web Service for the authentication check.

1.1 Create a Web Service project.

1.2 Add System.DirectoryServices.AccountManagement as a reference.

1.3 Add a setting DomainName as string for storing the domain name.

1.4 Add a service like this:

    public class LDAPAuthentication : System.Web.Services.WebService
    {
        static string domainName = Settings.Default.DomainName;

        [WebMethod]
        public bool Check(string userName, string password)
        {
            using (PrincipalContext pc = new PrincipalContext(ContextType.Domain, domainName))
            {
                // validate the credentials
                bool isValid = pc.ValidateCredentials(userName, password);
                return isValid;
            }
        }
    }

1.5 Deploy this service and use a application pool running by a domain user. This user should be added to IIS_WPG group in Windows Server 2003.

2 Create a SQLCLR project to call the Web Service.

2.1 Create a SQLCLR project in Visual Studio 2005.

2.2 Add a Web Service reference. It’s named as LDAP in my project.

2.3 Add a User Defined Functions.

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean LDAPUserCheck(
        SqlString username, SqlString password)
    {
        using (LDAPAuthentication.LDAP.LDAPAuthentication service = new LDAPAuthentication.LDAP.LDAPAuthentication())
        {
            if (service.Check(username.ToString(), password.ToString()))
            {
                return SqlBoolean.True;
            }
            else
            {
                return SqlBoolean.False;
            }
        }
    }

2.4 Set Permission Level to External in Database page of project setting.

2.5 Build this project to get the dll files. In my case, these files are named LDAPAuthentication.dll and LDAPAuthentication.XmlSerializers.dll.

3 Deploy this project into SQL Server 2005.

3.1 Enable the CLR support in SQL Server 2005 by running this code:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

3.2 Create a database for storing this function. Or, you can use any existed database as well. In my case, I created a database “tester”.

3.3 Set trustworthy on this database by running:

ALTER DATABASE [tester] SET trustworthy ON

3.4 Copy the 2 dll files created in step 2 to this server. In my case, these are stored in C:\SQLCLR folder.

3.5 Create assemblies in SQL Server by running:

create assembly [LDAPAuthentication] from 'C:\SQLCLR\LDAPAuthentication.dll' with permission_set = external_access
create assembly [LDAPAuthentication.XmlSerializers] from 'C:\SQLCLR\LDAPAuthentication.XmlSerializers.dll' with permission_set = external_access

3.6 Create function to run the method we’ve created in VS 2005:

CREATE FUNCTION [dbo].[LDAPUserCheck](@username [nvarchar](4000), @password [nvarchar](4000))
RETURNS [bit] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [LDAPAuthentication].[UserDefinedFunctions].[LDAPUserCheck]

Now everything is done. You can call this function like all others created by SQL. Run this for test.

select dbo.LDAPUserCheck('myusername','mypassword')

 

Solved: Why Linq Where.FirstOrDefault is faster than use FirstOrDefault with condition on an array?

Hi all.

 

Why applying “.Where(condition).FirstOrDefault()” is much faster than “.FirstOrDefault(condition) when it’s applied on an array?

 

Answer: While calling method Where() on an Array or a List, it’s optimized to use the enumerator of the source directly. But FirstOrDefault(), which doesn’t have this mechanism, runs the virtual method version from IEnumerator.GetEnumerator().

Special thanks to Iris Sakura.

C#: Convert a tree node and sub nodes to text

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace TreeToText
{
    class TreeToText
    {
        const string LineMid = "├─";
        const string LineLast = "└─";
        const string Line = "│  ";
        const string Space = "    ";

        /// <summary>
        /// Convert a tree node to text
        /// </summary>
        /// <param name="node">Root node to convert</param>
        /// <returns>Text</returns>
        public static string Tree2Text(TreeNode node)
        {
            if (node == null) return null;
            StringBuilder builder = new StringBuilder();
            builder.AppendLine(TreeNodeText(node));
            Tree2Text(builder, node, "");
            return builder.ToString();
        }

        static string TreeNodeText(TreeNode node)
        {
            return node.Text;
        }

        static void Tree2Text(StringBuilder builder, TreeNode parent, string prefix)
        {
            int nodesCount = parent.Nodes.Count;
            if (nodesCount == 0) return;
            int nodeMaxIndex = nodesCount - 1;
            for (int i = 0; i < nodesCount; i++)
            {
                builder.Append(prefix);
                TreeNode node = parent.Nodes[i];
                if (i != nodeMaxIndex)
                {
                    builder.Append(LineMid);
                    builder.AppendLine(TreeNodeText(node));
                    Tree2Text(builder, node, prefix + Line);
                }
                else
                {
                    builder.Append(LineLast);
                    builder.AppendLine(TreeNodeText(node));
                    Tree2Text(builder, node, prefix + Space);
                }
            }
        }
    }
}

中国IP地址段抽取工具

本工具可以将所有中国的IP v4地址段抽取出来,并按照用户给定的格式保存。
通常可以用于制作特定的路由表。

IP信息来源:每次运行时自动获取自APNIC。
运行需要:dotnet Framework 4.0

运行前,请用文本编辑器打开CNRouteExtractor.exe.config,按照注释修改其中的Format字符串。
运行时的格式:CNRouteExtractor filename
将生成filename作为目标输出文件。如不指定filename则不输出(仅测试下载与抽取)。

下载地址

C#: Split a Distinguished Name of Active Directory into array of string

class DistinguishedNameSplit
{
    static string[] hexCodes = new string[] { "0""1""2""3""4""5""6""7""8""9""a""A""b""B""c""C""d""D""e""E""f""F" };
 
    public static List<string> Split(string distinguishedName)
    {
        List<string> pool = new List<string>();
        StringBuilder working = new StringBuilder();
        string hexHigh = "";
        MachineStatus status = MachineStatus.A;
        int index = 0;
        int indexMax = distinguishedName.Length - 1;
 
        while (index <= indexMax)
        {
            string value = distinguishedName.Substring(index, 1);
            switch (status)
            {
                case MachineStatus.A:
                    if (value == ",")
                    {
                        pool.Add(working.ToString());
                        working.Clear();
                    }
                    else if (value == "\"")
                    {
                        working.Append("\"");
                        status = MachineStatus.B;
                    }
                    else if (value == "\\")
                    {
                        status = MachineStatus.C;
                    }
                    else
                    {
                        working.Append(value);
                    }
                    break;
                case MachineStatus.B:
                    if (value == "\"")
                    {
                        working.Append("\"");
                        status = MachineStatus.A;
                    }
                    else if (value == "\\")
                    {
                        status = MachineStatus.E;
                    }
                    else
                    {
                        working.Append(value);
                    }
                    break;
                case MachineStatus.C:
                    if (value == "," || value == "\"" || value == "\\")
                    {
                        working.Append(value);
                        status = MachineStatus.A;
                    }
                    else if (hexCodes.Contains(value))
                    {
                        hexHigh = value;
                        status = MachineStatus.D;
                    }
                    else
                    {
                        throw new ArgumentException("The distinguished name specified is not typed legally.");
                    }
                    break;
                case MachineStatus.D:
                    if (hexCodes.Contains(value))
                    {
                        working.Append((char)Convert.ToByte(String.Format("{0}{1}", hexHigh, value), 16));
                        status = MachineStatus.A;
                    }
                    else
                    {
                        throw new ArgumentException("The distinguished name specified is not typed legally.");
                    }
                    break;
                case MachineStatus.E:
                    if (value == "," || value == "\"" || value == "\\")
                    {
                        working.Append(value);
                        status = MachineStatus.B;
                    }
                    else if (hexCodes.Contains(value))
                    {
                        hexHigh = value;
                        status = MachineStatus.F;
                    }
                    else
                    {
                        throw new ArgumentException("The distinguished name specified is not typed legally.");
                    }
                    break;
                case MachineStatus.F:
                    if (hexCodes.Contains(value))
                    {
                        working.Append((char)Convert.ToByte(String.Format("{0}{1}", hexHigh, value), 16));
                        status = MachineStatus.B;
                    }
                    else
                    {
                        throw new ArgumentException("The distinguished name specified is not typed legally.");
                    }
                    break;
            }
            index++;
        }
 
        if (status == MachineStatus.A)
        {
            pool.Add(working.ToString());
            return pool;
        }
        else
        {
            throw new ArgumentException("The distinguished name specified is not ended correctly.");
        }
    }
 
    enum MachineStatus
    {
        A, B, C, D, E, F
    }
}